R语言自动化报表实现Excel的数据一键更新

加载包

library(openxlsx)

设置工作目录

file <- "C:\\Users\\dell\\Desktop\\excelbao"
name <- "DCAT inputs.xlsx"

读取该目录下的数据

setwd(file)
options(scipen = 200)
file_name <- paste(file, "\\", name, sep="")
df_forplan <- read.xlsx(file_name, sheet = "FORPLAN")
df_cededpct <- read.xlsx(file_name, sheet = "Ceded pct")

生成MNA数据

MNA <- data.frame()
df_forplan_mna11 <- df_forplan[which(grepl("MNA", df_forplan$Segment) == TRUE), ]
MNA[1:length(df_forplan_mna11$UY), "UWYr"] <- df_forplan_mna11$UY
MNA[,"EGPI"] <- df_forplan_mna11$EGPI
df_cededpct_mna11 <- df_cededpct[which(grepl("MNA", df_cededpct$Segment) == TRUE), ]
df_cededpct_mna11 <- df_cededpct_mna11[, -1]
MNA <- merge(MNA, df_cededpct_mna11, by.x = "UWYr", by.y = "UY", all.x = TRUE)
names(MNA)[3] <- "Ceded"
MNA$Ceded[which(is.na(MNA$Ceded) == TRUE)] <- 0 #将Ceded中的缺失值替换为0

输出MNA

write.xlsx(MNA, "MNA.xlsx")