2

If anyone can help me out it will be very grateful. I am trying to read an excel (.xlsx, excel-2007) which have different sheets (Headers are not fixed). The below code works for me in most of the cases, but throws exception in some of the cases as entitled. 

        public static bool ReadExcelData(string ExcelFilePath, string SheetName, out DataTable dt)
        {
            dt = new DataTable();

            bool isXlsx = ExcelFilePath.Substring(ExcelFilePath.LastIndexOf('.') + 1).ToLower() == "xlsx";
            string excelConnectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;HDR=yes;\"";
            if (isXlsx)
                excelConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0\";";

            OleDbConnection objConn = null;
            try
            {
                objConn = new OleDbConnection(excelConnectString);
                if (objConn.State == ConnectionState.Closed)
                {
                    objConn.Open();
                    dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
            }
            catch (Exception ex)
            {
                dt = null;
                return false;
            }

            try
            {
                dt.Clear();
                string query = "select * from ["+SheetName+"$] ";                
                OleDbCommand objCmd = new OleDbCommand(query, objConn);
                OleDbDataAdapter objDatAdap = new OleDbDataAdapter();
                objDatAdap.SelectCommand = objCmd;

                objDatAdap.Fill(dt);
                Boolean result = (dt.Rows.Count >= 1) ? true : false;
                objConn.Close();
                return true;
            }
            catch (Exception ex)
            {
                dt = null;
                return false;
            }

        }

If, in case of exception, I open this excel (on which it is giving error) manually (double clicking the excel) before going in to the code, it will not generate any exception, rather reads that excel smoothly.

What can be better or alternative way so that it may work for all the cases?

neelam paliwal
  • 39
  • 1
  • 3
  • @Vladimir , The exception is- "External table is not in the expected format." – neelam paliwal Jul 03 '17 at 12:57
  • 1
    I don't have a definitive answer as it's been over a year since I last dealt with this problem, but I can offer some advice. In general, you can end up in a world of pain if you try to interop directly with Excel through the COM (component object model) using OLE. There are several libraries that have been written that wrap up all the complexity of doing this such that most of the time you can not worry about application versions and file formats. Try looking here: http://nugetmusthaves.com/Tag/Excel – Thomas Cook Jul 03 '17 at 12:59
  • 1
    You can just use the Microsoft.ACE.OLEDB.12.0 Provider. It will open both file types just fine if you specify Excel 12.0. Also open the file in notepad and make sure is's not HTML. I've had files with a .xls extension which are actually HTML tables. – Ciarán Jul 03 '17 at 13:00
  • https://stackoverflow.com/questions/1139390/excel-external-table-is-not-in-the-expected-format might be usefull – Vladimir Jul 03 '17 at 13:01

1 Answers1

2

Issue is in your excel sheet, not in your code, please just saveas your excel sheet in .xls or .xlsx format again and then use the same code. It will work.

Kailash Bhakuni
  • 389
  • 1
  • 4