Reading from Excel Sheet usng OleDb

See more:

C#

XML

ADO.NET

Hi,

I have an excel sheet which holds the test results in the form of XML. When I open the workbook in excel, asks to select Open as an xml table.

I have written the code for retrieving the records from the same excel sheet using OleDb provider of ADO.Net. Below is the Code.

OleDbConnection objConnection = new OleDbConnection();

                    string con = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ strSourcePath + "\\" + row.Cells[2].Value.ToString() + ".xls" + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1;\"";
                    objConnection.ConnectionString = con;

 

                    OleDbCommand objCommand = new OleDbCommand("SELECT * FROM [Sheet1$]", objConnection);
                    OleDbDataAdapter da = new OleDbDataAdapter(objCommand);

                    DataSet ds = new DataSet();

                    da.Fill(ds);

                    dataGridView2.DataSource = ds.Tables[0];

It is throwing an OleDbException, "External table is not in the expected format." when the Fill metod is called.

Please suggest a resolution for this.

Thanks & Regards,
Raghuveer