How to write data from arrayList to multiple excel sheets

debugcn Published at Dev

mayank bisht

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..

jmarkmurphy

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-25

Comments

0 comments

From Dev

Excel VBA Macro - Copying data from multiple sheets in existing file, and creating new file and pasting selected data into separate sheets

From Dev

Related Related

Article