CODEWITHSUNDEEP
c#exceloledb
neelam paliwal
neelam paliwal

Reputation: 39

C# exception: External table is not in the expected format. while using excel with oledb

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?

Upvotes: 2

Views: 6615

Answers (1)

Kailash Bhakuni
Kailash Bhakuni

Reputation: 407

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.

Upvotes: 2

Related Questions