asp.net (C #) reading Excel Plus resolution Date problem sharing _ practical tips

You can read an Excel file using OLE DB, as long as you have the Excel file as the data source.

Create Excel file Test.xls in D disk:

Second, read the contents of the worksheet Sheet1 to the dataset

Copy Code code 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:

You can see from the diagram that the first row in the Excel file becomes the column name in the dataset, which is the default setting for the system.

If you want to take the first row as a data row, then we can add a Hdr=no property to the connection string, such as:

Copy Code code 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 out of your mind:

The first and third columns of the first row are empty, because the system identifies the first column as a number, and the third column is identified as a date.

The first line of data does not conform to the eligibility requirements, so it becomes empty.

We can also read all the columns as strings, just add the attribute imex=1

Copy Code code 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 will be the result?

is not again to your surprise, the third line of the date how to become a number, in fact, excel in the conversion format automatically turned the date into a number,

So how did this figure come from? If you change the date to January 1, 1900, then you can see that the result of his conversion is 1, and so on, 39902 is the day you see it.

Here's the solution:

Method One:

Copy Code code 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 Two:

Copy Code code as follows:

Datetime.fromoadate (Convert.ToInt32 (strvalue)). ToString ("D");

Five maybe you don't want to read the entire Excel content

If you want to read only the first two columns: SELECT * FROM [SHEET1$A:B]

If you want to read only the contents of A1 to B2, use: SELECT * FROM [SHEET1$A1:B2]

Six if you do not know the name of the worksheet or the name has been artificially modified what to do?

We can get the name of the specified worksheet by index, and the following methods can be used to get an array of the names of the worksheets:

Copy Code code 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, NULL, NULL, "TABLE"});
Conn. Close ();
foreach (DataRow Dr in Sheetnames.rows)
{
Al. ADD (dr[2]);
}
Return al;

Imex=1 are not all handled as strings, depending on the default settings of the system, usually if the first 8 lines have strings, the column is treated as a string, and if all numbers are numeric, the date is the same.

If you think 8 lines are not enough or too many, you can only modify the registry hkey_local_machine/software/microsoft/jet/4.0/engines/excel/typeguessrows, if this value is 0, is based on all rows to determine what type of use, usually not recommended, unless your data volume is indeed relatively small.

Unable to read data cells in Excel. There is data, but the readout is all null values.

Workaround:

1. In the import data connection string, Imex=1 is added, "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" C:\Data.xls; Extended properties= "Excel 8.0; Hdr=yes;imex=1; "So be it."

Note:

"Hdr=yes;" Indicates that the first row contains column names, not data;

"Imex=1;" Alert Driver

The order always reads the blend data column as text.

Both must be used together.

I thought this would be OK. But in the actual use of the process, such settings or not, looked at a lot of information to find that there is a registration table information needs to be modified, so that you can make Excel no longer use the first 8 lines of content to determine the type of the column.

The registry modification reads as follows:

The

has a typeguessrows value in Hkey_local_machine\software\microsoft\jet\4.0\engines\excel, and the preset is 8, which means that the first 8 columns are read first to determine the type of each field, So if the first 8 columns of data are numbers, to the 9th column after the occurrence of the text will become null, so if you want to solve this problem, as long as the TypeGuessRows machine code value to 0, you can solve this problem.