エクセルファイルの作成に大変便利な「openxlsx」パッケージのコマンドを紹介です。パッケージでは「createWorkbook」コマンドで「Workbook object」を作成、データの追加、セルの体裁を条件付き書式や罫線で整える、「ggplot2」パッケージなどの出力を追加してエクセルファイルの作成が可能です。
パッケージバージョンは4.2.5.1。R version 4.2.2で確認しています。
パッケージのインストール
下記コマンドを実行してください。
#パッケージのインストール
install.packages("openxlsx")
実行コマンドの前に
実行コマンドの前に下記コマンドを実行し、各種パッケージを読み込んでください。
#openxlsxパッケージの読み込み
library("openxlsx")
#tidyverseパッケージがなければインストール
#ggplot2パッケージで図を作成するのに使用します
if(!require("tidyverse", quietly = TRUE)){
install.packages("tidyverse");require("tidyverse")
}
#tkgetOpenFileコマンド読み込みのため実行
library("tcltk")
実行コマンド
詳細はコマンド、パッケージのヘルプを確認してください。
xlsxファイルの読込み:「read.xlsx」コマンド
#xlsxファイルの読込み:read.xlsxコマンド
#xlsxファイルを選択
XLPath <- paste0(as.character(
tkgetOpenFile(title = "xlsxファイルを選択",
filetypes = '{"xlsxファイル" {".xlsx"}}',
initialfile = c("*.xlsx"))), collapse = " ")
#xlsxファイルの読み込み:read.xlsxコマンド
#読み込みxlsxファイルを指定:xlsxFileオプション
#読み込むシートを指定:sheetオプション
#読み込み開始行を指定:startRowオプション
#列名を設定:colNamesオプション;TRUE:最初の行が列名/FALSE:なし
#行名を設定:rowNamesオプション;TRUE:最初の列が行名/FALSE:なし
#日付の検出と変換設定:detectDatesオプション;TRUE/FALSE
#データまでの空列の処理:skipEmptyColsオプション;TRUE:空行をスキップ/FALSE:空をNA
#データまでの空行の処理:skipEmptyRowsオプション;TRUE:空行をスキップ/FALSE:空をNA
#データを読み込む行範囲:rowsオプション;NULLで全て,単一/c(5, 7),c(2:12)などと指定
#データを読み込む列範囲:colsオプション;NULLで全て,単一/c(5, 7),c(2:12)などと指定
#列名の重複をチェック:check.namesオプション;TRUE:重複した場合[.No]を付与/FALSE
#列名の空白部分に挿入:sep.namesオプション
#指定した名前付き領域を取得:namedRegionオプション;NULL/"領域名"を設定
#NA値と認識する文字の設定:na.stringsオプション
#「セルの結合」に対する処理:fillMergedCellsオプション;TRUE:セル結合範囲に同じ値
read.xlsx(xlsxFile = XLPath, sheet = 1,
startRow = 1, colNames = TRUE,
rowNames = FALSE, detectDates = FALSE,
skipEmptyCols = FALSE, #skipEmptyRows = TRUE,
rows = NULL, cols = NULL,
check.names = FALSE, sep.names = ".",
namedRegion = NULL, na.strings = "てすと",
fillMergedCells = FALSE)
xlsxファイルを保存:「write.xlsx」コマンド
#データを指定:xオプション;list clsassに複数データを格納して一括保存も可能
#ファイル名を設定:fileオプション;ファイルパスがなければ作業フォルダに保存
#上書き設定:overwriteオプション;TRUE/FALSE
###セルの体裁はcreateStyleコマンドで別に作成し指定が可能###
###その他パラメータオプションも紹介しています###
###[体裁を整えて保存:write.xlsxコマンド]を参照###
write.xlsx(x = list("Iris" = iris, "Cars" = cars),
file = "Test.xlsx",
overwrite = TRUE)
セルの体裁を作成:「createStyle」コマンド
「write.xlsx」コマンドだけでなく、「createWorkbook」コマンドと組み合わせて使用する「addStyle」コマンド、「conditionalFormatting」コマンドでもセルの体裁を設定するのに必要な「Style Object」を作成するコマンドです。後者の方が細かい調整が可能です。
#フォント:fontNameオプション;初期値:"Calibri"
#フォントサイズ:fontSizeオプション;初期値:11
#フォント色:fontColourオプション;初期値:"black"
#セルの書式:numFmtオプション;"GENERAL","NUMBER","CURRENCY",
##"ACCOUNTING","DATA","LONGDATE","TIME","PERCENTAGE","FRACTION","SCIENTIFIC",
##"TEXT","COMMA",ユーザー定義の'm"月"d"日"','0.00'などが可能
#罫線の位置:borderオプション;"NULL","top","bottom","left","right","TopBottom",
##"LeftRight","TopLeftRight","TopBottomLeftRight"が可能
#罫線の色:borderColourオプション;borderオプション"NULL"以外で適応
#罫線のスタイル:BorderStyleオプション;borderオプション"NULL"以外で適応,
##"none","thin","medium","dashed","dotted","thick","double","hair","mediumDashed",
##"dashDot","mediumDashDot","mediumDashDotDot","slantDashDot"が可能
#条件付きセル塗り色:bgFillオプション;条件付き書式のみ適応
#セル塗り色:fgFillオプション;通常の背景色の設定
#文字横位置:halignオプション;初期値:NULL,"left","right","center","justify"が可能
#文字縦位置:valignオプション;初期値:NULL,"top","center","buttom"が可能
#文字スタイル:textDecorationオプション;初期値:NULL,"bold","strikout","italic",
##"underline","underline2","accounting","accounting2"が可能
#文字を折り返して全体を表示する:wrapTextオプション;TRUE/FALSE
#文字の方向:textRotationオプション;初期値:NULL,0:255
#文字のインデント:indentオプション;初期値:NULL
#保護のロック設定:lockedオプション;初期値:NULL,TRUE/FALSE
#保護の表示設定:hiddenオプション:NULL,TRUE/FALSE
CellStyle <- createStyle(fontName = "Arial",
fontSize = 12,
fontColour = "black",
numFmt = 'm"月"d"日"',
border = "top",
borderColour = "black",
borderStyle = "none",
bgFill = NULL,
fgFill = "green",
halign = "center",
valign = NULL,
textDecoration = "accounting2",
wrapText = FALSE,
textRotation = 0,
indent = 1,
locked = TRUE,
hidden = FALSE)
体裁を整えて保存:「write.xlsx」コマンド
#NAで置換
iris[1, 2:3] <- NA
#保存
write.xlsx(x = list("Iris" = iris, "Cars" = cars),
file = "Test.xlsx",
overwrite = TRUE,
#シート名を設定:sheetNameオプション
#単一/ベクトルで指定が可能
sheetName = c("Iris", "くるま"),
#目盛り線の表示設定:gridLinesオプション;TRUE/FALSE:表示/非表示
#単一/ベクトルで指定が可能
gridLines = c(FALSE,TRUE),
#タブの色を設定:tabColourオプション
tabColour = c("red", "green"),
#シートの拡大設定:zoomオプション;10:400の範囲
zoom = c(80, 120),
#書き込み開始列を設定:startColオプション;c(sheet1, sheet2, ...)
startCol = c(3, 5),
#書き込み開始行を設定:startRowオプション;c(sheet1, sheet2, ...)/一括
startRow = 2,
#書き込み開始列行の一括設定:xyオプション;c(startCol, startRow)
#xy = c("A2", "c1"),
#列名の付与;colnamesオプション:TRUE/FALSE
colNames = TRUE,
#行名の付与:rowNamesオプション:TRUE/FALSE
rowNames = FALSE,
#行名の体裁調整:headerStyleオプション
#createStyleコマンドで作成したStyleオブジェクトを指定
headerStyle = CellStyle,
#データを囲うように罫線を引く:bordersオプション;
#NULL,"surrounding","columns","rows"が可能
borders = "surrounding",
#bordersの色:borderColour
borderColour = "red",
#borderの線種:borderStyleオプション,
#createStyleコマンド「borderStyle」オプション同様
borderStyle = "medium",
#NAを空白でなく#N/Aで保存;TRUE/FALSE
keepNA = TRUE,
#ウィンドウ枠の行固定:firstActiveRowオプション
firstActiveRow = 3,
#ウィンドウ枠の列固定:firstActiveColオプション
firstActiveCol = 4,
#列幅の指定:colWidthsオプション;"auto"/数値で指定
#colWidths = c("auto", "auto"),
#フィルターを設定:withFilterオプション;TRUE/FALSE
withFilter = c(TRUE, FALSE)
)
「Workbook object」を細かく体裁を整える
「createWorkbook」コマンドで「Workbook object」を作成し、データの書き込み、各種体裁を整えてエクセルファイルを作成します。実行コマンドではggplot2パッケージの出力をシートに追加する例も紹介します。
上から順番に実行することで出力例のエクセルを作成することが出来ます。
#Workbook objectの作成:createWorkbookコマンド
#作成者の設定:creatorオプション;初期値:ログインユーザー名
#タイトルプロパティの設定:titleオプション;初期値:NULL
#件名の設定:subjectオプション;初期値:NULL
#分類プロパティの設定:categoryオプション;初期値:NULL
TestWb <- createWorkbook(creator = "からだにいいもの",
title = "KARADA",
subject = "GOOD",
category = "Rテスト")
#ワークシートの追加:addWorksheetコマンド
#Workbook objectを指定:wbオプション
#作成シート名:sheetNameオプション
#目盛り線の表示:gridLinesオプション;TRUE/FALSE
#タブ色:tabColourオプション;初期値:NULL
#ズーム設定:zoomオプション;10:400の範囲
###下記ヘッダー,フッターで使用可能なショートコード###
#&[Page]:ページ番号,&[Pages]:ページ数,
#&[Date]:現在の日付,&[Time]:現在の時刻,
#&[Path]:ファイルのパス,&[File]:ファイル名,
#&[Tab]:シート名
#ヘッダー設定:headerオプション;c(左側,中央部,右側)
#フッター設定:footerオプション;c(左側,中央部,右側)
#偶数ページのヘッダー設定:evenHeaderオプション;c(左側,中央部,右側)
#偶数ページのフッター設定:evenFooterオプション;c(左側,中央部,右側)
#先頭ぺージヘッダー設定:firstHeaderオプション;c(左側,中央部,右側)
#先頭ぺージフッター設定:firstFooterオプション;c(左側,中央部,右側)
#表示設定:visibleオプション;TRUE/FALSE:シートが一つの時はFALSE不可
#印刷サイズ:paperSizeオプション;8:A3,9:A4,11:A5,12:B4,13:B5,
#67:A3横,55:A4横,61:A5横,その他サイズは?pageSetupで確認
#印刷の縦横方向:orientationオプション;"portrait"/"landscape"
addWorksheet(wb = TestWb,
sheetName = "からだにいいもの",
gridLines = FALSE,
tabColour = "blue",
zoom = 80,
header = c("からだに", "いいもの", "R情報"),
footer = c("KARADA", "GOOD", "R INFO &[Page],&[Pages]"),
evenHeader = c("偶数からだに&[Date],&[Time],", "偶数いいもの", NA),
evenFooter = c("EVEN KARADA", "EVEN GOOD", "EVEN R INFO"),
firstHeader = c("先頭からだに", "&[Path],&[File],&[Tab]",
"先頭R情報"),
firstFooter = c("First KARADA", NA, "First R INFO"),
visible = TRUE,
paperSize = 9,
orientation = "landscape")
#シートにデータを書き込む:writeDataコマンド
#Workbook objectを指定:wbオプション
#書き込み対象シート名:sheetオプション;名前/インデックス番号
#書き込みデータ:xオプション
#読み込み開始行を指定:startRowオプション
#読み込み開始列を指定:startcolオプション
#データ範囲に名前を付ける:nameオプション
#他は「体裁を整えて保存:write.xlsxコマンド」を参照
writeData(wb = TestWb,
sheet = 1, x = iris,
startRow = 3, startCol = 2,
array = FALSE,
colNames = TRUE, rowNames = FALSE,
name = "からだにいいもの",
headerStyle = CellStyle,
borders = "columns",
borderColour = "blue",
borderStyle = "double",
withFilter = TRUE,
keepNA = TRUE)
#ウィンドウ枠の固定:freezePaneコマンド
#「体裁を整えて保存:write.xlsxコマンド」を参照
freezePane(wb = TestWb,
sheet = 1,
firstActiveRow = 3,
firstActiveCol = 4)
#列の幅を指定:setColWidthsコマンド
#幅を指定:widthsオプション;"auto"/数値
setColWidths(wb = TestWb, sheet = 1,
cols = 2:6, widths = "auto")
#Style Objectを指定範囲に適応:addStyleコマンド
#新規Styleを作成
CellStyle2 <- createStyle(fgFill = "lightblue",
textDecoration = "underline2")
#Style Objectを適応
#cols,rowsの範囲が同じでない場合:gridExpandオプション:TRUEに設定
addStyle(wb = TestWb, sheet = 1,
style = CellStyle2,
rows = 8:10, cols = 2:6,
gridExpand = TRUE)
#Style Objectを書式ルールで適応:conditionalFormattingコマンド
#新規Styleを作成
#ポイント::bgFillオプションを使用する
CellStyle3 <- createStyle(bgFill = "red",
textDecoration = "underline2")
CellStyle4 <- createStyle(bgFill = "gold",
textDecoration = "underline2")
CellStyle5 <- createStyle(bgFill = "green",
textDecoration = "underline2")
CellStyle6 <- createStyle(bgFill = "black",
textDecoration = "underline2")
#指定条件内容:typeオプション
#ruleが"<","<=",">",">=","==","!=":"expression"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle3,
cols = 2, rows = 4:30,
type = "expression",
rule = ">=5")
#ruleが2または3の数値範囲でグラデーション:"colourScale"
#ruleが2または3の数値範囲でデータバー:"databar"
conditionalFormatting(wb = TestWb, sheet = 1,
style = c("pink", "yellow"),
cols = 3, rows = 4:30,
type = "colourScale",
rule = c(3, 5))
#ruleが重複値:"duplicates"
#ruleが指定の値を含む:"contains"
#ruleが指定した範囲の値を含む:"between"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle4,
cols = 4, rows = 4:30,
type = "duplicates")
#ruleがある文字で始まる:"beginsWith"
#ruleがある文字で終わる:"endsWith"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle5,
cols = 6, rows = 4:30,
type = "endsWith",
rule = "a")
#ruleが上位の値:"topN"
#ruleが下位の値:"bottomN"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle6,
cols = 5, rows = 4:30,
type = "topN",
rank = 20, percent = TRUE)
######################
#列の幅を指定:setColWidthsコマンド
#列幅:widthsオプション;"auto"/数値
setColWidths(wb = TestWb, sheet = 1,
cols = 2:5, widths = "18.3")
#plotやggplot2パッケージの出力を挿入:insertPlotコマンド
#BoxPlotを作成
BoxPlot <- ggplot(cars, aes(x = factor(speed),
y = dist, fill = factor(speed))) +
geom_boxplot()
#BoxPlotを追加:insertPlotコマンド
#最後にprintした図が挿入されます
#サイズを指定:width,heightオプション;単位はインチ
#単位を変更する場合はunitsオプションを使用
print(BoxPlot)
insertPlot(wb = TestWb, sheet = 1,
width = 15, height = 15,
units = "cm", xy= c("H", 7),
fileType = "png")
#作業フォルダにxlsxファイルを保存:saveWorkbookコマンド
saveWorkbook(wb = TestWb,
file = "からだにいいもの.xlsx",
overwrite = TRUE)
xlsxファイルやシートの保護、複製、削除など
#xlsxファイルにパスワードを設定する:protectWorkbookコマンド
#パスワードを指定:passwordオプション
protectWorkbook(wb = TestWb,
protect = TRUE,
password = "KARADAGOOD",
lockStructure = FALSE,
lockWindows = FALSE)
#ワークシートにパスワードやセルの保護などを設定する:protectWorksheetコマンド
#パスワードを指定:passwordオプション
#セルの保護:lockSelectingLockedCellsオプション
#その他保護設定は?protectWorksheet
protectWorksheet(wb = TestWb,
sheet = 1,
protect = TRUE,
password = "KARADAGOOD",
lockSelectingLockedCells = TRUE)
#シートの複製:cloneWorksheetコマンド
#コピー後のシート名:sheetNameオプション
cloneWorksheet(wb = TestWb,
sheetName = "からだにいいもの_2",
clonedSheet = 1)
#シート名の変更:renameWorksheetコマンド
#対象シート:sheetオプション
#変更後の名前:newNameオプション
renameWorksheet(wb = TestWb,
sheet = "からだにいいもの_2",
newName = "からだにいいもの_3")
#シートの削除:removeWorksheetコマンド
#対象シート:sheetNameオプション
removeWorksheet(wb = TestWb,
sheetName = "からだにいいもの_2")
その他
・ハイパーリンクの設定など
#ハイパーリンクの作成:getNamedRegionsコマンド
#URLやPathを含むデータが対象
#書き込みデータを"hyperlink class"に設定してから書き込む
class(対象データ) <- "hyperlink"
#もしくは文字列をHYPERLINK関数として準備する
#windowsの場合は「￥」は「\\」と入力する
paste0('HYPERLINK(リンク先, 表示名)')
#作成者の取得:getCreatorsコマンド
getCreators(wb = TestWb)
#シート名の取得:getSheetNamesコマンド
#xlsx/xlsmファイルを指定:fileオプション
getSheetNames(file)
・罫線のスタイルと位置：出力例を参照
#エクセルファイルを新規作成
TestWb <- createWorkbook()
#シートを追加
addWorksheet(wb = TestWb,
sheetName = "からだにいいもの",
gridLines = FALSE,
tabColour = "blue",
zoom = 80)
###罫線のスタイル#####
StyleName <- c("thin", "medium", "dashed", "dotted", "thick", "double",
"hair", "mediumDashed", "dashDot", "mediumDashDot",
"mediumDashDotDot", "slantDashDot")
#文字を書き込み
writeData(wb = TestWb, sheet = 1, x = "罫線のスタイル",
startRow = 2, startCol = 3)
#罫線を書き込む
for(i in seq(StyleName)){
ifelse(i <= 6, Sc <- 3, Sc <- 5)
ifelse(i <= 6, Sr <- 2 + i, Sr <- i - 4)
#書き込み
writeData(wb = TestWb, sheet = 1, x = StyleName[i],
startRow = Sr, startCol = Sc)
#セルのスタイル
CellStyle <- createStyle(fontSize = 12,
fontColour = "black",
border = "bottom",
borderColour = "red",
borderStyle = StyleName[i])
#スタイルを適応
addStyle(wb = TestWb, sheet = 1,
style = CellStyle,
rows = Sr, cols = Sc,
gridExpand = TRUE)
}
########
###罫線の位置#####
AllBorder <- c("top","bottom","left","right","TopBottom")
SrNo <- c(2, 3, 4, 5, 6)
#文字を書き込み
writeData(wb = TestWb, sheet = 1, x = "罫線の位置",
startRow = 2, startCol = 7)
#罫線を書き込む
for(i in seq(AllBorder)){
Sr <- SrNo[i] + i
#書き込み
writeData(wb = TestWb, sheet = 1, x = AllBorder[i],
startRow = Sr, startCol = 7)
#セルのスタイル
CellStyle <- createStyle(fontSize = 12,
fontColour = "black",
border = AllBorder[i],
borderColour = "blue",
borderStyle = "medium")
#スタイルを適応
addStyle(wb = TestWb, sheet = 1,
style = CellStyle,
rows = Sr, cols = 7,
gridExpand = TRUE)
}
########
#列の幅を指定
setColWidths(wb = TestWb, sheet = 1,
cols = c(3, 4, 5, 6, 7),
widths = c(23, 2.86, 23, 2.86, 23))
#作業フォルダにxlsxファイルを保存:saveWorkbookコマンド
saveWorkbook(wb = TestWb,
file = "からだにいいもの.xlsx",
overwrite = TRUE)
・条件付き書式：出力例を参照
#エクセルファイルを新規作成
TestWb <- createWorkbook()
#シートを追加
addWorksheet(wb = TestWb,
sheetName = "からだにいいもの",
gridLines = FALSE,
tabColour = "blue",
zoom = 80)
###条件付き書式#####
#条件付き書式名
RuleName <- c("expression", "colourScale", "databar", "duplicates",
"contains", "between", "beginsWith", "endsWith",
"topN", "bottomN")
#セルのスタイル
CellStyle <- createStyle(bgFill = "red")
CellStyle_BW <- createStyle(bgFill = c("pink", "blue"))
#データ位置
Sr <- rep(seq(3, 11, by = 2), 2)
LSc <- rep(c(2, 12), each = 5)
Sc <- rep(c(3, 13), each = 5)
#データ書き込み
for(i in seq(RuleName)){
#条件付き書式名を書き込み
writeData(wb = TestWb, sheet = 1, x = RuleName[i],
startRow = LSc[i], startCol = Sr[i])
#データ書き込み
if(RuleName[i] %in% c("contains", "beginsWith", "endsWith")){
writeData(wb = TestWb, sheet = 1,
x = c("karada", "からだに", "Good", "いいもの"),
startRow = Sc[i], startCol = Sr[i])
}else{
writeData(wb = TestWb, sheet = 1,
x = c(-1, -1, 2, 3, 3, 4, 5, 6),
startRow = Sc[i], startCol = Sr[i])}
}
###書式ルールを適応#####
#"expression"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 3, rows = 3:10,
type = "expression",
rule = ">2")
#"colourScale"
conditionalFormatting(wb = TestWb, sheet = 1,
style = c("pink", "blue", "yellow"),
cols = 5, rows = 3:10,
type = "colourScale",
rule = c(1, 3, 6))
#"databar"
conditionalFormatting(wb = TestWb, sheet = 1,
style = c("pink", "blue"),
cols = 7, rows = 3:10,
type = "databar")
#"duplicates"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 9, rows = 3:10,
type = "duplicates")
#"contains"/"notcontainsText"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 11, rows = 3:10,
type = "contains",
rule = "oo")
#"between"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 3, rows = 13:20,
type = "between",
rule = c(2, 4))
#"beginsWith"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 5, rows = 13:20,
type = "beginsWith",
rule = "か")
#"endsWith"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 7, rows = 13:20,
type = "endsWith",
rule = "d")
#"topN"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 9, rows = 13:20,
type = "topN",
rank = 3)
#"bottomN"
conditionalFormatting(wb = TestWb, sheet = 1,
style = CellStyle,
cols = 11, rows = 13:20,
type = "bottomN",
rank = 3)
#列の幅を指定
setColWidths(wb = TestWb, sheet = 1,
cols = c(3:11),
widths = rep(c(23, 2.86), length = 9))
#作業フォルダにxlsxファイルを保存:saveWorkbookコマンド
saveWorkbook(wb = TestWb,
file = "からだにいいもの.xlsx",
overwrite = TRUE)
出力例
・罫線のスタイルと位置
・条件付き書式
少しでも、あなたの解析が楽になりますように！！
