I want to import an excel sheet to DataTable. i use the following code:
string path = @"" + /****path of excel file****/;
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;";
OleDbConnection xlConn = new OleDbConnection(connectionString);
xlConn.Open();
OleDbCommand selectCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", xlConn);
OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
dataAdapter.SelectCommand = selectCommand;
itemMasterUploadDataTable = new System.Data.DataTable();
dataAdapter.Fill(itemMasterUploadDataTable );
xlConn.Close();
Everything works fine except for the left aligned values in the excel sheet.
The above image shows a part of one of the columns of the excel sheet. The right aligned values(1511, 1511, 2202, 2202) are getting entered into datatable properly but the left aligned values(0450, 0405, 0406, 0406, 0406, 0406, 0401) are getting entered as null. what am i doing wrong? I want to import all the values into the datatable.
It will work if you do this in excel file: