XLConnect Problem

[Uday]

All,
I have an Excel template. I would like to use XLConnect package in R, modify the contents of the file while retaining the format of the file and save the changes. I am using the default template provided in Excel for Loan Amortization. I am unable to upload the file as .xlsx is not an allowed format.
Please note that I am open to using a different package as long as the format of the data can be retained.

# Clear Console and screen 
cat("\014") # Clear Console 
rm(list = ls(all = TRUE)) # Clear Workspace
options(java.parameters = "- Xmx1024m")

# Load desired packages
suppressPackageStartupMessages(library(XLConnect))

# Define FilePaths 
FolderPath <- "C:\\Users\\Temp\\Desktop"
TemplateFilePath <- paste(FolderPath, "LoanTemplate.xlsx", sep = "\\")
OutputFilePath <- paste(FolderPath, "MyLoan.xlsx", sep = "\\")
OutputFilePath1 <- paste(FolderPath, "MyLoan1.xlsx", sep = "\\")
OutputFilePath2 <- paste(FolderPath, "MyLoan2.xlsx", sep = "\\")

# Read Data 
TemplateData <- readWorksheetFromFile(TemplateFilePath, sheet = 'Loan Schedule', header = T)
MyData <- TemplateData
MyData[[4]][2] <- 10000

# Write specific sheet for my case, but preserve the template
file.copy(TemplateFilePath, OutputFilePath, overwrite = T)

# Trial 1
writeWorksheetToFile(OutputFilePath, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT,clearSheets = TRUE)

# Trial 2
writeWorksheetToFile(OutputFilePath1, MyData, 'Loan Schedule', styleAction = XLC$STYLE_ACTION.XLCONNECT, clearSheets = FALSE)

#Trial 3
writeWorksheetToFile(OutputFilePath2, MyData, 'Loan Schedule')

Please help. It is important for the format to be retained as much as for the updated data to show up in the new file.

Best
Uday

[rexevan]

You can open .xlsx file with readxl or openxlsx. I suggest the later because it can also write an .xlsx file.

[Uday]

@rexevan,
The issue is not about being able to open the file. I want to be able to write into the template. Retaining the format of the file is most important for my case.

[cderv]

For writing to excel, openxlsx or writexl are good alternative to XLconnect that do not rely on JAVA.
I think openxlsx can allow you to fill in your template.

[Uday]

@cderv,
Thank you for your response. I guess this proves it can be done.

Feature request: add an "update" argument to write.xlsx

I will try to test it on my case.

UPDATE:

# Clear Console and screen 
cat("\014") # Clear Console 
rm(list = ls(all = TRUE)) # Clear Workspace
#options(java.parameters = "- Xmx1024m")

# Load desired packages 
suppressPackageStartupMessages(library(openxlsx))

# Define File Path 
FolderPath <- "C:\\Users\\Test\\Desktop"
TemplateFile <- paste(FolderPath, "LoanTemplate.xlsx",sep = "\\")

wb <- loadWorkbook(TemplateFile)
writeData(wb, 'Loan Schedule', x = 10000, startCol = as.character(5), startRow = 3) 
writeData(wb, 'Loan Schedule', x = 0.05, startCol = as.character(5), startRow = 4)
saveWorkbook(wb, TemplateFile,overwrite = T)