XLConnectでハマったこと備忘
- Introduction
- 罠①：日本語を含んだfile pathが指定できない
- 罠②：値だけ書き込むためには、事前にsetStyleAction()での指定が必要
- 罠③：xlcFreeMemory()を忘れると直接編集したExcelの変更が直接保存できない。
- Conclusion
Introduction
VBAも一応かけるけど、あまり言語的な仕様が好きでない*1ので、なるべくRから制御することでVBAを書かずにすませたいという欲求がある。
RからExcelを利用する際に使えるパッケージはいくつかある（参考：RでExcelのデータを読む方法）がXLConnectを使っていく。
単なるデータの書き込みだけでなく、書式の設定等にも使える。
（openxlsxはなぜか文字化け*2の問題が解決できなかった）
Java依存なのもあり、別のアプリケーション制御をする系ライブラリ特有のわかりにくさもあり、ハマったことがあったのでいくつか備忘。
参考URL
罠①：日本語を含んだfile pathが指定できない
XLConnectにおいては、ファイルの読み書きをするさいにその対象となるpathに日本語が含まれていると、Error(FileNotFoundException)が吐かれてしまう。
はじめは、文字コードの問題なのだと思っていたが、pathの文字コードを変換して渡してもずっとErrorが吐かれ続けるので、頭を抱えた。
まぁ全て英語のディレクトリ・ファイル名のものだけ対象にしてプログラムを使えばいいじゃん、っていう考え方もあるが、やはり使い勝手が悪い。
何か助けになる情報がないかとさまよい出たネットの情報の海で、「readrパッケージがWindows上だと日本語のパスを読めない問題の現状 - Technically, technophobic.」という記事を発見し、tmpfileを作成してそこにリンクを貼る、という方法でこのErrorを回避できることを発見した。
具体的には、以下のようなやり方によって回避する
#Errorが出る方法 test_dir <- "C:/Users/usr1/デスクトップ/test.xlsx" #日本語を含む wb <- XLConnect::loadWorkbook(filename = test_dir) #読み込み →エラーが出る #回避方法 tmp <- tempfile(fileext=".xlsx") #一時fileの作成 file.link(test_dir, tmp) #一時ファイルにリンクを貼る wb <- XLConnect::loadWorkbook(filename =tmp) #これならErrorが出ない！
※Rでのtempfile, tempdirの扱い方は以下の記事に詳しい。
qiita.com
罠②：値だけ書き込むためには、事前にsetStyleAction()での指定が必要
主にRからデータをExcelファイルに書き込むときには、writeWorksheet()関数を使う。
しかし単純にこの関数を使ってしまうと、セルその枠線に設定されていた書式はすべてclearされてしまう。
こういった仕様は、たとえばすでに表の見た目や体裁のstyleはExcelのほうでテンプレートとして整えてあって、XLConnectに求めているのは値をそこに入れるということだけ、といったケースで邪魔になる。
このような状態を回避するには、setStyleAction()関数を事前に使っておく必要がある。
setStyleAction-methods function | R Documentation
この関数の引数のtypeに、予め用意されているstyle actionの種類の指定定数のうち、XLC$"STYLE_ACTION.NONE"を使う。
上記URLには、このtypeについて以下のような説明がなされている
XLC$"STYLE_ACTION.NONE":
This style action instructs XLConnect to apply no cell styles when writing data. Cell styles are kept as they are. This is useful in a scenario where all styling is predefined in an Excel template which is then only filled with data.
下線部にあるように、styleはすでにExcel側で設定していて、データはあと入れるだけになっているとき（XLConnect側でStyleを設定する必要がないとき）これを使う。
以下のように使う。
#セルの書式はそのままにデータだけ入れるようにするよう指定 XLConnect::setStyleAction(wb, type=XLC$STYLE_ACTION.NONE) #データの書き込み XLConnect::writeWorksheet( wb, sheet = sheet , data =test_data ,startCol=5 , startRow=2,header=F)
罠③：xlcFreeMemory()を忘れると直接編集したExcelの変更が直接保存できない。
これは罠というかついうっかりやってしまう初歩的なミス、といった類のものなのだが、
loadWorkbook()して一度XLConnectによる編集の対象としてしまうと、その間にExcelファイルに直接施した変更は（ロックされているので）そのまま保存できない。
必要な処理が終わったら、xlcFreeMemory()をしてメモリを解放しましょう。。。。
Conclusion
まぁ色々分かりにくいところはあるんだけど、こうやってなじみのあるアプリケーションを自動制御できるのはプログラミングを学び始めたころのwktk感を思い出させてくれる感じがしてなかなか新鮮ではあった。
Enjoy!
