export file to xlsx format in java-CodePudding

I am getting 5 lakh records per request from the Database then mapping the data to a list of POJO classes then writing the records to an excel sheet (I have the requirement to export the data to an excel sheet), is there any better way to generate ".xlsx " file? Here I need to iterate the list (loop) (5 lakh times) get the record and then write the record to .xlsx file.

Is there any better way to do it, for better performance and leat memory use?

CodePudding user response：

The performance of Apache POI can be greatly improved if you use the streaming API instead of the normal one. POI will keep all your data in the memory while you are writing, and with large files, this can be quite expensive. What you could do is use the streaming API and instruct POI to flush data progressively.

Here is an example from POI's site:

  public static void main(String[] args) throws Throwable {
       SXSSFWorkbook wb = new SXSSFWorkbook(100); // keep 100 rows in memory, exceeding rows will be flushed to disk
       Sheet sh = wb.createSheet();
       for(int rownum = 0; rownum < 1000; rownum  ){
           Row row = sh.createRow(rownum);
           for(int cellnum = 0; cellnum < 10; cellnum  ){
               Cell cell = row.createCell(cellnum);
               String address = new CellReference(cell).formatAsString();
               cell.setCellValue(address);
           }
       }
       // Rows with rownum < 900 are flushed and not accessible
       for(int rownum = 0; rownum < 900; rownum  ){
         Assert.assertNull(sh.getRow(rownum));
       }
       // the last 100 rows are still in memory
       for(int rownum = 900; rownum < 1000; rownum  ){
           Assert.assertNotNull(sh.getRow(rownum));
       }
       FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
       wb.write(out);
       out.close();
       // dispose of temporary files backing this workbook on disk
       wb.dispose();
   }

Check Apache POI's website for more. (under SXSSF (Streaming Usermodel API))

CodePudding user response：

Try Apache POI project (https://poi.apache.org/). Or Aspose, but it is not open source.