Uday
December 20, 2018, 10:16pm
1
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
December 20, 2018, 10:46pm
2
You can open .xlsx file with readxl
or openxlsx
. I suggest the later because it can also write an .xlsx file.
Uday
December 20, 2018, 11:07pm
3
@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
December 22, 2018, 4:23pm
4
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.
3 Likes
Uday
December 23, 2018, 2:47am
5
@cderv ,
Thank you for your response. I guess this proves it can be done.
opened 04:09PM - 19 Jul 16 UTC
closed 01:24PM - 01 Feb 17 UTC
Currently write.xlsx is a great shorthand way to _create_ a workbook. I am h op… ing it would also be fairly straightforward to allow you to _update_ a workbook that already exists.
I'm envisioning a new argument to write.xlsx called `update` which takes either TRUE or FALSE as arguments. For update=TRUE, the function should first attempt to read the file and if it finds a valid excel workbook, only make updates to the sheets with data listed in the x argument. You could even leave the formatting alone and update just the data values for those sheets, which would preserve any formatting that existed previously.
This would significantly speed up workflows for folks doing work in R, who are collaborating with others using excel.
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
system
Closed
December 30, 2018, 2:47am
6
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.