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
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.
