C# で Excel を読み取る 3 つの方法と比較分析

(1) OleDB 方式

利点: Excel を直接データ ソースとして扱い、SQL を通じてコン​​テンツを直接読み取り、読み取り速度が速くなります。

短所: データの読み取り方法は柔軟性が低く、特定のセルを直接読み取ることができません。シート ページ全体を読み取った後でのみ (結果はデー​​タテーブルになります)、行数とデータテーブルに基づいて指定された値を取得できます。列。

Excelデータの量が多い場合。大量のメモリを占有するため、メモリが不足するとメモリ オーバーフロー例外がスローされます。

読み取りコードは次のとおりです:

public DataTable GetExcelTableByOleDB(string strExcelPath, string tableName)
{
  try
  {
    DataTable dtExcel = new DataTable();
    //数据表
    DataSet ds = new DataSet();
    //获取文件扩展名
    string strExtension = System.IO.Path.GetExtension(strExcelPath);
    string strFileName = System.IO.Path.GetFileName(strExcelPath);
    //Excel的连接
    OleDbConnection objConn = null;
    switch (strExtension)
    {
      case ".xls":
        objConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1;\"");
        break;
      case ".xlsx":
        objConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";" + "Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1;\"");
        break;
      default:
        objConn = null;
        break;
    }
    if (objConn == null)
    {
      return null;
    }
    objConn.Open();
    //获取Excel中所有Sheet表的信息
    //System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
    //获取Excel的第一个Sheet表名
    //string tableName = schemaTable.Rows[0][2].ToString().Trim();
    string strSql = "select * from [" + tableName + "]";
    //获取Excel指定Sheet表中的信息
    OleDbCommand objCmd = new OleDbCommand(strSql, objConn);
    OleDbDataAdapter myData = new OleDbDataAdapter(strSql, objConn);
    myData.Fill(ds, tableName);//填充数据
    objConn.Close();
    //dtExcel即为excel文件中指定表中存储的信息
    dtExcel = ds.Tables[tableName];
    return dtExcel;
  }
  catch
  {
    return null;
  }
}

以下は、接続文字列

HDR=Yes について説明します。これは、最初の行がタイトルであり、データとして使用されないことを意味します (ただし、私の実際の使用では、最初の行に複雑な値がある場合、読み取られたデータテーブルの列タイトルは自動的に F1、F2 などに設定されますが、これは実際のアプリケーションと矛盾します。したがって、すべての内容がデータテーブルに読み込まれます。 HDR=いいえ、最初の列がデータテーブルに手動で追加されました。 IMEX (IMport EXport モード) 設定
IMEX には 3 つのモードがあります:
0 はエクスポート モード
1 はインポート モード
2はリンク モード (完全な更新機能) です。
ここで説明したいのは、IMEX パラメーターです。異なるモードは異なる読み取り動作と書き込み動作を表すためです。
IMEX=0 の場合、このモードで開かれた Excel ファイルは「エクスポート モード」になります。 「書き込み」の目的にのみ使用してください。
IMEX=1の場合は「インポートモード」です。このモードで開いたExcelファイルは「読み込み」のみに使用できます。
IMEX=2の場合は「リンクモード」となります。このモードで開いたExcelファイルは「読み取り」と「書き込み」の両方の目的に対応できます。

---------------------------------

また、Excel2007 バージョンのファイルを読み取ると、バージョンは 8.0 から 12.0 に変更されます。同時に、ドライバーは Jet を使用できなくなり、ACE を使用する必要があります。 「インストール可能な ISAM が見つかりません」エラーの原因。

---------------------------------

ネットで調べたら、シートの取り出しもあることを知りました。この方法では、実際の Excel テーブルにあるシート テーブルの数より多くのテーブルが存在します:

1. 抽出された名前には、XL ネーミング マネージャーの名前が含まれています (XL2007 - ネーミング マネージャーのショートカット キーを参照)。 Crtl+F3);

2. 抽出された名前には、フィルター範囲を記録するために XL によって使用される FilterDatabase サフィックスが含まれます。

最初のポイントは比較的単純で、既存の名前付けマネージャーのコンテンツを削除するだけです。2 番目のポイントは、フィルターが削除された後も保持されるので、より面倒です。簡単な方法は、新しいシートを追加することです。そして元のシートをコピーして入力します。しかし、実際にはすべての Excel に対して上記のチェックを行うことはできません。フィルタリングスキームを以下に示します。 (この問題は確認済みですので、ご自身でご確認ください)

//objConn为读取Excel的链接，下面通过过滤来获取有效的Sheet页名称集合
 System.Data.DataTable schemaTable = objConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
 List<string> lstSheetNames = new List<string>();
 for (int i = 0; i < schemaTable.Rows.Count; i++)
 {
   string strSheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
   if (strSheetName.Contains("$") && !strSheetName.Replace("&#39;", "").EndsWith("$"))
   {
     //过滤无效SheetName完毕....
     continue;
   }
   if (lstSheetNames != null && !lstSheetNames.Contains(strSheetName))
     lstSheetNames.Add(strSheetName);
 }

読み出された SheetName が無効であり、一般に最後の文字が $ にならないためです。 SheetName に特殊な記号が含まれている場合、読み取られた SheetName は一重引用符で自動的に追加されます。たとえば、Excel で SheetName を MySheet(1) に編集すると、読み取られる SheetName は 'MySheet(1)$' となるため、最後の文字が $ であるかどうかを判断する前に一重引用符をフィルタリングすることをお勧めします。

---------------------------------

(2) Com コンポーネントの方法 (Microsoft.Office を追加することによって) Interop.Excel リファレンス実装)

利点: Excel のデータを非常に柔軟に読み取ることができ、ユーザーは処理のためにさまざまな関数を柔軟に呼び出すことができます。

欠点: セルベースの処理は読み取り速度が遅いので、この方法で大量のデータを含むファイルを読み込まないことをお勧めします。

Web サイトが IIS に展開されている場合は、対応する DLL 参照を追加する必要があります。場合によっては、IIS のアクセス許可も設定する必要があります。 。

読み取りコードは次のとおりです:

private Stopwatch wath = new Stopwatch();
/// <summary>
/// 使用COM读取Excel
/// </summary>
/// <param name="excelFilePath">路径</param>
/// <returns>DataTabel</returns>
public System.Data.DataTable GetExcelData(string excelFilePath)
{
  Excel.Application app = new Excel.Application();
  Excel.Sheets sheets;
  Excel.Workbook workbook = null;
  object oMissiong = System.Reflection.Missing.Value;
  System.Data.DataTable dt = new System.Data.DataTable();
  wath.Start();
  try
  {
    if (app == null)
    {
      return null;
    }
    workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
      oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
    //将数据读入到DataTable中——Start
    sheets = workbook.Worksheets;
    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
    if (worksheet == null)
      return null;
    string cellContent;
    int iRowCount = worksheet.UsedRange.Rows.Count;
    int iColCount = worksheet.UsedRange.Columns.Count;
    Excel.Range range;
    //负责列头Start
    DataColumn dc;
    int ColumnID = 1;
    range = (Excel.Range)worksheet.Cells[1, 1];
    while (range.Text.ToString().Trim() != "")
    {
      dc = new DataColumn();
      dc.DataType = System.Type.GetType("System.String");
      dc.ColumnName = range.Text.ToString().Trim();
      dt.Columns.Add(dc);

      range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
    }
    //End
    for (int iRow = 2; iRow <= iRowCount; iRow++)
    {
      DataRow dr = dt.NewRow();
      for (int iCol = 1; iCol <= iColCount; iCol++)
      {
        range = (Excel.Range)worksheet.Cells[iRow, iCol];
        cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
          dr[iCol - 1] = cellContent;
      }
      dt.Rows.Add(dr);
    }
    wath.Stop();
    TimeSpan ts = wath.Elapsed;
    //将数据读入到DataTable中——End
    return dt;
  }
  catch
  {
    return null;
  }
  finally
  {
    workbook.Close(false, oMissiong, oMissiong);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
    workbook = null;
    app.Workbooks.Close();
    app.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
    app = null;
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}
/// <summary>
/// 使用COM，多线程读取Excel（1 主线程、4 副线程）
/// </summary>
/// <param name="excelFilePath">路径</param>
/// <returns>DataTabel</returns>
public System.Data.DataTable ThreadReadExcel(string excelFilePath)
{
  Excel.Application app = new Excel.Application();
  Excel.Sheets sheets = null;
  Excel.Workbook workbook = null;
  object oMissiong = System.Reflection.Missing.Value;
  System.Data.DataTable dt = new System.Data.DataTable();
  wath.Start();
  try
  {
    if (app == null)
    {
      return null;
    }
    workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
      oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
    //将数据读入到DataTable中——Start
    sheets = workbook.Worksheets;
    Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(1);//读取第一张表
    if (worksheet == null)
      return null;
    string cellContent;
    int iRowCount = worksheet.UsedRange.Rows.Count;
    int iColCount = worksheet.UsedRange.Columns.Count;
    Excel.Range range;
    //负责列头Start
    DataColumn dc;
    int ColumnID = 1;
    range = (Excel.Range)worksheet.Cells[1, 1];
    while (iColCount >= ColumnID)
    {
      dc = new DataColumn();
      dc.DataType = System.Type.GetType("System.String");
      string strNewColumnName = range.Text.ToString().Trim();
      if (strNewColumnName.Length == 0) strNewColumnName = "_1";
      //判断列名是否重复
      for (int i = 1; i < ColumnID; i++)
      {
        if (dt.Columns[i - 1].ColumnName == strNewColumnName)
          strNewColumnName = strNewColumnName + "_1";
      }
      dc.ColumnName = strNewColumnName;
      dt.Columns.Add(dc);
      range = (Excel.Range)worksheet.Cells[1, ++ColumnID];
    }
    //End
    //数据大于500条，使用多进程进行读取数据
    if (iRowCount - 1 > 500)
    {
      //开始多线程读取数据
      //新建线程
      int b2 = (iRowCount - 1) / 10;
      DataTable dt1 = new DataTable("dt1");
      dt1 = dt.Clone();
      SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);
      Thread othread1 = new Thread(new ThreadStart(sheet1thread.SheetToDataTable));
      othread1.Start();
      //阻塞 1 毫秒，保证第一个读取 dt1
      Thread.Sleep(1);
      DataTable dt2 = new DataTable("dt2");
      dt2 = dt.Clone();
      SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);
      Thread othread2 = new Thread(new ThreadStart(sheet2thread.SheetToDataTable));
      othread2.Start();
      DataTable dt3 = new DataTable("dt3");
      dt3 = dt.Clone();
      SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);
      Thread othread3 = new Thread(new ThreadStart(sheet3thread.SheetToDataTable));
      othread3.Start();
      DataTable dt4 = new DataTable("dt4");
      dt4 = dt.Clone();
      SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);
      Thread othread4 = new Thread(new ThreadStart(sheet4thread.SheetToDataTable));
      othread4.Start();
      //主线程读取剩余数据
      for (int iRow = b2 * 4 + 2; iRow <= iRowCount; iRow++)
      {
        DataRow dr = dt.NewRow();
        for (int iCol = 1; iCol <= iColCount; iCol++)
        {
          range = (Excel.Range)worksheet.Cells[iRow, iCol];
          cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
          dr[iCol - 1] = cellContent;
        }
        dt.Rows.Add(dr);
      }
      othread1.Join();
      othread2.Join();
      othread3.Join();
      othread4.Join();
      //将多个线程读取出来的数据追加至 dt1 后面
      foreach (DataRow dr in dt.Rows)
        dt1.Rows.Add(dr.ItemArray);
      dt.Clear();
      dt.Dispose();
      foreach (DataRow dr in dt2.Rows)
        dt1.Rows.Add(dr.ItemArray);
      dt2.Clear();
      dt2.Dispose();
      foreach (DataRow dr in dt3.Rows)
        dt1.Rows.Add(dr.ItemArray);
      dt3.Clear();
      dt3.Dispose();
      foreach (DataRow dr in dt4.Rows)
        dt1.Rows.Add(dr.ItemArray);
      dt4.Clear();
      dt4.Dispose();
      return dt1;
    }
    else
    {
      for (int iRow = 2; iRow <= iRowCount; iRow++)
      {
        DataRow dr = dt.NewRow();
        for (int iCol = 1; iCol <= iColCount; iCol++)
        {
          range = (Excel.Range)worksheet.Cells[iRow, iCol];
          cellContent = (range.Value2 == null) ? "" : range.Text.ToString();
          dr[iCol - 1] = cellContent;
        }
        dt.Rows.Add(dr);
      }
    }
    wath.Stop();
    TimeSpan ts = wath.Elapsed;
    //将数据读入到DataTable中——End
    return dt;
  }
  catch
  {
    return null;
  }
  finally
  {
    workbook.Close(false, oMissiong, oMissiong);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
    System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets);
    workbook = null;
    app.Workbooks.Close();
    app.Quit();
    System.Runtime.InteropServices.Marshal.ReleaseComObject(app);
    app = null;
    GC.Collect();
    GC.WaitForPendingFinalizers();
  }
}

(3) Excelを読み取るNPOIメソッド(このメソッドはテストされていません)

NPOIはPOIプロジェクトの.NETバージョンです。 POI は、Excel や WORD などの Microsoft OLE2 コンポーネント ドキュメントを読み書きするためのオープン ソース Java プロジェクトです。 NPOI を使用すると、Office がインストールされていないマシンや対応する環境でも、WORD/EXCEL ドキュメントの読み取りと書き込みが可能になります。

利点: Excel の読み取りが速く、読み取り方法が柔軟です。

欠点: 対応するプラグインをダウンロードしてシステム参照に追加する必要があります。

/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
  ISheet sheet = null;
  DataTable data = new DataTable();
  int startRow = 0;
  try
  {
    fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
    if (fileName.IndexOf(".xlsx") > 0) // 2007版本
      workbook = new XSSFWorkbook(fs);
    else if (fileName.IndexOf(".xls") > 0) // 2003版本
      workbook = new HSSFWorkbook(fs);
    if (sheetName != null)
    {
      sheet = workbook.GetSheet(sheetName);
    }
    else
    {
      sheet = workbook.GetSheetAt(0);
    }
    if (sheet != null)
    {
      IRow firstRow = sheet.GetRow(0);
      int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
      if (isFirstRowColumn)
      {
        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
        {
          DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
          data.Columns.Add(column);
        }
        startRow = sheet.FirstRowNum + 1;
      }
      else
      {
        startRow = sheet.FirstRowNum;
      }
      //最后一列的标号
      int rowCount = sheet.LastRowNum;
      for (int i = startRow; i <= rowCount; ++i)
      {
        IRow row = sheet.GetRow(i);
        if (row == null) continue; //没有数据的行默认是null

        DataRow dataRow = data.NewRow();
        for (int j = row.FirstCellNum; j < cellCount; ++j)
        {
          if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
            dataRow[j] = row.GetCell(j).ToString();
        }
        data.Rows.Add(dataRow);
      }
    }
    return data;
  }
  catch (Exception ex)
  {
    Console.WriteLine("Exception: " + ex.Message);
    return null;
  }
}

C# で Excel を読み取る 3 つの方法と比較分析に関するその他の記事については、PHP 中国語 Web サイトに注目してください。