定义动态单元格范围 [英] 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.

秒的解决方案:动态更改单元格引用.

Solution of Second: Changing the cell references dynamically.

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

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