主页 / coding / 问题 / 79160290
Accepted
Mary Rachel
Asked: 2024-11-06 02:25:03 +0800 CST

如何将格式应用于导出到 Excel 的数据框的“总计”行

  • 772

我使用包将一些数据框导出到 excel 文档openxlsx，并使用createStyle()和addStyle()函数应用美学。但是，我的“总计”行的格式与其他单元格不同。

我认为这可能是将其格式化为数据框的问题，因为我的格式化会调用数据框的行，而 R 可能不会将总行视为真正的行。我尝试将数据框重新格式化为 tibble，但并没有解决问题。

我不确定是否需要在我的addStyle()行中调整参数，是否需要添加另一addStyle()行并以相对方式调用总行（而不是使用索引值来调用特定数据框的特定行号），或者是否需要将原始表重新格式化为数据框或 tibble 以外的其他内容。

实际产量：

Excel 表格

代码：

# Sample Code #

library("tidyverse")
library("openxlsx")
library("openxlsx2")
library("lubridate")
library("zoo")

## Minimum Reproducible Data ##

servicetypes_2024<- structure(list(`Service Type` = c("a", "b", 
"c", "d", "e", 
"f", "g", "h", 
"i", "j", "k", "l", 
"m", "n", "o", "Total"),`Percentage of Interactions` = c("5.8%", "5.7%", "2.9%", "1.4%", 
"1.2%", "1%", "0.7%", "0.7%", "0.6%", "0.4%", "0.4%", "0.1%", 
"0.1%", "0.1%", "0.3%", "21.5%")), row.names = c(NA, -16L), class = c("tbl_df", 
"tbl", "data.frame"))

## Aesthetics ##

cellStyle <- createStyle(wrapText = TRUE,
                         halign = "right",
                         border= "TopBottomLeftRight",
                         borderColour = getOption("openxlsx.borderColour", "black"),
                         borderStyle = getOption("openxlsx.borderStyle", "thin")) #creates a custom cell style

notesStyle<-createStyle(wrapText = TRUE, 
                        border= "TopBottomLeftRight",
                        borderColour = getOption("openxlsx.borderColour", "black"),
                        borderStyle = getOption("openxlsx.borderStyle", "thin"),
                        halign = "center",
                        textDecoration = "italic")

rowlabelStyle<-createStyle(wrapText = TRUE,
                           border= "TopBottomLeftRight",
                           borderColour = getOption("openxlsx.borderColour", "black"),
                           borderStyle = getOption("openxlsx.borderStyle", "thin"),
                           halign = "left",
                           textDecoration = "italic")

columnlabelStyle<-createStyle(wrapText = TRUE,
                              border= "TopBottomLeftRight",
                              borderColour = getOption("openxlsx.borderColour", "black"),
                              borderStyle = getOption("openxlsx.borderStyle", "thin"),
                              halign = "center",
                              textDecoration = "bold")

## Creating Worksheet ##
output<-createWorkbook() #opens an active workbook for binding

addWorksheet(output,"2024 Service Type Analysis")
writeData(output, "2024 Service Type Analysis", servicetypes_2024, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)
setHeaderFooter(output, 
                sheet = "2024 Service Type Analysis", 
                header = c(NA, "2024 Service Type Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - December 31st, 2024 unless otherwise stated", NA, NA))
setColWidths(output, "2024 Service Type Analysis", cols = 1:ncol(servicetypes_2024), widths="auto")
pageSetup(output, "2024 Service Type Analysis", orientation = "landscape", scale = 100, fitToWidth = TRUE)
addStyle(output, sheet = "2024 Service Type Analysis", cellStyle, rows = 1:nrow(servicetypes_2024), cols = 1:ncol(servicetypes_2024), gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", rowlabelStyle, rows = 1:nrow(servicetypes_2024), cols = 1, gridExpand = T)
addStyle(output, sheet = "2024 Service Type Analysis", columnlabelStyle, rows = 1, cols = 1:ncol(servicetypes_2024), gridExpand = T)

saveWorkbook(output, "Reporting.xlsx", overwrite= TRUE)

1 个回答

  1. Best Answer

    您必须添加“+ 1”来表示包含列标题的行，即使用例如rows = seq(nrow(servicetypes_2024)) + 1从第二行开始添加样式。

    library("openxlsx")

output <- createWorkbook() # opens an active workbook for binding

addWorksheet(output, "2024 Service Type Analysis")
writeData(output, "2024 Service Type Analysis", servicetypes_2024,
  startCol = 1,
  startRow = 1,
  colNames = TRUE,
  rowNames = FALSE,
  keepNA = TRUE
)
setHeaderFooter(output,
  sheet = "2024 Service Type Analysis",
  header = c(NA, "2024 Service Type Analysis", NA),
  footer = c("Data Window: January 1st, 2024 - December 31st, 2024 unless otherwise stated", NA, NA)
)
setColWidths(output, "2024 Service Type Analysis",
  cols = seq(ncol(servicetypes_2024)), widths = "auto"
)
pageSetup(output, "2024 Service Type Analysis", orientation = "landscape", scale = 100, fitToWidth = TRUE)
addStyle(output,
  sheet = "2024 Service Type Analysis", cellStyle,
  rows = seq(nrow(servicetypes_2024)) + 1,
  cols = seq(ncol(servicetypes_2024)), gridExpand = T
)
addStyle(output,
  sheet = "2024 Service Type Analysis", rowlabelStyle,
  rows = seq(nrow(servicetypes_2024)) + 1,
  cols = 1, gridExpand = T
)
addStyle(output,
  sheet = "2024 Service Type Analysis", columnlabelStyle,
  rows = 1, cols = seq(ncol(servicetypes_2024)), gridExpand = T
)

saveWorkbook(output, "Reporting.xlsx", overwrite = TRUE)

    在此处输入图片描述

    • 1

相关问题