We are using Aspose cell in our project to copy data from one sheet in an excel file to another exel sheet , run some formula on 2nd sheet & save the result as csv..below is the sample code
In order to start supporting excel 2007 , I downloaded the lastest version of Aspose Cell from ur website(v1.9.5.0) & the same code genertaed output csv with some blanks rows between rows making the result csv extremely big.
I remember having similar issue before , after I had downloaded a previous latest version ..but Aspose team provide me a fix & it worked...Please provide a fix so that same previous functionaility can work along with excel 2007.
Could you elaborate your issue and explain it more. We appreciate if you could provide us your input and template excel files (etc.) to reproduce the issue. We will check it soon.
By the way, could you give a try with the latest fix (1.9.5.22) downloading @: https://forum.aspose.com/t/87401 and check if it works fine.
I have attached the input & template xls files ...Once u run the sample code , please open the csv file..it gives warning on opening & when it opens u will see blank rows between data rows...
Please let me know if you need any other info
FYI, the version u mention(v1.9.5.22) doesnot fix the issue
Well, I found the issue (blank rows are inserted between data rows in the .csv file) although I did not get any warning message opening the .csv file into MS Excel (2000,2003, 2007). We will figure out the issue soon.
When I replaced the template file (in the example above) with one in similar as above but in excel 2007 version..the result output I got was a blank file with just headers..formulas were not evaluated
The fix works..but I ran into a new problem.. The template file I increased the number of formulas to 70K rows..it gives me following error..I have java heap size to 2G still doesnot work..
The same input file works with 65K formulas in excel 2003 version template..Please help..this is getting very critical to be fixed
-----------------
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space at java.util.ArrayList.(ArrayList.java:113) at org.dom4j.tree.AbstractElement.createAttributeList(AbstractElement.java:1641) at org.dom4j.tree.DefaultElement.attributeList(DefaultElement.java:1001) at org.dom4j.tree.AbstractElement.setAttributes(AbstractElement.java:528) at org.dom4j.io.SAXContentHandler.addAttributes(SAXContentHandler.java:916) at org.dom4j.io.SAXContentHandler.startElement(SAXContentHandler.java:249) at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:533) at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:330) at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(XMLDocumentFragmentScannerImpl.java:1693) at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:368) at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:834) at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:764) at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:148) at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1242) at org.dom4j.io.SAXReader.read(SAXReader.java:465) at org.dom4j.io.SAXReader.read(SAXReader.java:343) at com.aspose.cells.ab.a(Unknown Source) at com.aspose.cells.aX.(Unknown Source) at com.aspose.cells.dv.a(Unknown Source) at com.aspose.cells.Workbook.a(Unknown Source) at com.aspose.cells.Workbook.open(Unknown Source) at com.aspose.cells.Workbook.a(Unknown Source) at com.aspose.cells.Workbook.open(Unknown Source) ----------
I simulated your situation, with the template.xlsx you attached in prevoius post, when the sheet "CI" extends its rows to 70K more with formulas, the generated excel file would be about 30-35M. As you know, for Excel2007 xlsx file format, its data is saved in XML files and then compress XML files into Zip format file. In fact, for the template.xlsx, the size of XML file for data of sheet "CI" is about 220-230M. Currently we use DOM to parse the XML file, to build such a DOM object, only this one XML file will need more than 1G memory. We will look into parsing the XML data by SAX mode, but that need some more time for us to rebuild our data model and implement such a feature.
For your situation currently, I think you'd better split the data of this sheet into multiple sheets, and use FileFormatType parameter to specify Excel file format as EXCEL2007 when open the file with API Workbook.open() rather than let Aspose.Cells component to determine the file format automatically. Thank you.
1) Splitting the files into multiple sheets is not possible as the input file is csv..creating multiple files has some application issues..hence can't be supported
2) Tried by using FileFormatType parameter as EXCEL2007when opening template file..doesn't work..
I understand implementing SAX parsing is not a 1 day fix..but if you could provide me some sort ETA when this fix would be available..that would be really helpful
I migrated to v1.9.5.25 (with fixes above) . I found an issue when the getting the output in csv. In the same code as above but with different set of input/output file ( see attached samples).
Instead of getting output as "1236,,-123.00".. I am getting csv output as "1236,-123.00" missing a comma.