Java 在 POI 中合并后为单元格设置值

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/20675866/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-13 03:31:35  来源:igfitidea点击:

Setting value to cells after merging in POI

javaapache-poi

提问by Vicky

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

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

enter image description here

在此处输入图片说明

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

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

The lists are as below:

名单如下:

ListA - Contains values for column A

ListA - 包含 A 列的值

ListB - Contains values for column B

ListB - 包含 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.

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

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.

List2 和 List3 - 与 List1 类似,每个值分别位于 Header3、4 和 5 下。List2 位于 D、G 和 J 列下,而 List3 分别位于 E、H 和 K 列下。

Issue 1:

问题 1:

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

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

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:$E"));
sheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress.valueOf("$F:$H"));
sheet.addMergedRegion(org.apache.poi.ss.util.CellRangeAddress.valueOf("$I:$K"));

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:

问题 2:

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

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

Thanks for reading!

谢谢阅读!

采纳答案by Sankumarsingh

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.

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

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

  3. CellRangeAddress merge = sheet.getMergedRegion(int index);会给你指定索引的范围地址 getNumMergedRegions()

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

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.

通过包含合并单元格的行和不包含任何合并单元格的行的迭代没有太大区别。希望这将帮助您解决您的问题。