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"?>
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!