Setting value to cells after merging in POI

Question

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

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!

Answer 1

in reality I did not understand your problem completely, but I have some key points, that I think, will be helpful to you.

1. The first Cell of merged region keeps the value of the merged cell, rest cells keep blank value. Means if cell A2 to A5 is merged with value "Test" then on iteration A2 will show "Test" while rest will show blank. SO if you want to read the merged cell value, you need to read its first cell only, and similarly if you want to write in the merged cells, you just need to write in first cell only.

2. sheet.getNumMergedRegions(); return integer that will be the total number of merged region in the sheet. you can iterate through it using loop as well.

3. CellRangeAddress merge = sheet.getMergedRegion(int index); will give you the range address of the specified index obtained from getNumMergedRegions()

4. now merge.getFirstRow(),merge.getLastRow(), merge.getFirstColumn(), merge.getLastColumn(), merge.getNumberOfCells(), are the methods for the same.

The iteration through row containing merged cells and row not containing any merged cell are not much different. Hope this will help you to resolve your problem.