When querying Excel Data, column data is empty

When querying Excel data, if the values in a column contain numbers and texts, some text values may not be read from the data obtained using the query statement. for this reason, I want to find a large segment on the Internet.

In fact, it is very easy to solve this problem, as long as the connection string instructs all columns to be processed by text, so that all the data can be obtained.

String sconnectionstring = "provider = Microsoft. Jet. oledb.4.0; Data Source =" + httpcontext. Current. server. mappath ("~ /App_data/database accessories example Table .xls ") + "; Extended properties = 'excel 8.0; IMEX = 1; HDR = 1 ;'";
Oledbconnection objconn = new oledbconnection (sconnectionstring );
Objconn. open ();
Oledbcommand objcmdselect = new oledbcommand ("select * from [sheet1 $]", objconn );
Oledbdataadapter objadapter1 = new oledbdataadapter ();
Objadapter1.selectcommand = objcmdselect;
Dataset objdataset1 = new dataset ();

Objadapter1.fill (objdataset1, "xldata ");
Objconn. Close ();
Objconn. Dispose ();
Dt = objdataset1.tables [0];

 

The key is the property setting in red.IMEXIndicates whether to forcibly convert to text

 HDR has no field. Generally, the column title of row 1st in the Excel table is the field name by default, such as name and age.