时间戳

首页 > 解决方案 > R中的右对齐列

r - R中的右对齐列

问题描述

我正在尝试将我的“可见性”和“欺诈”列与单元格的右侧对齐，但我似乎找不到一种简单的方法来做到这一点。我尝试了几种不同的选项，但一切都返回错误。请在下面查看我的代码：

library(tidyverse)
library(xlsx)
library(openxlsx)
library(data.table)
library(taskscheduleR)

#Create Workbook

workbook <- createWorkbook()
addWorksheet(workbook, "Viewability")
addWorksheet(workbook, "Fraud")

#Read Files and Create Data

files <- file.info(Sys.glob("C:/Users/briantos/Documents/Blacklist Raw/*.csv"))
newestfile <- row.names(files)[which.max(files[["ctime"]])]
blacklist <- fread(newestfile)

#Create Viewability Data

blacklist_viewability <- blacklist %>% 
  select(`Delivery Site`, `Video Viewable Impressions`, `Measured Impressions`) %>% 
  group_by(`Delivery Site`) %>% 
  summarize(Sum_of_Measured_Impressions = sum(`Measured Impressions`, na.rm = TRUE),
            Sum_of_Video_Viewable_Impressions = sum(`Video Viewable Impressions`, na.rm = TRUE)) %>% 
  mutate(Viewability = `Sum_of_Video_Viewable_Impressions`/`Sum_of_Measured_Impressions`) %>%
  arrange(Viewability, desc(Sum_of_Measured_Impressions)) %>% 
  select(-Sum_of_Video_Viewable_Impressions) %>% 
  mutate(Viewability = paste(round(100*Viewability, 2), "%", sep=""))

#Create Fraud Data

blacklist_fraud <- blacklist %>% 
  select(`Delivery Site`, `Fraud/SIVT Incidents`, `Monitored Impressions`) %>% 
  group_by(`Delivery Site`) %>% 
  summarize(Sum_of_Monitored_Impressions = sum(`Monitored Impressions`, na.rm = TRUE),
            Sum_of_Incidents = sum(`Fraud/SIVT Incidents`, na.rm = TRUE)) %>% 
  mutate(Fraud = `Sum_of_Incidents`/`Sum_of_Monitored_Impressions`) %>%
  arrange(desc(Fraud), desc(Sum_of_Monitored_Impressions)) %>% 
  select(-Sum_of_Incidents) %>% 
  mutate(Fraud = paste(round(100*Fraud, 2), "%", sep=""))

#Turn Into Data Frame

blacklist_viewability <- as.data.frame(blacklist_viewability)
blacklist_fraud <- as.data.frame(blacklist_fraud)

#Write Data into Workbook

writeData(workbook, sheet = "Viewability", blacklist_viewability)
writeData(workbook, sheet = "Fraud", blacklist_fraud)

#Set Column Widths

setColWidths(workbook, sheet = "Viewability", cols = 1:3, widths = "auto")
setColWidths(workbook, sheet = "Fraud", cols = 1:3, widths = "auto")

#Write File

saveWorkbook(workbook, paste("C:/Users/briantos/Documents/Blacklist Complete/",Sys.Date(),"_Blacklist.xlsx"))## Heading ##

我已经尝试过alignment()函数、CellStyle()函数等。它们都返回错误。请帮忙！

标签： r

解决方案

这是一个使用openxlsx. 您正在寻找的是halign;valign方法中的参数createStyle。

library(openxlsx)
wb <- createWorkbook()
wb$addWorksheet('test')
wb$writeData(sheet = 'test', 
             df = cbind.data.frame(matrix(1:9,3,3), let=letters[1:3]), 
             colClasses = c(rep('integer',3),'character'), keepNA = TRUE,
             colNames = TRUE, startRow = 1L, startCol = 1L)
wb$addStyle(sheet = 1, rows = 1:4, cols = 4L, 
            style = createStyle(halign = 'right'))  # set halign='right' - leave every other element to default value
saveWorkbook(wb,'d:/Test/test_wb.xlsx') # last column is right-aligned

你也可以简单地做一些形式

addStyle(wb = wb, sheet = 'test', cols = 4L, rows = 1:4, 
         style = createStyle(halign = 'right'))

编辑

行/列选择：我通常做的是使用df（相关工作表的数据框）并清楚地确定我想在哪里应用格式

# Ex. 1: all rows, column 4
addStyle(wb = wb, sheet = 'test', cols = 4L, rows = 1:nrow(df), 
         style = createStyle(halign = 'right'))

# Ex. 2: all rows, all columns
addStyle(wb = wb, sheet = 'test', cols = 1:ncol(df), rows = 1:nrow(df),
         gridExpand = TRUE, # make sure to set this to TRUE in order to really select everything
         style = createStyle(halign = 'right'))

然后，您可以轻松地选择行或列组，例如rows = c(1,3)（您也可以设置最大列数和行数，但我猜对每个单元格应用格式不是很有效）。

此外，如果您的数据不是从第 1 行/第 1 列开始，而是从 n 开始，那么您需要改变选择，例如rows = n:(nrow(df)+n-1)（可能需要删除-1- 不确定。我总是在这里犯错）。

推荐阅读