library(tidyverse)
library(openxlsx)
wb.df1 <- loadWorkbook(file="./data/chap7data.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

#-Testing the presence of xls tables in the "CacData" wksht

xls.tables <- getTables(wb=wb.df1, sheet="CacData")
print(length(xls.tables))
if (length(xls.tables)>0) {
  print(length(xls.tables))
  removeTable(wb=wb.df1, sheet="CacData", table="group2pivot")
  removeTable(wb=wb.df1, sheet="CacData", table="allpivot")
}

#- write the 2 pivot tables formatted as Excel tables

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