C # completely solve the problem of inconsistent Excel Data Types connected to Oledb

C # completely solve the problem of inconsistent Excel Data Types connected to Oledb

When Microsoft. Jet. OLEDB.4.0 is used to connect to Excel to read data, it is highly efficient to use traditional COM to read data. However, compared with the traditional COM Excel operation, the data type conversion problem exists.

When using OLEDB to connect to Excel to read data, you need to determine the data type. 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 using the connection string above to connect to Excel, you may encounter inconsistent data types. The so-called data types are inconsistent, which means that there may be multiple data types in the same column, such as floating point numbers, strings, and dates. In this case, the data read is empty, an error may even be reported, for example, "invalid Date Format. When this problem occurs, we all think of reading all the data by character, but what data type is not controlled by us, it is controlled by OLEDB, at least for the moment, I have not found a method to control the output data type. Since I used convert and cast functions to convert the types of output columns, the SQL statements used by oledb to connect to Excel do not support these functions. Therefore, this problem can only be solved from other perspectives. I also searched for many solutions online. The most comprehensive solution is http://www.douban.com/note/18510 /. The following lists the methods to solve this problem on the Internet:

A more convenient method is provided here, but the premise is that the first row must be of the struct type as the field name or the first row. In this case, you will understand. First, modify the connection string:

 
 

  
  
view source 
  
  
print ? 
  
  
 
  
  
//2003Microsoft.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); 
  
  
 
  
  
//2010Microsoft.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);  
 
 

Set HDR to NO here, because I read the first row as data, and IMEX = 1 indicates to judge the Data Type of the Column Based on the first eight rows, then, the hybrid data is forced to be converted to text. Here, we will understand why we need to ensure that the first behavior is balanced. If you can set the Data Type of a column to the bytes type, you are not afraid of any data types in the column. The task is to reset the field name and delete the first record after obtaining the data. The Code is as follows:

 
 

  
  
view source 
  
  
print ? 
  
  
01. DataTable dt = new DataTable(); 
  
  
02.    
  
  
03. using(OleDbCommand cmd = new OleDbCommand()){ 
  
  
04.     cmd.Connection = conn; 
  
  
05.     cmd.CommandType = CommandType.Text; 
  
  
06.     cmd.CommandTimeout = 6; 
  
  
07.     cmd.CommandText = string.Format("select * from [{0}$]", sheetName); 
  
  
08.    
  
  
09.     OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); 
  
  
10.     adapter.Fill(dt); 
  
  
11. } 
  
  
12.    
  
  
13. if (dt.Rows.Count > 0) { 
  
  
14.     DataRow dr = dt.Rows[0]; 
  
  
15.    
  
  
16.     for (int col = 0; col < dt.Columns.Count; col++) { 
  
  
17.         dt.Columns[col].ColumnName = dr[col].ToString(); 
  
  
18.     } 
  
  
19.    
  
  
20.     dt.Rows[0].Delete(); 
  
  
21.     dt.AcceptChanges(); 
  
  
22. }