Trois façons de lire Excel en C# et analyse comparative

(1) Méthode OleDB

Avantages : traitez Excel directement comme une source de données, lisez le contenu directement via SQL et la vitesse de lecture est plus rapide.

Inconvénients : La méthode de lecture des données n'est pas assez flexible et ne peut pas lire directement une certaine cellule. Ce n'est qu'après avoir lu toute la page de la feuille (le résultat est Datatable) que la valeur spécifiée peut être obtenue dans le Datatable en fonction du. nombre de lignes et de colonnes.

Lorsque la quantité de données Excel est importante. Cela occupera beaucoup de mémoire et lorsqu'il n'y aura pas assez de mémoire, une exception de dépassement de mémoire sera levée.

Le code de lecture est le suivant :

 
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;
  }
}

Ce qui suit explique la chaîne de connexion

HDR=Oui, ce qui signifie que la première ligne est le titre et n'est pas utilisée comme données (mais dans mon utilisation réelle, s'il y a des valeurs complexes dans la première ligne, les titres des colonnes Datatable lues seront automatiquement définis sur F1, F2, etc., ce qui n'est pas cohérent avec les applications réelles. , donc tout le contenu a été lu dans le Datatable via HDR=No, puis la première ligne a été définie manuellement comme titre) ; paramètre IMEX (mode IMport EXport)
IMEX a trois modes :
0 est le mode Exportation
1 est le mode Importation
2 est le mode lié (capacités de mise à jour complètes)
Ce que je souhaite particulièrement expliquer ici, c'est le paramètre IMEX, car différents modes représentent différents comportements de lecture et d'écriture :
Lorsque IMEX=0, c'est "mode export". Le fichier Excel ouvert dans ce mode ne peut être utilisé qu'à des fins "d'écriture".
Quand IMEX=1, c'est "Mode Import". Le fichier Excel ouvert dans ce mode ne peut être utilisé qu'à des fins de "lecture".
Lorsque IMEX=2, c'est le "mode lien". Le fichier Excel ouvert dans ce mode peut prendre en charge à la fois des fins de "lecture" et "d'écriture".

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

En plus, lisez Excel2007 version du fichier, la version doit être modifiée de 8.0 à 12.0. Dans le même temps, le pilote ne peut plus utiliser Jet, mais doit utiliser ACE. Responsable de l'erreur « Aucun ISAM installable trouvé ».

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

Aussi trouvé en ligne Dans ce Selon cette méthode, le nombre de tableaux Sheet récupérés peut être supérieur au nombre de tableaux Sheet dans le tableau Excel réel. Il y a deux raisons à cela :

1. Les noms extraits incluent ceux du nom XL Naming Manager ( voir la formule de XL2007 - Naming Manager, touche de raccourci Crtl F3

2) Le nom extrait inclut le suffixe FilterDatabase, qui est utilisé par XL pour enregistrer la plage de filtre.

Le premier point est relativement simple, il suffit de supprimer le contenu dans le gestionnaire de noms existant ; le deuxième point est plus difficile à gérer. Ces noms seront toujours conservés après la suppression du filtre. ajoutez une nouvelle feuille, puis copiez-y la feuille d'origine. Cependant, la situation réelle ne permet pas d'effectuer les vérifications ci-dessus pour chaque Excel. Le schéma de filtrage est donné ci-dessous. (Nous avons vérifié ce problème, veuillez le vérifier vous-même)

//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);
 }

Parce que le SheetName lu n'est pas valide et que le dernier caractère n'est généralement pas $. Si le SheetName comporte des symboles spéciaux, le SheetName lu sera automatiquement ajouté avec des guillemets simples. Par exemple, si vous modifiez SheetName en MySheet(1) dans Excel, le SheetName lu sera : « MySheet(1)$ », il est donc préférable de filtrer les guillemets simples avant de juger si le dernier caractère est $.

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

(2) Composant Com méthode (implémentée en ajoutant la référence Microsoft.Office.Interop.Excel)

Avantages : il peut lire les données dans Excel de manière très flexible et les utilisateurs peuvent appeler de manière flexible diverses fonctions pour le traitement.

Inconvénients : le traitement basé sur les cellules a une vitesse de lecture lente. Il est préférable de ne pas lire les fichiers contenant de grandes quantités de données de cette manière.

Vous devez ajouter la référence DLL correspondante. Cette référence doit exister avant de pouvoir être utilisée. Si le site Web est déployé sur IIS, Excel doit être installé sur la machine serveur, et parfois les autorisations IIS doivent être installées. être configuré.

Le code de lecture est le suivant :

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();
  }
}

NPOI est la version .NET du projet POI. POI est un projet Java open source permettant de lire et d'écrire des documents Excel, WORD et d'autres composants Microsoft OLE2. À l’aide de NPOI, vous pouvez lire et écrire des documents WORD/EXCEL sur un ordinateur sur lequel Office n’est pas installé ou un environnement correspondant.

Avantages : Lecture rapide d'Excel, opération de lecture flexible

Inconvénients : Vous devez télécharger le plug-in correspondant et l'ajouter à la référence système.

Pour plus d'articles sur les trois façons de lire Excel en C# et l'analyse comparative, veuillez faire attention au site Web PHP chinois !

/// <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;
  }
}