library(tidyverse)
library(xlsx)
passWD <- "Microsoft365"
wb.df1 <- loadWorkbook(file="./data/chap6datasets.xlsx",
                       password = passWD)
sht.names <- names(getSheets(wb.df1))
print(sht.names)

#-- Reading the password-protected Excel workbook -
irrData.df <- read.xlsx(file="./data/chap6datasets.xlsx", 
                        sheetName = "IrrData",
                        rowIndex = c(2:17),
                        colIndex = c(19:24),
                        password = passWD)

#-- Compute summary table with max values per Group and Target
summary.df <- as_tibble(irrData.df) %>%
  group_by(Group,Target) %>%
  summarise(across(c(J1,J2,J3,J4),mean)) %>%
  mutate(across(c(J1,J2,J3,J4), max, 
                .names="gMax.{.col}")) %>%
  ungroup() %>%
  mutate(across(!c(Group,Target),round,3))
print(summary.df)

#-- Write the input file (summary.df) & the summary table
#   to a new Excel workbook named chap8data1a.xlsx

write.xlsx(x = as.data.frame(irrData.df), 
           file="./data/chap8data1a.xlsx", 
           sheetName = "Input Data",row.names = FALSE)
write.xlsx(x = as.data.frame(summary.df), 
           file="./data/chap8data1a.xlsx", 
           sheetName = "summaryStats1",append = TRUE,
           row.names = FALSE)

# Write the data frame "summary.df" to a newly-created
# worksheet named "summaryStats2", &  add some formatting

wb.df2 <- loadWorkbook(file="./data/chap8data1a.xlsx")
my.sheet <- createSheet(wb=wb.df2, sheetName="summaryStats2")

MaxRow <- summary.df %>% #Calculating the max of each column
  summarise(across(c(Group,Target),~"All(Max)"),
            across(!c(Group,Target),max))
summary.df %>% #change Target type to char
  mutate(Target=as.character(Target))-> summary1.df

# Creating 4 cell blocks that define, 
# (1) The top label row, 
# (2) The bottom row containing column max values, 
# (3) The first 2 columns containing group & target labels, 
# (4) All cells containing input data points.

topRow.cb <- CellBlock(sheet=my.sheet, startRow=5, 
                       startColumn=3, noRows = 1, 
                       noColumns = 10)
botRow.cb <- CellBlock(sheet=my.sheet, startRow=11, 
                       startColumn=3, noRows = 1, 
                       noColumns = 10)
rlabels.cb <- CellBlock(sheet=my.sheet, startRow=6, 
                        startColumn=3, noRows = 5, 
                        noColumns = 2)
data.cb <- CellBlock(sheet=my.sheet, startRow=6, 
                     startColumn=5, noRows = 5, 
                     noColumns = 8)

# Defining cell styles for the cell blocks defined above

borders.sty <- Border(color="black", 
                      position=c("BOTTOM", "LEFT",
                                 "TOP","RIGHT"), 
                      pen=c("BORDER_THIN", "BORDER_THIN",
                            "BORDER_THIN","BORDER_THIN"))
topRow.cs <- CellStyle(wb.df2) +
  Alignment(horizontal="ALIGN_CENTER") +
  Font(wb.df2,isBold=F, isItalic=F,color="yellow",
       heightInPoints=12) + borders.sty
botRow.cs <- CellStyle(wb.df2) + 
  Alignment(horizontal="ALIGN_CENTER") +
  Font(wb.df2, isBold=T,isItalic=F,color="9") + borders.sty
lcols2.cs <- CellStyle(wb.df2) + 
  Alignment(horizontal="ALIGN_CENTER") +
  Font(wb.df2, isBold=F,isItalic=F) + borders.sty
borders.cs <- CellStyle(wb.df2) + borders.sty

# Defining background colors for top and bottom rows, and
# row labels in the first 2 columns.
top.fill <- Fill(foregroundColor = "blue", 
                 backgroundColor="blue")
bot.fill <- Fill(foregroundColor = "darkred", 
                 backgroundColor="darkred")
rlabels.fill <- Fill(foregroundColor = "grey75", 
                     backgroundColor="grey75")

#- Writing data values in the different table cells

CB.setRowData(cellBlock = topRow.cb,x=colnames(summary1.df),
              rowIndex=1,rowStyle = topRow.cs)
CB.setRowData(cellBlock = botRow.cb,x=MaxRow,
              rowIndex=1,rowStyle = botRow.cs)
CB.setMatrixData(cellBlock = rlabels.cb,
                 x=as.matrix(summary1.df)[1:5,1:2],
                 startRow=1, startColumn=1,
                 cellStyle = lcols2.cs)
CB.setMatrixData(cellBlock = data.cb,
                 x=as.matrix(summary1.df[,3:10]),
                 startRow=1,startColumn = 1,
                 cellStyle=borders.cs)
CB.setFill(topRow.cb, fill=top.fill, rowIndex=rep(1,10),
           colIndex=1:10)
CB.setFill(botRow.cb, fill=bot.fill, rowIndex=rep(1,10),
           colIndex=1:10)
CB.setFill(rlabels.cb, fill=rlabels.fill, 
           rowIndex=rep(1:5,2), 
           colIndex = c(rep(1,5),rep(2,5)))

saveWorkbook(wb.df2, file="./data/chap8data1b.xlsx")