I have the Excel file with 5 columns as the below picture:
Take a look on TentacCD column, the Item code can be all number digit or combined with character letter. My problem is when loading this Excel file I can get all the value except the TentacCD where Item code contains both number and letter. It will return the empty value (nothing). For Example : TentacCD : 3020M4370, 3020H0002 will return nothing. Other ones return exactly value.Please see the result picture when debugging:
I found that if TentacCD contain the Item Code with same format look like: 3020H0001,3020M0002,3020L0003,3020P0004,3020Q0005,... It will return all values correctly. I could not find any document explain why this error happen. Here is my code:
if (result == DialogResult.OK)
{
oXL = new Microsoft.Office.Interop.Excel.Application();
oXL.DisplayAlerts = false;
mWorkBook = oXL.Workbooks.Open(filePath, 0, true, 5, "", "", true, XlPlatform.xlWindows, "", false, false, 0, true, false, false);
mWSheet1 = (Worksheet)mWorkBook.Worksheets["アソート表"];
OleDbConnection OleDbConn = new OleDbConnection(conString);
OleDbConn.Open();
string trim_sheet1 = "ABC";
dtExcelData.Columns.AddRange(new DataColumn[5]
{
new DataColumn("TentacCD", typeof(String)),
new DataColumn("サイズ", typeof(String)),
new DataColumn("カラー", typeof(String)),
new DataColumn("数量", typeof(Int32)),
new DataColumn("Key", typeof(String))
});
string sqlcmd = string.Format(@"select [TentacCD],[サイズ],[カラー],[数量],[Key] FROM [" + trim_sheet1 + "$A10:Q50]");
OleDbCommand comm = new OleDbCommand();
comm.Connection = OleDbConn;
comm.CommandType = CommandType.Text;
comm.CommandText = sqlcmd;
OleDbDataAdapter oda = new OleDbDataAdapter();
oda.SelectCommand = comm;
oda.Fill(dtExcelData);
mWorkBook.Close(Type.Missing);
mWSheet1 = null;
mWorkBook = null;
oXL.Quit();
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
OleDbConn.Close();
}
return dtExcelData;
}