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#.