CODEWITHSUNDEEP
javaexcelapache-poi
user1631306
user1631306

Reputation: 4470

define dynamic cell range

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.

Currently, I have 

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

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

Upvotes: 0

Views: 1290

Answers (1)

Sankumarsingh
Sankumarsingh

Reputation: 10079

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.

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

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

Upvotes: 2

Related Questions