gpt4 book ai didi

R (xlsx) - 自动创建边框

转载 作者:行者123 更新时间:2023-12-04 15:46:29 30 4
gpt4 key购买 nike

在使用 r 导出 excel 文件时,我无法自动生成边框。下面是我的代码和我当前生成的输出以及我希望的结果。

我已经尝试通过解决方案帮助自己 here ,但无法使其适用于我的示例。

这里是重现问题的一些代码:

#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

如图所示,自动列宽也无法正常工作,其大小取决于列标题的长度,而不是列中最长的单词。关于如何解决这个问题的任何想法?

感谢您的帮助!

最佳答案

您可以使用 openxlsx 包执行此操作。

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) - 自动创建边框,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55530797/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com