C# 3.5 Reading Excel files

Question

I have a strange behavior when i try to read an XML worksheet using this code :

string CONNEC_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No;IMEX=2;\"";
            string fullFilePath = @"C:\Tmp\TestFile.xls";
            using (OleDbConnection objCon = new OleDbConnection(string.Format(CONNEC_STRING, fullFilePath)))
            {
                using (OleDbCommand cm = new OleDbCommand("Select * From [MYCELLSRANGE]", objCon))
                using (OleDbDataAdapter da = new OleDbDataAdapter(cm))
                {
                    DataTable dt = new DataTable();
                    objCon.Open();
                    da.Fill(dt);
                    objCon.Close();
                }
            }

If the Excel file is closed, I receive the error 'External table is not in the expected format.' When I open the file, if I execute the above code, it works fine and I can read data contained in MYCELLSRANGE. So, has anyone any idea about this problem ? Thanks for answers.

Answer 1

I believe its because the Jet Engine uses the Excel app to interpret the file. I ran into this issue once myself. When you use the Excel.dll reference and you interpret the Excel file from that you have to create a new Application instance to read the file. Jet just does this very thing to get the data. I suggest using that code instead.

• C# Corner example of how to Import Excel to a ListView
• C# Corner example of how to Create an Excel Worksheet in C#
• MSDN users discussing the same topic

If you would like further assistance, let me know. I have successfully done this in VB.NET in various applications of mine.