C # completely resolves an issue where OLE DB joins Excel data types that are not uniform

It is very efficient to use microsoft.jet.oledb.4.0 to connect to Excel, to read data, and to read data relative to traditional COM. But relative to traditional COM operations Excel, and the existence of data type conversion problems.
Because when you use OLE DB to connect to Excel to read data, you need to determine the type of data. The connection string is used by default:
View Source
Print?
1. String connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Excelfile + "; Extended properties= ' Excel 8.0; ';
When you use the connection string above to connect to Excel, you may encounter problems with inconsistent data types. The so-called inconsistent data type, refers to the same column of data types may appear a variety of, such as floating point, string, date, etc., when such a situation, the data read is empty, and even error, such as "Illegal date format" and other exceptions. When this happens, we all think of the data being read by character data, but what data type to read is not what we can control, it's OLE DB control, at least for the moment I haven't found a way to control the output data type. Because I tried to use the Convert,cast function to type-convert the output columns, but OLE DB does not support these functions when it connects to Excel. Therefore, the problem can only be resolved from other angles. I also searched the Internet a lot of solutions, the most comprehensive solution is: http://www.douban.com/note/18510346/. The following is a comparison of the methods that appear on the web to resolve this problem:

This provides a more convenient approach, but only if the first row must be a character type, either as a field name or as the first row of data. That said, everyone will understand. First modify the connection string to:
View Source
Print?

2003 (MICROSOFT.JET.OLEDB.4.0)
String strconn = String. Format ("Provider=Microsoft.Jet.OLEDB.4.0;Data source={0}; Extended properties= ' Excel 8.0; hdr=yes;imex=1; ' ", Excelfilepath);

(microsoft.ace.oledb.12.0)
String strconn = String. Format ("Provider=microsoft.ace.oledb.12.0;data source={0}; Extended properties= ' Excel 8.0; hdr=yes;imex=1; ' ", Excelfilepath);
This sets HDR to no because I'm reading the first row as data, and imex=1 the data type of the column based on the first 8 rows, forcing mixed data to be converted to text if there is character data. Here we understand why the first behavior of the character type is guaranteed. You can force a column's data type to be set to a character type, so what type of data appears in the column is not afraid. The job you need to do is to reset the field name and delete the first record after you've finished getting the data. The code is as follows:
View Source
Print?
The. DataTable dt = new DataTable ();
02.
. using (OleDbCommand cmd = new OleDbCommand ()) {
. cmd. Connection = conn;
Cmd.commandtype = CommandType.Text;
Cmd.commandtimeout = 6;
. Cmd.commandtext = String. Format ("select * from [{0}$]", sheetname);
08.
OleDbDataAdapter adapter = new OleDbDataAdapter (cmd);
Ten. Adapter. Fill (DT);
11.}
12.
if (dt. Rows.Count > 0) {
DataRow dr = dt. Rows[0];
15.
for (int col = 0; col < dt. Columns.count; col++) {
. Dt. Columns[col]. ColumnName = Dr[col]. ToString ();
18.}
19.
DT. Rows[0]. Delete ();
DT. AcceptChanges ();
22.}

C # completely resolves an issue where OLE DB joins Excel data types that are not uniform