Reading excel data into datatable
-
Hello Friends, Recently i have tried to load a excel data into a data table.But when it load data into data table some of cell of data table left blank.But there have data into excel cells.I have not found any special character in those cell.I made column format into text.
-
Hello Friends, Recently i have tried to load a excel data into a data table.But when it load data into data table some of cell of data table left blank.But there have data into excel cells.I have not found any special character in those cell.I made column format into text.
Excel by default has a very loose type binding, i.e. if you set the column's formatting to text and insert a numeric value in it then it treats the data as text. When you set the format to numeric and enter textual data it changes the type to general which accepts all data values. Now, Datatables in ADO.NET are type sensitive and are tight binding. When you execute a query against a cell range in excel .NET sets the datatype of the column as the data type of the first cell in that column in excel. Now if there is data of other type in that row then that data is ignored when reading the cells and is left as NULL in the data table. As far as I know the cell formatting does not matter, the ADO.NET engine analyzes the data in the first cell and sets the data type. You cannot control the way it is done. So, if you have text data and numeric data in the same column then make sure that the fist row has text data than numeric data. HTH!