How to write data from arrayList to multiple excel sheets

Question

I have data stored in ArrayList ,and i want to write that data in excel workbook in multiple sheets. I am able to write data in excel workbook, but it's writing in same sheet. As of now i am able to write data in the following way, as shown below in the pic:

But I want to break data into multiple sheets, Data should break from headers i,e S.NO,Col1...Col6, when ever this comes up, the data should write to new sheet.

I am using this code to write data into excel

for (int i = 0; i < listOfResults.size(); i++) {
            row = sheet.createRow(i);
            for (int j = 0; j < 7; j++) {
                cell = row.createCell(j);
                if (j + add < listOfResults.size()) {
                    cell.setCellValue(listOfResults.get(j + add));

                }
            }

            add += 7;
        }

Please help..

I suspect that you took some code from the internet and are trying to adapt it to your needs. The best would be to carefully go through that code, understand each call, and check with the Apache POI documentation what that call does exactly, and what other possibilities the API offers. This will give you the knowledge you require to create the program that meets your own individual needs. — RealSkeptic
– RealSkeptic, Commented Feb 25, 2017 at 9:35
@ user7291698:- I am creating sheet in the following way: Before first for loop Sheet sheet = wb.createSheet("ExpectedResults"); — mayank bisht
– mayank bisht, Commented Feb 25, 2017 at 10:06
RealSkeptic - yeah I already checked the Apache POI documentation , but still there is nothing which could help me.. — mayank bisht
– mayank bisht, Commented Feb 25, 2017 at 10:08

jmarkmurphy · Accepted Answer · 2017-02-27 12:43:23Z

This is not so much a POI problem as a break logic problem.

Your Code

for (int i = 0; i < listOfResults.size(); i++) {
    row = sheet.createRow(i);
    for (int j = 0; j < 7; j++) {
        cell = row.createCell(j);
        if (j + add < listOfResults.size()) {
            cell.setCellValue(listOfResults.get(j + add));
        }
    }
    add += 7;
}

There is nothing here to detect the header line, and thus you are not creating a new sheet. You need to detect the break, then create a new sheet, and reset the row index to zero for each header.

Modified Code

int rowIndex = 0;
int firstCellInRow = 0;
int cellCount = listOfResults.size();
int cellsPerRow = 7;
int rowCount = cellCount / cellsPerRow;

for (int i = 0; i < rowCount; i++) {

    // detect first header cell
    firstCellInRow = i * cellsPerRow;
    if (listOfResults.get(firstCellInRow).equals("S.NO")) {
        // create a new sheet
        sheet = wb.createSheet();
        // reset the row index to 0
        rowIndex = 0;
    }

    // create the row and increment the row index
    row = sheet.createRow(rowIndex++);

    // now add the data to the cells
    for (int j = 0; j < cellsPerRow; j++) {
        cell = row.createCell(j);
        if (firstCellInRow + j < listOfResults.size()) {
            cell.setCellValue(listOfResults.get(firstCellInRow + j));
        }
    }
}

The other issue is that you are running through the main loop too many times. You are looping through that once for each cell, but you really want to loop through once per row. This is the reason for the division of the length of the list (number of cells) by 7 (number of cells per row) in the first loop.

wieczorekm · Accepted Answer · 2017-02-25 09:36:35Z

0

You can check if current element is like S.NO. If it is, you have to open new sheet and continue writing.

answered Feb 25, 2017 at 9:36

wieczorekm

1582 silver badges9 bronze badges

5 Comments

mayank bisht Over a year ago

@xarines- I already tried this, using this condition I was able to break data into multiple sheets, but first sheets , but i was getting this type of output: Sheet 1: only header, Sheet 2:table 1 data plus header2, Sheet 3: table data plus header3, Like that i am getting data

mayank bisht Over a year ago

I creating new sheet after checking this condition:

if(listOfResults.get(j + add).contains("S.NO")) 					{ 						sheet = wb.createSheet("sheet"+i); 					} 					cell.setCellValue(listOfResults.get(j + add));

mayank bisht Over a year ago

@ Betlista- i am first checking for "S.NO" then i am creating new sheet and then writing data.

if(listOfResults.get(j + add).contains("S.NO")) { sheet = wb.createSheet("sheet"+i); } cell.setCellValue(listOfResults.get(j + add));

RealSkeptic Over a year ago

@mayankbisht When you create the new sheet, you also need to recreate your row and cell because the ones you have belong to the old sheet...

mayank bisht Over a year ago

RealSkeptic:- thanks that worked well, i am able to write data into multiple sheets as required. First sheet is correct, second sheet is starting from blank rows,after that data is there. These blank rows are equal to the size of first sheet data. Similarly, third sheet starts with blank rows(sheet1+sheet2 )

Collectives™ on Stack Overflow

How to write data from arrayList to multiple excel sheets

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related