[函式庫] Microsoft.Office.Interop.Excel

在 C# 中如何讀取 Excel 檔呢，使用 Microsoft.Office.Interop.Excel 套件就可以拉

using Excel = Microsoft.Office.Interop.Excel;

• 在引用前須先去 NuGet 套件管理下載

開啟 Excel

ExcelApp = new Excel.Application();
object objOpt = System.Reflection.Missing.Value;
Excel.WorkbookClass wbclass = (Excel.WorkbookClass)ExcelApp.Workbooks.Open(filepath, objOpt, false, objOpt, objOpt, objOpt, true, objOpt, objOpt, true, objOpt, objOpt, objOpt, objOpt, objOpt);

讀取 Excel

Excel.Sheets sheets = wbclass.Worksheets;
foreach (Excel.Worksheet sheet in sheets)
{
    //獲取工作表名稱
    string SheetName = sheet.Name;

    //獲取工作表上所使用過的範圍
    int RowsCount = sheet.UsedRange.Rows.Count;
    int ColsCount = sheet.UsedRange.Columns.Count;
    
    //獲取所有資料的 Range
    Excel.Range TopLeft = (Excel.Range)sheet.Cells[1, 1];
    Excel.Range BottomRight = (Excel.Range)sheet.Cells[RowsCount, ColsCount];
    Excel.Range range = (Excel.Range)sheet.get_Range(TopLeft, BottomRight);
    Array array = (Array)range.Cells.Value2;

    //印出所有資料
    for (int i = 1;i < RowsCount;i++)
    {
        for (int j = 1;j < RowsCount;j++)
        {
            Console.Write(array[i][j]);
        }
    }
}

正確關閉 Excel

• 若無正確關閉會吃一堆記憶體跟 CPU

if (sheets != null)
{
    Marshal.FinalReleaseComObject(sheets);
}
if (wbclass != null)
{
    wbclass.Close(false); //忽略尚未存檔內容，避免跳出提示卡住
    Marshal.FinalReleaseComObject(wbclass);
}
if (ExcelApp != null)
{
    ExcelApp.Workbooks.Close();
    ExcelApp.Quit();
    Marshal.FinalReleaseComObject(ExcelApp);
}

tags: 函式庫 C# Excel Microsoft.Office.Interop.Excel