How modify numbers in Excel using R?

Question

I have an Excel file test.xlsx on my desktop and have a dataframe in R. How can I overwrite numbers in Excel file sheet "Capitals"? Say table in excel starts at B6 and has the same size as table in R.

I tried to run following command, however, it creates a new excel file, however I need to make changes in the existing one.

write.xlsx(df, file = "//Desktop//test.xlsx",
           sheetName = "Capitals", append = TRUE)

Does this help you out? https://stackoverflow.com/questions/34172353/how-do-i-modify-an-existing-a-sheet-in-an-excel-workbook-using-openxlsx-package — setty, Mar 04 '21 at 17:54
using method writeData() gives an error: `Error: NoSuchMethodError (Java): org.apache.poi.ss.usermodel.FillPatternType.getCode()S` — Priit Mets, Mar 05 '21 at 10:32
Did you create a workbook object using the ```loadWorkbook()``` function? The ```writeData()``` function requires a ```wb``` object to write to. — setty, Mar 08 '21 at 19:41
See this documentation for more explanation on the ```writeData()``` function: https://www.rdocumentation.org/packages/openxlsx/versions/4.2.3/topics/writeData — setty, Mar 08 '21 at 19:42

score 0 · Answer 1 · answered May 18 '23 at 11:00

You can consider the following approach :

library(RDCOMClient)

xlApp <- COMCreate("Excel.Application")
xlApp[["DisplayAlerts"]] <- FALSE
xlApp[["Visible"]] <- TRUE

path_To_Excel_File <- "D:/excel_File.xlsx"
xlWbk <- xlApp$Workbooks()$Open(path_To_Excel_File)

y <- rnorm(3)
x <- rnorm(3)
df <- data.frame(x, y)

nb_Row <- nrow(df)
nb_Col <- ncol(df)

sheet <- xlWbk$Sheets(1)

for(i in 1 : (5 + nb_Row))
{
  for(j in 1 : (1 + nb_Col))
  {
    cell_Obj <- sheet$cells(i, j)    
    cell_Obj[["Value"]] <- df[i, j]
  }
}

xlWbk$SaveAs(path_To_Excel_File)
xlWbk$Close()

How modify numbers in Excel using R?

1 Answers1