R (xlsx) - automatically create borders

Question

I have problems to automatically generate borders while exporting an excel file with r. Below is my code and the output I am currently generating and how I would like it to be.

I have tried to help myself with the solution here, but could not make it work on my example.

Here is some code to reproduce the problem:

#some dataframes to export as excel files
Agent1 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(6,15,3,7), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)
Agent2 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(7,13,5,3), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)
Agent3 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(4,4,3,7), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)

nr_of_agents <- 3 

# Variable creation for counting cases per agent
for (a in 1 : nr_of_agents) {
  agent_s <- paste0("Agent",a,"sum")
  assign(agent_s, 0)
}

for (a in 1:nr_of_agents){      #Counting cases per agent
  agent <- paste0("Agent",a)
  tempv <- eval(as.name(agent))
  agent_s <- paste0("Agent",a,"sum")
  tempv1 <- eval(as.name(agent_s))
  tempv1 <- sum(tempv$NR)
  assign(agent_s, paste("Total cases: ", tempv1)) 
}

## EXCEL OUTPUT 
wb<-createWorkbook(type="xlsx")    


TITLE_STYLE <- CellStyle(wb)+ Font(wb,  heightInPoints=16, color=NULL, isBold=TRUE) +
  Alignment(h="ALIGN_CENTER")

TEXT_STYLE <- CellStyle(wb)+ Font(wb,  heightInPoints=12, color=NULL, isBold=FALSE) +
  Alignment(h="ALIGN_RIGHT")+
  Border(color="black", position=c("TOP"), 
         pen=c("BORDER_THIN")) 

# Styles for the data table row/column names
TABLE_ROWNAMES_STYLE <- CellStyle(wb) + Font(wb, isBold=TRUE) 

TABLE_COLNAMES_STYLE <- CellStyle(wb) + Font(wb,color="#FFFAFA", heightInPoints=12, name="Calibri Light", isBold=TRUE) +
  Fill(foregroundColor="#9e2b11",pattern="SOLID_FOREGROUND")+#, backgroundColor="lightblue")
  Alignment(wrapText=TRUE, horizontal="ALIGN_CENTER")+
  Border(color="black", position=c("TOP", "BOTTOM", "LEFT", "RIGHT"), 
         pen=c("BORDER_THIN")) 

#Code to add title 
xlsx.addTitle<-function(sheet, rowIndex, title, titleStyle){
  rows <-createRow(sheet,rowIndex=rowIndex)
  sheetTitle <-createCell(rows, colIndex=3)
  setCellValue(sheetTitle[[1,1]], title)
  setCellStyle(sheetTitle[[1,1]], titleStyle)

}
#Code to add sums of cases per agent
xlsx.addsums<-function(sheet, rowIndex, title, titleStyle){
  rows <-createRow(sheet,rowIndex=rowIndex)
  sheetTitle <-createCell(rows, colIndex=3)
  setCellValue(sheetTitle[[1,1]], title)
  setCellStyle(sheetTitle[[1,1]], titleStyle)
}

names <- c("Mark", "Neli", "Sara")    # Agents names

for (a in 1 : nr_of_agents) {         
  agent <- paste0("Agent",a)
  tempv <- eval(as.name(agent))
  agent_S <- paste0("Agent",a,"sum")
  tempv1 <- eval(as.name(agent_S))

  sheet<-createSheet(wb, sheetName = names[a])      #sheet creation 

  xlsx.addTitle(sheet, rowIndex=1, title=names[a],  #Adding title to each sheet
                titleStyle = TITLE_STYLE)

  addDataFrame(tempv, sheet, startRow=3, startColumn=1,   #Adding the dataframes
               colnamesStyle = TABLE_COLNAMES_STYLE,
               rownamesStyle = TABLE_ROWNAMES_STYLE
  )

  xlsx.addsums(sheet, rowIndex=(3+ nrow(tempv)+1), title= tempv1,    #Adding total sum for every agent
               titleStyle = TEXT_STYLE)

  autoSizeColumn(sheet, colIndex=c(1:ncol(tempv)))       #Auto size columns 
}

saveWorkbook(wb, paste0(Sys.Date()," Test_file",".xlsx"))

Picture of current and desired output

As seen in the picture also the automatic column width is not working correctly, its size is dependant of the length of the column header and not the longest word in the column. Any idea on how to solve this?

Thanks for the help!

MKa · Accepted Answer · 2019-04-18T06:49:30.677

You can do this with openxlsx package.

library(openxlsx)

# Data
Agent1 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(6,15,3,7), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)
Agent2 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(7,13,5,3), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)
Agent3 <- data.frame("QUEUE" = c("call PA", "call", "Call", "call CB"), "NR" = c(4,4,3,7), "Client" = c("xyz company", "some other company", "Company abs", "BNM"), stringsAsFactors = FALSE)

agents <- c("Mark", "Neli", "Sara")

wb <- createWorkbook()

for (i in 1:length(agents)) {

  agent <- paste0("Agent", i)  
  agent_nam <- agents[i]

  agent_df <- eval(as.name(agent))

  # Add sheet
  addWorksheet(wb, agent_nam)

  # Save Header (agent name)
  writeData(wb, sheet = agent_nam, x = agent_nam, startRow = 1, startCol = 3)

  # Write Dataframe
  writeData(wb, sheet = agent_nam, x = agent_df, startRow = 3, rowNames = TRUE)

  # Total cases
  writeData(wb, sheet = agent_nam, x = paste0("Total cases: ", sum(agent_df$NR)), startRow = 8, startCol = 3)

  # style 1: Agent names in bold
  s1 <- createStyle(fontSize = 16, textDecoration = c("BOLD"), halign = "center")

  # style 2: Bold white font with red background fill for table header
  s2 <- createStyle(fontName = "Calibri Light", fontColour = "#FFFFFF", 
                     fgFill = "#9e2b11", textDecoration = c("BOLD"), halign = "center",
                     border = "TopBottomLeftRight")

  # style 3: border around the data
  s3 <- createStyle(border = "TopBottomLeftRight")

  # style 4: Text in the center for Total cases
  s4 <- createStyle(halign = "center")

  # Apply styles to the workbook
  addStyle(wb, sheet = agent_nam, style = s1, rows = 1, cols = 3, gridExpand = TRUE)
  addStyle(wb, sheet = agent_nam, style = s2, rows = 3, cols = 2:4, gridExpand = TRUE)
  addStyle(wb, sheet = agent_nam, style = s3, rows = 4:7, cols = 2:4, gridExpand = TRUE)
  addStyle(wb, sheet = agent_nam, style = s4, rows = 8, cols = 3, gridExpand = TRUE)

  # Column widths
  setColWidths(wb, sheet = agent_nam, cols = 1:4, widths = "auto")

}

saveWorkbook(wb, paste0(Sys.Date()," Test_file (openxlsx)",".xlsx"))

R (xlsx) - automatically create borders

1 Answers1