create excel pivot tables from R

Question

I am performing a number of data transformations within R. My manager wants the output to be an excel export with an excel pivot table, with all the features that an excel pivot table offers (such as double clicking to see data makeup). Does anyone know of a way to create excel pivot tables from R? I'm currently considering running VBA code through R just to create that pivot table; but I wanted to know what others think. Thanks!

There are ways to replicate some functionality of pivot tables in R (for instance cran.r-project.org/web/packages/pivottabler/vignettes/…), but if the end goal is an excel pivot table, I don't know what value would be created by sending to R and then back. — Jon Spring

mutaween464 mutaween464 · Accepted Answer · 2019-01-30T01:55:48

I came up with a unique solution in case anyone else needs to do this. I create a excel template workbook ahead of time (this can be reused over and over). I have the pivot table look for a specific named table. Then from R, I load in the excel template workbook, load in my data from R into excel using openxlsx. I use openxlsx's feature to create a named data table; naming it the name the pivot table looks for. Then I save the excel file as a new file. The pivot table is automatically populated from the data table.

create excel pivot tables from R

1 Answers