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.