Shulou(Shulou.com)06/03 Report--

This article mainly introduces ASP.NET how to read EXCEL and solve the date problem, the article is very detailed, has a certain reference value, interested friends must read it!

You can use OLEDB to read the excel file, as long as we use the excel file as the data source.

First, create an excel file test.xls on disk D:

Second, read the contents of worksheet Sheet1 to DataSet

The copy 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:

You can see from the figure that the first line in the excel file becomes the column name in DataSet, which is the default setting of the system.

Third, if you want to use the first row as a data row, we can add a HDR=No attribute to the connection string, such as:

The copy code is as follows:

String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;" +

"Extended Properties='Excel 8.0 share HDR broadcast No'"

DataSet ds = new DataSet ()

OleDbDataAdapter oada = new OleDbDataAdapter ("select * from [Sheet1 $]", strConn)

Oada.Fill (ds)

The result may surprise you a little bit:

The first and third columns of the first row are 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 requirements of the format, so it becomes empty.

Fourth, we can also read all the columns as strings, as long as we add the attribute IMEX=1

The copy code is as follows:

String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;" +

"Extended Properties='Excel 8.0 position HDR subscription Notification IMEXPON 1'"

DataSet ds = new DataSet ()

OleDbDataAdapter oada = new OleDbDataAdapter ("select * from [Sheet1 $]", strConn)

Oada.Fill (ds)

What will be the result?

Does it surprise you again how the date in the third line becomes a number? in fact, excel automatically turns the date into a number when it converts the format.

So where did this number come from? If you change the date to January 1st, 1900, then you can see that his conversion result is 1, and so on, which day 39902 is.

Here's the solution:

Method 1:

The copy 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:

The copy code is as follows:

DateTime.FromOADate (Convert.ToInt32 (strValue)) .ToString ("d")

Five, maybe you don't want to read the whole excel.

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

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

What should I do if I don't know that the name of the worksheet has been artificially changed?

We can get the name of the specified worksheet through the index, and the following method can be used to get an array of worksheet names:

The copy 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 is treated as a string. According to the default settings of the system, usually if there is a string in the first 8 lines, the column is treated as a string, and if it is all numeric, the column is listed as a numeric column, and the date is the same.

If you think 8 rows 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, you will determine what type to use based on all rows, which is usually not recommended unless you do have a small amount of data.

Unable to read data cells in EXCEL. There is data, but it is full of null values.

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

Note:

"HDR=Yes;" indicates that the first row contains column names instead of data

"IMEX=1;" notification driver

The preface always reads "mixed" data columns as text.

Both must be used together.

I thought it would be OK. However, in the process of actual use, this setting is still not good. After consulting a lot of data, I found that there is still a registry information that needs to be modified, so that excel can no longer use the contents of the first 8 rows to determine the type of the column.

The registry changes are as follows:

In HKEY_LOCAL_MACHINE\ Software\ Microsoft\ Jet\ 4.0\ Engines\ Excel, there is a TypeGuessRows value of 8, which means that the first 8 columns will be read first to determine the type of each field, so if the data in the first 8 columns are numbers, all the text data that appears after column 9 will become null, so if you want to solve this problem, simply change the TypeGuessRows machine code value to 0, you can solve this problem.

The above is all the contents of the article "how to read EXCEL and solve the date problem by ASP.NET". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.