library(tidyverse)
library(openxlsx)
wb.df1 <- loadWorkbook(file="./data/chap6datasets.xlsx")
names(wb.df1)
cac.df <- read.xlsx(xlsxFile=wb.df1, sheet = "CacData",
                    cols = 1:5)
#-Pivot table for Group-2 subjects only and Var1 variable
pivot1.df <- cac.df %>%
  filter(Group==2) %>%
  select(-c(Group,Var2)) %>%
  pivot_wider(names_from =Raters,values_from=Var1,
              values_fill=0)
pivot1.df

#-- Pivot table for all subjects (groups 1 and 2 combined) 
#   and for the Var1 and Var2 variables
pivot2.df <- cac.df %>%
  group_by(Group) %>%
  pivot_wider(names_from =Raters,values_from=c(Var1,Var2),
              values_fill=0)
pivot2.df

#- Writing both pivot tables to an Excel Worksheet 
#- formatted as Excel tables
removeTable(wb=wb.df1, sheet="CacData", table="group2pivot")
removeTable(wb=wb.df1, sheet="CacData", table="allpivot")
writeData(wb=wb.df1, sheet="CacData",
          x="Pivot Table/Group=2 & Variable=Var1",
          startRow=1, startCol=9)
writeData(wb=wb.df1, sheet="CacData",
          x="Pivot Table/All Groups & Variables",
          startRow=1, startCol=14)
writeDataTable(wb=wb.df1, sheet="CacData", x=pivot1.df, 
               tableName = "group2pivot", startRow=2, startCol=9, 
               rowNames = FALSE)
writeDataTable(wb=wb.df1, sheet="CacData", x=pivot2.df, 
               tableName = "allpivot", startRow=2, startCol=14, 
               rowNames = FALSE)
saveWorkbook(wb.df1, "./data/Pivot Tables.xlsx", 
             overwrite = TRUE)

#- Export an R both Pivot Tables to a New Excel Workbook

write.xlsx(x=list(pivot1.df,pivot2.df),
           file="./data/workbook of Pivot Tables.xlsx", 
           sheetName=c("Pivot(Group 2)","Pivot(All Groups)"),
           overwrite = TRUE)