How do I update values in (and remove old values from) data for pivot tables in excel from R?

Question

I have been asked to convert our power queries to R, which I have managed to do. Unfortunatly, I must now make the output look exactly the same as the old one, which means pivot tables in excel:

My strategy involves updating the data sheet for the pivot table while keeping the pivot table itself unchanged (as in the answer to this question). Unfortunately I can't overwrite the sheet itself (openxlsx throws an error complaining that headers can't be overwritten). I can delete the sheet and create a new one with the same name, but that erases the link to the pivot table. I have managed to update the "values" for the data sheet using the following code:

# Load the copied workbook
library(openxlsx)
wb <- loadWorkbook(copied_file)

data <- readWorkbook(wb, sheet = "Data veckostatistik aktiv dos")

# Remove all but the first row (headers)
removeRow(wb, sheet = "Data veckostatistik aktiv dos", rows = 2:nrow(data))

# Add new worksheets and data
writeData(wb, "Data veckostatistik aktiv dos", test, startRow = 2, colNames = FALSE)


saveWorkbook(wb, copied_file, overwrite = TRUE)

This preserves the link to the pivot table. Unfortunately some obsolete data points remain, since my old data set is larger than my new one. These obsolete data points are not shown (erroneously) in my pivot table.

So what I would need to do is to somehow delete the old data values from the sheet and replace them with new ones, without breaking the link to the existing pivot table. Is there a way of doing this?

Repex:

new data:

structure(list(region = c("10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10"), region_namn = c("Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län"), kommun = c("1060", "1060", "1060", "1060", 
"1060", "1060", "1060", "1060", "1060", "1060", "1060", "1060", 
"1060", "1060", "1060", "1060", "1060", "1060", "1060", "1060", 
"1060", "1060", "1080", "1080", "1080", "1080", "1080", "1080", 
"1080", "1080", "1080", "1080", "1080", "1080", "1080", "1080", 
"1080", "1080", "1080", "1080", "1080", "1080", "1080", "1080", 
"1081", "1081", "1081", "1081", "1081", "1081", "1081", "1081", 
"1081", "1081", "1081", "1081", "1081", "1081", "1081", "1081", 
"1081", "1081", "1081", "1081", "1081", "1081", "1082", "1082", 
"1082", "1082", "1082", "1082", "1082", "1082", "1082", "1082", 
"1082", "1082", "1082", "1082", "1082", "1082", "1082", "1082", 
"1082", "1082", "1082", "1082", "1083", "1083", "1083", "1083", 
"1083", "1083", "1083", "1083", "1083", "1083", "1083", "1083", 
"1083", "1083", "1083", "1083", "1083", "1083", "1083", "1083", 
"1083", "1083"), kommun_namn = c("Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg"
), `År/Vecka` = c("2023-09", "2023-10", "2023-11", "2023-12", 
"2023-13", "2023-14", "2023-15", "2023-16", "2023-17", "2023-18", 
"2023-19", "2023-20", "2023-21", "2023-22", "2023-23", "2023-24", 
"2023-25", "2023-26", "2023-27", "2023-28", "2023-29", "2023-30", 
"2023-09", "2023-10", "2023-11", "2023-12", "2023-13", "2023-14", 
"2023-15", "2023-16", "2023-17", "2023-18", "2023-19", "2023-20", 
"2023-21", "2023-22", "2023-23", "2023-24", "2023-25", "2023-26", 
"2023-27", "2023-28", "2023-29", "2023-30", "2023-09", "2023-10", 
"2023-11", "2023-12", "2023-13", "2023-14", "2023-15", "2023-16", 
"2023-17", "2023-18", "2023-19", "2023-20", "2023-21", "2023-22", 
"2023-23", "2023-24", "2023-25", "2023-26", "2023-27", "2023-28", 
"2023-29", "2023-30", "2023-09", "2023-10", "2023-11", "2023-12", 
"2023-13", "2023-14", "2023-15", "2023-16", "2023-17", "2023-18", 
"2023-19", "2023-20", "2023-21", "2023-22", "2023-23", "2023-24", 
"2023-25", "2023-26", "2023-27", "2023-28", "2023-29", "2023-30", 
"2023-09", "2023-10", "2023-11", "2023-12", "2023-13", "2023-14", 
"2023-15", "2023-16", "2023-17", "2023-18", "2023-19", "2023-20", 
"2023-21", "2023-22", "2023-23", "2023-24", "2023-25", "2023-26", 
"2023-27", "2023-28", "2023-29", "2023-30"), pop = c(10738L, 
10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 
10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 10738L, 
10738L, 10738L, 10738L, 10738L, 10738L, 53220L, 53220L, 53220L, 
53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 
53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 53220L, 
53220L, 53220L, 53220L, 22943L, 22943L, 22943L, 22943L, 22943L, 
22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 
22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 22943L, 
22943L, 25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 
25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 
25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 25932L, 14278L, 
14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 
14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 14278L, 
14278L, 14278L, 14278L, 14278L, 14278L), Antal = c(145, 48, 81, 
68, 65, 45, 93, 88, 393, 56, 83, 15, 81, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 764, 772, 649, 464, 435, 225, 411, 865, 798, 368, 397, 
186, 161, 59, 14, 76, 23, 31, 0, 37, 0, 18, 167, 208, 282, 218, 
217, 171, 170, 338, 503, 214, 229, 87, 60, 34, 14, 49, 17, 7, 
0, 8, 5, 6, 147, 275, 294, 254, 252, 208, 303, 248, 546, 144, 
192, 63, 39, 38, 16, 5, 0, 0, 0, 0, 0, 8, 46, 138, 77, 158, 119, 
90, 42, 247, 281, 66, 44, 23, 28, 22, 0, 43, 0, 16, 0, 0, 0, 
10)), row.names = c(NA, -110L), class = c("tbl_df", "tbl", "data.frame"
))

old data (in existing sheet):

structure(list(region = c("10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10", "10", "10", "10", "10", 
"10", "10", "10", "10", "10", "10", "10"), region_namn = c("Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län", 
"Blekinge län", "Blekinge län", "Blekinge län", "Blekinge län"
), kommun = c("1060", "1060", "1060", "1060", "1060", "1060", 
"1060", "1060", "1060", "1060", "1060", "1060", "1060", "1060", 
"1060", "1060", "1060", "1060", "1060", "1060", "1060", "1060", 
"1060", "1060", "1060", "1060", "1060", "1060", "1060", "1080", 
"1080", "1080", "1080", "1080", "1080", "1080", "1080", "1080", 
"1080", "1080", "1080", "1080", "1080", "1080", "1080", "1080", 
"1080", "1080", "1080", "1080", "1080", "1080", "1080", "1080", 
"1080", "1080", "1080", "1080", "1081", "1081", "1081", "1081", 
"1081", "1081", "1081", "1081", "1081", "1081", "1081", "1081", 
"1081", "1081", "1081", "1081", "1081", "1081", "1081", "1081", 
"1081", "1081", "1081", "1081", "1081", "1081", "1081", "1081", 
"1081", "1082", "1082", "1082", "1082", "1082", "1082", "1082", 
"1082", "1082", "1082", "1082", "1082", "1082", "1082", "1082", 
"1082", "1082", "1082", "1082", "1082", "1082", "1082", "1082", 
"1082", "1082", "1082", "1082", "1082", "1082", "1083", "1083", 
"1083", "1083", "1083", "1083", "1083", "1083", "1083", "1083", 
"1083", "1083", "1083", "1083", "1083", "1083", "1083", "1083", 
"1083", "1083", "1083", "1083", "1083", "1083", "1083", "1083", 
"1083", "1083", "1083"), kommun_namn = c("Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Olofström", "Olofström", "Olofström", 
"Olofström", "Olofström", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", "Karlskrona", 
"Karlskrona", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", "Ronneby", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", 
"Karlshamn", "Karlshamn", "Karlshamn", "Karlshamn", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", "Sölvesborg", 
"Sölvesborg", "Sölvesborg", "Sölvesborg"), `År/Vecka` = c("2022-33", 
"2022-34", "2022-35", "2022-36", "2022-37", "2022-38", "2022-39", 
"2022-40", "2022-41", "2022-42", "2022-43", "2022-44", "2022-45", 
"2022-46", "2022-47", "2022-48", "2022-49", "2022-50", "2022-51", 
"2022-52", "2023-01", "2023-02", "2023-03", "2023-04", "2023-05", 
"2023-06", "2023-07", "2023-08", "2023-09", "2022-33", "2022-34", 
"2022-35", "2022-36", "2022-37", "2022-38", "2022-39", "2022-40", 
"2022-41", "2022-42", "2022-43", "2022-44", "2022-45", "2022-46", 
"2022-47", "2022-48", "2022-49", "2022-50", "2022-51", "2022-52", 
"2023-01", "2023-02", "2023-03", "2023-04", "2023-05", "2023-06", 
"2023-07", "2023-08", "2023-09", "2022-33", "2022-34", "2022-35", 
"2022-36", "2022-37", "2022-38", "2022-39", "2022-40", "2022-41", 
"2022-42", "2022-43", "2022-44", "2022-45", "2022-46", "2022-47", 
"2022-48", "2022-49", "2022-50", "2022-51", "2022-52", "2023-01", 
"2023-02", "2023-03", "2023-04", "2023-05", "2023-06", "2023-07", 
"2023-08", "2023-09", "2022-33", "2022-34", "2022-35", "2022-36", 
"2022-37", "2022-38", "2022-39", "2022-40", "2022-41", "2022-42", 
"2022-43", "2022-44", "2022-45", "2022-46", "2022-47", "2022-48", 
"2022-49", "2022-50", "2022-51", "2022-52", "2023-01", "2023-02", 
"2023-03", "2023-04", "2023-05", "2023-06", "2023-07", "2023-08", 
"2023-09", "2022-33", "2022-34", "2022-35", "2022-36", "2022-37", 
"2022-38", "2022-39", "2022-40", "2022-41", "2022-42", "2022-43", 
"2022-44", "2022-45", "2022-46", "2022-47", "2022-48", "2022-49", 
"2022-50", "2022-51", "2022-52", "2023-01", "2023-02", "2023-03", 
"2023-04", "2023-05", "2023-06", "2023-07", "2023-08", "2023-09"
), pop = c(10738, 10738, 10738, 10738, 10738, 10738, 10738, 10738, 
10738, 10738, 10738, 10738, 10738, 10738, 10738, 10738, 10738, 
10738, 10738, 10738, 10738, 10738, 10738, 10738, 10738, 10738, 
10738, 10738, 10738, 53220, 53220, 53220, 53220, 53220, 53220, 
53220, 53220, 53220, 53220, 53220, 53220, 53220, 53220, 53220, 
53220, 53220, 53220, 53220, 53220, 53220, 53220, 53220, 53220, 
53220, 53220, 53220, 53220, 53220, 22943, 22943, 22943, 22943, 
22943, 22943, 22943, 22943, 22943, 22943, 22943, 22943, 22943, 
22943, 22943, 22943, 22943, 22943, 22943, 22943, 22943, 22943, 
22943, 22943, 22943, 22943, 22943, 22943, 22943, 25932, 25932, 
25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 
25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 
25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 25932, 
14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 
14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 
14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 14278, 
14278, 14278), Antal = c(0, 133, 80, 138, 266, 629, 478, 347, 
369, 179, 170, 16, 219, 86, 63, 69, 104, 116, 54, 29, 74, 45, 
25, 11, 9, 6, 0, 0, 53, 30, 52, 740, 2077, 2272, 2121, 1831, 
1775, 1538, 1486, 1141, 907, 704, 658, 408, 345, 603, 750, 556, 
181, 197, 332, 180, 87, 67, 31, 55, 10, 280, 18, 259, 290, 524, 
846, 711, 806, 868, 809, 584, 430, 249, 326, 217, 155, 172, 193, 
309, 253, 74, 8, 160, 111, 37, 14, 9, 29, 0, 83, 10, 24, 332, 
186, 748, 1538, 1494, 1352, 709, 215, 820, 330, 219, 181, 369, 
159, 302, 350, 182, 13, 13, 154, 73, 44, 28, 24, 18, 21, 49, 
8, 55, 329, 330, 430, 665, 436, 329, 247, 313, 150, 276, 158, 
117, 166, 136, 139, 184, 88, 49, 54, 80, 74, 15, 15, 20, 0, 25, 
29)), class = c("tbl_df", "tbl", "data.frame"), row.names = c(NA, 
-145L))

score 1 · Answer 1 · answered Aug 04 '23 at 15:30

It would be possible to modify the sheet (load a workbook, delete the data and replace it (wb_load(), wb_clean_sheet(), and wb_add_data() in openxlsx2). Afterwards you have to open the sheet in spreadsheet software and refresh it.

Another approach would be to create the pivot table with openxlsx2s wb_add_pivot_table(). If you want to read the data afterwards you still have to open and save the file after creation.


library(openxlsx2)

## Assumtions
# Radetiketter is just the cell name
# Andel is the ratio (we calculate it in R)
# the data is in an object data
data$Radetiketter <- data$`År/Vecka`
data$Andel <- data$Antal / sum(data$Antal)

# create the workbook with the data and a numformat for the percentage values
wb <- wb_workbook()$add_worksheet()$
  add_data(x = data)$add_numfmt(dims = "I2:I111", numfmt = "#0.#%")

# create the data for the pivot table
df <- wb_data(wb)

# create the pivot table sheet and insert some text above the position where the pivot table will appear
wb$add_worksheet("Pivot")
wb$add_data(dims = "A1", x = "My strategy involves updating the data sheet for the pivot table while keeping the pivot table itself unchanged")
wb$add_data(dims = "A2", x = "I can delete the sheet and create a new one with the same name")

# create the pivot table
wb$add_pivot_table(df, sheet = "Pivot", dims = "A6", rows = "År/Vecka", data = c("Antal", "Andel"), fun = c("sum"), filter = "kumun_namn")

# open the workbook
if (interactive()) wb$open()

How do I update values in (and remove old values from) data for pivot tables in excel from R?

1 Answers1