Example of C# method to read Excel to DataTable

premise

The most common situation when data processing is performed under Windows is to read Microsoft Excel files. Excel's popularity rate is amazing and is the de facto standard. In the past, I used to call third-party libraryNPOI way to handle Excel. This method has two disadvantages:

1. Need to rely on third-party library NPOI
2. NPOI supports almost full-featured Office conditions, the disadvantage is that it is also complex.

If you just simply import data, you can have a simpler solution, and the restrictions of the solution are;

• Only support Windows platforms
• Read only Excel files
• Support xls and xlsx file formats

rely

Still have dependencies2007 Office System Driver: Data Connectivity Components

If Driver is not installed, you will get the following error:

.12.0' provider is not registered on the local machine

Code

public static DataTable ReadAsTable(string xlsxFile, string sheetName = "Sheet1")
{
  var connectionString = ("Provider=.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"", xlsxFile);
  var adapter = new OleDbDataAdapter($"SELECT * FROM [{sheetName}$]", connectionString);

  var ds = new DataSet();
  (ds, sheetName);

  return [sheetName];
}

There are two Extended Properties in the connectionString that can be modified as needed:

• HDR, this property indicates whether the first row of Excel is converted into the Column Name of DataTable or the normal data processing.
• IMEX, this data indicates whether it is the read type or is it all read as text. For safe reading, it is best to set it to 1, that is, it is treated as text:Treat data as text 。

Skill

Sometimes we need to bind the read data to a specific type, we can do this:

var query = dt
  .AsEnumerable()
  .Where(x => <string>("phoneNumber") != )
  .Select(x => new Contact
    {
      FirstName= <string>("First Name"),
      LastName = <string>("Last Name"),
      PhoneNumber =<string>("Phone Number"),
    });

The above is all the content of this article. I hope it will be helpful to everyone's study and I hope everyone will support me more.