Converting ibfs (Downloaded Excel files from websites) files to .xls/.xlsx file format without installing Office using C# Asp.net Web application

2

I have a requirement where I need to format input files from different resources. My application receives input (xls/xlsx/ibfs) files through file upload and performs condition checks and generates formatted output in .xlsx file format. All my input files are reports generated by different online public data reporting websites. When I downloaded in excel format some websites are producing in “WFServlet.ibfs” file formats.

This is not a duplicate post. I tried different approaches and followed suggestions here and tried several ways but didn't solve my issue. Sorry for the long post. I want to have seniors advise or help to solve this problem.

I’m able to handle xls and xlsx file formats with using C# OLEDB ACE engine. It’s working perfectly fine in my local machine and also in my locally hosted IIS server. But when I upload .ibfs file formats I’m getting issues.

Sample input

enter image description here

Here I'm posting my most effective two different approaches:

Approach 1 (Using Microsoft Office Interop)
Approach 2 (Using third party EPPlus library)

1. Approach 1 (Using Microsoft Office Interop):

In this approach, I used Microsoft Interop dlls. Initially before conversion I changed the extension of .ibfs to .xls and then I used Microsoft Interop to convert xls into xlsx file format. In this approach its working fine in my local machine. But it’s not working in my local IIS server (In my local IIS server I’m able change the extension from .ibfs to .xls but after that it’s not creating xlsx file from xls). I added dlls of Office12 "Microsoft.Office.Interop.Excel.dll" and "Office.dll" to my project reference.

But with this approach I may have a problem in future. Currently Office is installed in my local machine, but when we move code to the server there we don’t have Office installed and client don’t want to install Office in the server. I'm not sure whether it will work in the server with the same dll's without installing the office.

Below is the code:

Step 1: Change extention from .ibs to .xls and call conversion method, if user uploaded file is .ibfs file type

 string path ="C:\\testinput\\";
     string extension = Path.GetExtension(InputFile.FileName); // get the extension of user upload file 
     string fileName = "testFile"+ extension; // make a new name to assign to the user uplaoded file
     InputFile.SaveAs(path + fileName); // save the user uploaded file into the testinput folder with testFile file name
     inputFileWithPath = path + fileName; // copy the path of saved file "C:\\testinput\\testFile+extenstion"
     newPath = inputFileWithPath; // used if input file is of .ibfs or .xls extn
     if (extension.Equals(".IBFS") || extension.Equals(".ibfs"))
     {
     //input user uploaded file extension is .ibfs , If file already exist in the upload folder path then delete the old one before File.Move
         if (File.Exists(newPath + ".ibfs"))
             {
             File.Delete(newPath);
             }
             else
             {
             newPath = Path.ChangeExtension(inputFileWithPath, ".xls"); // chnage the file extension from .ibfs to .xls 
             File.Move(inputFileWithPath, newPath); // move the new file .xls to testinput path 
             inputFileWithPath = excelComm.convertExel(newPath); // convert the .xls file into .xlsx file format
             }
     }

Step 2 Now conversion logic from .xls to xlsx using Interop

public string convertExel(string FilePath)
    {
       string path = "";
       var app = new Microsoft.Office.Interop.Excel.Application();
       try
        {
        if (File.Exists(FilePath + "x"))  // check if file with .xlsx is already exist, if exist delete it
         { File.Delete(FilePath + "x"); }
        else
        {
        var wb = app.Workbooks.Open(FilePath);
        wb.SaveAs(Filename: FilePath + "x", FileFormat: Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook);
        path = FilePath + "x";
        wb.Close();
        }
        } // end of try
        catch (Exception ex)
         {
         string errorMsg = "";
         CatchException(ex, errorMsg);
         }
        return path;
    }

2. Approach 2 (Using third party EPPlus library): I downloaded EPPlus.dll and added to my project reference. I used below code. Which is basically changes the extension of .ibfs to xls and calls the convertExcel methods, where it converts the xls into dataset from that dataset , I copied data table into to the workbook sheet and saved it as .xlsx file. But it is not working.

Below is the code sample

Step 1: Change extension from .ibs to .xls and call conversion method, if user uploaded file is .ibfs file type

Step 1 is same as above as mentioned in Approach 1.

Step 2: Conversion from .xls to xlsx using EPPlus library. For this I followed solution from C# - convert xls file to xlsx without office components

public string convertExel(string FilePath)
  {
   string path = "";
    try
    {
    if (File.Exists(FilePath + "x"))// check if file with .xlsx is already exist, if exist delete it
    {File.Delete(FilePath + "x");}
    else
   {
    string fileName = Path.GetFileNameWithoutExtension(FilePath);
    string filePathXlsx = "C:\\testinput\\"+ fileName + ".xlsx ";
    using (ExcelPackage epackage = new ExcelPackage())
    {
    ExcelWorksheet excel = epackage.Workbook.Worksheets.Add("Sheet1");
    DataSet ds = ReadExcelFile(FilePath); // Causing Error HERE
    DataTable dtbl = ds.Tables[0];
    excel.Cells["A1"].LoadFromDataTable(dtbl, true);
    System.IO.FileInfo file = new System.IO.FileInfo(filePathXlsx);
    epackage.SaveAs(file);
    path = filePathXlsx;
    } // end of using
    }// end of else
    }//end of try
    catch (Exception ex)
    {
    string errorMsg = "";
    CatchException(ex, errorMsg);
    }
    return path;
    } // end of method

// generate dataset from excel file

   private static DataSet ReadExcelFile(string FilePath)
  {
    string constr = "";
    DataSet ds = new DataSet();
    string extension = Path.GetExtension(FilePath);
    if (extension.Equals(".xls", StringComparison.CurrentCultureIgnoreCase))//Checking for the extentions, if XLS connect using ACE OleDB
            {
                constr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\"";
            }
            //Use ACE OleDb if xlsx extention
            else if (extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
            {
                constr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\"", FilePath);
            }
            else
            {
                constr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\"";
            }

    using (OleDbConnection conn = new OleDbConnection(constr))
      {
       conn.Open(); // causing error HERE
       OleDbCommand cmd = new OleDbCommand();
       cmd.Connection = conn;
       DataTable dtSheet = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });  // Get all Sheets in Excel File
       foreach (DataRow dr in dtSheet.Rows) // Loop through all Sheets to get data
        {
         string sheetName = dr["TABLE_NAME"].ToString();
         cmd.CommandText = "SELECT * FROM [" + sheetName + "]"; // Get all rows from the Sheet
         DataTable dt = new DataTable();
         dt.TableName = sheetName;
         OleDbDataAdapter da = new OleDbDataAdapter(cmd);
         da.Fill(dt);
         ds.Tables.Add(dt);
         } // end of for

        cmd = null;
        conn.Close();
        } // end of using

      return ds;
   }

Its giving me error “System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format. at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)”

Its changing the extension from .ibfs to xls but after that its not generating any xlsx file. I tried with different connection strings, ACE, Jet engines with Xml format, HTML import, with single quotes and double quotes but nothing is working. Is it problem with the downloaded web excel file in any specific format which is not supported by OLEDB? I'm not sure how to handle such type of specific formats.

I appreciate if any one can give me any idea how I can solve the problem with the 'ibfs' file formats.

My Latest update: I tried with the Spire.XLS but it didn't work with '.ibfs' file formats. Its just working fine with xls and xlsx formats.

Just one request, please only suggest open-source dll's. I can't install any software in client machine (server). I have only option to use open-source libraries like EPPlus or anything supported by just dll's without any installation. Thank you.

c#
asp.net
excel
oledb
epplus
asked on Stack Overflow May 25, 2016 by priya777 • edited May 23, 2017 by Community

2 Answers

0

Try replacing Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\" with Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES\" in

//Use ACE OleDb if xlsx extention
        else if (extension.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase))
        {
            constr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\"", FilePath);
        }
answered on Stack Overflow May 25, 2016 by Pushpendra • edited May 25, 2016 by Pushpendra
0

If IBFS files are Excel files, you can try using Essential XlsIO. I couldn't find any IBFS files to check with.

The whole suite of controls is available for free (commercial applications also) through the community license program if you qualify. The community license is the full product with no limitations or watermarks.

Note: I work for Syncfusion.

answered on Stack Overflow May 25, 2016 by Davis Jebaraj

User contributions licensed under CC BY-SA 3.0