Two ways to read excel data in c#

Method 1: OleDb:This method is still very fast to read Excel, but this method is not very flexible when reading data. However, you can delete and modify the data in DataTable.

Advantages: Simple reading method and fast reading speed

Disadvantages: In addition to the inflexible reading process, this reading method has another disadvantage, which is when the amount of Excel data is large. It will consume a lot of memory, and when there is insufficient memory, a memory overflow exception will be thrown.

But it's pretty good in general.

(The code has been modified compared to the original text)

DataTable GetDataFromExcelByConn(bool hasTitle = false)
{
  OpenFileDialog openFile = new OpenFileDialog();
   = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
   = ();
   = false;
  if (() == ) return null;
  var filePath = ;
  string fileType = (filePath);
  if ((fileType)) return null;

  using (DataSet ds = new DataSet())
  {
    string strCon = ("Provider=.{0}.0;" +
            "Extended Properties=\"Excel {1}.0;HDR={2};IMEX=1;\";" +
            "data source={3};",
            (fileType == ".xls" ? 4 : 12), (fileType == ".xls" ? 8 : 12), (hasTitle ? "Yes" : "NO"), filePath);
    string strCom = " SELECT * FROM [Sheet1$]";
    using (OleDbConnection myConn = new OleDbConnection(strCon))
    using (OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn))
    {
      ();
      (ds);
    }
    if (ds == null ||  <= 0) return null;
    return [0];
  }
}

Method 2: Read Excel in the Com component

This method needs to be referenced first. The advantages and disadvantages of this method are preferred

Advantages: It can read data in Excel very flexibly

Disadvantages: If the Web site is deployed on IIS, the server machine needs to have Excel installed, and sometimes it also needs to configure IIS permissions. The most important point is that it is read based on cell mode, so the data is very slow (I have tried to directly read files of thousands of rows and more than 200 columns, and it takes 15 minutes to read directly. Even if multi-thread segmented reading is used to improve CPU utilization, it takes 8 minutes. PS: CPU I3)

Children's shoes who need to read large files are cautious. . .

(The code has been modified compared to the original text)

DataTable GetDataFromExcelByCom(bool hasTitle = false)
{
  OpenFileDialog openFile = new OpenFileDialog();
   = "Excel(*.xlsx)|*.xlsx|Excel(*.xls)|*.xls";
   = ();
   = false;
  if (() == ) return null;
  var excelFilePath = ;

   app = new ();
   sheets;
  object oMissiong = ;
   workbook = null;
  DataTable dt = new DataTable();

  try
  {
    if (app == null) return null;
    workbook = (excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong,
      oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
    sheets = ;

    //Read the data into the DataTable     worksheet = ()sheets.get_Item(1);//Read the first table    if (worksheet == null) return null;

    int iRowCount = ;
    int iColCount = ;
    //Generate column header    for (int i = 0; i &lt; iColCount; i++)
    {
      var name = "column" + i;
      if (hasTitle)
      {
        var txt = (()[1, i + 1]).();
        if (!(txt)) name = txt;
      }
      while ((name)) name = name + "_1";//Repeat line names and errors will be reported.      (new DataColumn(name, typeof(string)));
    }
    //Generate row data     range;
    int rowIdx = hasTitle ? 2 : 1;
    for (int iRow = rowIdx; iRow &lt;= iRowCount; iRow++)
    {
      DataRow dr = ();
      for (int iCol = 1; iCol &lt;= iColCount; iCol++)
      {
        range = ()[iRow, iCol];
        dr[iCol - 1] = (range.Value2 == null) ? "" : ();
      }
      (dr);
    }
    return dt;
  }
  catch { return null; }
  finally
  {
    (false, oMissiong, oMissiong);
    (workbook);
    workbook = null;
    ();
    ();
    (app);
    app = null;
  }
}

Method 2 of the original text also provides code for multi-threading data processing, and copied it here (a type of SheetOptions appears here, and it cannot be verified from its source. If you know, please leave a message, thank you.):

/// &lt;summary&gt; 
/// Using COM, multi-threading Excel (1 main thread, 4 secondary threads)/// &lt;/summary&gt; 
/// <param name="excelFilePath">Path</param>/// &lt;returns&gt;DataTabel&lt;/returns&gt; 
public  ThreadReadExcel(string excelFilePath)
{
   app = new ();
   sheets = null;
   workbook = null;
  object oMissiong = ;
   dt = new ();

  try
  {
    if (app == null)
    {
      return null;
    }

    workbook = (excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, 
      oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);

    //Read the data into the DataTable - Start    sheets = ;
     worksheet = ()sheets.get_Item(1);//Read the first table    if (worksheet == null)
      return null;

    string cellContent;
    int iRowCount = ;
    int iColCount = ;
     range;

    //Responsible for the Start    DataColumn dc;
    int ColumnID = 1;
    range = ()[1, 1];
    //while (().Trim() != "") 
    while (iColCount &gt;= ColumnID)
    {
      dc = new DataColumn();
       = ("");

      string strNewColumnName = ().Trim();
      if ( == 0) strNewColumnName = "_1";
      //Judge whether the column name is duplicate      for (int i = 1; i &lt; ColumnID; i++)
      {
        if ([i - 1].ColumnName == strNewColumnName)
          strNewColumnName = strNewColumnName + "_1";
      }

       = strNewColumnName;
      (dc);

      range = ()[1, ++ColumnID];
    }
    //End 

    //The data is greater than 500, and multiple processes are used to read data    if (iRowCount - 1 &gt; 500)
    {
      //Start multi-threading data reading      // Create a new thread      int b2 = (iRowCount - 1) / 10;
      DataTable dt1 = new DataTable("dt1");
      dt1 = ();
      SheetOptions sheet1thread = new SheetOptions(worksheet, iColCount, 2, b2 + 1, dt1);
      Thread othread1 = new Thread(new ThreadStart());
      ();

      //Block for 1 millisecond, ensure that the first read dt1 is read      (1);

      DataTable dt2 = new DataTable("dt2");
      dt2 = ();
      SheetOptions sheet2thread = new SheetOptions(worksheet, iColCount, b2 + 2, b2 * 2 + 1, dt2);
      Thread othread2 = new Thread(new ThreadStart());
      ();

      DataTable dt3 = new DataTable("dt3");
      dt3 = ();
      SheetOptions sheet3thread = new SheetOptions(worksheet, iColCount, b2 * 2 + 2, b2 * 3 + 1, dt3);
      Thread othread3 = new Thread(new ThreadStart());
      ();

      DataTable dt4 = new DataTable("dt4");
      dt4 = ();
      SheetOptions sheet4thread = new SheetOptions(worksheet, iColCount, b2 * 3 + 2, b2 * 4 + 1, dt4);
      Thread othread4 = new Thread(new ThreadStart());
      ();

      //The main thread reads the remaining data      for (int iRow = b2 * 4 + 2; iRow &lt;= iRowCount; iRow++)
      {
        DataRow dr = ();
        for (int iCol = 1; iCol &lt;= iColCount; iCol++)
        {
          range = ()[iRow, iCol];
          cellContent = (range.Value2 == null) ? "" : ();
          dr[iCol - 1] = cellContent;
        }
        (dr);
      }

      ();
      ();
      ();
      ();

      //Add data read by multiple threads to the following dt1      foreach (DataRow dr in )
        ();
      ();
      ();

      foreach (DataRow dr in )
        ();
      ();
      ();

      foreach (DataRow dr in )
        ();
      ();
      ();

      foreach (DataRow dr in )
        ();
      ();
      ();

      return dt1;
    }
    else
    {
      for (int iRow = 2; iRow &lt;= iRowCount; iRow++)
      {
        DataRow dr = ();
        for (int iCol = 1; iCol &lt;= iColCount; iCol++)
        {
          range = ()[iRow, iCol];
          cellContent = (range.Value2 == null) ? "" : ();
          dr[iCol - 1] = cellContent;
        }
        (dr);
      }
    }
    //Read the data into the DataTable—End    return dt;
  }
  catch
  {
    return null;
  }
  finally
  {
    (false, oMissiong, oMissiong);
    (workbook);
    (sheets);
    workbook = null;
    ();
    ();
    (app);
    app = null;
    ();
    ();
  }
}

Supplement the SheetOptions code:

class SheetOptions
{
   worksheet;
  int iColCount;
  int star;
  int end;
   dt;
  public SheetOptions( worksheet, int iColCount, int star, int end,  dt)
  {
     = worksheet;
     = iColCount;
     = star;
     = end;
     = dt;
  }

  public void SheetToDataTable()
  {
    string cellContent;
     range;
    for (int iRow = star; iRow <= end; iRow++)
    {
       dr = ();
      for (int iCol = 1; iCol <= iColCount; iCol++)
      {
        range = ()[iRow, iCol];
        cellContent = (range.Value2 == null) ? "" : ();
        dr[iCol - 1] = cellContent;
      }
      (dr);
    }
  }
}

The original text also provides a third method. Those who are interested can care about it:

Method 3: Read Excel in NPOI,NPOI is a set of open source components, similar to Java's POI. Including: NPOI,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Advantages: fast reading Excel, flexible operation of reading mode

Disadvantages: Only supports Excel with 03, xlsx cannot be read. Due to this point, not many people use this method, so there is no reason to ask customers to use version 03 Excel. Besides, version 03 Excel still has a limit on the number of rows, and only supports 65536 lines.

(I heard from their developers that they will launch a new version at the end of 2012 to support xlsx reading. But they have been very busy and have no time to pay attention to this matter. Interested students can take a look)

using System;
using ;
using ;
using ;
using NPOI;
using ;
using ;
using ;
using ;
using ;
using ;
using ;

public class NPOIHelper
{
  private static int ExcelMaxRow = Convert.ToInt32(["ExcelMaxRow"]);
  /// &lt;summary&gt;
  /// Export Excel from DataSet  /// &lt;/summary&gt;
  /// <param name="sourceTable">DataTable to export data</param>  /// <param name="sheetName">Worksheet name</param>  /// <returns>Excel worksheet</returns>  private static Stream ExportDataSetToExcel(DataSet sourceDs)
  {
    HSSFWorkbook workbook = new HSSFWorkbook();
    MemoryStream ms = new MemoryStream();

    for (int i = 0; i &lt; ; i++)
    {
      HSSFSheet sheet = (HSSFSheet)([i].TableName);
      HSSFRow headerRow = (HSSFRow)(0);
      // handling header.
      foreach (DataColumn column in [i].Columns)
        ().SetCellValue();
      // handling value.
      int rowIndex = 1;
      foreach (DataRow row in [i].Rows)
      {
        HSSFRow dataRow = (HSSFRow)(rowIndex);
        foreach (DataColumn column in [i].Columns)
        {
          ().SetCellValue(row[column].ToString());
        }
        rowIndex++;
      }
    }
    (ms);
    ();
     = 0;
    workbook = null;
    return ms;
  }
  /// &lt;summary&gt;
  /// Export Excel from DataSet  /// &lt;/summary&gt;
  /// <param name="sourceTable">DataTable to export data</param>  /// <param name="fileName">Specify the Excel worksheet name</param>  /// <returns>Excel worksheet</returns>  public static void ExportDataSetToExcel(DataSet sourceDs, string fileName)
  {
    //Check whether there are more than 65325 Tables    for (int t = 0; t &lt; ; t++)
    {
      if ([t]. &gt; ExcelMaxRow)
      {
        DataSet ds = GetdtGroup([t].Copy());
        (t);
        //Insert the obtained ds into sourceDs        for (int g = 0; g &lt; ; g++)
        {
          DataTable dt = [g].Copy();
          (dt);
        }
        t--;
      }
    }

    MemoryStream ms = ExportDataSetToExcel(sourceDs) as MemoryStream;
    ("Content-Disposition", "attachment;filename=" + fileName);
    (());
    ();
    //();
    ();
    ms = null;
  }
  /// &lt;summary&gt;
  /// Export Excel from DataTable  /// &lt;/summary&gt;
  /// <param name="sourceTable">DataTable to export data</param>  /// <returns>Excel worksheet</returns>  private static Stream ExportDataTableToExcel(DataTable sourceTable)
  {
    HSSFWorkbook workbook = new HSSFWorkbook();
    MemoryStream ms = new MemoryStream();
    HSSFSheet sheet = (HSSFSheet)();
    HSSFRow headerRow = (HSSFRow)(0);
    // handling header.
    foreach (DataColumn column in )
      ().SetCellValue();
    // handling value.
    int rowIndex = 1;
    foreach (DataRow row in )
    {
      HSSFRow dataRow = (HSSFRow)(rowIndex);
      foreach (DataColumn column in )
      {
        ().SetCellValue(row[column].ToString());
      }
      rowIndex++;
    }
    (ms);
    ();
     = 0;
    sheet = null;
    headerRow = null;
    workbook = null;
    return ms;
  }
  /// &lt;summary&gt;
  /// Export Excel from DataTable  /// &lt;/summary&gt;
  /// <param name="sourceTable">DataTable to export data</param>  /// <param name="fileName">Specify the Excel worksheet name</param>  /// <returns>Excel worksheet</returns>  public static void ExportDataTableToExcel(DataTable sourceTable, string fileName)
  {
    //If the data exceeds 65325, it will be divided into multiple tables to export    if ( &gt; ExcelMaxRow)
    {
      DataSet ds = GetdtGroup(sourceTable);
      //Export DataSet      ExportDataSetToExcel(ds, fileName);
    }
    else
    {
      MemoryStream ms = ExportDataTableToExcel(sourceTable) as MemoryStream;
      ("Content-Disposition", "attachment;filename=" + fileName);
      (());
      ();
      //();
      ();
      ms = null;
    }
  }

  /// &lt;summary&gt;
  /// The table with more than 65325 rows is passed, and the DataSet is returned  /// &lt;/summary&gt;
  /// &lt;param name="dt"&gt;&lt;/param&gt;
  /// &lt;returns&gt;&lt;/returns&gt;
  public static DataSet GetdtGroup(DataTable dt)
  {
    string tablename = ;

    DataSet ds = new DataSet();
    (dt);

    double n =  / (ExcelMaxRow);

    //Create a table    for (int i = 1; i &lt; n; i++)
    {
      DataTable dtAdd = ();
       = tablename + "_" + ();
      (dtAdd);
    }

    //Decompose the data    for (int i = 1; i &lt; ; i++)
    {
      //The number of new table rows reaches the maximum or the number of base tables is insufficient      while ([i]. != ExcelMaxRow &amp;&amp; [0]. != ExcelMaxRow)
      {
        [i].([0].Rows[ExcelMaxRow].ItemArray);
        [0].(ExcelMaxRow);

      }
    }

    return ds;
  }

  /// &lt;summary&gt;
  /// Export Excel from DataTable  /// &lt;/summary&gt;
  /// <param name="sourceTable">DataTable to export data</param>  /// <param name="fileName">Specify the Excel worksheet name</param>  /// <returns>Excel worksheet</returns>  public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName)
  {
    int rowIndex = 2;//Start from the second line, because the first two lines are the contents in the template    int colIndex = 0;
    FileStream file = new FileStream(modelpath + modelName + ".xls", , );//Read in Excel template    HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
    HSSFSheet sheet1 = (HSSFSheet)("Sheet1");
    (0).GetCell(0).SetCellValue("excelTitle");   //Set the table header    foreach (DataRow row in )
    {  //Double loop to write data in sourceTable      rowIndex++;
      colIndex = 0;
      HSSFRow xlsrow = (HSSFRow)(rowIndex);
      foreach (DataColumn col in )
      {
        (colIndex).SetCellValue(row[].ToString());
        colIndex++;
      }
    }
     = true;
    FileStream fileS = new FileStream(modelpath + fileName + ".xls", );//save    (fileS);
    ();
    ();
  }
}

This is the end of this article about the implementation of two methods of reading Excel data in C#. For more related C# reading Excel content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!