Tuesday, July 2, 2013

Generate Large Excel Report by Using Apache POI Performance Tuning

If you are using Apache POI to generate large excel file, please take note the sheet.autoSizeColumn((short) p); line because this will impact the performance.


import java.io.DataInputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class CSVToExcelConverter {

       public static void main(String args[]) throws IOException {
              ArrayList arList = null;
              ArrayList al = null;
              String fName = "test.csv";
              String thisLine;
              int count = 0;
              FileInputStream fis = new FileInputStream(fName);
              DataInputStream myInput = new DataInputStream(fis);
              int i = 0;
              arList = new ArrayList();
              while ((thisLine = myInput.readLine()) != null) {
                     al = new ArrayList();
                     String strar[] = thisLine.split(",");
                     for (int j = 0; j < strar.length; j++) {
                           al.add(strar[j]);
                     }
                     arList.add(al);
                     i++;
              }

              try {
                     HSSFWorkbook hwb = new HSSFWorkbook();
                     HSSFSheet sheet = hwb.createSheet("new sheet");
                     for (int k = 0; k < arList.size(); k++) {
                           ArrayList ardata = (ArrayList) arList.get(k);
                           HSSFRow row = sheet.createRow((short) 0 + k);
                           for (int p = 0; p < ardata.size(); p++) {
                                  HSSFCell cell = row.createCell((short) p);
                                  sheet.autoSizeColumn((short) p); // this will slow down the performance
                                  String data = ardata.get(p).toString();
                                  if (data.startsWith("=")) {
                                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                         data = data.replaceAll("\"", "");
                                         data = data.replaceAll("=", "");
                                         cell.setCellValue(data);
                                  } else if (data.startsWith("\"")) {
                                         data = data.replaceAll("\"", "");
                                         cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                         cell.setCellValue(data);
                                  } else {
                                         data = data.replaceAll("\"", "");
                                         cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
                                         cell.setCellValue(data);
                                  }
                                  // */
                                  // cell.setCellValue(ardata.get(p).toString());
                           }
                           System.out.println();
                     }
                     FileOutputStream fileOut = new FileOutputStream("test.xls");
                     hwb.write(fileOut);
                     fileOut.close();
                     System.out.println("Your excel file has been generated");
              } catch (Exception ex) {
                     ex.printStackTrace();
              } // main method ends
       }
}



Hope this can save you time.