Search code examples
ropenxlsx2

Formatting a pivot table in OpenXLSX2 in R

I'm trying to add borders and a number format and a sort by units to a pivot table built with the wb_add_pivot_table function in openXLSX2, but the pivot table seems to overwrite the styling? I write this:

df<- tibble::tribble(
   ~Plant,    ~Location, ~Status,  ~Units,
  "A",       "E",           "good",  0.95,
  "C",       "F",           "good",  0.95,
  "C",       "E",           "good",  0.95,
  "B",       "E",           "good",  0.95,
  "B",       "F",           "good",  0.89,
  "C",       "E",           "good",  0.89,
  "C",       "E",           "good",  0.94,
  "C",       "G",           "good",  0.94,
  "A",       "E",           "good",   0.9,
  "C",       "F",           "bad",    0.9
)

wb <- wb_workbook()
wb$add_worksheet("Data")
wb$add_worksheet("Pivot")
wb$add_data(1, df, startCol = 1, startRow = 2)

df<- wb_data(wb, 1, dims = "A2:D10")
wb <- wb %>% wb_add_pivot_table(df, 2, dims = "A3", rows = "Plant", filter = c("Location", "Status"), data = "Units") 
wb$add_border(2, dims = "A1:B8", bottom_border = "medium", left_border= "medium", right_border = "medium", top_border = "medium")
wb$add_numfmt(2, dims = "B5:B8", numfmt = "#,##0")   

wb_save(wb, "test.xlsx", overwrite = T)

but I get this message upon saving:

enter image description here

If I hit yes, my pivot table has all the correct information, but none of the formatting. I get the same message if I put the wb_add_border and wb_add_numfmt first, and I couldn't find anything to even try to sort Units high to low. (I do not get the message if I take out the lines adding formatting).

Solution

  • There is no pivot table style format right now in openxlsx2. Until I add a way to assign the numfmt, a somewhat hacky way would be this:

    library(openxlsx2)

## example code
df<- tibble::tribble(
  ~Plant,    ~Location, ~Status,  ~Units,
  "A",       "E",           "good",  0.95,
  "C",       "F",           "good",  0.95,
  "C",       "E",           "good",  0.95,
  "B",       "E",           "good",  0.95,
  "B",       "F",           "good",  0.89,
  "C",       "E",           "good",  0.89,
  "C",       "E",           "good",  0.94,
  "C",       "G",           "good",  0.94,
  "A",       "E",           "good",   0.9,
  "C",       "F",           "bad",    0.9
)

## Create the workbook and the pivot table
wb <- wb_workbook()$
  add_worksheet("Data")$
  add_data(x = df, startCol = 1, startRow = 2)

df <- wb_data(wb, 1, dims = "A2:D10")
wb$add_worksheet("Pivot")$
  add_pivot_table(df, "Pivot", dims = "A3", rows = "Plant",
                  filter = c("Location", "Status"), data = "Units")

## add a new number style for the pivot table
numfmtid <- wb$styles_mgr$next_numfmt_id()
pivot_sum_numfmt <- create_numfmt(numfmtid, formatCode = "#,##0")
wb$add_style(pivot_sum_numfmt, "psn")


## we have to assign this style to the pivot table field
pt <- wb$pivotTables

# extract field and update the numfmtId
new_datafield <- pt %>% 
  xml_node("pivotTableDefinition", "dataFields", "dataField") %>% 
  xml_attr_mod(xml_attributes = c(numFmtId = numfmtid))

# remove the original dataField xml node and add the new
wb$pivotTables <- pt %>% 
  xml_rm_child("dataField", level = c("dataFields")) %>% 
  xml_add_child(new_datafield, level = c("dataFields"))

if (interactive()) wb$open()

    Regarding the pivot table styles, this is currently not possible. You can use one of the many predefined pivot table styles see here for an example (the line with the autoformatid comment, the valid id range for the autoformatid is 4096-4117), but everything else would require a custom pivot table style and this is currently not implemented (and has a very low priority anyways. You could create a styled table with cell formula pointing into the pivot table). In addition you might try params = list(no_style = TRUE).

    You cannot format the pivot table because it does not exist when you write the workbook. It does not exist on the worksheet, the area where the pivot table will appear is completely empty. It will not appear until the workbook is loaded into the spreadsheet software (imagine that the recipe for how to create the table exists, and your spreadsheet is instructed to initially create the table during the load process). Anything else would have required complete reverse engineering of pivot tables, and the current implementation was already complicated enough.