1、导入原始数据并做基本处理
library(pacman)
p_load(dplyr,openxlsx,stringr)
df <- read.xlsx("./data_set/1201after.xlsx",sheet=1,colNames=T)
dim(df)
## [1] 7051 12
文件共7051行，12个变量。
str(df)
## 'data.frame': 7051 obs. of 12 variables:
## $ Company : chr "AVXE" "SECG" "SECG" "SECI" ...
## $ Staff.NO : chr "195163" "901033" "901033" "905586" ...
## $ Staff.Name : chr "Yang XIAOXIA" "XinQuan Huang" "XinQuan Huang" "Hong Gu" ...
## $ Create.Date : chr "43801" "43801" "43801" "43801" ...
## $ Last.Modified: chr "43801" "43810" "43810" "43802" ...
## $ Cost.Center : chr "AVXE-LHENG" "SED10" "SED10" "AUD01" ...
## $ Amount : chr "1066.5RMB[Hardcopy]" "1467RMB[Hardcopy]" "2930RMB[Hardcopy]" "8442.2999999999993RMB[Hardcopy]" ...
## $ Type : chr "WBS" "WBS" "WBS" "WBS" ...
## $ Days : num 70 70 70 70 69 63 63 61 61 61 ...
## $ GSC/SEC : chr "GSC" "SEC" "SEC" "SEC" ...
## $ IMR.BU : chr "AVXE" "Field Service" "Field Service" "Projects&Equipment China" ...
## $ BU : chr "ETO" "Global Solution" "Global Solution" "PEC" ...
有几个变量的类型明显不对，其中Create.Date和Last.Modified应该为日期型；Amount 应该去除后缀转换为数值型。
# 还原日期格式
df$Create.Date <- df$Create.Date %>% as.numeric %>%
as.Date(origin='1899-12-30')
df$Last.Modified <- df$Last.Modified %>% as.numeric %>%
as.Date(origin='1899-12-30')
# 清除非数字和点的字符并转换为数值型
df$Amount <- df$Amount %>% gsub("[^0-9\\.]","",.) %>%
as.numeric(df$Amount)
# 清除可能存在的空格
df$BU <- df$BU %>% str_trim(side = "both")
df$Staff.Name <- df$Staff.Name %>% str_trim(side = "both")
str(df)
## 'data.frame': 7051 obs. of 12 variables:
## $ Company : chr "AVXE" "SECG" "SECG" "SECI" ...
## $ Staff.NO : chr "195163" "901033" "901033" "905586" ...
## $ Staff.Name : chr "Yang XIAOXIA" "XinQuan Huang" "XinQuan Huang" "Hong Gu" ...
## $ Create.Date : Date, format: "2019-12-02" "2019-12-02" "2019-12-02" "2019-12-02" ...
## $ Last.Modified: Date, format: "2019-12-02" "2019-12-11" "2019-12-11" "2019-12-03" ...
## $ Cost.Center : chr "AVXE-LHENG" "SED10" "SED10" "AUD01" ...
## $ Amount : num 1066 1467 2930 8442 2957 ...
## $ Type : chr "WBS" "WBS" "WBS" "WBS" ...
## $ Days : num 70 70 70 70 69 63 63 61 61 61 ...
## $ GSC/SEC : chr "GSC" "SEC" "SEC" "SEC" ...
## $ IMR.BU : chr "AVXE" "Field Service" "Field Service" "Projects&Equipment China" ...
## $ BU : chr "ETO" "Global Solution" "Global Solution" "PEC" ...
2、生成新列
查看Amount列有无缺失值，存在缺失值后面的计算会存在问题。
sum(is.na(df$Amount))
## [1] 0
查看Amount列的数值区间，后面需要按区间分类。
range(df$Amount)
## [1] 13.46 99486.18
查看Days列有无缺失值，后面需要按区间分类。
# 将Amount列按0-3000-5000-10000-30000-区间生成Amount_range列
df <- df %>% mutate(Amount_range=case_when(Amount<=3000 ~ "<￥3000",
Amount<=5000 ~ "￥3000~￥5000",
Amount<=10000 ~ "￥5000~￥10000",
Amount<=30000 ~ "￥10000~￥30000",
Amount>30000 ~ ">￥30000"))
sum(is.na(df$Days))
## [1] 0
查看Days列数据区间。
range(df$Days)
## [1] 0 71
# 将Days列按0-30-60-90区间生成Days_range列
df <- df %>% mutate(Days_range=case_when(Days<=30 ~ "<30Days",
Days<=60 ~ "31~60Days",
Days<=90 ~ "61~90Days"))
3、设计excel样式
# 表头样式
hs <- createStyle(fgFill = "#00B0F0", # 文字背景色
fontColour = "#FCFCFC", # 文字颜色
halign = "center", # 上下居中对齐
valign = "center", # 左右居中对齐
textDecoration = "bold", # "italic"，"bold"，"underline"
border = "TopBottom", # 边框
borderColour = "black",
fontSize=12, # 字号
fontName="Arial Narrow")
# 创建内容主体样式
bs1 <- createStyle(border="TopBottom",
borderColour = "black",
halign = "center", # 上下居中对齐
valign = "center", # 左右居中对齐
fontSize=12, # 字号
fontName="Arial Narrow")
# 特殊内容主体样式，某些内容需要根据条件标记出来
bs2 <- createStyle(border="TopBottom",
borderColour = "red",
halign = "center",
valign = "center",
fontSize=12,
fontName="Arial Narrow",
fontColour = "red")
4、构造函数并将结果写入文件
my_fun <- function(x) {
# 创建excel文件
wb <- createWorkbook()
# 向该文件中添加sheet
addWorksheet(wb,as.character(x))
# 抽取该BU内容
all <- df %>% filter(BU==x) %>% select(-c("GSC/SEC","IMR.BU","BU"))
# 金额栏添加千位分隔符
all$Amount <- prettyNum(all$Amount,big.mark = ",")
# 写入sheet
writeData(wb, as.character(x), all, colNames = T,
rowNames = F, startCol=1, startRow = 1,
borders="rows", headerStyle = hs,
withFilter = T)
# 添加内容主体样式（会还原日期格式）
# addStyle(wb, as.character(x), style = bs1, gridExpand = T,
# rows = 2:(nrow(all) + 1),
# cols = 1:ncol(all))
# 设置自动列宽
setColWidths(wb,sheet = 1,widths = "auto",
cols = 1:ncol(all),
ignoreMergedCells = FALSE)
# 还原Amount列数值型类型，否则后面无法计算
all$Amount <- all$Amount %>% gsub(",","",.) %>% as.numeric()
ely <- all %>% group_by(Staff.Name) %>% summarise(Sum_of_Amount=sum(Amount))
Grand_Total <- tibble(Staff.Name="Sum_of_Amount:",Sum_of_Amount=sum(ely$Sum_of_Amount))
employee <- rbind(ely,Grand_Total) %>% arrange(-Sum_of_Amount)
ls1 <- which(employee$Sum_of_Amount>=5000)
employee$Sum_of_Amount <- prettyNum(employee$Sum_of_Amount,big.mark = ",")
# 创建sheet并写入数据
addWorksheet(wb,"By_Employee")
writeData(wb, "By_Employee", employee, colNames = T,
rowNames = F,startCol=2, startRow = 1,
borders="rows", headerStyle = hs)
# 添加主体样式
addStyle(wb, "By_Employee", style = bs1, gridExpand = T,
rows = 2:(nrow(employee) + 1),
cols = 2:(ncol(employee) + 1))
# 使用样式2（bs2）将Amount列大于5000的行标红
addStyle(wb, "By_Employee", style = bs2, gridExpand = T,
# 需要+1（标题栏）
rows = ls1 + 1,
cols = 2:(ncol(employee) + 1))
setColWidths(wb,sheet = 2,widths = "auto",
cols = 2:(ncol(employee) + 1),
ignoreMergedCells = FALSE)
employ <- ely %>% select(Staff.Name,Amount_range)
Aging_by_amount <- employ %>% group_by(Amount_range) %>%
summarise(Staff_Volume=n(), Amount=sum(Amount)) %>%
arrange(-Amount)
# 将缺失值用0填充
Aging_by_amount$Staff_Volume[which(is.na(Aging_by_amount$Staff_Volume))] <- 0
Aging_by_amount$Amount[which(is.na(Aging_by_amount$Amount))] <- 0
Grand_Total <- tibble(Amount_range="Sum_of_Amount:",Staff_Volume=NA,Amount=sum(Aging_by_amount$Amount))
Aging_by_amount <- bind_rows(Aging_by_amount,Grand_Total)
Aging_by_amount$Amount <- prettyNum(Aging_by_amount$Amount,big.mark = ",")
# 创建sheet并写入数据
addWorksheet(wb,"By_amount_range")
writeData(wb, "By_amount_range", Aging_by_amount, colNames = T,
rowNames = F,startCol=2, startRow = 1,
borders="rows", headerStyle = hs)
addStyle(wb, "By_amount_range", style = bs1, gridExpand = T,
rows = 2:nrow(Aging_by_amount),
cols = 2:(ncol(Aging_by_amount) + 1))
addStyle(wb, "By_amount_range", style = bs2, gridExpand = T,
rows = nrow(Aging_by_amount) + 1,
cols = 2:(ncol(Aging_by_amount) + 1))
setColWidths(wb,sheet = 3,widths = "auto",
cols = 2:(ncol(Aging_by_amount) + 1),
ignoreMergedCells = FALSE)
amt <- all %>% group_by(Days_range) %>% summarise(Sum_of_Amount=sum(Amount))
# 用0填充缺失值
amt$Sum_of_Amount[which(is.na(amt$Sum_of_Amount))] <- 0
Grand_Total <- tibble(Days_range="Sum_of_Amount:",Sum_of_Amount=sum(amt$Sum_of_Amount))
amount <- amt %>% bind_rows(Grand_Total)
# 添加千位分隔符
amount$Sum_of_Amount <- prettyNum(amount$Sum_of_Amount,big.mark = ",")
# 写入数据
addWorksheet(wb,"By_days_range")
writeData(wb, "By_days_range", amount, colNames = T,
rowNames = F,startCol=2, startRow =1,
borders="rows", headerStyle = hs)
addStyle(wb, "By_days_range", style = bs1, gridExpand = T,
rows = 2:(nrow(amount) + 1),
cols = 2:(ncol(amount) + 1))
addStyle(wb, "By_days_range", style = bs2, gridExpand = T,
rows = nrow(amount) + 1,
cols = 2:(ncol(amount) + 1))
setColWidths(wb,sheet = 4,widths = "auto",
cols = 2:(ncol(amount) + 1),
ignoreMergedCells = FALSE)
saveWorkbook(wb, file = paste0("./ww/",x,".xlsx"), overwrite = TRUE)
}
5、迭代所有部门
# 获取所有部门名称
BU <- df %>% distinct(BU) %>% as.list
bu <- BU$BU
for (var in bu) {
print(var)
print("-------------------------------------------")
my_fun(var)
}
分部门生成文件
按sheet生成内容
按金额标红
写入内容并标记
