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