Append function of xlsx in openxlsx

General
1

In xlsx I had the possibility to append the new data on the bottom.
Does openxlsx have a similar function?
As a different solution I had to create a workbook for each data frame and copy paste it in the sheet.It does work till the 5 th loop of the second forloop. Then it shows an error,
I would rather just put all values in one Sheet and append the new values on the bottom (with a header that we are now in ff 02,
Can anyone help? :slight_smile:
Here is my code:

for (ff in 1:12) {

a= sprintf("Actuals_predict_r_%02d_hwa", ff)
addWorksheet(wb,a)

if ( ff==1) {
writeData (wb,a, Actuals_predict_r_01_hwa)}
if ( ff==2) {
writeData (wb,a, Actuals_predict_r_02_hwa)}
if ( ff==3) {
writeData (wb,a, Actuals_predict_r_03_hwa)}
if ( ff==4) {
writeData (wb,a, Actuals_predict_r_04_hwa)}
if ( ff==5) {
writeData (wb,a, Actuals_predict_r_05_hwa)}
if ( ff==6) {
writeData (wb,a, Actuals_predict_r_06_hwa)}
if ( ff==7) {
writeData (wb,a, Actuals_predict_r_07_hwa)}
if ( ff==8) {
writeData (wb,a, Actuals_predict_r_08_hwa)}
if ( ff==9) {
writeData (wb,a, Actuals_predict_r_09_hwa)}
if ( ff==10) {
writeData (wb,a, Actuals_predict_r_10_hwa)}
if ( ff==11) {
writeData (wb,a, Actuals_predict_r_11_hwa)}
if ( ff==12) {
writeData (wb,a, Actuals_predict_r_12_hwa)}
}

for (ff in 1:12) {

b= sprintf("Actuals_predict_r_%02d_ets", ff)
addWorksheet(wb,b)

if ( ff==1) {
writeData (wb, b, Actuals_predict_r_01_ets)}
if ( ff==2) {
writeData (wb, b, Actuals_predict_r_02_ets)}
if ( ff==3) {
writeData (wb, b, Actuals_predict_r_03_ets)}
if ( ff==4) {
writeData (wb, b, Actuals_predict_r_04_ets)}
if ( ff==5) {
writeData (wb, b, Actuals_predict_r_05_ets)}
if ( ff==6) {
writeData (wb, b, Actuals_predict_r_06_ets)}
if ( ff==7) {
writeData (wb, b, Actuals_predict_r_07_ets)}
if ( ff==8) {
writeData (wb, b, Actuals_predict_r_08_ets)}
if ( ff==9) {
writeData (wb, b, Actuals_predict_r_09_ets)}
if ( ff==10) {
writeData (wb, b, Actuals_predict_r_10_ets)}
if ( ff==11) {
writeData (wb, b, Actuals_predict_r_11_ets)}
if ( ff==12) {
writeData (wb, b, Actuals_predict_r_12_ets)}
}

Save workbook to working directory
saveWorkbook(wb, file = "Actuals_forecast.xlsx", overwrite = TRUE)

1 Like
2

I use the following pseudo-code to "append" new information to the same worksheet:

df_for_workbook <- # Code to create data frame here

out_xlsx <- # File name here

# Check to see if file doesn't exist
  if (!file.exists(out_xlsx))  {
    # Create workbook using openxlsx
    wb <- createWorkbook()
    # Add worksheet
    addWorksheet(wb, "worksheetName")
    # Write data frame to new worksheet
    writeData(wb, "worksheetName", df_for_workbook)
    # Save file
    saveWorkbook(wb, file = out_xlsx)
  } else {
    # Read in existing data
    old_wb <-
      readWorkbook(out_xlsx,
                   sheet = "worksheetName",
                   detectDates = TRUE)
    # Append new data
    new_data <-
      bind_rows(old_wb,
                df_for_workbook)
    # Load and write updated data frame to existing worksheet
    wb <-  loadWorkbook(out_xlsx)
    writeData(wb, "worksheetName", new_data)
    # Save file
    saveWorkbook(wb, out_xlsx, overwrite = TRUE)
  }
4 Likes