Rで解析:記事更新。エクセルの作成と保存が楽ちんです。「XLConnect」パッケージの紹介

Rの解析に役に立つ記事

Rでデータを保存するにはPDFや画像、HTML出力などありますが、余程大きなデータでなければエクセルファイルで保存するのが無難です。そこで、Rからエクセルファイルの作成、読み込みの機能が一通りそろっている「XLConnect」パッケージを紹介します。

紹介する実行コードを確認いただくことで、本パッケージに収録されている機能を使いこなせると思います。ぜひ、この機会に「XLConnect」パッケージを利用ください。

パッケージバージョンは1.0.6です。windows11のR version 4.2.2で確認しています。

xlsxファイルを操作する他のパッケージを参考に紹介します。

スポンサーリンク
スポンサーリンク

パッケージのインストール

下記コマンドを実行してください。

#パッケージのインストール
install.packages("XLConnect")

オリジナル関数「SelectXLS」を紹介

下記コードを実行いただくことで、SelectXLSコマンドでエクセルファイルの保存や読み込みのパスを取得します。内容はコメントを確認してください。保存はsaveWorkbookコマンド、読み込みはreadWorksheetFromFileコマンドと組み合わせる必要があります。

#actionオプション;"save:保存場所の指定(初期値)", "read:読み込むエクセルファイルのパスを取得"
SelectXLS <- function(action = "save"){
  
  #ライブラリの読み込み
  library("tcltk")
  
  switch(action, save = {
    #エクセルファイルの保存場所を指定
    setwd(paste(as.character(tkchooseDirectory(title = "エクセルファイルの保存場所を選択"),
                             sep = "", collapse ="")))},
    read = {
      #読み込むエクセルファイルのパスをGetXLSXPathに格納
      assign("GetXLSXPath", paste0(as.character(tkgetOpenFile(title = "エクセルファイルを選択",
                                                              filetypes = '{"エクセルファイル" {".xlsx" ".xls"}}',
                                                              initialfile = c("*.xlsx", "*.xls")))),
             env = .GlobalEnv)})
}

#使用例
#エクセルファイルの保存先指定
SelectXLS("save")
#エクセルファイルの保存:saveWorkbookコマンド
saveWorkbook(XLData)
#作業フォルダの確認
#getwd()

#読み込むエクセルファイルのパスをGetXLSXPathに格納
#キャンセルでGetXLSXPathは空になります
SelectXLS("read")
#エクセルファイルの読み込み:readWorksheetFromFileコマンド
ReadData <- readWorksheetFromFile(GetXLSXPath, sheet = 1)
#ファイルパスの表示
#GetXLSXPath
########

エクセルファイルの作成と保存コマンド

実行することで、冒頭画像のエクセルファイルを作成し保存することができます。セルの固定、オートフィル、セルの塗りつぶし、グラフ画像の挿入などが可能です。詳細はコメントを確認してください。なお、XLCオブジェクト一覧は記事最下部にまとめています。

#パッケージの読み込み
library("XLConnect")

###エクセルファイルの出力####
#データ例の作成
TestData <- data.frame(Group = I(paste0("TESTてすと", 1:10)),
                          Data = 1:10)

#空のエクセルオブジェクト(以下XLData)を作成:loadWorkbookコマンド
#loadWorkbook("ファイル名.xlsx or .xls", create = ファイルの作成:TRUE or FALSE)
XLData <- loadWorkbook("TEST.xlsx", create = TRUE)

#XLDataにシートを作成:createSheetコマンド
#createSheet(エクセルオブジェクト, name = シート名)
createSheet(XLData, name = "てすと")

#シートタブに色を設定:setSheetColorコマンド
#colorはXLCオブジェクトで指定します, 省略表記でも可能です
#記事最下部XLCオブジェクト一覧を参照してください
setSheetColor(XLData, sheet = 1, color = 14)

#作成したシートにデータを書き込む:writeWorksheetコマンド
#writeWorksheet(エクセルオブジェクト, data = 書き込むデータ,
#               sheet = データを書き込むシート名 or シート番号,
#               startRow = データ書き込み開始列:初期値は1,
#               startCol = データ書き込み開始行:初期値は1,
#               header = データ列名を書き込むかの指定:TRUE or FALSE)
writeWorksheet(XLData, data = TestData, sheet = 1,
               startRow = 3, startCol = 3, header = TRUE)

#セルパターン、塗りつぶしの色を設定
#設定はXLCオブジェクトで指定します
#createCellStyleコマンドでオブジェクトを作成します
Cstyle = createCellStyle(XLData, name = "色")
#setFillPatternコマンドでパターンを指定します
setFillPattern(Cstyle, fill = XLC$FILL.FINE_DOTS)
#setFillForegroundColorコマンドでセルの塗りつぶしの色を設定します
setFillForegroundColor(Cstyle, color = XLC$COLOR.GREEN)
#setCellStyleコマンドでセルに適応します
setCellStyle(XLData, sheet = 1, row = 4:10, col = 3,
             cellstyle = Cstyle)

###こんなこともできます#####
#例えばData列の2の倍数を赤く塗る
#createCellStyleコマンドでオブジェクトを作成します
Cstyle2 = createCellStyle(XLData, name = "2の倍数")
#setFillForegroundColorコマンドでセルの塗りつぶしの色を設定します
setFillForegroundColor(Cstyle2, color = XLC$COLOR.RED)
#setFillPatternコマンドでべた塗りを設定
setFillPattern(Cstyle2, fill = XLC$FILL.SOLID_FOREGROUND)
#setBorderコマンドで枠線を描写
#sideオプション:"bottom", "left", "right", "top", "all"
setBorder(Cstyle2, side = "all", type = XLC$BORDER.MEDIUM, color = XLC$COLOR.CORAL)

#元データで2の倍数の位置をwhichで取得後、データの書き込み開始行「3」を追加して塗りつぶし範囲を調整
RowIndex <- which(TestData[, 2] %% 2 == 0) + 3
#セルを塗りつぶし
if (length(RowIndex) != 0){
  setCellStyle(XLData, sheet = 1, row = RowIndex, col = 4,
               cellstyle = Cstyle2)}
########

#オートフィルタを設定:setAutoFilterコマンド
setAutoFilter(XLData, sheet = 1, reference = "D3:D13")

#セルの幅を設定:setColumnWidthコマンド
setColumnWidth(XLData, sheet = 1, column = 3, width = -1)

#セルの固定:createFreezePaneコマンド
#固定位置は行列番号で指定
createFreezePane(XLData, sheet = 1, colSplit = 6, rowSplit = 2)

#指定したセル範囲に名前をつける:createNameコマンド
#createName(エクセルオブジェクト, name = 範囲名, fomula = 範囲を指定:シート名!$行$列)
createName(XLData, name = "てすと", formula = "てすと!$C$4:$D$5", overwrite = TRUE)

###グラフを作成しエクセルに貼り付ける:addImageコマンド#####
#createNameコマンドと組み合わせます
#作業フォルダを取得
WDPath <- getwd() 
#一時フォルダに切り替え
setwd(tempdir())
#図の描写、ggplot2でも可能です
png("TEST.png", width = 400, height = 400)
plot(TestData[, 2], main = "TEST", col = "red", pch = 17)
dev.off()
#グラフの貼り付け位置を指定
createName(XLData, name = "グラフ", formula = "てすと!$F$3", overwrite = TRUE)
#addImageコマンドで追加:nameはcreateNameで指定した名前となります
addImage(XLData, filename = "TEST.png", name = "グラフ", originalSize = TRUE)
#作業フォルダをエクセルファイル保存先に戻す
setwd(WDPath)
########
#おまけ:欠損値に値を代入するsetMissingValueコマンド#
#setMissingValue(エクセルオブジェクト, value = 代入する値)

#エクセルファイルの保存:saveWorkbookコマンド
saveWorkbook(XLData)

エクセルファイルの読み込みコマンド

「実行コマンド:エクセルファイルの作成と保存の紹介」で作成したエクセルを使用し、すべてと指定範囲のデータを読み込む方法を紹介します。詳細はコメントを確認してください。

なお、読み込むエクセルのサイズが大きくエラーが出る場合は下記の記事を参考にしてください。
Rとウェブ解析:ファイルサイズが大きくても大丈夫「read.csv」の紹介
https://www.karada-good.net/analyticsr/r-41/

###エクセルファイルの読み込み#####
SelectXLS("read")

#エクセルファイルの読み込み:readWorksheetFromFileコマンド
#readWorksheetFromFile(ファイルの場所, sheet = 読み込むシートを指定)
#startRow , startCol , endRow , endColオプションで読み込み範囲を指定できます
ReadData <- readWorksheetFromFile(GetXLSXPath, sheet = 1)
Group  Data
1   TESTてすと1    1
2   TESTてすと2    2
3   TESTてすと3    3
4   TESTてすと4    4
5   TESTてすと5    5
6   TESTてすと6    6
7   TESTてすと7    7
8   TESTてすと8    8
9   TESTてすと9    9
10 TESTてすと10   10

#セル範囲名のデータを読み込み:readNamedRegionFromFileコマンド
ReadData <- readNamedRegionFromFile(GetXLSXPath, name = "てすと", header = FALSE)
Col1 Col2
1 TESTてすと1    1
2 TESTてすと2    2

XLCオブジェクト一覧

XLC$BORDER:セル枠線関係、XLC$COLOR:セルの色関係、XLC$COLOR:セルパターン関係です。

XLCオブジェクト名省略設定
XLC$ERROR.WARN"WARN"
XLC$ERROR.STOP"STOP"
XLC$DATA_TYPE.BOOLEAN"BOOLEAN"
XLC$DATA_TYPE.NUMERIC"NUMERIC"
XLC$DATA_TYPE.STRING"STRING"
XLC$DATA_TYPE.DATETIME"DATETIME"
XLC$STYLE_ACTION.XLCONNECT"XLCONNECT"
XLC$STYLE_ACTION.NONE"NONE"
XLC$STYLE_ACTION.PREDEFINED"PREDEFINED"
XLC$STYLE_ACTION.NAME_PREFIX"STYLE_NAME_PREFIX"
XLC$STYLE_ACTION.DATA_FORMAT_ONLY"DATA_FORMAT_ONLY"
XLC$BORDER.DASHED3
XLC$BORDER.DASH_DOT9
XLC$BORDER.DASH_DOT_DOT11
XLC$BORDER.DOTTED7
XLC$BORDER.DOUBLE6
XLC$BORDER.HAIR4
XLC$BORDER.MEDIUM2
XLC$BORDER.MEDIUM_DASHED8
XLC$BORDER.MEDIUM_DASH_DOT10
XLC$BORDER.MEDIUM_DASH_DOT_DOT12
XLC$BORDER.NONE0
XLC$BORDER.SLANTED_DASH_DOT13
XLC$BORDER.THICK5
XLC$BORDER.THIN1
XLC$COLOR.BLACK8
XLC$COLOR.WHITE9
XLC$COLOR.RED10
XLC$COLOR.BRIGHT_GREEN11
XLC$COLOR.BLUE12
XLC$COLOR.YELLOW13
XLC$COLOR.PINK14
XLC$COLOR.TURQUOISE15
XLC$COLOR.DARK_RED16
XLC$COLOR.GREEN17
XLC$COLOR.DARK_BLUE18
XLC$COLOR.DARK_YELLOW19
XLC$COLOR.VIOLET20
XLC$COLOR.TEAL21
XLC$COLOR.GREY_25_PERCENT22
XLC$COLOR.GREY_50_PERCENT23
XLC$COLOR.CORNFLOWER_BLUE24
XLC$COLOR.MAROON25
XLC$COLOR.LEMON_CHIFFON26
XLC$COLOR.ORCHID28
XLC$COLOR.CORAL29
XLC$COLOR.ROYAL_BLUE30
XLC$COLOR.LIGHT_CORNFLOWER_BLUE31
XLC$COLOR.SKY_BLUE40
XLC$COLOR.LIGHT_TURQUOISE41
XLC$COLOR.LIGHT_GREEN42
XLC$COLOR.LIGHT_YELLOW43
XLC$COLOR.PALE_BLUE44
XLC$COLOR.ROSE45
XLC$COLOR.LAVENDER46
XLC$COLOR.TAN47
XLC$COLOR.LIGHT_BLUE48
XLC$COLOR.AQUA49
XLC$COLOR.LIME50
XLC$COLOR.GOLD51
XLC$COLOR.LIGHT_ORANGE52
XLC$COLOR.ORANGE53
XLC$COLOR.BLUE_GREY54
XLC$COLOR.GREY_40_PERCENT55
XLC$COLOR.DARK_TEAL56
XLC$COLOR.SEA_GREEN57
XLC$COLOR.DARK_GREEN58
XLC$COLOR.OLIVE_GREEN59
XLC$COLOR.BROWN60
XLC$COLOR.PLUM61
XLC$COLOR.INDIGO62
XLC$COLOR.GREY_80_PERCENT63
XLC$COLOR.AUTOMATIC64
XLC$FILL.NO_FILL0
XLC$FILL.SOLID_FOREGROUND1
XLC$FILL.FINE_DOTS2
XLC$FILL.ALT_BARS3
XLC$FILL.SPARSE_DOTS4
XLC$FILL.THICK_HORZ_BANDS5
XLC$FILL.THICK_VERT_BANDS6
XLC$FILL.THICK_BACKWARD_DIAG7
XLC$FILL.THICK_FORWARD_DIAG8
XLC$FILL.BIG_SPOTS9
XLC$FILL.BRICKS10
XLC$FILL.THIN_HORZ_BANDS11
XLC$FILL.THIN_VERT_BANDS12
XLC$FILL.THIN_BACKWARD_DIAG13
XLC$FILL.THIN_FORWARD_DIAG14
XLC$FILL.SQUARES15
XLC$FILL.DIAMONDS16

少しでも、あなたのウェブや実験の解析が楽になりますように!!

タイトルとURLをコピーしました