Working with Excel files in C#

Question

I have Excel files that are in 2000 & 2003 format. I need to import them via C# code into an access DB. I have written a method to read the file into a data table. No matter which connection string i use (I have checked the other posts on this topic) I continue to get "Table is not in the correct format" error. Can someone please explain to me what I am doing wrong.

        public static DataSet ParseExcel(string excelFile)
        {
            string sheetName = Path.GetFileNameWithoutExtension(excelFile);
            string excelQuery = @"SELECT * FROM [" + sheetName + "]";
            string excelConnctionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "" + excelFile + "" +
                                    @";Extended Properties=" + "" + @"Excel 8.0;HDR=Yes;" + "";
            if(File.Exists(excelFile))
            {
                var myConnection = new OleDbConnection(excelConnctionString);
                myConnection.Open();
                var myCommand = new OleDbDataAdapter(excelQuery, excelConnctionString);
                myCommand.TableMappings.Add("Table", "TestTable");
                var dtSet = new DataSet();
                myCommand.Fill(dtSet);
                myConnection.Close();
                return dtSet;                
            }
            return null;
        }

Answer 1

Go through this code example carefully and try to understand the work flow. You will get it real easy to write any kind programs for accessing excel data according to your requirements.

1.Here I just have a Upload Field in order to select the Excel File in .aspx file

<asp:FileUpload ID="Upload_File" runat="server" />
<asp:Button ID="Upload_Button" runat="server"  Text="Upload" onclick="btnUpload_Click"/>
<asp:GridView ID="Gridview_Name" runat="server">
</asp:GridView>

2. Now lets see what happens in code behind file (.aspx.cs file)

protected void Upload_Button_Click(object sender, EventArgs e) { string connectionString = ""; if (Upload_File.HasFile) // checking whether file is selected to be uploaded

{
       //getting name of the file
 string fileName = Path.GetFileName(Upload_File.PostedFile.FileName);  
//getting extension of the file (for checking purpose - which type .xls or .xlsx)
string fileExtension = Path.GetExtension(Upload_File.PostedFile.FileName);  
string fileLocation = Server.MapPath("" + fileName);    //exact location of the excel files
Upload_File.SaveAs(fileLocation);
 //Check whether file extension is xls or xslx

 if (fileExtension == ".xls")
 {
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
     }
 else if (fileExtension == ".xlsx")
    {
   connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
      }   
     //Create OleDB Connection and OleDb Command

                    OleDbConnection con = new OleDbConnection(connectionString);
                    OleDbCommand cmd = new OleDbCommand();
                    //cmd.CommandType = System.Data.CommandType.Text;
                    cmd.Connection = con;
                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(cmd);
                    DataTable dtExcelRecords = new DataTable();
                    con.Open();
DataTable dtExcelSheetName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string getExcelSheetName = dtExcelSheetName.Rows[0]["Table_Name"].ToString();
                    cmd.CommandText = "SELECT * FROM [" + getExcelSheetName + "]";
                    dAdapter.SelectCommand = cmd;
                    dAdapter.Fill(dtExcelRecords);
                    con.Close();
                    Gridview_Name.DataSource = dtExcelRecords;
                    GridView_Name.DataBind();
                }
                else
                {
                    Response.Write("Please Select a File to extract data ");
                }
            }

Step by Step Explanation :

• We get the file name, extension, location .
• And check it is the type (.xls or .xlsx - particularly for excels of 2003 or other formats).
• Set connection strings according to the previous step.
• Open oledb connection
• Create the necessary data adapter and data table
• open the connection
• store the current table( where the data from excel is stored) in a datatable instance
• Store the name of the sheet in astring by getting it from the current table
• Fill the data adapter object(dAdapter) with the our data table (dtExcelRecords)
• close the connection
• Set datasource for the grid as out data table.
• Bind it with our grid.

...And We are done!

Hope it helps.

Answer 2

Try this

OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";