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.
我有一个要求,我需要格式化来自不同资源的输入文件。我的应用程序通过文件上载接收输入(xls / xlsx / ibfs)文件,并执行条件检查并生成.xlsx文件格式的格式化输出。我的所有输入文件都是由不同的在线公共数据报告网站生成的报告。当我以excel格式下载时,一些网站以“WFServlet.ibfs”文件格式生成。
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.
我可以使用C#OLEDB ACE引擎处理xls和xlsx文件格式。它在我的本地计算机以及本地托管的IIS服务器中运行良好。但是当我上传.ibfs文件格式时,我遇到了问题。
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(使用Microsoft Office Interop)方法2(使用第三方EPPlus库)
1. Approach 1 (Using Microsoft Office Interop):
1.方法1(使用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.
在这种方法中,我使用了Microsoft Interop dll。最初在转换之前我将.ibfs的扩展名更改为.xls然后我使用Microsoft Interop将xls转换为xlsx文件格式。在这种方法中,它在我的本地机器上工作正常。但它不能在我的本地IIS服务器上工作(在我的本地IIS服务器中,我可以将扩展名从.ibfs更改为.xls但在此之后它不会从xls创建xlsx文件)。我在我的项目参考中添加了Office12“Microsoft.Office.Interop.Excel.dll”和“Office.dll”的dll。
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.
但是通过这种方法,我将来可能会遇到问题。目前Office安装在我的本地计算机上,但是当我们将代码移动到服务器时,我们没有安装Office,客户端也不想在服务器中安装Office。我不确定它是否可以在没有安装办公室的情况下在具有相同dll的服务器中工作。
Below is the code:
以下是代码:
Step 1: Change extention from .ibs to .xls and call conversion method, if user uploaded file is .ibfs file type
步骤1:如果用户上传的文件是.ibfs文件类型,则将扩展名从.ibs更改为.xls并调用转换方法
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
步骤2现在使用Interop将.xls转换为xlsx
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.
2.方法2(使用第三方EPPlus库):我下载了EPPlus.dll并添加到我的项目参考中。我使用下面的代码。这基本上是将.ibfs的扩展名更改为xls并调用convertExcel方法,它将xls转换为该数据集中的数据集,我将数据表复制到工作簿表并将其保存为.xlsx文件。但它没有用。
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
步骤1:将扩展名从.ibs更改为.xls并调用转换方法,如果用户上传的文件是.ibfs文件类型
Step 1 is same as above as mentioned in Approach 1.
步骤1与方法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
第2步:使用EPPlus库从.xls转换为xlsx。为此,我遵循C#的解决方案 - 将xls文件转换为没有办公室组件的xlsx
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
//从excel文件生成数据集
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)”
它给我的错误“System.Data.OleDb.OleDbException(0x80004005):外部表格不是预期的格式。在System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr,OleDbConnection连接)“
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.
它将扩展名从.ibfs更改为xls但在此之后它不生成任何xlsx文件。我试过不同的连接字符串,ACE,Jet引擎与Xml格式,HTML导入,单引号和双引号但没有任何工作。下载的web excel文件是否存在OLEDB不支持的任何特定格式的问题?我不知道如何处理这种类型的特定格式。
I appreciate if any one can give me any idea how I can solve the problem with the 'ibfs' file formats.
我很感激,如果有人能告诉我如何用'ibfs'文件格式解决问题。
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.
我的最新更新:我尝试使用Spire.XLS,但它不适用于'.ibfs'文件格式。它只适用于xls和xlsx格式。
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.
只需一个请求,请仅建议开源dll。我无法在客户端计算机(服务器)中安装任何软件。我只能选择使用像EPPlus这样的开源库或只有dll支持的任何东西而无需任何安装。谢谢。
2 个解决方案
#1
0
Try replacing Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\" with Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES\" in
尝试使用扩展属性替换扩展属性= \“Excel 12.0; IMEX = 1; HDR = YES \”= \“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);
}
#2
0
If IBFS files are Excel files, you can try using Essential XlsIO. I couldn't find any IBFS files to check with.
如果IBFS文件是Excel文件,则可以尝试使用Essential XlsIO。我找不到要检查的任何IBFS文件。
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.
注意:我为Syncfusion工作。
#1
0
Try replacing Extended Properties=\"Excel 12.0;IMEX=1;HDR=YES\" with Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES\" in
尝试使用扩展属性替换扩展属性= \“Excel 12.0; IMEX = 1; HDR = YES \”= \“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);
}
#2
0
If IBFS files are Excel files, you can try using Essential XlsIO. I couldn't find any IBFS files to check with.
如果IBFS文件是Excel文件,则可以尝试使用Essential XlsIO。我找不到要检查的任何IBFS文件。
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.
注意:我为Syncfusion工作。