定义动态单元格范围 [英] define dynamic cell range

问题描述

我使用 APACHE POI 进行条件格式设置.我将 txt 文件作为输入并将其转换为工作表，然后执行条件格式.现在，第一列和标题是文本，我不会对其进行任何格式化.我必须对其余单元格进行条件格式设置，例如 B2:I10(但它会不断变化)如何动态定义单元格范围.

I am using APACHE POI for conditional formatting. I get txt file as input and i convert it into worksheet and then perform the conditional formatting. Now, the first column and header are text on which I wont do any formatting. I have to do conditional formatting on rest of the cells, like B2:I10(but it would keep changing) How can i define the cell range dynamically.

目前，我有

CellRangeAddress[] my_data_range = {CellRangeAddress.valueOf("B2:I10")};
my_cond_format_layer.addConditionalFormatting(my_data_range,my_rule1)

我将如何动态定义 my_data_range，将第一列和第一行放在一边.

how would i define my_data_range dynamically, keeping first column and row aside.

推荐答案

根据我的理解，我将问题分为两部分.

As per my understanding, I am breaking the problem in two parts.

首先:-需要知道我们动态获取数据的单元格范围地址

First:- need to know the cell range address in which we have data dynamically

第二:-重新定义单元格范围地址.

Second:- re-define the cell range address.

第一种解决方案:动态获取需要在范围内引用的单元格范围.

Solution of First: get cell range dynamically that need to refer in range.

您可以轻松获取数据的第一个单元格(第一行/第一列数据)(比如它的 A3)和最后一个单元格(最后一行/最后一列)(比如它的 F9)

You can easily get the first cell (first row/first column of data) of the data (Say its A3) and last cell (last row/last column) (say its F9)

如果你有任何单元格，你可以很容易地从

If you have any cell you can easily get its reference from

cell.getReference();  //will give you A3 

现在我们需要使用简单的字符串操作将A和3分开

Now we need to seperate A and 3 using simple string operation

char startCellColRef = cell.getReference().toString().charAt(0); // will Give you A 
int startCellRowRef = cell.getReference().toString().charAt(1);  // will give you 3

使用同样的方法你也可以得到结束索引.

Using same way you can get the end index as well.

第二种解决方案:动态更改单元格引用.

Name reference = wb.getName("NameReferenceInExcelSheet");
referenceString = sheetName+"!$"+startCellColRef+"$"+startCellRowRef+":$"+endCellColRef+"$"+endCellRowRef;
reference.setRefersToFormula(referenceString);

这篇关于定义动态单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！