Hello, I wanted to understand whether Aspose.Cells was able to access the value that a conditional formatting rule has evaluated a cell color too in an Excel notebook. The scenario here is that I’d like to write validations that a conditional formatting rule within a generated spreadsheet is working as expected.
However, when I try to access the color the conditional formatting rule evaluates to, both foreground_color and background_color are empty. This happens even after opening the Excel spreadsheet in Excel, and “saving” it.
Is this something that it’s possible to do in Aspose.Cells? I am using the python SDK around the .NET version.
@aaronadditive
To access the color that a conditional formatting rule has evaluated for a cell in an Excel workbook using Aspose.Cells, you can utilize the GetConditionalFormattingResult method. This method provides you with the resultant color based on the conditional formatting applied to the cell.
Here’s a sample code snippet in Python that demonstrates how to achieve this:
import aspose.cells as cells
# Load the workbook
workbook = cells.Workbook("Book1.xlsx")
# Access the first worksheet
worksheet = workbook.get_worksheets().get(0)
# Access the specific cell (e.g., A1)
cell = worksheet.get_cells().get("A1")
# Get the conditional formatting result for the cell
cfr = cell.get_conditional_formatting_result()
# Access the evaluated color
color = cfr.get_color_scale_result()
# Print the color information
print(f"Color ARGB: {color.to_argb()}")
print(f"Color Name: {color.get_name()}")
In this example, after loading the workbook and accessing the desired cell, the GetConditionalFormattingResult method is called to retrieve the conditional formatting result. You can then access the evaluated color using the ColorScaleResult property.
Make sure that the conditional formatting rules are correctly set in your Excel file, as the method will return the evaluated color based on those rules. If the foreground_color and background_color properties are empty, it may indicate that the conditional formatting is not being applied as expected, or the rules may not be triggering for the specific cell value.
If you continue to experience issues, ensure that the Excel file is saved properly after applying the conditional formatting rules, and that you are using the latest version of Aspose.Cells for Python.
For further details, you can refer to the example provided in the Aspose.Cells GitHub repository here.
Sources:
[1]: ComputeColorChoosenByMSExcel.cs
@aaronadditive,
If conditional formatting is applied to a cell, the Cell.get_style() method does not return information about conditional formattings and table styles. You need to use Cell. get_display_style() method to get the display style/formatting. See the following sample code for your reference.
# Load the workbook
file_path = "e:\\test2\\Book1.xlsx"
workbook = Workbook(file_path)
# Access the first worksheet and cells
cells = workbook.worksheets[0].cells
# Access specific cells
v4 = cells.get("V4")
d20 = cells.get("D20")
# Get the style foreground color of cells
print("v4 style: ", v4.get_style().foreground_argb_color)
print("d20 style: ", d20.get_style().foreground_argb_color)
# Get the display style foreground color of conditinally formatted cells
print("v4 display style: ", v4.get_display_style().foreground_argb_color)
print("d20 display style: ", d20.get_display_style().foreground_argb_color)
If you still find the issue, kindly do provide your template Excel file(please zip the file prior attaching here) and sample code to reproduce the issue on our end, we will check your issue soon.
1 Like
Thank you, this resolved the issue! It is working as expected now.
@aaronadditive,
It is good to hear the suggested code segment meets your requirements. Please don’t hesitate to reach out if you have additional questions or feedback.