Support export to specific worksheet

[leeper]

It would be awesome to have support for writing to a specific worksheet within a workbook. In rio, we currently do that using some complex openxlsx code that either adds a named worksheet or overwrites that sheet if it exists. Basically, current behavior is:

# export to first sheet in new workbook
rio::export(mtcars, "mtcars.xlsx")

# export to a named sheet in new or existing workbook
rio::export(mtcars, "mtcars.xlsx", which = "sheetname")

Would be awesome to switch over to writexl for that.

[jeroen]

We can easily add support for writing (multiple) data frames to an xlsx with named sheets, but I don't think our C library can modify existing xlsx files if that's what you're after. You'd have to import it with readxl and then write a new xlsx file with writexl.

Edit: turns out this is FAQ 1 of the library:

Q. Can Libxlsxwriter use an existing Excel file as a template? No. Libxlsxwriter is designed only as a file writer. It cannot read or modify an existing Excel file.

[jeroen]

@leeper I have added support for writing multiple named sheets to an xlsx, see https://github.com/ropensci/writexl/issues/3. Does this help?

[flodel]

Hello, my wish might be a bit different from @leeper's but I think it still fits within this issue's title: <>.

At work, I maintain a script (excel_paste_csv) that writes data into an Excel workbook (xlsx or xlsm). A call to the script from the command line might look like this:

excel_paste_csv output.xlsx template.xlsx file1.csv Sheet1 file2.csv Sheet2!B2

and it will:

• open the input worbook template.xlsx
• paste the contents of file1.csv into Sheet1 (from cell A1, the default)
• paste the contents of file2.csv into Sheet2 (from cell B2, as specified)
• save the resulting worbook into output.xlsx

Typically, the input workbook (template.xlsx) is not empty. Instead, it already contains raw data, formats, formulas, analysis, graphs, etc. And by pasting new data into the template, we create an updated report.

To support this, I have written a function to paste a data.frame (or matrix) into an existing worksheet, at a specified location (row/col indices or address or named range). Also, the pasting is preserving all existing formats. This is currently implemented using the rJava>xlsxjars>xlsx combo which is hard to install and maintain. I would welcome an alternative if you think this is possible within your own package. Regards.

[timwinke]

Hi, I would also be interested in such a feature, that is, (over)writing a specific spreadsheet of an existing xlsx file that contains already multiple sheets.

[jeroen]

Unfortunately the library we wrap does not support this: https://libxlsxwriter.github.io/faq.html