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

    How to read .xlsb file and convert to data table in c#

    How to read .xlsb file and convert to data table in c# with out help of 'Microsoft.Office.Interop.Excel.Application()'

    with help of free DLL.

    S.Tamil
  • #765384
    I recommend to use given code snippet to Convert a .xlsb file to data table in c#

    protected void FillDataSet()
    {

    try
    {

    string strFilename = FileUpload.PostedFile.FileName;
    strFilename = System.IO.Path.GetFileName(strFilename);
    string ext = Path.GetExtension(strFilename);
    bool hasHeaders = true;
    string HDR = hasHeaders ? "Yes" : "No";
    string strConn;



    if (ext.ToLower() == ".xls")
    {

    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fu + " ;Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=1\"";
    Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
    app.Visible = false;

    OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
    myCmd.Fill(myDs);
    string source = @"F:\yourdrive\" + strFilename;
    string target = @"F:\yourdrive\temp\" + strFilename;

    if (File.Exists(target))
    File.Delete(target);
    File.Move(source, target);
    }

    if (ext.ToLower() == ".xlsx")
    {


    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fu + ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=2\"";
    Microsoft.Office.Interop.Excel.WorkbookClass workBook = (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open(fu, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
    oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
    app.Visible = false;
    OleDbDataAdapter myCmd = new OleDbDataAdapter("SELECT * FROM [" + oSheet.Name + "$]", strConn);
    myCmd.Fill(myDs);
    string source = @"F:\Exadrive\" + strFilename;
    string target = @"F:\Exadrive\temp\" + strFilename;



    if (File.Exists(target))
    File.Delete(target);
    File.Move(source, target);


    }
    }
    catch (Exception ex)
    {
    Response.Write("Error,!");
    }
    }

  • #765385
    Is possible to convert without this dll Microsoft.Office.Interop.Excel


Sign In to post your comments