Conditional Formatting Relations

Aspose.Cells Product Family
1

Hi.


There is exists some relation object for conditional formating. In other words, there is some way to know - how cells were affected when valued cell was changed.

Please see attached file.

Workbook wb = new Workbook(“conditional formatting.xlsx”);
wb.getWorksheets().get(0).getCells().get(2,1).setValue(11);
//Some method wb.getLastAffectedCells should return array “B3”,"F3"

wb.getWorksheets().get(0).getCells().get(2,1).setValue(17);
//Some method wb.getLastAffectedCells should return array “B3”,“F3”,"F4"

Is very hardly to implement this?

Best regards. Alexey



2

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

Currently, this feature is not available in Aspose.Cells. We will analyze it and implement it if possible. We have logged a New Feature request for this issue. Once, there is some fix or other update for you, we will let you know asap.

This issue has been logged as CELLSJAVA-40689.

3

Many thanks!

4

Hi Alexey,

Thanks for considering Aspose.Cells.

We have looked into this feature and we are afraid, we cannot support this feature because it is too difficult to implement it. Thanks for your understanding.

5

Ok Faiz.


May be you able to proivde some conditional formatting API for parse valued cells?

For example

Cell A1 had conditional formatting “Red, when $C$1 > 10”. Can i receive from Conditional formatting object Cell C1?

Best regards. Alexey
6

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

Currently, you can iterate the conditional formatting collection and parse the formulas if it could fit your needs. Please see the following code and its output. It used the same source file as you have provided.

Java


String filePath = “F:\Shak-Data-RW\Downloads\conditional+formatting.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


ConditionalFormattingCollection cfc = worksheet.getConditionalFormattings();


FormatCondition fcc = cfc.get(0).get(0);


System.out.println(fcc.getFormula1());


Output:
=$B$3>10
7

Thanks, but it is not what we needed.


Please see attached file.
There is several lines with 2 conditional formatting. You able to write in column “I” values “Red” or “Yellow” and corresponding line will be colored.

But for each line conditional formatting formuala will be like $I3=

We need to have some relation object for each cell to corresponding cell in a row:

B3:I3 -> I3
B4:I4 -> I4
…
B12:I12 -> I12

Best regards. Alexey
8

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

It seems, you are again demanding the last affected cells which should return cells B3:I3 when value of the cell I3 is changed to Red or Yellow.

Similarly, when the cell I4 is changed to Red or Yellow, B4:I4 cells are affected.

As we mentioned earlier, it is a difficult feature for us so we cannot implement it.

9
Hi Shakeel Faiz.

I tried to receive some information about internal engine of conditional formatting in Aspose. Do you have some license for retrieving a source codes?
May be you have some internal structures, that could be helpful for our task. Now i see only string values of conditional formatting conditions.

Best regards. Alexey
10

Hi Alexey,

Thanks for using Aspose.Cells.

We are afraid, we do not provide license for retrieving a source code. Please see the following code if it is any helpful. The code retrieves all the format conditions that have been applied on cell B3. Similarly, you can retrieve the format conditions applied on any cell in this way.

Java


String filePath = “F:\Shak-Data-RW\Downloads\Conditional.xlsx”;


Workbook workbook = new Workbook(filePath);


Worksheet worksheet = workbook.getWorksheets().get(0);


Cell cell = worksheet.getCells().get(“B3”);


FormatConditionCollection fcc = cell.getFormatConditions();


for (int i = 0; i < fcc.getCount(); i++)

{

System.out.println(fcc.get(i).getFormula1());

}

Output:
=$I3=“Yellow”
=$I3=“Red”