主页 / user-17175603

Mary Rachel's questions

Martin Hope
Mary Rachel
Asked: 2024-11-26 23:59:07 +0800 CST
  • 8

我正在清理公司名称列表，但很难有选择地将大小写转换为标题大小写。我可以使用mutate(str_to_title(...))函数将整个字段转换为标题大小写，这对我的大多数值都很有效，但有少数公司的标题是“ABC Company”或“John Doe Company LLC”，当我应用标题大小写时，它们的正确大小写就会混乱（“Abc Company”和“John Doe Company Llc”）。

我以为我可以使用case_when()特定值的向量来创建一个函数，该函数告诉 R 仅将标题大小写应用于不等于我指定的值向量的值。但是，我要么收到警告“较长的对象长度不是较短的对象长度的倍数”，并且所有值都转换为标题大小写，要么我只是得到字段中的向量值的 NA，并为不在向量中的值更正标题大小写值。我哪里做错了？

# Example Code #

library(tidyverse)

## Reproducible Example ##

test<-structure(list(`Company Name` = c("ABC Company", "John Doe Company LLC", 
"rainbow road company", "yellow brick road incorporated", "XYZ", 
"Mostly Ghostly Company", "hot Leaf juice tea company")), class = c("tbl_df", 
"tbl", "data.frame"), row.names = c(NA, -7L))

test<-test%>%
  mutate(`Company Name`= case_when(`Company Name`!= c("ABC Company","John Doe Company LLC","XYZ") ~ str_to_title(`Company Name`)))

# Error #
Warning message:
There was 1 warning in `mutate()`.
ℹ In argument: `Company Name = case_when(...)`.
Caused by warning in `` `Company Name` != c("ABC Company", "John Doe Company LLC", "XYZ") ``:
! longer object length is not a multiple of shorter object length
Martin Hope
Mary Rachel
Asked: 2024-11-06 02:25:03 +0800 CST
  • 5

我使用包将一些数据框导出到 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)
Martin Hope
Mary Rachel
Asked: 2024-10-29 05:53:10 +0800 CST
  • 6

我正在编写一个脚本来使用 呈现 Excel 输出openxlsx，但在单元格内换行文本时遇到了困难。

我的输出中有一张“报告说明”表，其中包含有关文件中分析的常见免责声明，该免责声明由写入变量的文本字符串填充。我必须在单独的工作表中执行此操作，因为所需的免责声明文本太长，无法包含在分析表的页眉或页脚中。问题是文本字符串不会在单元格中换行，这会产生可读性问题。我尝试了各种方法来强制执行此操作，但都没有成功。

#### Example Data ####

library(tidyverse)
library(openxlsx)
library(openxlsx2)

reportnotes<- "THIS IS AN EXAMPLE TEXT STRING THAT I WANT TO WRITE TO AN EXCEL DOCUMENT. HOPEFULLY IT'S LONG ENOUGH TO MAKE MY POINT."

output<-createWorkbook()

addWorksheet(output, "Report Notes")

writeData(output, "Report Notes", reportnotes,
          startCol = 1,
          startRow = 1,
          colNames = TRUE,
          rowNames = FALSE,
          keepNA = TRUE)

pageSetup(output, "Report Notes", orientation = "landscape", scale = 100, fitToWidth = TRUE)

我尝试了以下解决方案：

#### Attempted Solutions ####

# Solution 1) Using str_wrap() and setColWidths() and setRowHeights() to create the wrap. 
# Resulted in an adjusted column and row height but no wrapped text:

reportnotes<-reportnotes%>%
   str_wrap(width = 80, indent = 0, whitespace_only = FALSE) 

setColWidths(output, "Report Notes", cols = 1, widths=70)

setRowHeights(output, "Report Notes", rows = 1, heights = 150)



# Solution 2) Defining a cell style. This solution produced an error:

wb_add_cell_style(output, sheet="Report Notes", dims = "A1", apply_border = TRUE, wrap_text=TRUE)

# Error: wb must be class wbWorkbook or R6

我的控制台是RStudio 2023.06.1+524 "Mountain Hydrangea" Release (547dcf861cac0253a8abb52c135e44e02ba407a1, 2023-07-07) for windows，我的 R 版本是R version 4.3.0 (2023-04-21 ucrt) -- "Already Tomorrow"。由于组织政策，我对更新版本的访问受到限制。

鉴于这些限制，我该怎么做才能强制文本在 Excel 输出中换行？

Martin Hope
Mary Rachel
Asked: 2024-10-25 22:52:34 +0800 CST
  • 5

我正在使用 openxlsx 包准备一个 Excel 工作簿，我想用它setColWidths()来自动调整每个工作表中的列大小。由于我有多个工作表，并且我希望我的代码是相对的，所以我需要编写cols=参数来调用工作表中的所有列以进行自动调整大小。但是，似乎我只能使用特定索引来填充该参数，并且该参数似乎是该函数所必需的。

现在，我当然可以计算出一张表中有多少列并调用该特定索引值，但由于这个项目涉及许多张表，这很快就会变得繁琐，并且代码无法适应不同的表。有没有一种相对的方法来调用一张表中的所有列setColWidths()？

下面是我的虚拟数据以供参考（本例中只有一张表）。此虚拟数据将产生所需的输出，但cols=参数不是如此处所写的相对值。有没有相对的方式来调用表中的所有列？

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

#### Sample Data ####

month_2024<-structure(list(`CAL MONTH` = c("Jan 2024", "Feb 2024", "Mar 2024", 
"Apr 2024", "May 2024", "Jun 2024", "Jul 2024", "Aug 2024", "Sep 2024", 
"Oct 2024", "Total"), `Total Number of Interactions` = c(17L, 
11L, 18L, 9L, 15L, 29L, 28L, 16L, 10L, 13L, 166L)), row.names = c(NA, 
-11L), class = "data.frame")

####
                       
output<-createWorkbook()

addWorksheet(output,"2024 Analysis")

writeData(output, "2024 Analysis", `month_2024`, 
          startCol = 1, 
          startRow = 1, 
          colNames = TRUE, 
          rowNames = FALSE, 
          keepNA = TRUE)

setHeaderFooter(output, 
                sheet = "2024 Analysis", 
                header = c(NA, "2024 Analysis", NA), 
                footer = c("Data Window: January 1st, 2024 - &[Date] unless otherwise stated", NA, NA))

setColWidths(output, "2024 Analysis", cols = 1:2, widths="auto")

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