Exporting an interactive pivot table to excel

General
1

At my company, Excel pivot tables are an extremely popular way to view and manipulate data. Of course, we all know that pivots are really just aggregation operations, easily accomplished with the tidyverse, but the interactivity of the pivot table is a powerful thing. I now find myself in a situation where the most effective way forward is to provide my raw data as a data source, but also provide an Excel pivot table so that business users have a convenient way to interactively aggregate/disaggregate as needed.

Question being, is there any set of R tools that would allow me to take a data frame and export an Excel pivot table, one that properly has the expand/collapse functionality? I've looked at both pivottabler and rpivottable, both of which seem to rely on HTML widgets. Is there a package out there that can produce the Excel-native format?

2 Likes
2

Hi. Looking for the same. Any leads?

3

I had no luck. Packages like OfficeR (impossible to Google: https://davidgohel.github.io/officer/index.html) can do a lot, but PivotTables aren't yet supported. Office is all XML under the hood these days, so conceivably someone could create the functionality to generate pivots straight from R, but as far as I know it hasn't been done yet.

1 Like
4

it seems impossible to export rendered html element to Excel directly. If one option is building your report or dashboard by Rmarkdown, or using tidyr::spread and export the result data set into Excel.

5

Hey, I was just wondering if anyone meanwhile knows the solution.