Conditional Formatting in Workbook Designer

Aspose.Cells Product Family
1

I’m trying to figure out a way to make this work and haven’t been able to acheive a solution. I have a template with smart markers that I load in using Cells. I want a cell to have conditional formatting based on comparing the value of that cell with the value of another cell in that row (i.e. if(G{r}<>K{r}) then apply the conditional format. If I try to use a dynamic formula using “Formula is” in the Conditional Formatting dialog in Excel, it tells me it’s an invalid formula and makes it a text string and it does nothing.
If I try to use “Cell value is” in the condition, then select “not equal to” then select the cell to compare it to, then every row that is output all uses the first row for the conditional formatting. (i.e. G6<>K6 is the formula for every row so if the first row is not equal, every row output is formatted with the same conditional formatting.)

Does Cells support conditional formatting like this? If so, how do I implement it?

2
Hi,

We will enhance smart markers to support conditional formattings with dyanmic formulas precisely for your need.

Currently I think as a workaround you may try to use Aspose.Cells APIs to set conditional formattings after filling data to those smart markers (after workbookdesigner.Process statement), it will work fine for you.

E.g.,

wd.Process();
.
int conditionalFormattingIndex = wd.Workbook.Worksheets[0].ConditionalFormattings.Add();
FormatConditions formatConditions = wd.Workbook.Worksheets[0].ConditionalFormattings[conditionalFormattingIndex];
CellArea cellArea = new CellArea();
cellArea.StartColumn = 12;
cellArea.StartRow = 5;

cellArea.EndColumn =12;
cellArea.EndRow = 100;//you may utilize Cells.MaxDataRow / MaxDataRowIn Column members to get the last row index dynamically
formatConditions.AddArea(cellArea);

int formatConditionIndex = formatConditions.AddCondition(FormatConditionType.Expression, OperatorType.None,"=G6<>K6", null);
FormatCondition formatCondition = formatConditions[formatConditionIndex];
formatCondition.Style.BackgroundColor = Color.Yellow;
formatCondition.Style.Pattern = BackgroundType.Solid;

Thank you.

3

Thanks very much!

4

Hi,

Yes, now, Smart Markers support conditional formatting. You should try latest version of the product (v4.8.2.x) if possible.

Thank you.

5

Whenever I try to use them - with the latest product - excel, in the condiotional formatting bar - reverts the values back =S$3$ from &=&=S{r}

Can you provide an example?

6

Hi,

If you use dynamic formulas in smart markers e.g “&=&=S{r}”, after processing the smart markers, I think it works fine to give you =S$3$ formula.

For complete reference on smart markers, see the document:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/smart-markers.html

thank you.

7

Will that work, if I use it in Excel Conditional Formatting? I have my template open - I click the conditional format button in excel - publish my report to ssrs - then export and the conditional formatting is not there

8

Hi,

Do you use Aspose.Cells for RS?
Thank you.

9

Do you mean the API? If so - no - I am just opening Excel - opening a rdl and making my changes and publishing to SSRS

10

Hi,

Currently, Aspose.Cells for Reporting Services client component only supports conditional cells formatting for the textbox item. Does it meet your requirement ? If not, we can add new feature that Aspose.Cells for Reporting Services client will support conditional cells formatting for table item. Could you confirm?

Thank you

11

Yes - it would be a tremendous benefit to be able to set it at the table level!

12

Hi,

Thanks for the confirmation.

I have logged the feature into out issue tracking system with an id: CELSSREP-22371. Once we have any update about it, we will let you know.

Thank you.

13

Hi ,

We have added new feature, now Aspose.Cells for Reporting Services Report Designer supports conditional formatting for table item.

Please try the latest version (V1.6.0.8) (attached).

Thank you.

14

The issues you have found earlier (filed as 22371) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by aspose.notifier.
15

Is it possible for the row numbers inside of a format condition expression/formula to be updated using Smart Markers or through adding a condition via code to an area without looping. For example if A1 is less than or equal to B1 apply the style and have that continue down x number of rows so that the condition on row 200 is A200 less than or equal to B200.

Thanks

16

Hi,


I think MS Excel will not except to add smart markers inside the condition’s expression/formula. Currently I think there is no way but as a workaround you should try to use Aspose.Cells APIs to set conditional formattings accordingly after filling data to those smart markers (after workbookdesigner.Process statement), it may work fine for you.


Thank you.




17

Hi,

After further evaluation, we think if you need relative reference in conditional formatting, MS Excel will work as your expected.

Please set the formula as :=A1<B1.

If you still have any issue, please post a template file and your expected file, we will check it soon.