locked
Cant open xlsx file using Asp.net/C# app. RRS feed

  • Question

  • HI,

    I am using upload file control from asp.net and trying to read xlsx file but I am stuck with problem "External table is not in the expected format". I can upload xls files. I tried various connection strings. Did I miss any thing in xlsx connection string? I even tried linqtoExcel still get the same error "Ëxternal table is not in the expected format."

      private void ExcelConn(string FilePath)
            {
                string extension = Path.GetExtension(FilePath);
                //constr =
                switch (extension)
                {
                    case ".xls":
                        
                        constr = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=""Excel 8.0""",FilePath);

                        break;
                    case ".xlsx":
                        constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source= {0}; Extended Properties=""Excel 12.0 Macro; HDR=YES""",FilePath);

               
                        break;

                }
                Econ = new OleDbConnection(constr);
            }

            private void connection()
            {
                sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;
                con = new SqlConnection(sqlconn);
            }


            private void InsertExcelRecords(string FilePath)
            {
                try
                {
                    ExcelConn(FilePath);
                    var dsExcel = new System.Data.DataSet();
                    DataTable dtExcel = new DataTable();
                    OleDbCommand MyCommand = new OleDbCommand();
                    //Select Taxable Vehicles from sheet[VECHILE DETAILS]
                    OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet1$]", Econ);
                    da.Fill(dsExcel);
                    dtExcel = dsExcel.Tables[0];
                    connection();
                    //creating object of SqlBulkCopy    
                    SqlBulkCopy objbulk = new SqlBulkCopy(con);
                    //assigning Destination table name    
                    objbulk.DestinationTableName = "TestEmployeeinfo";
                    //Mapping Table column    
                    //objbulk.ColumnMappings.Add("id", "id");
                    objbulk.ColumnMappings.Add("Name", "Name");
                    objbulk.ColumnMappings.Add("City", "City");
                    objbulk.ColumnMappings.Add("Address", "Address");
                    //inserting Datatable Records to DataBase    
                    con.Open();
                    objbulk.WriteToServer(dtExcel);
                    con.Close();
                }
                catch (Exception)
                {

                    throw;
                }
            }

            protected void btnImport_Click(object sender, EventArgs e)
            {
                string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
                InsertExcelRecords(CurrentFilePath);
            }

    Wednesday, September 2, 2015 1:27 PM