使用 Apache-POI 隐藏所有额外的 excel 列 [英] Hide all extra excel columns with Apache-POI
问题描述
这是我的目标:
我正在使用 Apache POI 生成一个 xlsx 文件.我想隐藏我正在使用的列右侧的所有未使用的列.在 Excel 中,这是一个相对简单的操作:我可以选择所有无关的列,并在一个操作中告诉它们全部隐藏.在 Apache POI 中,我似乎唯一的选择是 sheet.setColumnHidden(index, true)
.如果我尝试为每个无关列执行此操作:
I am generating an xlsx file using Apache POI. I would like to hide all the unused columns to the right of the columns I am using. In Excel, this is a relatively simple operation: I can select all the extraneous columns and tell them all to hide in one action. In Apache POI, the only option I seem to have is sheet.setColumnHidden(index, true)
. If I try to do this for every extraneous column:
for (int i = myLastColumn+1; i < 16384; ++i) {
sheet.setColumnHidden(i, true);
}
然后该库尝试创建超过 16,000 个列以隐藏它们,这是不切实际的:它会变得越来越慢,并且永远不会完成.似乎没有 setColumnRangeHidden
或类似的东西.
then the library tries to create over 16 thousand columns in order to hide them, which is impractical: it gets slower and slower as it goes, and never finishes. There doesn't seem to be a setColumnRangeHidden
or anything like that.
有没有办法隐藏 Apache POI 中的几乎所有列?
Is there a way to hide nearly all the columns in Apache POI?
推荐答案
最终,通过查看 XSSFSheet
和 ColumnHelper
的工作原理,我设法找到了我想要的功能不见了.
Eventually, looking through how XSSFSheet
and ColumnHelper
work, I managed to find the bit of functionality I was missing.
当您尝试隐藏单个列时,ColumnHelper
会为您请求的索引创建一个新列(如果它不存在),然后将其设置为隐藏.但是,列对象实际上是一个 CTCol
,其中 min
和 max
字段设置为相同的索引.如果您创建具有不同 min
和 max
字段的 CTCol
对象,则可以在一个操作中设置所有匹配列的状态.
When you try and hide a single column, the ColumnHelper
makes a new column for the index you requested (if it didn't exist), and then sets it to be hidden. However, the column object is actually a CTCol
with min
and max
fields set to the same index. If you create a CTCol
object with different min
and max
fields, you can set the state of all matching columns in one action.
因此:
CTCol col = sheet.getCTWorksheet().getColsArray(0).addNewCol();
col.setMin(myLastColumn+2);
col.setMax(16384); // the last column (1-indexed)
col.setHidden(true);
这篇关于使用 Apache-POI 隐藏所有额外的 excel 列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!