XLConnect Problem

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

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

@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.

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

3 Likes

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


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)

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.