Format Pivot Tables

[carolinavelarde]

Hi! I have been using openxlsx and openxlsx2 for the past few weeks. I'm creating pivot tables with openxlsx2 and want to know if there is a way to format currency for pivot table values. I''ve tried manually adding style to the specific cells with currency but it does not work.

[JanMarvin]

Hi @carolinavelarde ,

it's possible but the function is not well polished. I had do to dig a bit into the code to create an example, have a look here:

library(openxlsx2)

## Pivot table example 1
wb <- wb_workbook() %>% wb_add_worksheet() %>% wb_add_data(x = mtcars, inline_strings = F)

wb$add_numfmt(dims = wb_dims(x = mtcars, cols = "disp"), numfmt = "$ #,###")

df <- wb_data(wb)

# basic pivot table with filter, rows, cols and data
wb$add_pivot_table(df, dims = "A3", filter = "mpg", rows = "cyl", cols = "gear", data = "disp",
                   params = list(
                     numfmt = c(formatCode = "$ ###")
                   ))

if (interactive()) wb$open()

[JanMarvin]

There are a few more currently hardly documented options for pivot tables: https://janmarvin.github.io/ox2-book/chapters/openxlsx2_pivot_tables.html

Colors, captions etc. but all of these are a bit rough to work with.

[carolinavelarde]

thank you! How would I modify this if in data I have to count and keep as a regular number and also sum with currency?

[JanMarvin]

Like this. (I'm kinda amazed that this works 😃 )

library(openxlsx2)

## Pivot table example 1
wb <- wb_workbook() %>% wb_add_worksheet() %>% wb_add_data(x = mtcars, inline_strings = F)

wb$add_numfmt(dims = wb_dims(x = mtcars, cols = "disp"), numfmt = "$ #,###")

df <- wb_data(wb)

# basic pivot table with filter, rows, cols and data
wb$add_pivot_table(
  df,
  rows = "cyl", cols = "gear",
  data = c("disp", "hp"),
  fun = c("sum", "count"),
  params = list(
    numfmt = c(formatCode = "$ ###", formatCode = "#")
  ))

if (interactive()) wb$open()