user2739418 user2739418 - 1 year ago 185 Question

SpreadsheetGear Excel using EPPLUS/NPOI not readable

Have anyone comes across this? I receive file from client which is generated through SpreadsheetGear. It looks fine if I open it in excel

But it can't find data into cells if I try to read through EPPLUS or NPOI

I believe SpresheetGear misses some of the properties. But which one I don't know

It works, if I open the excel file and then save as different file (size increases) and then use the file through EPPLUS/NPOI.

Not sure what to tell them as I don't know what Spreadsheetgear misses.

Answer Source

It could be that EPPlus depends on certain cell address-related attributes that are marked as "Optional" in the Open XML file format, but which SpreadsheetGear does not write out by default, in order to reduce the size of the file.

If you have access the SpreadsheetGear application that is generating these files, you might try altering it to write out these optional attributes to the file format by setting the IWorkbookSet.Experimental property to the string OleDbOpenXmlWorkaround prior to saving the file to disk. For instance:

SpreadsheetGear.IWorkbookSet workbookSet = SpreadsheetGear.Factory.GetWorkbookSet();
workbookSet.Experimental = "OleDbOpenXmlWorkaround";
SpreadsheetGear.IWorkbook workbook = workbookSet.Workbooks.Open(@"C:\myWorkbook.xlsx");

Similar to saving this file through Excel, you should find that the resultant file is larger than before.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download