はじめに
高校で非常勤講師をしております。
学校でもあらゆる場面でExcelが利用されています。
本稿では,「学校業務でありそうな作業」を例に,Excel作業におけるF#インタラクティブ(以下 fsi)の活用法を書いてみました。
まずは,VBAでマクロを書いた経験のある方,そして,F#に関心のある方を対象に,fsiの導入から単独セルの読み書きまでを準備編としてまとめました。(実例は実例編で)
ご注意
本稿は「覚え書き」であり,個人的な試行錯誤の結果をまとめたものです。
不正確な表記や誤った説明もあるかと思います。
また,Officeを外部スクリプトで操作すると,「COMオブジェクトの解放漏れ」などの問題もつきまといます。
素人の実験記事として参考程度にお取り扱いください。
作業環境
OS : Windows 10 Pro(Ver. 20H2)
Excel : Excel for Microsoft 365 MSO 32bit
導入
.NET5 SDK または Visual Studio Community 2019
F# 5.0での作業を前提としています。
本稿では,5.0未満のバージョンでは動作しない例が含まれておりますので,ご了承ください。
F#インタラクティブの起動
コマンドラインでfsiを起動する手段を2つあげました。
コマンドプロンプトやPowerShell,VSCodeのターミナルなどから起動する例です。
(1) Visual Studio Community 2019をインストールした場合
"C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\Common7\IDE\CommonExtensions\Microsoft\FSharp\"
にfsi.exeが見つかりました。
ここでは,PowerShellにフルパスを貼り付けてfsi.exeを動かしてみます。
(普段はデスクトップにショートカットを貼り付けて使っています。)
【画面1】fsi.exeの起動 → .NETのバージョン確認 → Excelの起動まで
(fsiの起動前にWindows10とPowerShellのバージョンも表示させています。)
起動メッセージとプロンプトが出たら,せっかくの対話環境なので,.NETのバージョンを確認してみます。
fsiではセミコロン2つ「;;
」が実行の指示です。
以下,「;;
」の有無も意識してお読みください。
System.Runtime.InteropServices.RuntimeInformation.FrameworkDescription;;
fsi.exeで起動した場合,.NET Framework 4.8を土台にしていることがわかります。
続いて,本題のExcelを起動させます。
#r "Microsoft.Office.Interop.Excel"
#r "Office"
open Microsoft.Office.Interop.Excel
let ex = ApplicationClass(Visible = true) :> _Application
;;
ここまででExcelが起動されます。
(2) .NET5 SDKをインストールした場合
コマンドラインからdotnet fsi
で起動します。
【画面2】dotnet fsiの起動 → .NETのバージョン確認 → Excelの起動まで
こちらは,.NETのバージョンが5.0です。
fsi.exeの場合と同様に
#r "Microsoft.Office.Interop.Excel"
でライブラリへの参照を設定しようとすると,「見つかりません」と返されてしまいます。
PC本体から Microsoft.Office.Interop.Excel.dll を探して,フルパスで参照設定することは可能ですが,NuGetから取り込む方法が使えます。
#r "nuget: Microsoft.Office.Interop.Excel"
#r "nuget: Office"
open Microsoft.Office.Interop.Excel
let ex = ApplicationClass(Visible = true) :> _Application
;;
NuGetを使った場合,【画面2】のように互換性に関するwarningが表示されますが,使用時のトラブルにはこれまで遭遇していません。(私の場合)
両者の違い
F#のバージョンはどちらも5.0ですので,F#の言語機能は同じです。
ただ,.NET Framework と .NET5 の違いが影響をおよぼす場合があります。
Excel操作に関連して例示すると
.NET Frameworkでは,
System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application")
により,通常の手段によってすでに起動されているExcelの操作も可能になります。(【画面3】)
一方,.NET5はGetActiveObject
メソッドが存在しないためエラーになります。(【画面4】)
【画面3】.NET Framework 4.8(fsi.exe) GetActiveObject
成功
【画面4】.NET 5(dotnet fsi) GetActiveObject
未定義エラー
ですので,.NET5上でF#を使って既存のExcelファイルを操作したい場合は,F#からExcelを起動して,Workbooks.Open
メソッドでファイルを開くことになります。
ブックとシートへのアクセス
Excelが起動したらブックやシートに名前(識別子)をつけます。(let 束縛)
(すでに,起動したExcelにはex
と名付けています。)
let wb = ex.Workbooks.Add() :> _Workbook // 新規ブックを作成する場合
// 既存ファイルを開く場合の例
// let wb = ex.Workbooks.Open(@"C:\Users\user1\Desktop\sample.xlsx") :> _Workbook
let ws = wb.Worksheets.[1] :?> _Worksheet
;;
fsi上でExcelの各オブジェクトを取得するプロパティやメソッドを操作すると,多くの場合でExcelオブジェクトを「obj
型」として返します。
その場合,:?>
演算子で目的のオブジェクトにダウンキャストする必要があります。
ここでは,let ws = wb.Worksheets.[1] :?> _Worksheet
が該当します。
上記ex
やwb
における:>
演算子については割愛します。
単独セルの操作
まず,セル操作の前にSheet1に適当なデータを手入力で用意しましょう。
Range
とCells
まずは,VBAでおなじみのRange
とCells
について確認します。
セルB2をそれぞれの方法でlet束縛してみます。
let cell1 = ws.Range("B2")
let cell2 = ws.Cells.[2, 2]
;;
val cell1 : Range
val cell2 : obj
cell1
の型は「Range
」です。一方,cell2
は「obj
」です。
cell2
の式について補足すると,Cells
プロパティは全てのセル範囲を示すRange
オブジェクトを返しますが,その先のItem
プロパティ(ws.Cells.[2, 2]
はws.Cells.Item(2, 2)
の省略形)がobj
を返します。
それぞれValue
プロパティで値を見てみます。
Value
プロパティは引数をとるので,引数は省略できても( )は省略できません。Value()
とします。
cell1.Value();;
val it : obj = "阿部"
cell1
は値を取得できます。ただし,値の型はobj
です。
なお,fsiではlet束縛のない式を実行すると「it
」という識別子で結果を返します。
直前の実行結果をit
を使って利用することができます。
では,cell2
は,
cell2.Value();;
cell2.Value();;
------^^^^^
stdin(97,7): error FS0039: 型 'Object' は、フィールド、コンストラクター、またはメンバー 'Value' を定義していません。
エラーになります。Range
オブジェクトではないので当然ですね。
では,対応策です。
let cell3 = ws.Cells.[2, 2] :?> Range
let cell4 = ws.Cells.Resize(1, 1).Offset(1, 1)
;;
val cell3 : Range
val cell4 : Range
Cells.[行番号,列番号]
で取得したセルをRange
オブジェクトとして扱うには,前述した「ダウンキャスト」が必要です。(cell3
の式)
なお,VBAと違いCells.[行番号,列番号]
でセル内の値は取得できません。
数値でセル位置を指示する別の方法として,VBAでも多用されるResize
プロパティ,Offset
プロパティが使えます。(cell4
の式)
これらのプロパティはRange
を返してくれるのでキャストが不要です。
この方法は,単独セルに限らず,セル範囲を数値で指示したい場合に重宝します。
<注意>Cells
の戻り(指定シート上の全てのセル)に対して,リサイズ前にオフセットしてしまうと当然ながらセル範囲がオーバーフローしてエラーが起こります。
let cell4 = ws.Cells.Offset(1, 1).Resize(1, 1);;
VBA同様に,コツさえつかめば,これらのプロパティを組み合わせて柔軟にセル範囲を指定することができます。
値の取得
セルの値を取得するには,先に示したようにValue
プロパティかValue2
プロパティを使います。数値や文字列の取得であればどちらでも構いませんが,日付データの場合に違いがありますので,値の取得にはValue
プロパティを使った方が無難です。(詳しくは実例編で)
取得した値はfloat
(int
は不可)やstring
にキャストしないと数値や文字列としての処理ができません。
取得した値を処理をせずに,他のセルに貼り付けるだけならば,obj
型のままでキャストの必要はありません。
let no1 = ws.Range("A2").Value() :?> float
let sei1 = ws.Range("B2").Value() :?> string
let yomi1 = ws.Range("C2").Value() :?> string
;;
val no1 : float = 1.0
val sei1 : string = "阿部"
val yomi1 : string = "アベ"
値の書き込み
書き込みの際は,Value2
プロパティを使います。
Value
プロパティを書き込みに使う場合は引数を省略できませんが,Value2
プロパティには引数がありません。
前記「値の取得」で触れた日付データは,文字列(string
)で渡しても,System.DateTime
で渡してもExcel側でそれなりに解釈してくれます。もちろん,シリアル値を数値で渡すことも可能です。
取得したデータを少し加工してみます。
let kojin1 = $"{no1,2} {sei1}({yomi1})";;
結合した文字列ができました。
補間文字列$" (...) "
は,埋め込む値がobj
型のままでも使えるので便利です。
val kojin1 : string = " 1 阿部(アベ)"
この文字列を他のセルに書き込んでみます。
プロパティのセットは=
ではなく,<-
を使います。
まずは,Value
プロパティで引数を省略してみます。
ws.Range("E2").Value() <- kojin1;;
前述のとおり失敗します。
System.Runtime.InteropServices.COMException (0x80020005): 種類が一致しません。
(途中省略)
エラーのため停止しました
というわけで,必要な引数を入れますが,やたら長いのです。<-- 短くできます。(下記追記参照)
ws.Range("E2").Value(XlRangeValueDataType.xlRangeValueDefault) <- kojin1;;
成功しますが,これだったらValue2
プロパティの利用が適切ですね。
セルG2に書き込んでみます。
ws.Range("G2").Value2 <- kojin1;;
スッキリしました。
(2021/03/24追記)
値を設定する際のValue
プロパティの引数は,null
でも可能です。
ws.Range("E2").Value(null) <- kojin1;;
<おまけ>
これで単独セルの操作は十分ですが,書き込みに関してはCells
プロパティがVBAのように使えます。
セルH2に書き込んでみます。
ws.Cells.[2, 9] <- kojin1;;
これが成功します。
公式ドキュメントを見ると...
確かに,Item
プロパティはセット可能ですね。
単独セルの値をコピーするだけなら,VBA的な書き方が可能です。
ws.Cells.[1, 5] <- ws.Cells.[1, 1];;
forループでちょっとした連続コピーも可能です。(出番は少ないと思いますが...)
for row in 2..6 do ws.Cells.[row, 5] <- ws.Cells.[row, 1];;
一旦,後始末しましょう。
お疲れさまでした。
ここまでコピペで動作確認できるように示しましたが,fsiではVBAのインテリセンスほどではないですが,TABキー補完や上下カーソルキーで入力履歴が利用ができます。
また,VBAと違ってfsiでは大文字小文字の区別がありますが,入力補完のおかげで楽に作業ができます。
VSCodeと組み合わせるのが個人的にはおすすめです。
まだ,F#のメリットが見えませんが,実例編ではVBAでは得られないF#の強力さと爽快感を書きます。
では,重要な後始末です。気を抜くと,冒頭でお断りした「COMオブジェクトの解放漏れ」に遭遇します。(気を抜かなくてもよくやらかします...)
この件に関しては正直理解不足です。
ネット上には有益な情報が数多くありますが消化できていません。
ここでは結果としてうまくいっている範囲でポイントを記します。
まず,fsiで起動したExcelは,fsiで終了させるように心がけます。
もし,自分でExcelを閉じてしまった場合は,下記の手順からwb.Close()
, ex.Quit()
を抜いてください。
手順を示します。
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cell4)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cell3)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cell2)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(cell1)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ws)
wb.Close()
// wb.Close(true) 上書き保存して閉じる
// wb.Close(false) 保存しないで閉じる
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wb)
ex.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(ex)
;;
途中Workbook.Close
メソッドでExcelから保存確認が入りますので対応してください。
終了時のポイントは,
let束縛したExcelのオブジェクトを必ず解放(FinalReleaseComObject
)する
ことです。できれば,作業の途中でも使い終わった段階で解放してください。
let束縛しなくても,Excelのオブジェクトがit
で返ってきた場合は,すぐにit
を解放してください。
例えば,セルA1の内容を確認しようとして,うっかり.Value()
を忘れると・・・
ws.Range("A1");;
長いシーケンスが返ってきます...
val it : Range =
seq
[seq
[seq
[seq
(以下省略)
画面が埋め尽くされてしまいますが,スクロールアップして確認するとit
はRange
オブジェクトです。
すかさず
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(it);;
で,解放してください。
長ったらしいですが,タブ補完で容易に入力できます。
事前にopen System.Runtime.InteropServices
でインポート宣言しておけば,その分短く書くこともできます。(実例編参照)
なお,Value
プロパティで取得した値(本稿ではyomi1
など)は解放不要です。
というか解放できません。(エラーになります)
また,未解放の識別子を使い回し(上書き)しないでください。
上書き前のオブジェクト解放が困難になります。
失敗すると(無自覚な失敗もよくあります)Excelがゾンビ化してメモリを消費します。
作業後はタスクマネージャーでバックグラウンドプロセスを確認するようにお願いします。
見つけたらタスクマネージャーから終了してください。
fsiの終了は
#q;;
です。
以下,「実例編」に続きます。