Conditional Formatting with thicker bottom border in Excel cell using Aspose.Cells for Java

Aspose.Cells Product Family
1

Hi Aspose,

This is regarding a query about conditional formatting. We are using Aspose with Java
Attached is the excel sheet which demonstrates my issue.
Aspose_thicker_underline_in_cf.PNG (1.8 KB)

Aspose_underline.zip (8.4 KB)

Please try it out.
1.Enter the value “X” (without quotes and case insensitive) in column E from E5:E9.
2. We have configured the bottom border to change depending on value “X”.
3. Also bottom border color will change if corresponding row in column B is having value “Y”.

This is our problem statement. Only issue we are facing is the borderStyle is thin in case of conditional formatting.We would like the bottom border to have same thickness as the initial green border.

Please let us know on how to achieve this in aspose java.

2

@asarja,
It seems that this is not possible using Excel as I have tried to format the border using Excel formatting features. As Aspose.Cells mimics the behaviour of Excel, therefore I am afraid that this is not possible using Aspose.Cells as well. You may please try to achieve this functionality using Excel, and if found share the steps with us. We will try to provide the assistance for achieving the same using Aspose.Cells.

3

Thanks @ahsaniqbalsidiqui for your explanation.

We shall be trying different other ways to achieve this. We have one more problem while setting the condition formula in aspose FormatCondition
When I form this string
=UPPER(INDIRECT(ADDRESS(ROW(),COLUMN()-((COLUMN()-25)-(FLOOR((COLUMN()-(25-1))/5,1)*5)))))<>“X”
and set it to Formula1 , it always seen in excel as

=UPPER(INDIRECT(ADDRESS(ROW(),COLUMN()-((COLUMN()-25)-(FLOOR((COLUMN()-(25-1))/5,1)*5)))))<>"“X”"" and conditional formula never works.
Can you let us know the exact way to have quoted strings as formula in aspose.

4

@asarja,
Could you please share your template and output file with complete code snippet (runnable) for our analysis.

5

@ahsaniqbalsidiqui,

I have managed to fix this by
String formula = getFormula(); //Method which gets the formula…
formatCondition.setFormula1(String.format("=%s",formula));

I was setting only the formula and missing out “=”, assuming it will be autopopulated.
Once “=” was set , it is working fine.Thanks

6

@asarja,

Good to know that you have sorted out your issue. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

7

@ahsaniqbalsidiqui,

Can you help me with the solution to apply borders to merged cells. Currently we have not so elegant solution i.e creating dummy cells and applying only style for other cells with null value.
Example :
Consider A2, B2 , C2 cells are merged and need to apply border for the merged cell.

We use below code.
getAsposeCell().setValueAndStyle(“ABCD”,preferredBorderStyle)
getAsposeCell().moveRightBy(1).setValueAndStyle(null,preferredBorderStyle)
getAsposeCell().moveRightBy(2).setValueAndStyle(null,preferredBorderStyle)

8

@asarja,

Seeing your code segment, either you are not using Aspose.Cells or using some custom methods/objects. Could you share your sample code (runnable) and template file to show what you are currently doing. Also, provide more details on your workaround to cope with it, we will check it soon.