Writing a huge data 2D Array to excel file using java POI

Question

I have a 2D arrays about 16000 X 16000 and i want to export these records to excel file. currently i can export up to 1000 X1000 of that 2D array within short time. But when I increase the size the array for example 3000 X 3000 my program run for a long time without returning any data. I am asking for help to export the whole 2D array to an excel file and am using POI.

My sample codes to export data where one if the parameters is my 2D array.

public class exportData {

public static void exportDataToExcel(String fileName, String tabName, int[][] data) throws FileNotFoundException, IOException
  {
    //Create new workbook and tab
      Workbook wb = new XSSFWorkbook();
      FileOutputStream fileOut = new FileOutputStream(fileName);
      Sheet sheet = wb.createSheet(tabName);

      //Create 2D Cell Array
      Row[] row = new Row[data.length];
      Cell[][] cell = new Cell[row.length][];

      //Define and Assign Cell Data from Given
      for(int i = 0; i < row.length; i ++)
      {
          row[i] = sheet.createRow(i);
          cell[i] = new Cell[data[i].length];

          for(int j = 0; j < cell[i].length; j ++)
          {
              cell[i][j] = row[i].createCell(j);
              cell[i][j].setCellValue(data[i][j]);
          }

      }

      //Export Data
      wb.write(fileOut);
      fileOut.close();
      System.out.println("File exported successfully");
  }

}

Did you try debugging to find out which line the execution is hung? May be wb.write(fileOut);? — Maas
– Maas, Commented Jul 21, 2014 at 10:11
i didn't try to do that but i am looking for any suggestions of better way of output my whole array to excel file. — Ally Bitebo
– Ally Bitebo, Commented Jul 21, 2014 at 10:15
If possible you should avoid POI and such libraries and just save the file directly in CSV format. Excel can open those, convert them, etc. — user207421
– user207421, Commented Jul 21, 2014 at 10:23

Maas · Accepted Answer · 2014-07-21 10:26:06Z

2

As I see your data is int[][]. So I believe that its plan static data (without any excel formulas)

Then, why don't you write your data into a CSV file? it is quick + there is restriction on the number of rows as in POI limited to 65,000+ records new sheet.

You can use CSVWritter

answered Jul 21, 2014 at 10:26

Maas

1,3679 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ally Bitebo Over a year ago

Can you help with a tutorial to do this... @Girish

Ally Bitebo Over a year ago

I tried for a while now to intergrate my array in your sample tutorial i found some difficulties how to write 2D array. i understand the example but the problem is to print the repsective values from my array as shown in the code above. Please help @ Girish

Maas · Accepted Answer · 2014-07-22 06:28:15Z

Here the complete example of using CSVWritter to print your 2D array

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;

import au.com.bytecode.opencsv.CSVWriter;

/**
 * @author Girish
 * 
 */
public class CSVWritterExample
{
    public static void main(String[] args) throws FileNotFoundException, IOException
    {
        int[][] data = new int[100][100];

        for (int i = 0; i < 100; i++)
        {
            for (int j = 0; j < 100; j++)
            {
                data[i][j] = j * i;
            }
        }

        exportDataToExcel("D:/sample.csv", data);
    }

    public static void exportDataToExcel(String fileName, int[][] data) throws FileNotFoundException, IOException
    {
        File file = new File(fileName);
        if (!file.isFile())
            file.createNewFile();

        CSVWriter csvWriter = new CSVWriter(new FileWriter(file));

        int rowCount = data.length;

        for (int i = 0; i < rowCount; i++)
        {
            int columnCount = data[i].length;
            String[] values = new String[columnCount];
            for (int j = 0; j < columnCount; j++)
            {
                values[j] = data[i][j] + "";
            }
            csvWriter.writeNext(values);
        }

        csvWriter.flush();
        csvWriter.close();
    }
}

Collectives™ on Stack Overflow

Writing a huge data 2D Array to excel file using java POI

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related