2

I am able to read existing xlsm using XSSF and write data into worksheet using SXSSF. Finally output it as another xlsm with Outputstream.
SXSSF is mentioned for writing xlsx in documentation
Is it right approach to read and write xlsm for huge data or the file will get corrupted if done by this solution ?
This is the sample code that works,

public static void main(String[] args) throws Throwable {

    OPCPackage pkg = OPCPackage.open(new File("sample.xlsm"));
        XSSFWorkbook wb_template;
        wb_template = new XSSFWorkbook(
            pkg
        );
        System.out.println("package loaded");
    SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); 
    wb.setCompressTempFiles(true);

    SXSSFSheet sh = (SXSSFSheet) wb.createSheet();
    sh.setRandomAccessWindowSize(100);// keep 100 rows in memory, exceeding rows will be flushed to disk
    for(int rownum = 4; rownum < 5000; rownum++){
       Row row = sh.createRow(rownum);
       for(int cellnum = 0; cellnum < 10; cellnum++){
        Cell cell = row.createCell(cellnum);
        String address = new CellReference(cell).formatAsString();
        cell.setCellValue(address);
       }

    }
    FileOutputStream out = new FileOutputStream(new File("C:\\ouput\\new_file.xlsm"));
    wb.write(out);
    out.close();  }
Sva
  • 115
  • 1
  • 12
  • SXSSF is only needed for very large files. If your code works to load with XSSF, why not just keep using that? – Gagravarr Oct 23 '13 at 08:47
  • @Gagravarr: I tried it as web application and got heap dump for concurrent users.. is there any other way to load workbook and then use sxssf ? – Sva Oct 23 '13 at 12:45

1 Answers1

3

I don't see much wrong with your current code, however on the apache website it says that SXSSF leaves temporary files on your computer, and that you must call dispose as below:

Note that SXSSF allocates temporary files that you must always clean up explicitly, by calling the dispose method.
SXSSFWorkbook wb = new SXSSFWorkbook(100);
// dispose of temporary files backing this workbook on disk
wb.dispose();

I would recommend performing the dispose as it seems to prevent leftover information being stored. On whether it is the right approach, I would suggest taking a test file and giving it a couple of goes, how well this code executes will will be affected by your system (cpu power, memory e.t.c) and the size of the documents you are processing.

Good Luck!

Levenal
  • 3,796
  • 3
  • 24
  • 29
  • I got your point,.. Will add dispose in my code,.. Is there any other approach for xlsm with large data (since XSSF results in heap size problem).. Actually i need to add huge data over xlsm and output it.. This approach takes time and CPU process – Sva Oct 23 '13 at 08:24
  • I know if you run the program from the command line you can add more heap space to it, apart from that it could just be the size of the data that causes the slowness, if you have a massive amount then there may be no way around a certain amount of processing time. Unfortunately I have never done anything with Excel and large quantities of data so am unaware of the 'best' way to go at it. – Levenal Oct 23 '13 at 08:36