Hi Aspose Team,

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

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Workbook workbook = new Workbook();
String inputFileName ="c:\\temp\\input.xls";
workbook.open(inputFileName);
Worksheet inputWorksheet = workbook.getWorksheets().getSheet(0);

Workbook template = new Workbook();
template.open("C:\\temp\\template.xls");
Worksheet templateWorksheet = template.getWorksheets().getSheet(0);
templateWorksheet.copy(inputWorksheet);      
template.calculateFormula();
      
      
template.getWorksheets().setActiveSheet(1);
template.save("C:\\temp\\result.csv", FileFormatType.CSV);
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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.

Thxs,

Rohit

Hi Rohit,

Thanks for providing us the sample code

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 @: http://www.aspose.com/community/forums/permalink/144173/144392/showthread.aspx#144392 and check if it works fine.

Thank you.

Thxs Amjad for looking into this....

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

Thanks,

Rohit

Hi Rohit,

Thanks for providing us the files.

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.

Thank you.

Hi Rohit,

  Please try this fix, thank you.

Thxs Johnson ,

The fix works..will let u know if I find any other issue

Jhonson,

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

Hi,

Could you post your template file(s) here, we will check your issue soon.

Thank you.

Here you go..pls find attached

thxs,

Rohit

Hi,

Thanks for the template .xlsx file.

We found the issue, we will provide a fix here as soon as we figure it out.

Thank you for time and understanding.

Hi Rohit,

  Please try this attached fix, thank you.

Thxs Jhonson,

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.<init>(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.<init>(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)
----------

Hi,

   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.

Hi Johnson,

Thxs for looking into it..

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

Thanks,

Rohit

Hi Rohit,

  We will try to implement SAX parsing and release a new version at the end of Oct, thank you.