Remove a column from a workbook

[JanMarvin]

In this SO I demonstrate how to remove a column from a workbook. I do not recommend this in any way, it is nasty and does not handle all the many cases that can break along the way. Anyhow, sometimes nasty solutions are all we have.

We start with a workbook containing the data frame below and some styles:

## 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

Now we want to remove column B (the BBB column in the data)

# 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

Voila, we are done.

[JanMarvin]

Similar approach demonstrating the use of wb_copy_cells():

## 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

left_dat <- wb_data(wb, dims = "A1:A5", colNames = FALSE)
right_dat <- wb_data(wb, dims = "C1:C5", colNames = FALSE)

wb$add_worksheet()
wb$copy_cells(data = left_dat, dims = "A1")
wb$copy_cells(data = right_dat, dims = "B1")

# wb$open()