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

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

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

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

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

Rで解析：コマンドをパイプでつなげてxlsxファイルを操作！！「openxlsx2」パッケージ

xlsxファイルの操作に便利な「openxlsx」パッケージをパイプコマンドだけでなく、チェインコマンドも利用可能に改良した「openxlsx2」パッケージの紹介です。 png画像を取り込み、ピクセル...

www.karada-good.net

2024.06.04

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

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

#パッケージのインストール
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:セルパターン関係です。

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