Feature request: adding pivot tables

[swordguyman]

Hello,

Is it possible to have functionality to add pivot tables into worksheets?

[awalker89]

looks difficult - probably not going to happen anytime soon. An alternative is to create a template xlsx file with the pivot table set up and use openxlsx to write over the data the pivot table is looking at.

Will also need to set the pivot table in the template to auto refresh https://support.office.com/en-us/article/Update-refresh-data-in-a-PivotTable-d3f74ee2-0da7-4aef-8b2f-481bb2f18218

code will look something like this

wb <- loadWorkbook("template_with_pivot_table.xlsx")
writeData(wb, sheet = 1, x = some_new_data)
saveWorkbook(wb, "template_with_data_in_it.xlsx")

[audiolion]

maybe creating pivot tables, validation rules, and other things that are generally difficult to do could be done by running generic vbscript code from R on the workbook creation? the vbscript through its api could handle all the creation of pivot tables and be abstracted into R functions. Not sure if calling vbscript is possible though

[kerry-ja]

It is possible to call vbscript from R. I used this once to have a pattern in a cell. The code I used was:

# Unable to save it using Openxlsx with a pattern in a cell, so do that using VB Script ----
#Create a vbscript file 
FileName <- paste0(dirname(ExcelFile),"/ChangePattern.vbs")
fileConn <- file(FileName)
writeLines(c("Dim oExcel",
             paste0("Set oExcel = CreateObject(",'"',"Excel.Application",'"',")"),
             "",
             "' Specify parameters for Excel Object",
             "oExcel.Visible = False",
             "oExcel.DisplayAlerts = False",
             "oExcel.AlertBeforeOverwriting = False",
             "",
             paste0("Set oWorkbook = oExcel.Workbooks.Open(",'"',ExcelFile,'")'),
             "oExcel.Cells(5, 9).Interior.Pattern = 14",
             "",
             "' Save and close Microsoft Excel workbook",
             "oWorkbook.Save",
             "oWorkbook.Close",
             "",
             "' Exit Microsoft Excel",
             "oExcel.Quit",
             "Set oWorkbook = Nothing",
             "Set oExcel = Nothing"), fileConn)
close(fileConn)

# Run the vbscript
shell(shQuote(normalizePath(FileName)), "cscript", flag = "//nologo")

# Delete the vbscript
file.remove(FileName)