library(readxl)
library(tidyverse)
library(openxlsx)

old.table<-as.data.frame(read_excel("Nitrogen_Virus_transform.xlsx"))
old.table[grepl("^[0-9]*$", old.table$Passage),"Passage"]<-as.character(as.Date(as.numeric(old.table[grepl("^[0-9]*$", old.table$Passage),"Passage"]), origin = "1899-12-30"))

table.list<-old.table %>%
  group_by(Caja) %>% 
  group_split()
names(table.list)<-unique(old.table$Caja)
names(table.list)

caja_order.df<-data.frame(
  caja=names(table.list),
  Rack=gsub(" #[0-9]*$","",names(table.list)),
  ncaja=as.numeric(gsub("[A-Z]*-[0-9]* #","",names(table.list)))
)
caja_order<-caja_order.df %>% arrange(Rack, ncaja) %>% pull(caja)
table.list<-table.list[caja_order]

View(table.list[[1]])

caja.list<-list()
for (i in names(table.list)){
  m0<-matrix(nrow = 30, ncol=10, byrow = T)
  m0[seq(from=1, to=30, by=3),]<-matrix(data=pull(table.list[[i]],Cell.line), nrow = 10, ncol=10, byrow = T)
  m0[seq(from=2, to=30, by=3),]<-matrix(data=pull(table.list[[i]],Info.Descongelacion), nrow = 10, ncol=10, byrow = T)
  m0[seq(from=3, to=30, by=3),]<-matrix(data=pull(table.list[[i]],Passage), nrow = 10, ncol=10, byrow = T)
  caja.list[[i]]<-m0
}

wb <- openxlsx::createWorkbook()

for (i in names(caja.list)){

  addWorksheet(
    wb = wb,
    sheetName = i
  )
  
  rangeRows<-1:30
  rangeCols<-1:10
  
  ## left borders
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("left"),
      borderStyle = c("thick")
    ),
    rows = rangeRows,
    cols = rangeCols[1],
    stack = TRUE,
    gridExpand = TRUE
  )
  
  ##right borders
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("right"),
      borderStyle = c("thick")
    ),
    rows = rangeRows,
    cols = tail(rangeCols, 1),
    stack = TRUE,
    gridExpand = TRUE
  )
  
  ## top borders
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("top"),
      borderStyle = c("thick")
    ),
    rows = rangeRows[1],
    cols = rangeCols,
    stack = TRUE,
    gridExpand = TRUE
  )
  
  ##bottom borders
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("bottom"),
      borderStyle = c("thick")
    ),
    rows = tail(rangeRows, 1),
    cols = rangeCols,
    stack = TRUE,
    gridExpand = TRUE
  )
  
  ##row cells
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("bottom"),
      borderStyle = c("thin")
    ),
    rows = seq(from=3, to=27, by=3),
    cols = rangeCols,
    stack = TRUE,
    gridExpand = TRUE
  )
  
  ##column cells
  openxlsx::addStyle(
    wb = wb,
    sheet = i,
    style = openxlsx::createStyle(
      border = c("right"),
      borderStyle = c("thin")
    ),
    rows = 1:30,
    cols = 1:9,
    stack = TRUE,
    gridExpand = TRUE
  )
  
  writeData(wb, sheet=i, x=caja.list[[i]], colNames = FALSE)
}

saveWorkbook(wb, "Nitrogen_Virus.xlsx", overwrite = TRUE)