This article describes the method of C# using oledb to read the content of an excel table to a datatable. Share it for your reference. The specific analysis is as follows:
First look at an example code
OleDbConnection myConn = new OleDbConnection(strCon);
string strCom = "SELECT * FROM [Part$A7:AK] where [Part No]=10506";
();
OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
DataSet myDataSet = new DataSet();
(myDataSet, "Part");
();
The function of IMEX=1 is that when reading the value of each cell in Excel into the DataTable, no matter what data type it is in the Excel cell, it will be forced to convert it into a string type.
When there is no attribute IMEX=1, the default is to determine the data type of Column in DataTable based on the data type corresponding to Column in Excel. In this case, if the data types of a certain column in Excel are the same, the corresponding type will be set in the corresponding column in DataTable. However, if the type of this column in Excel is confused, for example, including both numerical and string types, when creating DataTable at runtime, you will first determine which type of data in Excel takes the main body, and then set the column of DataTable to this type. For example, if a column has both integer and character types, and integer cells occupy the main body, the column in DataTable is an integer type.
Another problem arises at this time. When you want to write the value to the DataTable, if the cell meets the type required in the DataTable, it will be written. If it does not meet, the system will cast it. For example, if the string is 5 in Excel, and the integer type of the column in which the cell is located is the main body, the column in DataTable is numerical, then the system will force the 5 of the string to 5 of the numerical type and assign it to the corresponding place of DataTable. However, at this time, if there is a problem with the conversion, for example, if there is a cell in this column that says "NO5", then there will be a problem with the casting, and the system will assign the value of DBNull to the corresponding place of DataTable. Now if you use DataGridView to display the DataTable, the display of this place is a blank grid. But it should be noted that the row and column in DataTable are not null, but DBNull. In fact, I think the function is the same as null, but it will not be displayed in DataGridView anyway. It’s just that if we need to filter the null element of DataTable, we need to pay attention to this issue.
If a certain line of string type takes the main body in Excel, then the column in DataTable will be set to string type, and any type can be successfully converted to string type. Therefore, the Excel class will be displayed in full, regardless of the string type cells in this column or the integer type cells, it can be displayed in full. This is a very special place. But this is just a special case.
Therefore, if the data type consistency during processing, if the data type in Excel is confused, IMEX=1 can be used to convert all columns in DataTable into character types.
Next, let’s talk about a related problem, that is, the problem of using statements in DataTable for filtering. At this time, you should also pay attention to the type of Column in DataTable. In the following example, F1, F2, F3, etc. are all column names of DataTable.
In the following example, column F1 is a numerical type and column F5 is a string type
DataRow[] rows = ("F1='1540' andF5='NO2'");
At this time, the program will not report an error because F1='1540'. Although the filtering condition gives the string type 1540 that does not match the table, the system will convert it itself, so it is OK to write F1='1540' and F1=1540 here. The F5 column is a string type, and F5='NO2' is no problem.
Now let’s look at another situation. Column F1 is a numerical type and column F5 is a numerical type.
DataRow[] rows = ("F1='1540' andF5='NO2'");
When F5='NO2' is encountered, the system will automatically convert. At this time, since it cannot be converted into a numerical type, an exception will be thrown when filtering here. Operator= can't perform on and
Let's look at another situation. Column F1 is a numerical type and column F5 is a character type.
DataRow[] rows = ("F1=1540 andF5='NO2'");
This situation is also OK. F1=1540, because F1 itself is a numerical type, so there is no need to quote 1540.
However, we pay attention to a special case where column F1 is a numerical type and column F5 is a character type.
DataRow[] rows = ("F1=1540 and F5=NO2');
In this case, an error will be reported. The system will not automatically convert NO2 into a string, but will regard Grade2 as a column. The system judges that the cell values of the two columns in this row are equal, and there is no column name in the DataTable, so an exception that the column cannot be found will be thrown. Therefore, when a column is of string type, you must add single quotes, otherwise an exception will be thrown.
I hope this article will be helpful to everyone's C# programming.