Delete a column keeping the format of the cells

Question

I want to remove column 2 from this excel while maintaining the color format.

library(openxlsx)

wkbook <- loadWorkbook(file = "data.xlsx")
deleteData(wkbook, sheet = 1, cols = 2, rows = 1:5, gridExpand = T)
saveWorkbook(wkbook, "data2.xlsx",overwrite = TRUE)

Data is removed, but column remains. I'm looking to completely remove the column. But I do not know how to do it.

Answer 1

I am not sure how to do something like this in openxlsx. It is possible with openxlsx2 and I show how to do it below. However, I will not add a function for this in openxlsx2, because it has a tendency to break workbooks to the left and right.

If you do something like this in a spreadsheet software like Excel, the software will take care of updating the references to column B. In openxlsx2 we do nothing of the sort. We do not track formulas, charts, or pivot tables, not in a single workbook and not across various workbooks. Therefore the behavior would be completely different and would only work in rare cases. Like the artificial example above with worksheets containing only (styled) data in the cells.

That said, here is what you asked for:

## create file
dat <- data.frame(
  AA = 1:4,
  BBB = 2:5,
  VVV = 6:9
)

library(openxlsx2)

wb <- wb_workbook()$add_worksheet()$add_data(x = dat)

wb$add_fill(dims = "A1:C5", color = wb_color("yellow"))
wb$add_fill(dims = "A2:A3", color = wb_color("red"))
wb$add_fill(dims = "A5", color = wb_color("lightgray"))
wb$add_fill(dims = "C4", color = wb_color("lightgray"))
wb$add_fill(dims = "B2;B4", color = wb_color("lightblue"))

###

wb_to_df(wb)
#>   AA BBB VVV
#> 2  1   2   6
#> 3  2   3   7
#> 4  3   4   8
#> 5  4   5   9

# get the internal data frame for the worksheet
cc <- wb$worksheets[[1]]$sheet_data$cc

# remove column B
cc <- cc[cc$c_r %in% c("A", "C"), ]
# make column C the new column B
cc$r <- gsub("C", "B", cc$r)
cc$c_r <- gsub("C", "B", cc$c_r)

# push the internal data frame back into the worksheet
wb$worksheets[[1]]$sheet_data$cc <- cc

wb_to_df(wb)
#>   AA VVV
#> 2  1   6
#> 3  2   7
#> 4  3   8
#> 5  4   9

# wb$save("example_file.xlsx")
# wb$open()