ASP. NET (C #) reading EXCEL and adding a solution to the date problem

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.
Copy codeThe Code is as follows:
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:

The first line in the excel file is changed to the column name 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:
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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?

Is it unexpected again that the date in the third row is changed to a number? In fact, excel automatically converts the date into a number when converting the format,

So how does this number come from? 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.

Solution:

Method 1:
Copy codeThe Code is as follows:
Public static string getDateStr (string strValue)
{
Int I = Convert. ToInt32 (strValue );
DateTime d1 = Convert. ToDateTime ("1900-1-1 ");
DateTime d2 = d1.AddDays (I-2 );
String strTemp = d2.ToString ("d ");

Return strTemp;
}

Method 2:
Copy codeThe Code is as follows:
DateTime. FromOADate (Convert. ToInt32 (strValue). ToString ("d ");

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:

Copy codeThe Code is as follows:
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, "TABLE "});
Conn. Close ();
Foreach (DataRow dr in sheetNames. Rows)
{
Al. Add (dr [2]);
}
Return al;

 

Not all IMEX = 1 will be processed as strings. According to the default settings of the system, if the first eight rows have strings, the column will be processed as strings, if all are numbers, the column is a number column, and the date is the same.

If you don't think there are enough 8 rows, you can only modify the Registry HKEY_LOCAL_MACHINE/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows. If the value is 0, it is usually not recommended to determine the type of data to be used based on all rows, unless your data volume is indeed small.

Cannot read data cells in EXCEL. Data is available, but all read results are null.

Solution:

1. in the import data connection string, add IMEX = 1, "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "C: \ Data.xls"; Extended Properties = "Excel 8.0; HDR = Yes; IMEX = 1.

Note:

"HDR = Yes;" indicates that the first line contains the column name, not the data;

"IMEX = 1;" Notification driver

Data columns are always read as text.

Both must be used together.

I thought it would be okay. However, in actual use, this setting still does not work. After reading a lot of information, we found that there was another information in the registry that needs to be modified, in this way, the content of the first eight rows can no longer be used in excel to determine the type of the column.

Modify the Registry as follows:

There is a TypeGuessRows value in HKEY_LOCAL_MACHINE \ Software \ Microsoft \ Jet \ 4.0 \ Engines \ Excel. The default value is 8, indicating that the first eight columns will be read to determine the type of each column, therefore, if the first eight columns of data are all numbers, the text that appears after the first eight columns will become null. To solve this problem, you only need to change the TypeGuessRows server code value to 0, you can solve this problem.