Read large size xlsx file and copy all

190 Views Asked by At

I am trying to open a large size(>30mb) .xlsx and copy all the rows and columns(consists of >200k rows) in that sheet into a new workbook sheet. I got an error on the following code:

FileInputStream fis = new FileInputStream(file);
XSSFWorkbook newWorkBook = new XSSFWorkbook(fis);

Increasing heap space does not help. After much research, i understand that a work around is either to use XSSF and SAX (Event API) or XLSX2CSV.java. I just need to copy the whole data from old sheet to new sheet. Somehow after trying SAX, i am stuck as i am not sure how to get the value from old sheet to copy to new sheet. Also, empty cells are not included in the SST. i need to copy over all cells, inclusive of empty cell.

    @Override
    public void startElement(String uri, String localName, String name,
            Attributes attributes) throws SAXException {
        // c => cell
        if(name.equals("c")) {
            //print cell reference
            System.out.print(attributes.getValue("r") + " - ");
            // Figure out if value is an index in the SST
            String cellType = attributes.getValue("t");
            System.out.println("CellType " + cellType);
            if(cellType != null && cellType.equals("s")) {
                isNextString = true;
            }else {
                isNextString = false;
            }
        }
        //Clear last content
        lastContents = "";
    }

I am using Java, though i can try on C#.

0

There are 0 best solutions below