Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
vergilcw opened this issue Jul 19, 2016 · 7 comments
Closed

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

vergilcw opened this issue Jul 19, 2016 · 7 comments

Comments

@vergilcw
Copy link

@vergilcw vergilcw commented Jul 19, 2016

Currently write.xlsx is a great shorthand way to create a workbook. I am h oping 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.

@jacquespeeters
Copy link

@jacquespeeters jacquespeeters commented Jul 26, 2016

Isn't read.xlsx(...) intented for this?

myFiles = read.xlsx(...)
#modify myFiles as you want
write.xlsx(...)

@vergilcw
Copy link
Author

@vergilcw vergilcw commented Jul 26, 2016

There are two issues I encounter when trying to use the approach you outlined:

First, if a spreadsheet has multiple sheets, you can only read in one sheet at a time (which makes sense for read.xlsx given that the output is a dataframe)

Second, write.xlsx can't just replace a single sheet, it seems to obliterate the entire workbook and make a new workbook with just one worksheet.

My feature request is to have an easy way to leave everything about an existing workbook intact, except write a dataframe to one of the sheets (either a new one, or overwrite the data on an existing sheet).

@nicholaelaw
Copy link

@nicholaelaw nicholaelaw commented Dec 29, 2016

@vergilcw I think you are looking for:

wb <- loadWorkBook(file)
writeDataTable(wb, sheet, ...)
saveWorkBook(wb, file)

This routine adds new sheet(s), and does not touch anything else in the workbook.

@vergilcw
Copy link
Author

@vergilcw vergilcw commented Feb 1, 2017

What if I have a sheet within an existing workbook, that, let's say has already been formatted in a specific way: column widths are pre-set (knowing what data will be going in there helps), maybe a specific font and font size are in the sheet already. It would be nice to just write a dataframe to that sheet, and not touch anything else about the sheet. This would be roughtly the equivalent of opening the workbook, copying data from somewhere else, and "paste values" (preserving formatting).

Neither of the solutions offered thus far get us there.

@awalker89
Copy link
Owner

@awalker89 awalker89 commented Feb 1, 2017

I believe the solution suggested by nicholaelaw does exactly what you want.
As an example using this empty & formatted file template.xlsx

library(openxlsx)
wb <- loadWorkbook("template.xlsx")
writeData(wb, sheet = "Iris Data", x = head(iris, 20))
saveWorkbook(wb, "populated_template.xlsx")
openXL("populated_template.xlsx")

@vergilcw
Copy link
Author

@vergilcw vergilcw commented Feb 1, 2017

Yes, I see you're correct. Thanks for providing an example proof! I guess from here It's up to me to create a little wrapper function for syntactical sugar that avoids the step of loading and saving the workbook, with syntax that looks something like:

updateWorkbook(x = head(iris, 20), file="myworksheet.xlsx", sheet = "Iris Data")

Shouldn't be too difficult on my end.

@vergilcw vergilcw closed this as completed Feb 1, 2017
@JDMathew
Copy link

@JDMathew JDMathew commented Jun 25, 2018

I find that the loadWorkbook() function corrupts my graphs that have specific formatting. It would be nice to be able to just update a workbook without touching the current sheets.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants