C#에서 Excel을 읽는 세 가지 방법 및 비교 분석

(1) OleDB 방식

장점: 엑셀을 직접 데이터소스로 취급하고, 내용을 SQL을 통해 직접 읽어 읽기 속도가 더 빠르다.

단점: 데이터를 읽는 방법이 유연하지 않고 특정 셀을 직접 읽을 수 없습니다. 전체 Sheet 페이지를 읽은 후에만(결과는 Datatable임) 이를 기반으로 Datatable에서 지정된 값을 얻을 수 있습니다. 행과 열의 수.

엑셀 데이터의 양이 많을 때. 많은 메모리를 차지하며, 메모리가 충분하지 않으면 메모리 오버플로 예외가 발생합니다.

읽는 코드는 다음과 같습니다.

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, 즉 첫 번째 행은 제목이고 데이터로 사용되지 않습니다. (그러나 실제 사용시 첫 번째 행에 복잡한 값이 있으면 읽은 Datatable 열 제목이 자동으로 F1, F2 등으로 설정되어 일관성이 없습니다. 실제 애플리케이션에서는 HDR=No를 통해 모든 콘텐츠를 Datatable로 읽은 다음 첫 번째 행을 수동으로 제목으로 설정했습니다. IMEX(IMport EXport 모드) 설정
IMEX에는 세 가지 모드가 있습니다.
0은 내보내기 모드
1은 가져오기 모드
2는 연결 모드(전체 업데이트 기능)
여기서 특히 설명하고 싶은 것은 IMEX 매개변수입니다. 모드에 따라 읽기 및 쓰기 동작이 다르기 때문입니다. :
IMEX =0이 "내보내기 모드"인 경우 이 모드에서 열린 Excel 파일은 "쓰기" 목적으로만 사용할 수 있습니다.
IMEX=1이면 "가져오기 모드"입니다. 이 모드에서 열린 Excel 파일은 "읽기" 목적으로만 사용할 수 있습니다.
IMEX=2이면 "링크 모드"입니다. 이 모드에서 열린 Excel 파일은 "읽기" 및 "쓰기" 목적을 모두 지원할 수 있습니다.

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

또 Excel2007도 읽어보세요 파일 버전을 8.0에서 12.0으로 변경해야 합니다. 동시에 드라이버는 더 이상 Jet를 사용할 수 없지만 ACE를 사용해야 합니다. "설치 가능한 ISAM을 찾을 수 없음" 오류의 원인입니다.

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

온라인에서도 찾을 수 있습니다. 방법을 사용하면 검색된 시트 테이블 수가 실제 Excel 테이블의 시트 테이블 수보다 클 수 있습니다. 여기에는 두 가지 이유가 있습니다.

1 추출된 이름에는 XL Naming Manager 이름( XL2007의 공식을 참조하세요. 바로 가기 키 Crtl+F3). 추출된 이름에는 XL에서 필터 범위를 기록하는 데 사용되는 FilterDatabase 접미사가 포함되어 있습니다.

첫 번째 사항은 비교적 간단합니다. 기존 이름 지정 관리자에서 콘텐츠를 삭제하면 됩니다. 두 번째 사항은 필터를 삭제한 후에도 계속 유지됩니다. 새 시트를 추가한 다음 원본 시트를 복사하세요. 그러나 실제 상황에서는 모든 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의 데이터를 매우 유연하게 읽을 수 있으며, 처리를 위한 다양한 기능을 사용자가 유연하게 호출할 수 있습니다.

단점: 셀 기반 처리는 읽기 속도가 느립니다. 이런 식으로 많은 양의 파일을 읽지 않는 것이 가장 좋습니다.

해당 DLL 참조를 추가해야 합니다. 이 참조를 사용하려면 먼저 존재해야 합니다. 웹 사이트가 IIS에 배포된 경우 Excel을 서버 컴퓨터에 설치해야 하며 경우에 따라 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) NPOI 모드에서 Excel 읽기(이 방법은 테스트되지 않았습니다.)

NPOI는 POI 프로젝트의 .NET 버전입니다. POI는 Excel, WORD 및 기타 Microsoft OLE2 구성 요소 문서를 읽고 쓰기 위한 오픈 소스 Java 프로젝트입니다. NPOI를 이용하면 Office가 설치되지 않은 기기나 해당 환경에서 WORD/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을 읽는 세 가지 방법과 비교 분석에 대한 더 많은 기사를 보려면 PHP 중국어 웹사이트를 주목하세요!