You must Sign In to post a response.
  • Category: Windows

    External table is not in the expected format when XLS file read in window application (ASP.net c#

    I want to read excel file data and show into grid in window application.
    I am getting External table is not in the expected format. is error again and again. how i can fix this error.

    i have installed the ddl as per required.
    1) Microsoft.Office.Interop.Excel
    2) Microsoft.Office.Interop.Access.Dao
    3) Interop.Microsoft.Office.Core

    Also add into the namespace.

    Code is given below:

    private void btnCreateFolder_Click(object sender, EventArgs e)
    {
    string filePath = string.Empty;
    string fileExt = string.Empty;
    OpenFileDialog file = new OpenFileDialog(); //open dialog to choose file
    if (file.ShowDialog() == System.Windows.Forms.DialogResult.OK) //if there is a file choosen by the user
    {
    filePath = file.FileName; //get the path of the file
    fileExt = Path.GetExtension(filePath); //get the file extension

    if (fileExt.CompareTo(".xls") == 0 || fileExt.CompareTo(".xlsx") == 0 || fileExt.CompareTo(".XLS") == 0 || fileExt.CompareTo(".XLSX") == 0 || fileExt.CompareTo(".csv") == 0 || fileExt.CompareTo(".CSV") == 0)
    {
    try
    {

    System.Data.DataTable dtExcel = new System.Data.DataTable();
    dtExcel = ReadExcel(filePath, fileExt); //read excel file
    dataGridView1.Visible = true;
    dataGridView1.DataSource = dtExcel;
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message.ToString());
    }
    }
    else
    {
    MessageBox.Show("Please choose .xls or .xlsx file only.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Error); //custom messageBox to show error
    }
    }
    }

    public System.Data.DataTable ReadExcel(string fileName, string fileExt)
    {
    string conn = string.Empty;
    System.Data.DataTable dtexcel = new System.Data.DataTable();

    string Import_FileName = fileExt;


    if (fileExt.CompareTo(".XLS") == 0 )

    conn = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HRD=Yes;IMEX=1';"; //for below excel 2007 //comment By Nitin

    else

    conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=NO';"; //for above excel 2007

    using (OleDbConnection con = new OleDbConnection(conn))
    {
    try
    {
    OleDbDataAdapter oleAdpt = new OleDbDataAdapter("select * from [Sheet1$]", con); //here we read data from sheet1
    oleAdpt.Fill(dtexcel); //fill excel data into dataTable
    }
    // catch { }

    catch (Exception ex)
    {

    }



    }
    return dtexcel;
    }
  • #768721
    Could you please share the error provided in your code

  • #768731
    Hi,

    What error did you got?

    Please share the error details to help you better.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments