CODEWITHSUNDEEP
rexcelopenxlsx
Ansh Pujara
Ansh Pujara

Reputation: 1

conditionalFormatting() with "databar" type used for more than once creates a corrupt file in openxlsx

I have been facing this issue since a long time now. Whenever I try to generate an excel sheet containing conditionalFormatting() of type "databar" for more than one columns it creates a corrupt excel sheet. The code to reproduce the error is below:

library(openxlsx)

wb <- createWorkbook("myworkbook")
addWorksheet(wb, sheetName = "databar")

writeData(wb, "databar", -5:5, startCol = 5)
conditionalFormatting(wb, "databar",
                      cols = 5, rows = 1:11,
                      type = "databar", style = c("#a6a6a6"), showValue = FALSE
)

writeData(wb, "databar", -5:5, startCol = 7)
conditionalFormatting(wb, "databar",
                      cols = 7, rows = 1:11,
                      type = "databar", style = c("#a6a6a6"), showValue = FALSE, gradient = FALSE
)

writeData(wb, "databar", -5:5, startCol = 9)
conditionalFormatting(wb, "databar",
                      cols = 9, rows = 1:11,
                      type = "databar", style = c("#a6a6a6", "#a6a6a6"), showValue = FALSE, gradient = FALSE
)
saveWorkbook(wb, "sample.xlsx")

Upvotes: 0

Views: 334

Answers (1)

Carlo
Carlo

Reputation: 11

you should use library(openxlsx2), in particular the function wb_add_conditional_formatting.

Here an example of the code:

wb <- wb_workbook()
wb$add_worksheet("Sheet1")
wb$add_data("Sheet1", dataframe ,start_row = 1)
wb <- wb_add_conditional_formatting(wb,sheet,dims = "G34:G36",type = "dataBar")

you can do multiple conditional formatting adding code lines such as the last one above with different "dims" parameter.

Upvotes: 1

Related Questions