two pivot table issues

[JanMarvin]

• pivot tables can not have duplicated names (probably the same logic as in distinct()).

library(openxlsx2)
# boom
dd <- data.frame(Foo = 1, foo = 2, bar = 2)
wb <- wb_workbook()$add_worksheet()$add_data(x = dd)
pt <- wb_data(wb)
wb$add_pivot_table(pt, cols = "Foo", data = "bar")
if (interactive()) wb$open()

# fine
dd <- data.frame(Foo = 1, foo2 = 2, bar = 2)
wb <- wb_workbook()$add_worksheet()$add_data(x = dd)
pt <- wb_data(wb)
wb$add_pivot_table(pt, cols = "Foo", data = "bar")
if (interactive()) wb$open()

• Sorting is still an issue. In the example below I select three cases (`"b", "e", "d"). But somehow I am unable to sort it correctly?

library(openxlsx2)
dd <- data.frame(Foo = sample(0:1, 26, TRUE), foo2 = letters, bar = TRUE)
wb <- wb_workbook()$add_worksheet()$add_data(x = dd)
pt <- wb_data(wb)

# so how am I supposed to order this?
wb$
  add_pivot_table(pt, rows = "foo2", data = "bar",
                  pivot_table = "pt1",
                  params = list(
                    sort_item = list(foo2 = c("b", "e", "d"))
                  ))$
  add_slicer(pt, slicer = "foo2", pivot_table = "pt1", dims = "A10",
             params = list(
               choose = c(foo2 = 'x %in% c("b", "e", "d")')
             ))
#> Warning in get_items(x, i, sort_itm, FALSE, choo): Length of sort order for 'Foo' does not match required length. Is 3, needs 26.
#> Check `openxlsx2:::distinct()` for the correct length. Resetting.Length of sort order for 'foo2' does not match required length. Is 3, needs 26.
#> Check `openxlsx2:::distinct()` for the correct length. Resetting.Length of sort order for 'bar' does not match required length. Is 3, needs 26.
#> Check `openxlsx2:::distinct()` for the correct length. Resetting.

if (interactive()) wb$open()

• Bonus issue: I have not yet found a way to disable sub totals. Probably it needs ..._subtable = "0" added to the field

[JanMarvin]

Excel solves the first issue by adding a number to the variable name. So in the list of variables it appears as: Foo and foo2.