R语言在定制Excel报表中的应用

叶韵韶 黄雪欢 韩 栋

1 广州市卫生信息中心,510080 广东 广州；2 东莞市人民医院,523018 广东 东莞; 3 南方医科大学第三附属医院，511400 广东 广州

Microsoft Excel依靠其友好的操作界面、强大的功能和广泛的普及性，无论是专业人员还是非专业人员，Excel都是不可或缺的电子制表软件。熟练使用R语言、STATA或SAS等专业统计软件的专家一般不将Microsoft Excel作为数据分析的工具，但是在日常工作中输出正式报表或与非统计专业人员进行数据交流时[1-2]， Excel依然是首选的数据交流工具。近年来，随着循证管理的需要，卫生统计工作中经常需要定期提供各类报表[3-4]，无论是直接在Excel中进行汇总计算及调整格式，还是采用统计软件输出粗略的数据再调整格式，无疑都是重复地手工劳动。

R语言作为专业的统计分析语言有着开源、自由度高、更新快等优点，已被统计专业人员广泛使用。R语言中可以操作Excel文件的软件包有很多，成体系且较容易使用地有xlsx包[5]和openxlsx包[6]。其中xlsx包需要依赖JAVA运行环境及rJAVA包的支持，而openxlsx包的发布摆脱了对JAVA环境的依赖，可以直接操作Excel文件。因此，本文旨在介绍openxlsx软件包的主要功能，并以复合表头“三线表”为例阐述其用法。本文所有程序基于R 3.6.1和openxlsx 4.1.3[6]。

1 openxlsx包介绍

openxlsx包拥有非常强大的Excel文件操作功能，主要包括对工作簿、工作表和单元格的操作，以及各项样式(style)的设置功能。

工作簿(workbook)作为一个对象，主要完成新建、读取和保存Excel文件的功能；工作表(worksheet)作为工作簿对象中的一个属性，当工作簿中包含多张工作表时，完成对工作表的命名与排序。工作簿和工作表主要是作为容纳表格的容器，并没有较为复杂地功能设置。

openxlsx强大的功能主要体现在对单元格格式的设定以及样式的自定义方面，如合并单元格(mergeCells)、创建样式(createStyle)和添加样式至单元格(addStyle)等功能，还包括使用Excel过程中常用到的筛选功能(adDfilter)、条件格式(conditionalFormat)、数据核查(dataValidation)等功能。

2 openxlsx的功能及使用方法

本文以复合表头的“三线表”为例，介绍openxlsx的功能及使用方法。保存复合表头“三线表”的程序分为1个函数(merged.yn)和1个主程序。merged.yn函数的作用为判断单元格是否已合并。主程序依次完成4个功能，分别为：1)创建Excel对象；2)计算可合并表头的单元格；3)合并单元格；4)设置报表样式等功能。

merged.yn函数共有2个参数，分别表示已合并的单元格矩阵(M)和欲合并的单元格向量(V)。M和V中每个行向量用来表示单元格的上、下、左和右边界，以此判断V是否被合并需判定V是否在任一方向上处于M之外，即以下4种情况(如图1所示)：V[1]>M[2](V在M下方)、V[2]M[4](V在M右侧)和V[4]

图1 判断单元格是否合并方法示意图

在V的不同坐标上加或减一个常数(10-5)以排除相等情况的干扰，具体程序如下：

merged.yn = function(merged,tomerge){

#用来判断tomerge所表达的格子是否已被合并过

#merged : Matrix，已合并单元格坐标，每行4个元素，分别表示上下左右边界

#tomerge: 向量，欲合并的单元格坐标，4个元素，表示同上

YN = apply(merged,1,function(x){

#判断欲合并单元格的4个坐标

#是否在与已合并单元格重叠

yn = tomerge + c(-1,1,-1,1)*1e-5 > x[c(2,1,4,3)]

return(!any(yn==c(TRUE,FALSE,TRUE,FALSE)))

})

#返回该格子是否包含在任意已合并格子中

return(any(YN))

}

假设R中已存在一个输出表格(读取外部文件或R中运算所得)如表1所示，该对象变量名为out。主程序部分主要包括4部分，分别为：创建Excel对象、表头预处理、合并表头、添加样式。将以下所有代码复制到function中，设定参数为out和filename，即可创建写入复合表头的函数。

设输出表格out为某医院的工作量与效率报表，多级表头层级用“.”分割，数据表格式如表1。

表1 输出表格out对象形式

1)创建Excel对象

使用openxlsx处理Excel文件首先应建立workbook对象(createWorkbook)，并在其中添加worksheet(addWorksheet)。所有针对Excel文件进行地写入数据、添加样式等操作均须注明workbook对象和worksheet编号。在对象的定义上，openxlsx包与xlsx包不同的是，其并未将工作表、行、列和单元格均定义为对象，而是仅将workbook作为对象，其他均作为workbook的属性，简化了对象的声明及调用过程。程序如下：

#创建Excel对象#

wb <- openxlsx::createWorkbook()

openxlsx::addWorksheet(wb,"tb") #工作表名称为tb

2)表头预处理

此部分主要完成将单级表头转换为多级表头，复合表头的不同级别采用split.symbol(默认为“.”)进行分隔，如工作量.门诊量分别表示工作量和门诊量为不同级的表头，另外同一级的表头文字相同时则合并单元格，读者可修改split.symbol的取值或将其设置为函数的参数即可自定义表头的分隔符。对于表头级别小于最大级别的，将最后一级的表头向下填补至最大级别。采用writeData函数将转换后的表头作为数据写入worksheet的相应格子中，本文中默认表格从第1行和第1列开始写入，读者可修改start_row和start_col设置不同的起始行与起始列，转换为函数时亦可将起始行列作为参数自由设定。

#表头预处理#

#将表头拆分

split.symbol = "."

header.all = stringr::str_split(colnames(out),split.symbol)

#复合表头行数

max.lev = max(sapply(header.all,length))

#复合表头列数

max.col = length(header.all)

#生成表头数据框

header.all = dplyr::bind_cols(

lapply(header.all,function(x){

c(x,rep(x[length(x)],max.lev-length(x)))

})

)

#写入复合表头数据

start_row = 1

start_col = 1

openxlsx::writeData(wb,sheet = 1, rowNames = FALSE,colNames = FALSE,

x=header.all, startCol = start_col,startRow = start_row)

3)合并表头、写入数据

合并表头部分主要功能在于计算应合并的单元格，并调用合并单元格函数(mergeCells)。计算方法为对不重复的表头向量(header)进行循环，计算每个表头内容能够合并的单元格坐标，计算过程中合并单元格优先进行横向合并，然后再进行纵向合并。采用which函数判断相应的行、列首个不相等的表头出现的位置，从而确定相同表头的范围。

写入表格的内容仍然是采用writeData函数，从表头层级数(max.lev)的下一行开始写入。具体代码与注释如下：

#判断并执行合并表头操作#

#获取不重复的表头向量

header = unique(unlist(header.all))

merged = matrix(numeric(4),nrow=1) #记录已合并单元格的矩阵

for(header.i in header){

dup.yn = header.all==header.i #与header.i内容相同的表头

if(sum(dup.yn)>1){

#与header.i内容相同的表头坐标

true.pos = cbind((which(dup.yn)-1) %% max.lev+1,

(which(dup.yn)-1) %/% max.lev+1)

#计算每个相同的表头坐标可能合并的范围

for(tr.p.i in 1:nrow(true.pos)){

tr.p = true.pos[tr.p.i,] #当前表头坐标

tr.p.col = tr.p.row = NA #用以保存合并表头的末端位置

#当前表头坐标tr.p未被合并时才进行计算

if(!merged.yn(merged,rep(tr.p,each = 2))){

#计算合并的列

tr.p.col = tr.p[2]+ which(!dup.yn[tr.p[1],tr.p[2]:max.col])[1]-2

tr.p.col = ifelse(is.na(tr.p.col),max.col,tr.p.col)

if(tr.p.col!= tr.p[2]){

#计算多列合并的行

tr.p.row = which(rowSums(dup.yn[,tr.p[2]:tr.p.col])==(tr.p.col-tr.p[2]+1))[1]

} else{

#计算单列合并的行

tr.p.row = tr.p[1]+which(!dup.yn[tr.p[1]:max.lev,tr.p[2]])[1]-2

tr.p.row = ifelse(is.na(tr.p.row),max.lev,tr.p.row)

}

if(any(c(tr.p.row,tr.p.col)!=tr.p)){

tomerge = c(tr.p[1], tr.p.row, tr.p[2], tr.p.col) #此处可加入起始行列

#更新已合并矩阵

merged = rbind(merged,matrix(tomerge,nrow=1))

#执行合并单元格操作

openxlsx::mergeCells(wb,1,rows = tomerge[1:2],cols = tomerge[3:4])

}

}

}

}

}

#写入表格数据

openxlsx::writeData(wb,1,as.data.frame(out),colNames=FALSE,rowNames=FALSE,

startRow = start_row+max.lev,startCol = start_col, keepNA=FALSE,

withFilter = FALSE

)

4)添加样式

单元格格式也是日常统计报表中需要手动设置的功能，涉及到字体、字号、颜色、边框等多个选项。对于单元格格式的调整也是制作统计报表过程中非常繁琐的工作，当多份表格都采用统一格式时，则需要较多的重复操作，效率低下。

openxlsx包中，设置表格样式包括创建样式(createStyle)，设置样式(addStyle)2个步骤， createStyle函数中可以设置字体(fontName)、字号(fontSize)、字体颜色(fontColour)等字体相关格式，边框(border)、边框颜色(borderColour)和边框线类型(borderStyle)等边框格式，该函数中还包括横向(halign)与纵向(valign)对齐方式、前景(fgFill)与背景(bgFill)填充颜色等功能。其中，需注意的是openxlsx包中的字体(fontName)仅支持英文字体。

采用addStyle函数指明需要设置格式的workbook、worksheet和单元格的行列，即可将格式对象应用于该单元格。

“三线表”中的要求将表格分割为表头、数据和末行三部分格式，表头部分除字体格式外，每个单元格需要设置上下边框，数据部分只需要设置字体，如有必要可分别定义文本和数值数据格式，并分别设置不同类型数据的格式。

#定义样式并添加样式#

#表头样式

headerStyle <- createStyle(

halign = "center",valign="center",

fontSize = 14, fontColour = "black",

fontName = " Times New Roman",

border="TopBottom",

borderColour = "black",

textDecoration = "bold"

)

#数据样式

dataStyle = createStyle(

halign = "center",valign="center",

fontSize = 12, fontColour = "black",

fontName = "Times New Roman"

)

#末行样式

lastrowStyle = createStyle(

halign = "center",valign="center",

fontSize = 12, fontColour = "black",

fontName = "Times New Roman",

border="bottom"

)

#添加表头格式

openxlsx::addStyle(wb,1,headerStyle,

cols = start_col -1 + rep(1:ncol(header.all),each = nrow(header.all)),

rows = start_row -1 + rep(1:nrow(header.all),ncol(header.all)),

gridExpand = TRUE

)

#添加数据栏格式

openxlsx::addStyle(wb,1,dataStyle,

cols = start_col -1 + rep(1:ncol(out),each = nrow(out)),

rows = start_row -1 + max.lev + rep(1:nrow(out),ncol(out)),

gridExpand = TRUE

)

#添加末行样式

openxlsx::addStyle(wb,1,lastrowStyle,

cols = start_col -1 + 1:ncol(out),

rows = start_row -1 + max.lev + rep(nrow(out),ncol(out)),

gridExpand = TRUE

)

5)设置列宽并保存文件

上述格式仅针对单元格的内容和边框，单元格行高会根据字体自动调整，而默认列宽设置为每列均相等，通常无法满足输出需要。列宽可使用setColWidths函数进行设置，宽度可以设置为固定值，也可以设置为自动调整(“auto”)。完成所有数据写入、格式设置的workbook对象，可采用saveWorkbook函数保存文件，overwrite可进行设置是否覆盖已有文件。此处输出路径与文件名保存在字符串变量filename中(如filename=’D:output.xlsx’)，该变量可在运行前进行赋值或替换saveWorkbook中的filename变量。完成的报表如图2所示。

#设置单元格宽度

openxlsx::setColWidths(wb,1,cols = 1:ncol(out),width = "auto")

#保存Excel文件

openxlsx::saveWorkbook(wb,filename,overwrite=TRUE)

图2 完成后的报表

3 讨论

本文介绍了R语言openxlsx包及其在统计报表输出中的应用，给出了复合表头的“三线表”输出程序。openxlsx包的出现使R语言操作xlsx不再依赖JAVA或Perl语言，因此软件包安装上有了极大地改进。本文未介绍其中一些非“三线表”所需的功能，如添加筛选条件、插入图形等，有兴趣的读者可根据实际需求进行添加。