在 POI 中合并后为单元格设置值 [英] Setting value to cells after merging in POI

问题描述

我想在 POI 中形成如下的 excel 输出:

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

从图片中可以清楚地看出，我在 Header3、Header4 和 Header5 下分别有 3 个子列.

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

名单如下:

ListA - 包含 A 列的值

ListA - Contains values for column A

ListB - 包含 B 列的值

ListB - Contains values for column B

List1 - 是每行 3 列的数据库行列表.第一列将在 Header3 下，第二列在 Header 4 下，第三列在 Header 5 下，即分别为 C、F 和 I 列.

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 和 List3 - 与 List1 类似，每个值分别位于 Header3、4 和 5 下.List2 位于 D、G 和 J 列下，而 List3 分别位于 E、H 和 K 列下.

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.

问题 1:

我将标题名称存储为具有 5 个值的列表.

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"));
        }

问题 2:

如何遍历 ListA 到 List3 以输入我上面解释过的值?

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

感谢阅读！

推荐答案

实际上我没有完全理解你的问题，但我有一些关键点，我认为对你有帮助.

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

1. 合并区域的第一个单元格保留合并单元格的值，其余单元格保留空白值.意味着如果单元格 A2 到 A5 与值测试"合并，则在迭代 A2 中将显示测试"，而其余部分将显示为空白.所以如果你想读取合并单元格的值，你只需要读取它的第一个单元格，同样如果你想写入合并的单元格，你只需要写入第一个单元格即可.

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.

sheet.getNumMergedRegions(); 返回整数，它将是工作表中合并区域的总数.您也可以使用循环遍历它.

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.

CellRangeAddress merge = sheet.getMergedRegion(int index); 会给你从 getNumMergedRegions()

现在 merge.getFirstRow(),merge.getLastRow(), merge.getFirstColumn(), merge.getLastColumn()、merge.getNumberOfCells() 是相同的方法.

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.

这篇关于在 POI 中合并后为单元格设置值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！