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語言，因此軟件包安裝上有了極大地改進。本文未介紹其中一些非“三線表”所需的功能，如添加篩選條件、插入圖形等，有興趣的讀者可根據實際需求進行添加。