Writing a large Excel sheet with 60+ columns using Apache POI
I am trying to create multiple Excel sheets using the data fetched from a database. Each Excel sheet contains 60+ columns and around 50k records (may vary). The problem is that the system is taking a lot of time (5+ minutes) and then ends up with java.lang.OutOfMemoryError: GC overhead limit exceeded exception.
I tried by reducing the number of columns to only 6 and there was a huge improvement in the turnaround time.
Here's a code that generates byte array of the Excel sheet:
int rowIndex = 0;
while (iterator.hasNext()) {
List<CustomCellDataBean> cellData = iterator.next();
// Insert generic data
Row dataContentRow = sheet.createRow((short) rowIndex);
for (int counter = 0; counter < cellData.size(); counter++) {
CustomCellDataBean cd = cellData.get(counter);
if (cd.getValue() != null) {
// switch case based on the datatype of the cell
switch (cd.getType()) {
}
}
}
rowIndex++;
}
// write to ByteArrayOutputStream and return the array of bytes
Have referred to several SO questions but unable to figure out anything useful. Would like to know if anything I should try in order to get over this problem.
Solution 1:
Without further information I can only make a guess what´s your real problem. But I can tell you that apache poi can create excel sheets with more than 1000 columns and over 20k rows with colors, styles and stuff (done already).
Make sure you use the streaming api of apache.poi API
org.apache.poi.xssf.streaming
Here is the demo of apache
Big Grid Demo
UPDATE
As it states in the demo I linked to you should maybe use the new SXSSF user model (I used that if I remember correct) because it handels all the streaming stuff for you ;-)
SXSSF (Streaming Usermodel API)
Solution 2:
It may seem a bit backwards, but I prefer to build worksheets manually when using large data sets with POI. Here's the helper class I use, to help you get started:
public class Worksheet {
private static Logger logger = Logger.getLogger(Worksheet.class);
/**
* XML data for building the worksheet.
*/
public StringBuilder data = new StringBuilder();
/**
* The name of this worksheet's entry in the XLSX file.
*/
public String zipEntryName;
/**
* Tracks the last row written to the spreadsheet.
*/
// xslx rows start at 1
// Changed lastRow to init at 0 after using startRow() for headers.
public int lastRow = 0;
/**
* Tracks the last cell written to the spreadsheet.
*/
public int lastCell = 0;
/**
* Stores any styles that have been generated using XSSF.
*/
public HashMap<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
/**
* Tracks any merged cells so that they can be appended to the worksheet XML.
*/
public List<String> merged = new ArrayList<String>();
private boolean inRow = false;
private XSSFSheet myWorksheet = null;
public void setPOIWorksheet(XSSFSheet sheet){
myWorksheet = sheet;
this.zipEntryName = sheet.getPackagePart().getPartName().getName().substring(1);
}
public XSSFSheet getPOIWorksheet(){
return this.myWorksheet;
}
/**
* Write the raw XML data of newSheets to the existing XLSX file in workbook.
* @param workbook The current XSLX file to overwrite data in.
* @param newSheets A Collection of Worksheet objects containing the XML data to insert into workbook.
* @param newFile The OutputStream to write the new XLSX file to.
*/
public static void writeWorksheetsToWorkbook(InputStream workbook, Collection<Worksheet> newSheets, OutputStream newFile)
{
ZipOutputStream zipStream = null;
try{
zipStream = new ZipOutputStream(newFile);
ZipInputStream zip = new ZipInputStream(workbook);
ZipEntry entry;
// Copy unaffected entries.
while((entry = zip.getNextEntry()) != null){
boolean found = false;
for(Worksheet ws : newSheets){
if(entry.getName().equals(ws.zipEntryName)){
found = true;
break;
}
}
if(!found){
zipStream.putNextEntry(new ZipEntry(entry.getName()));
byte[] buffer = new byte[1];
while((zip.read(buffer, 0, 1)) > -1)
zipStream.write(buffer);
}
}
// Insert XML for entries being replaced.
for(Worksheet ws : newSheets){
zipStream.putNextEntry(new ZipEntry(ws.zipEntryName));
byte[] data = ws.data.toString().getBytes();
zipStream.write(data, 0, data.length);
}
}catch(Exception e){
logger.error("Error creating xlsx", e);
}finally{
if(zipStream != null) try{ zipStream.close(); }catch(Exception e){}
if(newFile != null) try{ newFile.close(); }catch(Exception e){}
}
}
/**
* Write the raw XML data of newSheets to the existing XLSX file in workbook.
* @param workbook The current XSLX file to overwrite data in.
* @param newSheets A Collection of Worksheet objects containing the XML data to insert into workbook.
* @param return A byte[] containing the new workbook.
*/
public static byte[] writeWorksheetsToWorkbook(InputStream workbook, Collection<Worksheet> newSheets){
ByteArrayOutputStream bout = new ByteArrayOutputStream();
writeWorksheetsToWorkbook(workbook, newSheets, bout);
return bout.toByteArray();
}
public Worksheet setWorksheetName(XSSFSheet xssfWS){
zipEntryName = xssfWS.getPackagePart().getPartName().getName().substring(1);
return this;
}
/**
* Write all of the XML used for starting the worksheet.
*/
public Worksheet startWorksheet(){
data.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n");
data.append("<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" ");
data.append("xmlns:r=\"http://schemas.openxmlformats.org/officeDocument/2006/relationships\" ");
data.append("xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" >\n");
data.append("<sheetData>\n");
return this;
}
/**
* Write the XML for closing the worksheet, including merged cell data.
*/
public Worksheet closeWorksheet(){
data.append("</sheetData>\n");
if(merged.size() > 0){
data.append("<mergeCells count=\"");
data.append(merged.size());
data.append("\">");
for(String cells : merged){
data.append("<mergeCell ref=\"");
data.append(cells);
data.append("\"/>");
}
data.append("</mergeCells>\n");
}
data.append("</worksheet>\n");
return this;
}
/**
* Method for adding a new row to an Excel file. This was added as part of Period Compliance Report because
* we're not iterating over data like previous reports.
*
* This will automatically close the previous row if left open.
*/
public Worksheet startRow(){
lastCell = 0;
if(inRow)
endRow();
lastRow++;
data.append("<row r=\""+lastRow+"\">");
inRow = true;
return this;
}
/**
* Method for closing a row in an Excel file.
*/
public Worksheet endRow(){
data.append("</row>\n");
inRow = false;
return this;
}
/**
* Method for adding Date data to an Excel file cell.
* @param value The data to be added to the cell.
*/
public Worksheet addDateCell(String value){
return addTextCell(value, null);
}
/**
* Method for adding Date data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting to be used.
*/
public Worksheet addDateCell(String value, String formatting){
return addTextCell(value, formatting);
}
/**
* Method for adding String data to an Excel file cell. This was added as part of Period Compliance Report because
* we're not iterating over data like previous reports.
* @param value The data to be added to the cell.
*/
public Worksheet addTextCell(String value){
return addTextCell(value, null);
}
/**
* Method for adding String data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
*/
public Worksheet addTextCell(String value, String formatting){
return addCell(StringEscapeUtils.escapeXml(value), formatting, false, 0);
}
/**
* Method for adding String data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
* @param mergeRight The number of cells to the right of this one that should be merged.
*/
public Worksheet addMergedTextCell(String value, String formatting, int mergeRight){
return addCell(StringEscapeUtils.escapeXml(value), formatting, false, mergeRight);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
*/
public Worksheet addNumberCell(String value){
return addNumberCell(value, null);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
*/
public Worksheet addNumberCell(Number value){
return addNumberCell(value.toString(), null);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
*/
public Worksheet addNumberCell(String value, String formatting){
return addCell(value, formatting, true, 0);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
*/
public Worksheet addNumberCell(Number value, String formatting){
return addCell(value.toString(), formatting, true, 0);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
* @param mergeRight The number of cells to the right of this one that should be merged.
*/
public Worksheet addMergedNumberCell(String value, String formatting, int mergeRight){
return addCell(value, formatting, true, mergeRight);
}
/**
* Method for adding numerical data to an Excel file cell.
* @param value The data to be added to the cell.
* @param formatting Any style formatting used on the cell.
* @param mergeRight The number of cells to the right of this one that should be merged.
*/
public Worksheet addMergedNumberCell(Number value, String formatting, int mergeRight){
return addCell(value.toString(), formatting, true, mergeRight);
}
/**
* Method for adding data to an Excel file cell.
* @param value The data to be added to the cell.
* @param element The cell location on the table row.
* @param formatting The formatting style to use.
* @param mergeRight The number of cells that should be merged to the right.
* @return This Worksheet.
*/
private Worksheet addCell(String value, String formatting, boolean number, int mergeRight){
String ref = addCell(value, formatting, number);
if(mergeRight > 0){
String right = null;
for(int i = 1; i <= mergeRight; i++)
right = addCell("", formatting, false);
merged.add(ref+":"+right);
}
return this;
}
/**
* Method for adding data to an Excel file cell.
* @param value The data to be added to the cell.
* @param element The cell location on the table row.
* @param formatting The formatting style to use.
* @return A String with the new cell's location.
*/
private String addCell(String value, String formatting, boolean number){
String ref = new CellReference(lastRow-1,lastCell).formatAsString();
data.append("<c ");
if(formatting != null && styles.containsKey(formatting)){
XSSFCellStyle style = styles.get(formatting);
data.append("s=\"");
data.append(style.getIndex());
data.append("\" ");
}else if(formatting != null)
logger.debug("could not find style "+formatting);
data.append("r=\"");
data.append(ref);
data.append((number) ? "\">" : "\" t=\"inlineStr\">");
/*if(formatting == null) data.append((number) ? "\">" : "\" t=\"inlineStr\">");
else{
data.append("\" t=\"");
data.append(formatting);
data.append("\">");
}*/
data.append((number) ? "<v>" : "<is><t>");
data.append(value);
data.append((number) ? "</v>" : "</t></is>");
data.append("</c>");
lastCell++;
return ref;
}
/**
* Adds a bunch of cells to a row quickly.
* @param fields The fields to be added.
*/
public Worksheet quickAdd(String... fields){
if(!inRow)
startRow();
for(int i = 0; i < fields.length; i++)
addTextCell(fields[i]);
return this;
}
}