Large Excel Generation

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Large Excel Generation

dhanaraja
This post was updated on .
Hi,

We have a scenario of generating above 500000 records of data in xlsx
format. We are using poi and right now Since SXSSFWorkbook has the ability
to do without large memory occupation we
tried with following approach.

        SXSSFWorkbook workbook = new SXSSFWorkbook(xssfWorkbook, 100);

but as mentioned earlier after 15 mins of time we are getting out of memory
. We tried increasing app server JVM but due to business reasons increasing
RAM size not possible for us. So thought of going for below alternate
solution of unzipping excel file and write the content manually into that .

Step1: create empty excel template

Step2: unzip the excel file using below code
 public static void unzip(String zipFilePath, String destDir) {
    System.out.println("****DHANA**UNZIP_ENTRY****");
        File dir = new File(destDir);
        // create output directory if it doesn't exist
        if(!dir.exists())
        dir.mkdirs();
        FileInputStream fis;
        //buffer for read and write data to file
        byte[] buffer = new byte[1024];
        try {
            fis = new FileInputStream(zipFilePath);
            ZipInputStream zis = new ZipInputStream(fis);
            ZipEntry ze = zis.getNextEntry();
            while(ze != null){
                String fileName = ze.getName();
                System.out.println("UNZIP FILE
NAME***************"+fileName);
                System.out.println("UNZIP PATH**************"+destDir);
                File newFile = new File(destDir + File.separator +
fileName);
                System.out.println("Unzipping to
"+newFile.getAbsolutePath());
                //create directories for sub directories in zip
                new File(newFile.getParent()).mkdirs();
                FileOutputStream fos = new FileOutputStream(newFile);
                int len;
                while ((len = zis.read(buffer)) > 0) {
                fos.write(buffer, 0, len);
                }
                fos.close();
                //close this ZipEntry
                zis.closeEntry();
                ze = zis.getNextEntry();
            }
            //close last ZipEntry
            zis.closeEntry();
            zis.close();
            fis.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
Step 3:
After unzip we were able to see below structure
  <http://apache-poi.1045710.n5.nabble.com/file/t340716/excel_unzip.png

Step 4:
sharedStrings.xml file able to see text column values as expected

Step 5:
We are able to unzip the xlsx file and navigate to shared string and
worksheet files.
I have a query result which has around 5 lac records. Would like to manually
write this huge data into my worksheet and SharedString.xml file manually
using plain java code.
Do we have any standard way of doing this? if you have any sample snippets
can you share it with me?