R語(yǔ)言在定制Excel報(bào)表中的應(yīng)用

葉韻韶 黃雪歡 韓 棟

1 廣州市衛(wèi)生信息中心,510080 廣東 廣州；2 東莞市人民醫(yī)院,523018 廣東 東莞; 3 南方醫(yī)科大學(xué)第三附屬醫(yī)院，511400 廣東 廣州

Microsoft Excel依靠其友好的操作界面、強(qiáng)大的功能和廣泛的普及性，無(wú)論是專(zhuān)業(yè)人員還是非專(zhuān)業(yè)人員，Excel都是不可或缺的電子制表軟件。熟練使用R語(yǔ)言、STATA或SAS等專(zhuān)業(yè)統(tǒng)計(jì)軟件的專(zhuān)家一般不將Microsoft Excel作為數(shù)據(jù)分析的工具，但是在日常工作中輸出正式報(bào)表或與非統(tǒng)計(jì)專(zhuān)業(yè)人員進(jìn)行數(shù)據(jù)交流時(shí)[1-2]， Excel依然是首選的數(shù)據(jù)交流工具。近年來(lái)，隨著循證管理的需要，衛(wèi)生統(tǒng)計(jì)工作中經(jīng)常需要定期提供各類(lèi)報(bào)表[3-4]，無(wú)論是直接在Excel中進(jìn)行匯總計(jì)算及調(diào)整格式，還是采用統(tǒng)計(jì)軟件輸出粗略的數(shù)據(jù)再調(diào)整格式，無(wú)疑都是重復(fù)地手工勞動(dòng)。

R語(yǔ)言作為專(zhuān)業(yè)的統(tǒng)計(jì)分析語(yǔ)言有著開(kāi)源、自由度高、更新快等優(yōu)點(diǎn)，已被統(tǒng)計(jì)專(zhuān)業(yè)人員廣泛使用。R語(yǔ)言中可以操作Excel文件的軟件包有很多，成體系且較容易使用地有xlsx包[5]和openxlsx包[6]。其中xlsx包需要依賴(lài)JAVA運(yùn)行環(huán)境及rJAVA包的支持，而openxlsx包的發(fā)布擺脫了對(duì)JAVA環(huán)境的依賴(lài)，可以直接操作Excel文件。因此，本文旨在介紹openxlsx軟件包的主要功能，并以復(fù)合表頭“三線表”為例闡述其用法。本文所有程序基于R 3.6.1和openxlsx 4.1.3[6]。

1 openxlsx包介紹

openxlsx包擁有非常強(qiáng)大的Excel文件操作功能，主要包括對(duì)工作簿、工作表和單元格的操作，以及各項(xiàng)樣式(style)的設(shè)置功能。

工作簿(workbook)作為一個(gè)對(duì)象，主要完成新建、讀取和保存Excel文件的功能；工作表(worksheet)作為工作簿對(duì)象中的一個(gè)屬性，當(dāng)工作簿中包含多張工作表時(shí)，完成對(duì)工作表的命名與排序。工作簿和工作表主要是作為容納表格的容器，并沒(méi)有較為復(fù)雜地功能設(shè)置。

openxlsx強(qiáng)大的功能主要體現(xiàn)在對(duì)單元格格式的設(shè)定以及樣式的自定義方面，如合并單元格(mergeCells)、創(chuàng)建樣式(createStyle)和添加樣式至單元格(addStyle)等功能，還包括使用Excel過(guò)程中常用到的篩選功能(adDfilter)、條件格式(conditionalFormat)、數(shù)據(jù)核查(dataValidation)等功能。

2 openxlsx的功能及使用方法

本文以復(fù)合表頭的“三線表”為例，介紹openxlsx的功能及使用方法。保存復(fù)合表頭“三線表”的程序分為1個(gè)函數(shù)(merged.yn)和1個(gè)主程序。merged.yn函數(shù)的作用為判斷單元格是否已合并。主程序依次完成4個(gè)功能，分別為：1)創(chuàng)建Excel對(duì)象；2)計(jì)算可合并表頭的單元格；3)合并單元格；4)設(shè)置報(bào)表樣式等功能。

merged.yn函數(shù)共有2個(gè)參數(shù)，分別表示已合并的單元格矩陣(M)和欲合并的單元格向量(V)。M和V中每個(gè)行向量用來(lái)表示單元格的上、下、左和右邊界，以此判斷V是否被合并需判定V是否在任一方向上處于M之外，即以下4種情況(如圖1所示)：V[1]>M[2](V在M下方)、V[2]M[4](V在M右側(cè))和V[4]

圖1 判斷單元格是否合并方法示意圖

在V的不同坐標(biāo)上加或減一個(gè)常數(shù)(10-5)以排除相等情況的干擾，具體程序如下：

merged.yn = function(merged,tomerge){

#用來(lái)判斷tomerge所表達(dá)的格子是否已被合并過(guò)

#merged : Matrix，已合并單元格坐標(biāo)，每行4個(gè)元素，分別表示上下左右邊界

#tomerge: 向量，欲合并的單元格坐標(biāo)，4個(gè)元素，表示同上

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

#判斷欲合并單元格的4個(gè)坐標(biāo)

#是否在與已合并單元格重疊

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))

}

假設(shè)R中已存在一個(gè)輸出表格(讀取外部文件或R中運(yùn)算所得)如表1所示，該對(duì)象變量名為out。主程序部分主要包括4部分，分別為：創(chuàng)建Excel對(duì)象、表頭預(yù)處理、合并表頭、添加樣式。將以下所有代碼復(fù)制到function中，設(shè)定參數(shù)為out和filename，即可創(chuàng)建寫(xiě)入復(fù)合表頭的函數(shù)。

設(shè)輸出表格out為某醫(yī)院的工作量與效率報(bào)表，多級(jí)表頭層級(jí)用“.”分割，數(shù)據(jù)表格式如表1。

表1 輸出表格out對(duì)象形式

1)創(chuàng)建Excel對(duì)象

使用openxlsx處理Excel文件首先應(yīng)建立workbook對(duì)象(createWorkbook)，并在其中添加worksheet(addWorksheet)。所有針對(duì)Excel文件進(jìn)行地寫(xiě)入數(shù)據(jù)、添加樣式等操作均須注明workbook對(duì)象和worksheet編號(hào)。在對(duì)象的定義上，openxlsx包與xlsx包不同的是，其并未將工作表、行、列和單元格均定義為對(duì)象，而是僅將workbook作為對(duì)象，其他均作為workbook的屬性，簡(jiǎn)化了對(duì)象的聲明及調(diào)用過(guò)程。程序如下：

#創(chuàng)建Excel對(duì)象#

wb <- openxlsx::createWorkbook()

openxlsx::addWorksheet(wb,"tb") #工作表名稱(chēng)為tb

2)表頭預(yù)處理

此部分主要完成將單級(jí)表頭轉(zhuǎn)換為多級(jí)表頭，復(fù)合表頭的不同級(jí)別采用split.symbol(默認(rèn)為“.”)進(jìn)行分隔，如工作量.門(mén)診量分別表示工作量和門(mén)診量為不同級(jí)的表頭，另外同一級(jí)的表頭文字相同時(shí)則合并單元格，讀者可修改split.symbol的取值或?qū)⑵湓O(shè)置為函數(shù)的參數(shù)即可自定義表頭的分隔符。對(duì)于表頭級(jí)別小于最大級(jí)別的，將最后一級(jí)的表頭向下填補(bǔ)至最大級(jí)別。采用writeData函數(shù)將轉(zhuǎn)換后的表頭作為數(shù)據(jù)寫(xiě)入worksheet的相應(yīng)格子中，本文中默認(rèn)表格從第1行和第1列開(kāi)始寫(xiě)入，讀者可修改start_row和start_col設(shè)置不同的起始行與起始列，轉(zhuǎn)換為函數(shù)時(shí)亦可將起始行列作為參數(shù)自由設(shè)定。

#表頭預(yù)處理#

#將表頭拆分

split.symbol = "."

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

#復(fù)合表頭行數(shù)

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

#復(fù)合表頭列數(shù)

max.col = length(header.all)

#生成表頭數(shù)據(jù)框

header.all = dplyr::bind_cols(

lapply(header.all,function(x){

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

})

)

#寫(xiě)入復(fù)合表頭數(shù)據(jù)

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)合并表頭、寫(xiě)入數(shù)據(jù)

合并表頭部分主要功能在于計(jì)算應(yīng)合并的單元格，并調(diào)用合并單元格函數(shù)(mergeCells)。計(jì)算方法為對(duì)不重復(fù)的表頭向量(header)進(jìn)行循環(huán)，計(jì)算每個(gè)表頭內(nèi)容能夠合并的單元格坐標(biāo)，計(jì)算過(guò)程中合并單元格優(yōu)先進(jìn)行橫向合并，然后再進(jìn)行縱向合并。采用which函數(shù)判斷相應(yīng)的行、列首個(gè)不相等的表頭出現(xiàn)的位置，從而確定相同表頭的范圍。

寫(xiě)入表格的內(nèi)容仍然是采用writeData函數(shù)，從表頭層級(jí)數(shù)(max.lev)的下一行開(kāi)始寫(xiě)入。具體代碼與注釋如下：

#判斷并執(zhí)行合并表頭操作#

#獲取不重復(fù)的表頭向量

header = unique(unlist(header.all))

merged = matrix(numeric(4),nrow=1) #記錄已合并單元格的矩陣

for(header.i in header){

dup.yn = header.all==header.i #與header.i內(nèi)容相同的表頭

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

#與header.i內(nèi)容相同的表頭坐標(biāo)

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

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

#計(jì)算每個(gè)相同的表頭坐標(biāo)可能合并的范圍

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

tr.p = true.pos[tr.p.i,] #當(dāng)前表頭坐標(biāo)

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

#當(dāng)前表頭坐標(biāo)tr.p未被合并時(shí)才進(jìn)行計(jì)算

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

#計(jì)算合并的列

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]){

#計(jì)算多列合并的行

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

} else{

#計(jì)算單列合并的行

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))

#執(zhí)行合并單元格操作

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

}

}

}

}

}

#寫(xiě)入表格數(shù)據(jù)

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)添加樣式

單元格格式也是日常統(tǒng)計(jì)報(bào)表中需要手動(dòng)設(shè)置的功能，涉及到字體、字號(hào)、顏色、邊框等多個(gè)選項(xiàng)。對(duì)于單元格格式的調(diào)整也是制作統(tǒng)計(jì)報(bào)表過(guò)程中非常繁瑣的工作，當(dāng)多份表格都采用統(tǒng)一格式時(shí)，則需要較多的重復(fù)操作，效率低下。

openxlsx包中，設(shè)置表格樣式包括創(chuàng)建樣式(createStyle)，設(shè)置樣式(addStyle)2個(gè)步驟， createStyle函數(shù)中可以設(shè)置字體(fontName)、字號(hào)(fontSize)、字體顏色(fontColour)等字體相關(guān)格式，邊框(border)、邊框顏色(borderColour)和邊框線類(lèi)型(borderStyle)等邊框格式，該函數(shù)中還包括橫向(halign)與縱向(valign)對(duì)齊方式、前景(fgFill)與背景(bgFill)填充顏色等功能。其中，需注意的是openxlsx包中的字體(fontName)僅支持英文字體。

采用addStyle函數(shù)指明需要設(shè)置格式的workbook、worksheet和單元格的行列，即可將格式對(duì)象應(yīng)用于該單元格。

“三線表”中的要求將表格分割為表頭、數(shù)據(jù)和末行三部分格式，表頭部分除字體格式外，每個(gè)單元格需要設(shè)置上下邊框，數(shù)據(jù)部分只需要設(shè)置字體，如有必要可分別定義文本和數(shù)值數(shù)據(jù)格式，并分別設(shè)置不同類(lèi)型數(shù)據(jù)的格式。

#定義樣式并添加樣式#

#表頭樣式

headerStyle <- createStyle(

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

fontSize = 14, fontColour = "black",

fontName = " Times New Roman",

border="TopBottom",

borderColour = "black",

textDecoration = "bold"

)

#數(shù)據(jù)樣式

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

)

#添加數(shù)據(jù)欄格式

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)設(shè)置列寬并保存文件

上述格式僅針對(duì)單元格的內(nèi)容和邊框，單元格行高會(huì)根據(jù)字體自動(dòng)調(diào)整，而默認(rèn)列寬設(shè)置為每列均相等，通常無(wú)法滿足輸出需要。列寬可使用setColWidths函數(shù)進(jìn)行設(shè)置，寬度可以設(shè)置為固定值，也可以設(shè)置為自動(dòng)調(diào)整(“auto”)。完成所有數(shù)據(jù)寫(xiě)入、格式設(shè)置的workbook對(duì)象，可采用saveWorkbook函數(shù)保存文件，overwrite可進(jìn)行設(shè)置是否覆蓋已有文件。此處輸出路徑與文件名保存在字符串變量filename中(如filename=’D:output.xlsx’)，該變量可在運(yùn)行前進(jìn)行賦值或替換saveWorkbook中的filename變量。完成的報(bào)表如圖2所示。

#設(shè)置單元格寬度

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

#保存Excel文件

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

圖2 完成后的報(bào)表

3 討論

本文介紹了R語(yǔ)言openxlsx包及其在統(tǒng)計(jì)報(bào)表輸出中的應(yīng)用，給出了復(fù)合表頭的“三線表”輸出程序。openxlsx包的出現(xiàn)使R語(yǔ)言操作xlsx不再依賴(lài)JAVA或Perl語(yǔ)言，因此軟件包安裝上有了極大地改進(jìn)。本文未介紹其中一些非“三線表”所需的功能，如添加篩選條件、插入圖形等，有興趣的讀者可根據(jù)實(shí)際需求進(jìn)行添加。