Setting value to cells after merging in POI

Viewed 12971

I want to form a excel output as below in POI:

enter image description here

As clear from the image, I have 3 sub columns each under Header3, Header4 and Header5 respectively.

The lists are as below:

ListA - Contains values for column A

ListB - Contains values for column B

List1 - is a list of database rows with 3 columns per row. First column will go under Header3, Second under Header 4 and Third under Header 5 i.e. columns C, F and I respectively.

List2 and List3 - Similar to List1 with each values going respectively under Header3, 4 and 5. List2 under columns D, G and J while List3 under columns E, H and K respectively.

Issue 1:

I have Header Names stored as a list having 5 values.

How to iterate against this list to assign the values to the merged regions ?

I am doing something like below but is not working:

// first two headers

for (int i = 0; i < headers.size() - 3; i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(allHeaders.get(i).toUpperCase());
            headerCell.setCellStyle(styles.get("header"));
        }

// Merging
sheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress.valueOf("$C$1:$E$1"));
sheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress.valueOf("$F$1:$H$1"));
sheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress.valueOf("$I$1:$K$1"));

for (int i = 3; i < headers.size(); i++) {
            headerCell = headerRow.createCell(i);
            headerCell.setCellValue(allHeaders.get(i).toUpperCase());
            headerCell.setCellStyle(styles.get("header"));
        }

Issue 2:

How to iterate through ListA through List3 to put in values as I have explained above?

Thanks for reading!

1 Answers
Related