1

I have the Excel file with 5 columns as the below picture:

enter image description here

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: enter image description here

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;
        }
Cát Tường Vy
  • 398
  • 6
  • 32
  • Why do you have Interop code that opens a Worksheet while you're also using OleDb to access the same(?) data? Why are you building a DataTable *manually*, if you use a DataAdapter? Don't do that. The connection string is not visible. Take the code [here](https://stackoverflow.com/a/54352568/7444103) and try just that. – Jimi Jun 08 '20 at 04:34
  • Some information [here](https://stackoverflow.com/a/55240079/7444103) may also be useful. Specify the Provider in use and the version of the Excel file. – Jimi Jun 08 '20 at 04:41
  • Because this Excel file contains more than 5 columns but I only get the 5 specific columns so I must build the DataTable manully. I use Interop to get the Worksheet Name purpose. Excel 2003 (.xls) version. As I mentioned, I can get all value except this TnetacCD columns. It looks like if it meet the first value is all number so the remaining must be the whole number too and vice verse. Thank you for your help – Cát Tường Vy Jun 08 '20 at 04:55
  • No you don't need Interop to read the WorkSheets names and you don't need to build a DataTable. To read the WorkSheet names, you use OleDb, e.g., `string sheetName = [Connection].GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString();`, where `Row[0]` is the first sheet, `Row[1]` the second etc. To read a range of columns, you specify a Range, as in `$"SELECT * FROM [{sheetName}$A10:E50]`. Use the `Microsoft.ACE.OLEDB.12.0` or `Microsoft.ACE.OLEDB.16.0` providers. Add `IMEX = 1` in the connection, as shown in the code I linked. You can also test `ImportMixedTypes=Text` and `TypeGuessRows=1` – Jimi Jun 08 '20 at 05:10
  • The last two extended selectors are probably working only changing the corresponding Registry values in your case. Search about it. You can start from here: [Initializing the Microsoft Excel driver](https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver?tabs=office-2016). It doesn't mean that is necessary. It's just something that is good to know. – Jimi Jun 08 '20 at 05:15
  • @Jimi: @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0; HDR=Yes;ImportMixedTypes=Text;TypeGuessRows=0;AppendBlankRows=0;IMEX=1\""; It works for olny 17 rows, if I add any 01 more row , the same problem happen again. Don't know ! – Cát Tường Vy Jun 08 '20 at 06:42
  • If you're still using both Interop (keeping the file open) and OleDb, difficult to say what is going to happen, even if you have set the option, in the settings panel of Excel, to share the workbook instead of opening it exclusively. + As already mentioned, use one of the other providers, `Microsoft.Jet.OLEDB.4.0` is ancient. -- Maybe post the file somewhere, I'll give it a look. – Jimi Jun 08 '20 at 13:40

0 Answers0