CODEWITHSUNDEEP
rexcelopenxlsx2
Thomas Philips
Thomas Philips

Reputation: 1089

openxlsx2::write_xlsx overwrites existing data in an Excel worksheet even if I specify start_row and start_col

I using R 4.3.3 and am using openxlsx2 to open an Excel workbook with multiple worksheets, read data in the first four columns of each sheet one sheet at a time, process that data and then write the resulting dataframe back to the same worksheet starting in Row1 of column 10. The code I wrote follows:

PRICE_SHEETS <-c("2001", "2011", "2017", "2020", "2021", "2022", "2023")
for(i in seq_along(PRICE_SHEETS) ){
  sh <- PRICE_SHEETS[i]
  raw_prices   <- openxlsx2::wb_to_df(input_fn, sheet = sh, cols = c("A:D"))
  clean_prices <- cleanRawPrices(raw_prices, sh)
  openxlsx2::write_xlsx(price_table, file = input_fn, sheet = sh, 
                        start_row = 1, start_col = 10, 
                        col_names = TRUE, row_names = TRUE)
}

This correctly writes reads the data in the first four columns of sheet 2001 in input_fn and then writes clean_prices to cell J1 of worksheet "2001", but has the following unfortunate side effects:

  1. When it writes to cell J1 on sheet "2001", it wipes out all the data in the first 4 columns
  2. It also wipes out all the other worksheets and saves the spreadsheet. The saved spreadsheet has one worksheet ("2001") so the loop just stops.

What am I doing wrong and how can I fix it?

Sincerely and with many thanks in advance

Thomas Philips

Upvotes: 0

Views: 119

Answers (0)

Related Questions