Reviewing and updating conditional formatting in Aspose

Aspose.Cells Product Family
1

Hi,

I’m trying to go through a number of Excel files, and update conditional formatting of certain cells given some conditions.

I’ve looked through the example code on the sample site and I’m not sure if this can be done.

Below is some VBA code that achieves the same thing, however I don’t want to undertake automation and prefer to use Aspose.

Sub Macro1()

For Each Sheet In ActiveWorkbook.Sheets

If Sheet.Visible = True Then

Sheet.Select

Cells.Select

For Each FormatCondition In Selection.FormatConditions

With FormatCondition

If .Type = xlCellValue Then

If .Formula1 = "=0.495" Then

If .Formula2 = "=0.00001" Then

.Modify xlCellValue, xlBetween, "=0.00001", "=0.495"

End If

End If

End If

End With

Next

End If

Next

End Sub
2

Hi David,


Thank you for contacting Aspose support.

I think the heading Using Aspose.Cells to Apply Conditional Formatting Based on Formula from this article should serve your purpose. Please note, the code is creating the condition from scratch however, you may amend the code to use it for existing rules in a given spreadsheet. In case you still face any problem in achieving your goals, please provide us your desired results in spreadsheet format that you may create manually using Excel application.

3

Hi,


Thanks for your providing us some details and sample VBA code.

I have written a sample code to accomplish your task for your reference. I have used a template file (attached), loop through all format conditions in the worksheets in the workbook and updated the format conditions (Formula1 and Formula2 etc.) while evaluating and comparing each condition. I finally saved the Excel file, the output file is also attached for your reference. Please refer to the following sample code and write your own code accordingly for your requirements.
e.g
Sample code:

Workbook workbook = new Workbook(“e:\test2\Bk_conditionalformat1.xls”);
foreach (Worksheet ws in workbook.Worksheets)
{
foreach (FormatConditionCollection fcc in ws.ConditionalFormattings)
{
for (int i = 0; i < fcc.Count; i++)
{
FormatCondition fc = fcc[i];
Debug.WriteLine("Type: " + Enum.GetName(typeof(FormatConditionType), fc.Type));
if(fc.Type == FormatConditionType.CellValue)
{

if (fc.Formula1 == “0” & fc.Formula2 == “0.24999”)
{
fc.Formula1 = “0.00001”;
fc.Formula2 = “0.495”;
}
}
}
}
}

workbook.Save(“e:\test2\out1Bkconditionalformat1.xls”);


Hope, this helps a bit.

Thank you.