[FR/pivot table] Allow ordering of cases

[JanMarvin]

See https://stackoverflow.com/q/77062619/12340029

Basically we should allow setting the order here manually:

openxlsx2/R/helper-functions.R

Line 638 in 0471257

c(order(distinct(data[[x]])) - 1L, "default"),

[Amaru6]

Hi there, I noticed something while using this new functionality:
While this seem to work okay:

library(tidyverse); library(openxlsx2)

tbl_1 <- tibble(
  var_1 = seq(from = ymd("2023-01-01"),
              to = ymd("2024-03-31"),
              by = "month"),
  var_2 = seq_along(var_1)
) |> 
  uncount(2) |> 
  mutate(
    year = year(var_1),
    month = month(var_1, label = TRUE)
  )

wb_1 |> 
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum",
    params = list(
      sort_item = list(month = 12:1)
    )
  )

The following does not order properly the values of the month variable:

tbl_1 |> filter(var_1 != ymd("2023-01-01")) -> tbl_prueba_2
wb_1 <- wb_workbook() |>
  wb_add_worksheet() |>
  wb_add_data(x = tbl_prueba_2)
df <- wb_data(wb_1)
wb_1 |>
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum",
    params = list(
      sort_item = list(month = 12:1)
    )
  ) 

I guess it has something to do with the fact that not all levels of the month variable are present in all the years. So my workaround was to use complete(tbl_prueba_2, year,month) to create those levels, and then pass that df to wb_add_data and so on. Does it always have to be like that or is there something I'm missing ? Thanks in advance.

[JanMarvin]

Thanks for the report. Yes, this is due to the fact how item_selection works. It selects the unique variables in character form and in your case it looked like this:

 [1] 11 10  9  8  7  6  5  4  3  2  1 12
 [1] "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov" "Dec" "Jan"

The df object has no idea about the factor month, because in open xml there are only characters and numerics.

I have added a solution using characters for item sorting. The following should work in more cases (kinda reluctant to write all cases ...).

So this should work with #842 :

library(tidyverse); library(openxlsx2)

tbl_1 <- tibble(
  var_1 = seq(from = ymd("2023-01-01"),
              to = ymd("2024-03-31"),
              by = "month"),
  var_2 = seq_along(var_1)
) |> 
  uncount(2) |> 
  mutate(
    year = year(var_1),
    month = month(var_1, label = TRUE)
  )

tbl_1 |> filter(var_1 != ymd("2023-01-01")) -> tbl_prueba_2
wb_1 <- wb_workbook() |>
  wb_add_worksheet() |>
  wb_add_data(x = tbl_prueba_2)
df <- wb_data(wb_1)
wb_1 <- wb_1 |>
  wb_add_pivot_table(
    x = df,
    cols = c("year", "month"),
    data = "var_2",
    fun = "sum",
    params = list(
      sort_item = list(month = rev(levels(tbl_1$month)))
    )
  ) 

if (interactive()) wb_1$open()

Thanks for your testing, I have no need for most of these therefore it is unlikely that they would have been fixed otherwise. But please make the examples reproduce able as in I can copy & paste them into my R and run them (the upper does isn't and the lower neither; for instance have a look at reprex::reprex()).