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.
I'm having the same problem with an xlsx file generated by SpreadsheetGear (by an external vendor) that I am attempting to parse using EPPlus. When I step through my code, I can see that all the cell values are present, but they are being stored out of sequence (cell A1 value is showing up in the slot for cell A17, for example).
Similar to what Tim posted, the file created by SpreadsheetGear contains cell data that do not have cell references (A1, B3, H17,...). This seems to be a required value based on my reading of the specification document (available here) on page 19 (out of over 5,000 pages)
I have validated that this is my issue by manually editing the incoming xlsx file (you can do this by renaming the file to a .zip extension and then manually editing the appropriate sheet.xml file and adding the updated version back to the zip file, rename it to xlsx, and try again.)
In my case, I added the missing cell reference to the 'c' element under sheetData/row (r="A1" to indicate column 1, row 1, for instance) of some (but not all) of the cells that were missing this attribute. Apparently, this was enough to "trick" the EPPlus file parser into knowing how to treat the incoming data.
While informative, this knowledge doesn't really do me much good because I'm trying to process a file the generation of which is completely out of my control. Maybe for others, that won't be the case.
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");
workbook.Save();
Similar to saving this file through Excel, you should find that the resultant file is larger than before.
