Unable to extract conditional formatting using xlsx_formats

[awdursun]

I am unable to extract conditional formatting from an xlsx file using xlsx_formats.

The goal is to continuously rebuild a xlsx file using openxslx, but I want to maintain the highlighting from the imported xlsx file. I am able to extract the formatting from the original file and apply the highlighting to a new file via conditional formatting. This is because the original file is not conditionally formatted, but rather manually formatted. However, when I want to build a new file using the conditionally formatted file, the formatting is no longer detected using xslx_formats.

[nacnudus]

Hi @awdursun, thanks for explaining how you would use this feature.

I haven't implemented conditional formatting before, because it is based on cell values, so I thought it would be easier to filter for the cells by their values than their conditional formatting. Would that work in your case?

[awdursun]

@nacnudus , thanks so much for the quick response.

I would like to not use conditional formatting, but I'm unable to come up with a way that would work. Let's say the original file is a shared file, and users highlight values in a column using various colors. I can use xslx_formats to get the color, but I can't think of any way to use these colors for a new file other than to conditionally format. I'm using conditionalFormatting from the openxslx package and referencing the hex codes retrieved from the original file. The good thing about this is that I don't need to explicitly reference the hex codes, so if a new colors is used it would not be an issue.

To put it in a different way, I'm not sure how else to use the hex codes to highlight cells in a new file, other than to use conditional formatting to do so.

[nacnudus]

I see, there isn't really a rule for the conditional formatting, so you need to be able to read conditional formatting back into tidyxl.

From a quick read of the openxlsx documentation there might be a way using createStyle(fgFill = "#DCE6F1").

I'll think about how to implement this anyway. I doubt the formatting itself is in the file, probably only the rule. Would the rule alone be enough for your need?

[awdursun]

OHH, I think I can just implement the conditional formatting in R using addStyle from openxlsx so it is hard-coded into the new Excel file? I will try that and report back tomorrow.

[awdursun]

Applying the conditional formatting via openxlsx's addStyle in R before the new file creation allowed me to extract the formatting with tidyxl's xslx_formats. Thanks for your responses and great work on a very useful package!