C # How to read excel files

You can use OLEDB to read an excel file. You only need to use this excel file as the data source.

Create an excelfile test.xls on the ddisk:

2. Read the content of worksheet Sheet1 to DataSet.

C #-Code:

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties=Excel 8.0";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

The DataSet read is:

We can see thatThe first line in the excel file is the name of the column in DataSet.Which is the default setting of the system.

3. If you want to use the first row as the data row, you can add an HDR = No attribute to the connection string.

For example:

C #-Code:

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties=Excel 8.0;HDR=No";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

The result may be a bit unexpected:

The first and third columns in the first row become empty because the system recognizes the first column as a number and the third column as a date, the data in the first row does not meet the format requirements, so it becomes null.

4. We can also read all columns as strings, as long as the attribute IMEX = 1 is added.

C #-Code:

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties=Excel 8.0;HDR=No;IMEX=1";
DataSet ds = new DataSet();
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [Sheet1$]", strConn);
oada.Fill(ds);

What are the results?

Again, I was surprised to see how the date in the third row was changed to a number. In fact, when excel converted the format, it automatically changed the date to a number. How did you get this number? If you change the date to January 1, 1900, then you can see that the conversion result is 1, and so on. The day 39902 is displayed.

5. You may not want to read the entire excel file.

If you only want to read the first two columns, you can use: select * from [Sheet1 $ A: B].

If you only want to read A1 to B2, use: select * from [Sheet1 $ A1: B2].

6 What should I do if I do not know the worksheet name or the worksheet name has been manually modified?

You can use indexes to obtain the name of a specified worksheet. The following method can be used to obtain an array of worksheet names:

C #-Code:

ArrayList al = new ArrayList();
string strConn;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;"+
    "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable
    (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
    al.Add(dr[2]);
}
return al;