Conditional formatting being removed by Excel

[mccann1984]

Hi,

I am reading in data from an .xls sheet and then writing it out to a previously loaded .xlsx workbook. In the process I am trying to use conditionalFormatting() to shade the cells that are between certain values. The code appears to run correctly and saves out the finished workbook. However, when I go to open the excel file I get the error "we found a problem with some content....do you want us to try and recover as much as we can?". If I select "Yes" it then opens up the excel file but a window opens up saying "Removed Feature: Conditional formatting from /xl/worksheets/sheet7.xml part". Basically it has removed the conditional formatting for some reason and I have no idea why. The data is written in correctly but the cells aren't shaded.

Here is my R code:

# Load in xlsx file as a template that data will get written into.
  new_workbook <- loadWorkbook(paste0(data_template_root, "Occupation SOC20 (2) Table 3 (NI).1   Weekly pay - Gross 2022.xlsx"))
  
  # Read in xls file that data will come from
  data_from_xlsCV <- read_xls(paste0(data_input_root, "Work Region Occupation SOC20 (2) NI Table 3 (NI).1b   Weekly pay - Gross 2022 CV.xls"), sheet = "Male")
  
  selected_data <- data_from_xlsCV[6:41, 2:17]
  
  selected_data <- sapply(selected_data, as.numeric)
  
  # Write selected data into selected workbook
  writeData(new_workbook, sheet = "Table3.1b Male CV",
            x = selected_data,
            startRow = 10, startCol = 2,
            colNames = FALSE)
  
  # Create and apply formatting
  cvFive <- createStyle(fgFill = "#5bc0de", 
                        numFmt = "#,###.#",
                        halign = "right", 
                        fontSize = 12)
  
  cvTen <- createStyle(fgFill = "#428bca", 
                       numFmt = "#,###.#",
                       halign = "right", 
                       fontSize = 12)
  
  conditionalFormatting(new_workbook, sheet = "Table3.1b Male CV",
                        rows = 10:45, cols = 3:17,
                        rule = "between", operator = c(5,10), 
                        style = cvFive)
  
  conditionalFormatting(new_workbook, sheet = "Table3.1b Male CV",
                        rows = 10:45, cols = 3:17,
                        rule = "between", operator = c(10,20),
                        style = cvTen)
  
  # Save new workbook to Output folder
  saveWorkbook(new_workbook, paste0(data_output_root,"Occupation SOC20 (2) Table 3 (NI).1   Weekly pay - Gross 2022.xlsx"), overwrite = TRUE)
  

Here is the error log from Excel:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

error044000_01.xml

Errors were detected in file '\Output\2022\NI Table 3 SOC2\Occupation SOC20 (2) Table 3 (NI).1 Weekly pay - Gross 2022.xlsx'

Removed Feature: Conditional formatting from /xl/worksheets/sheet7.xml part

It looks like my conditional formatting has worked but when i try to view it, Excel has removed it.
Any help would be much appreciated!