How to Use OLEDB to read Excel files to DataTable

First, let's take a look at the instance code [csharp] string strCon = @ "Provider = Microsoft. ACE. OLEDB.12.0; Data Source = "+ excel_path +"; Extended Properties = 'excel 12.0; HDR = YES; IMEX = 1' "; OleDbConnection myConn = new OleDbConnection (strCon ); string strCom = "SELECT * FROM [Part $ A7: AK] where [Part No] = 10506"; myConn. open (); OleDbDataAdapter myCommand = new OleDbDataAdapter (strCom, myConn); DataSet myDataSet = new DataSet (); myCommand. Fill (myDataSet, "Part"); myConn. close (); IMEX = 1 is used to read the values of each cell in Excel to the able, regardless of the Data Type in the Excel cell, values are forcibly converted to the string type in DataTable. When the attribute IMEX = 1 is not available, the Data Type of the Column in the able is determined based on the data type of the corresponding Column in the Excel file by default. In this case, if the data type of a column in Excel is the same, no problem occurs. If the data type is the same, the corresponding type is set for the corresponding column in The DataTable. However, if the type of this column in Excel is confusing, for example, it includes both numeric and string types. When you create a able at runtime, the system first determines which type of data occupies the body of this column in Excel, and then sets the DataTable column to this type. For example, if a column contains both integer and numeric, while an integer cell occupies the body, the column in the able is integer. At this time, another problem occurs. When you want to write the value to the able, if the cell meets the type required by the DataTable, It will be written. If not, the system enforces the conversion. For example, if Excel contains 5 strings, and the integer column of the cell occupies the body, the column in the able is Numeric, the system forcibly converts string 5 to numeric 5 and then assigns it to the corresponding place of the DataTable. However, if there is a problem with the conversion, for example, if there is "NO5" in the cell in this column, the forced conversion will be problematic, and the system will assign DBNull to the corresponding location of the DataTable, if you use the DataGridView datagrito display the DataTable, this area is displayed as a blank space, but note that this column is not null for this row in the DataTable, instead, DBNull. in fact, I think the role of null is the same. It is not displayed in the DataGridView. We are writing a program. If you need to filter the null element of the DataTable, pay attention to this problem. In Excel, if a row of string type occupies the body, this column in able will be set to string type, and any type can be converted to string type smoothly, the Excel class is displayed completely. No matter the string type cells or Integer type cells in this column, they can be displayed completely. This is quite special. However, this is only a special case. Therefore, to ensure data type consistency during processing, if the data type in Excel is disordered, you can use IMEX = 1 to convert all columns in the DataTable to the bytes type. Next, we will discuss a related problem, that is, the use of statements in the DataTable for filtering. At this time, we should also pay attention to the Column type Problem in the DataTable. In the following example, F1, F2, and F3 are all columns of DataTable. In the following example, the F1 column is numeric and the F5 column is string [csharp] DataRow [] rows = table. select ("F1 = '000000' andF5 = 'no2 '"); in this case, the program will not report an error because F1 = '000000 ', although the filter condition does not match 1540 of the string type and table, the system will convert it by itself. Therefore, writing F1 = '20160301' and F1 = 1540 is acceptable here. The F5 column is a string type, and F5 = 'no2 'is OK. Now, we can see that the F1 column is numeric, And the F5 column is Numeric. [csharp] DataRow [] rows = table. select ("F1 = '000000' andF5 = 'no2 '"); When F5 = 'no2' is encountered, the system automatically converts it. At this time, because it cannot be converted to a numeric type, when filtering, an exception is thrown, Operator = can't perform on System. double and System. let's look at another case. The F1 column is numeric, And the F5 column is Numeric. [csharp] DataRow [] rows = table. select ("F1 = 1540 andF5 = 'no2 '"); this is also okay, F1 = 1540, because F1 is a numeric type, so you do not have to quote 1540. however, we note that the F1 column is numeric and the F5 column is Numeric. [csharp] DataRow [] rows = Table. select ("F1 = 1540 and F5 = NO2"); in this case, an error is reported. The system does not automatically convert NO2 to a string, but regards Grade2 as a column, the system determines that the cell values of these two columns in this row are equal, and the column name does not exist in the DataTable. Therefore, the system will throw an exception that the column cannot be found. Therefore, when a column is of the string type, you must add single quotation marks. Otherwise, an exception is thrown.