Writing a large resultset to an Excel file using POI

Question

This is sort of inline w  Writing a large ResultSet to a File but the file in question is an Excel file   I m using the Apache POI library to write an Excel file with a large data set retrieved from a ResultSet object  The data could range from a few thousand records to about 1 million  not sure how this translates into file system bytes in Excel format   The following is a test code I wrote to check out the time taken to write such a large result set and also the performance implication w r t CPU  amp  Memory   protected void writeResultsetToExcelFile ResultSet rs  int numSheets  String fileNameAndPath  throws Exception        BufferedOutputStream bos   new BufferedOutputStream new FileOutputStream fileNameAndPath        int numColumns   rs getMetaData   getColumnCount         Workbook wb   ExcelFileUtil createExcelWorkBook true  numSheets       Row heading   wb getSheetAt 0  createRow 1        ResultSetMetaData rsmd   rs getMetaData         for int x   0  x  lt  numColumns  x              Cell cell   heading createCell x 1           cell setCellValue rsmd getColumnLabel x 1               int rowNumber   2      int sheetNumber   0       while rs next               if rowNumber    65001                log  Sheet     sheetNumber    written  moving onto to sheet      sheetNumber   1                sheetNumber                rowNumber   2                     Row row   wb getSheetAt sheetNumber  createRow rowNumber           for int y   0  y  lt  numColumns  y                  row createCell y 1  setCellValue rs getString y 1                wb write bos                      rowNumber                 wb write bos        bos close        Not much luck with the above code  The file which is created seems to grow rapidly   70Mb per sec   So I stopped the execution after about 10 minutes  killed the JVM when the file reaches 7Gb  and tried to open the file in Excel 2007  The moment I open it  the file size becomes 8k    and only the header and the first row are created  Not sure what I m missing here   Any ideas

User · Answer

Unless you have to write formulas or formatting you should consider writing out a  csv file  Infinitely simpler  infinitely faster  and Excel will do the conversion to  xls or  xlsx automatically and correctly by definition

User · Answer

For now I took  Gian s advice  amp  limited the number of records per Workbook to 500k and rolled over the rest to the next Workbook  Seems to be working decent  For the above configuration  it took me about 10 mins per workbook

User · Answer

Oh   I think you re writing the workbook out 944 000 times   Your wb write bos  call is in the inner loop   I m not sure this is quite consistent with the semantics of the Workbook class    From what I can tell in the Javadocs of that class  that method writes out the entire workbook to the output stream specified   And it s gonna write out every row you ve added so far once for every row as the thing grows   This explains why you re seeing exactly 1 row  too   The first workbook  with one row  to be written out to the file is all that is being displayed - and then 7GB of junk thereafter

User · Answer

You can using SXSSFWorkbook implementation of Workbook  if you use style in your excel  You can caching style by Flyweight Pattern to improve your performance

User · Answer

I updated BigGridDemo to support multiple sheets   BigExcelWriterImpl java  package com gdais common apache poi bigexcelwriter   import static com google common base Preconditions     import java io File  import java io FileInputStream  import java io FileOutputStream  import java io IOException  import java io InputStream  import java io OutputStream  import java io OutputStreamWriter  import java io Writer  import java util Enumeration  import java util HashMap  import java util LinkedHashMap  import java util Map  import java util zip ZipEntry  import java util zip ZipFile  import java util zip ZipOutputStream   import javax annotation Nonnull  import javax annotation Nullable   import org apache commons io FilenameUtils  import org apache poi ss usermodel Workbook  import org apache poi xssf usermodel XSSFSheet  import org apache poi xssf usermodel XSSFWorkbook   import com google common base Function  import com google common collect ImmutableList  import com google common collect Iterables   public class BigExcelWriterImpl implements BigExcelWriter    private static final String XML ENCODING    UTF-8     Nonnull private final File outputFile    Nullable private final File tempFileOutputDir    Nullable private File templateFile   null    Nullable private XSSFWorkbook workbook   null    Nonnull private LinkedHashMap lt String  XSSFSheet gt  addedSheets   new LinkedHashMap lt String  XSSFSheet gt       Nonnull private Map lt XSSFSheet  File gt  sheetTempFiles   new HashMap lt XSSFSheet  File gt      BigExcelWriterImpl  Nonnull File outputFile        this outputFile   outputFile      this tempFileOutputDir   outputFile getParentFile        Override public BigExcelWriter createWorkbook         workbook   new XSSFWorkbook        return this      Override public BigExcelWriter addSheets String    sheetNames        checkState workbook    null   workbook must be created before adding sheets         for  String sheetName   sheetNames            XSSFSheet sheet   workbook createSheet sheetName           addedSheets put sheetName  sheet              return this      Override public BigExcelWriter writeWorkbookTemplate   throws IOException       checkState workbook    null   workbook must be created before writing template        checkState templateFile    null   template file already written         templateFile   File createTempFile FilenameUtils removeExtension outputFile getName                   -template     xlsx   tempFileOutputDir       System out println templateFile       FileOutputStream os   new FileOutputStream templateFile       workbook write os       os close         return this      Override public SpreadsheetWriter createSpreadsheetWriter String sheetName  throws IOException       if   addedSheets containsKey sheetName             addSheets sheetName              return createSpreadsheetWriter addedSheets get sheetName        Override public SpreadsheetWriter createSpreadsheetWriter XSSFSheet sheet  throws IOException       checkState  sheetTempFiles containsKey sheet    writer already created for this sheet         File tempSheetFile   File createTempFile              FilenameUtils removeExtension outputFile getName                           -sheet    sheet getSheetName      xml   tempFileOutputDir        Writer out   null      try           out   new OutputStreamWriter new FileOutputStream tempSheetFile   XML ENCODING           SpreadsheetWriter sw   new SpreadsheetWriterImpl out            sheetTempFiles put sheet  tempSheetFile           return sw        catch  RuntimeException e            if  out    null                out close                      throw e           private static Function lt XSSFSheet  String gt  getSheetName   new Function lt XSSFSheet  String gt            Override     public String apply XSSFSheet sheet            return sheet getPackagePart   getPartName   getName   substring 1              Override public File completeWorkbook   throws IOException       FileOutputStream out   null      try           out   new FileOutputStream outputFile           ZipOutputStream zos   new ZipOutputStream out            Iterable lt String gt  sheetEntries   Iterables transform sheetTempFiles keySet                    getSheetName           System out println  Sheet Entries      sheetEntries           copyTemplateMinusEntries templateFile  zos  sheetEntries            for  Map Entry lt XSSFSheet  File gt  entry   sheetTempFiles entrySet                  XSSFSheet sheet   entry getKey                substituteSheet entry getValue    getSheetName apply sheet   zos                     zos close            out close             return outputFile        finally           if  out    null                out close                       private static void copyTemplateMinusEntries File templateFile          ZipOutputStream zos  Iterable lt String gt  entries  throws IOException        ZipFile templateZip   new ZipFile templateFile         SuppressWarnings  unchecked       Enumeration lt ZipEntry gt  en    Enumeration lt ZipEntry gt   templateZip entries        while  en hasMoreElements              ZipEntry ze   en nextElement            if   Iterables contains entries  ze getName                   System out println  Adding template entry      ze getName                 zos putNextEntry new ZipEntry ze getName                  InputStream is   templateZip getInputStream ze               copyStream is  zos               is close                       private static void substituteSheet File tmpfile  String entry          ZipOutputStream zos          throws IOException       System out println  Adding sheet entry      entry       zos putNextEntry new ZipEntry entry        InputStream is   new FileInputStream tmpfile       copyStream is  zos       is close       private static void copyStream InputStream in  OutputStream out  throws IOException       byte   chunk   new byte 1024       int count      while   count   in read chunk    gt   0            out write chunk  0  count             Override public Workbook getWorkbook         return workbook      Override public ImmutableList lt XSSFSheet gt  getSheets         return ImmutableList copyOf addedSheets values            SpreadsheetWriterImpl java  package com gdais common apache poi bigexcelwriter   import java io IOException  import java io Writer  import java util Calendar   import org apache poi ss usermodel DateUtil  import org apache poi ss util CellReference   class SpreadsheetWriterImpl implements SpreadsheetWriter    private static final String XML ENCODING    UTF-8    private final Writer  out  private int  rownum   SpreadsheetWriterImpl Writer out         out   out      Override public SpreadsheetWriter closeFile   throws IOException        out close         return this      Override public SpreadsheetWriter beginSheet   throws IOException        out write   lt  xml version   1 0   encoding                   XML ENCODING                    gt                               lt worksheet xmlns   http   schemas openxmlformats org spreadsheetml 2006 main   gt          out write   lt sheetData gt  n         return this      Override public SpreadsheetWriter endSheet   throws IOException        out write   lt  sheetData gt          out write   lt  worksheet gt          closeFile        return this            Insert a new row         param rownum               0-based row number      Override public SpreadsheetWriter insertRow int rownum  throws IOException        out write   lt row r        rownum   1        gt  n        this  rownum   rownum       return this            Insert row end marker      Override public SpreadsheetWriter endRow   throws IOException        out write   lt  row gt  n         return this      Override public SpreadsheetWriter createCell int columnIndex  String value  int styleIndex          throws IOException       String ref   new CellReference  rownum  columnIndex  formatAsString         out write   lt c r       ref       t   inlineStr          if  styleIndex    -1             out write   s       styleIndex                     out write   gt          out write   lt is gt  lt t gt     value     lt  t gt  lt  is gt          out write   lt  c gt          return this      Override public SpreadsheetWriter createCell int columnIndex  String value  throws IOException       createCell columnIndex  value  -1        return this      Override public SpreadsheetWriter createCell int columnIndex  double value  int styleIndex          throws IOException       String ref   new CellReference  rownum  columnIndex  formatAsString         out write   lt c r       ref       t   n          if  styleIndex    -1             out write   s       styleIndex                     out write   gt          out write   lt v gt     value     lt  v gt          out write   lt  c gt          return this      Override public SpreadsheetWriter createCell int columnIndex  double value  throws IOException       createCell columnIndex  value  -1        return this      Override public SpreadsheetWriter createCell int columnIndex  Calendar value  int styleIndex          throws IOException       createCell columnIndex  DateUtil getExcelDate value  false   styleIndex        return this      Override public SpreadsheetWriter createCell int columnIndex  Calendar value          throws IOException       createCell columnIndex  value  -1        return this

User · Answer

You can increase the performance of excel export by following these steps   1  When you fetch data from database  avoid casting the result set to the list of entity classes  Instead assign it directly to List  List lt Object   gt  resultList  session createSQLQuery  SELECT t1 employee name  t1 employee id     from t employee t1    list      instead of  List lt Employee gt  employeeList  session createSQLQuery  SELECT t1 employee name  t1 employee id     from t employee t1    list      2  Create excel workbook object using SXSSFWorkbook instead of XSSFWorkbook and create new row using SXSSFRow when the data is not empty   3  Use java util Iterator to iterate the data list      Iterator itr   resultList iterator      4  Write data into excel using column     int rowCount   0  int column   0  while itr hasNext      SXSSFRow row   xssfSheet createRow rowCount       Object   object    Object    itr next       column 1       row setCellValue object column         write logic to create cell with required style in setCellValue method    column 2  row setCellValue object column       itr remove        5  While iterating the list  write the data into excel sheet and remove the row from list using remove method  This is to avoid holding unwanted data from the list and clear the java heap size      itr remove

User · Answer

Using SXSSF poi 3 8  package example   import java io FileInputStream  import java io FileOutputStream   import org apache poi ss usermodel Cell  import org apache poi ss usermodel Row  import org apache poi ss util CellReference  import org apache poi xssf streaming SXSSFSheet  import org apache poi xssf streaming SXSSFWorkbook  import org apache poi xssf usermodel XSSFWorkbook   public class SXSSFexample         public static void main String   args  throws Throwable           FileInputStream inputStream   new FileInputStream  mytemplate xlsx            XSSFWorkbook wb template   new XSSFWorkbook inputStream           inputStream close             SXSSFWorkbook wb   new SXSSFWorkbook wb template            wb setCompressTempFiles true            SXSSFSheet sh    SXSSFSheet  wb getSheetAt 0           sh setRandomAccessWindowSize 100     keep 100 rows in memory  exceeding rows will be flushed to disk     for int rownum   4  rownum  lt  100000  rownum             Row row   sh createRow rownum           for int cellnum   0  cellnum  lt  10  cellnum                 Cell cell   row createCell cellnum               String address   new CellReference cell  formatAsString                cell setCellValue address                          FileOutputStream out   new FileOutputStream  tempsxssf xlsx        wb write out       out close           It requires    poi-ooxml-3 8 jar  poi-3 8 jar  poi-ooxml-schemas-3 8 jar  stax-api-1 0 1 jar  xml-apis-1 0 b2 jar  xmlbeans-2 3 0 jar  commons-codec-1 5 jar  dom4j-1 6 1 jar   Useful link

[java] Writing a large resultset to an Excel file using POI

Examples related to java

Examples related to jdbc

Examples related to apache-poi