pivot-table sort

pivot_table_sort.R

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,
  "A",       "E",           "good",  0.89,
  "A",       "E",           "good",  0.94,
  "C",       "G",           "good",  0.94,
  "A",       "E",           "good",   0.9,
  "C",       "F",           "bad",    0.9
)


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")

pt <- wb$pivotTables


## this needs adding: sortType="ascending"

wb$pivotTables <- "<pivotTableDefinition xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" xmlns:mc=\"http://schemas.openxmlformats.org/markup-compatibility/2006\" mc:Ignorable=\"xr\" xmlns:xr=\"http://schemas.microsoft.com/office/spreadsheetml/2014/revision\" name=\"PivotTable1\" cacheId=\"1\" applyNumberFormats=\"0\" applyBorderFormats=\"0\" applyFontFormats=\"0\" applyPatternFormats=\"0\" applyAlignmentFormats=\"0\" applyWidthHeightFormats=\"1\" dataCaption=\"Values\" updatedVersion=\"8\" minRefreshableVersion=\"3\" useAutoFormatting=\"1\" itemPrintTitles=\"1\" createdVersion=\"8\" indent=\"0\" outline=\"1\" outlineData=\"1\" multipleFieldFilters=\"0\"><location ref=\"A3\" firstHeaderRow=\"1\" firstDataRow=\"2\" firstDataCol=\"1\" rowPageCount=\"1\" colPageCount=\"1\"/><pivotFields count=\"4\"><pivotField axis=\"axisRow\" showAll=\"0\" sortType=\"descending\"><items count=\"4\"><item x=\"0\"/><item x=\"2\"/><item x=\"1\"/><item t=\"default\"/></items></pivotField><pivotField axis=\"axisPage\" showAll=\"0\"><items count=\"4\"><item x=\"0\"/><item x=\"1\"/><item x=\"2\"/><item t=\"default\"/></items></pivotField><pivotField axis=\"axisPage\" showAll=\"0\"><items count=\"2\"><item x=\"0\"/><item t=\"default\"/></items></pivotField><pivotField dataField=\"1\" showAll=\"0\"/></pivotFields><rowFields count=\"1\"><field x=\"0\"/></rowFields><rowItems count=\"4\"><i><x/></i><i><x v=\"1\"/></i><i><x v=\"2\"/></i><i t=\"grand\"><x/></i></rowItems><pageFields count=\"1\"><pageField fld=\"1\" hier=\"-1\"/><pageField fld=\"2\" hier=\"-1\"/></pageFields><dataFields count=\"1\"><dataField name=\"Sum of Units\" fld=\"3\" baseField=\"0\" baseItem=\"0\"/></dataFields><pivotTableStyleInfo name=\"PivotStyleLight16\" showRowHeaders=\"1\" showColHeaders=\"1\" showRowStripes=\"0\" showColStripes=\"0\" showLastColumn=\"1\"/></pivotTableDefinition>"

if (interactive()) wb$open()