タイトルは某Python本リスペクトです:)
はじめに
Windows上で標準で使えるPowerShellのCOMを通してExcelを操作しようという内容です。
Q: 「VBAじゃダメなの？」
A: 「VBA使えるならそれでOKです」
Q: 「Pythonとか他の流行りの手段じゃダメなの？」
A: 「Pythonとか他の流行りの手段が使用できる環境であればそれでOKです」
とはいえ、PowerShellの何がいいって、「いろいろな理由で不自由な環境でもWindows OSのPCであれば使えるところ」だと思ってるので、(Excelに関係なく)使えるとサーバのメンテナンスなどでも何かと便利です。
また、PowerShellで(COM使って)Excel操作する記事はたくさんあるので、ここでは「Excel + PowerShell」と「Googleスプレッドシート + GAS」での比較についてまとめてみました。
ちなみにネット上では VBAで Excel操作する記事が圧倒的に多い(気がする)ですが、APIは同じものを使用しているため、ほとんどの操作はPowerShellからも同じようなAPIの実行で可能(のはず)です。
このあたりの要領は記事中最終行の取得などを参照。
以下、Excel+PowerShell環境はOffice365 + Windows 10のPowerShell 5.1
- 変更履歴
- 2019.07.10: GAS版アドレス指定のサンプルコード、("B10")でなく(10,2)と行・列指定になっていたので修正
ファイル(ブック)の読み込み
PowerShell + Excel
ブックの読み込み
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true
$excel.DisplayAlerts = $true
$book = $excel.Workbooks.Open($xlsx_file_path)
GAS + スプレッドシート
var ss = SpreadsheetApp.getActiveSpreadsheet();
シートの読み込み
PowerShell + Excel
$sheet = $book.Sheets.item("sheet_name")
GAS + スプレッドシート
var sheet = spredSheet.getSheetByName("sheet_name");
セルにアクセス
- VBA入門者の”どっち？” Cells or Range－Cellsでしかできないこと：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug
- 【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法
アドレス指定
B10セルの値をゲット/書き込みするには・・・
PowerShell + Excel
# 参照
$val = $sheet.Range("B10").Text
# 更新
$sheet.Range("B10") = "ここはB10"
GAS + スプレッドシート
// 参照
var val = sheet.getRange("B10").getValue();
// 更新
sheet.getRange("B10").setValue("ここはB10のはず")
行・列番号の指定
同じくB10セルの値を、「何カラム目何行目」という指定で参照するには・・・
ループ処理で1行ずつ番号指定で何度も処理するようなときはこちらが便利。
PowerShell + Excel
Cells(row, column)を使います。
rawに行番号(1開始)、columnに列番号(これも1開始)を指定する。
$val = $sheet.Cells(10, 2).Text
GAS + スプレッドシート
getRange(row, column)を使います。
引数はExcel+PowerShellと全く同じ。
var val = sheet.getRange(10, 2).getValue();
行・列番号の範囲指定
PowerShell + Excel
Range() + Cell()を組み合わせます。
範囲の左上のセルと右下のセルをCell()で指定し、それをRange()に指定。
$sheet.Range($sheet.Cells(5, 4), $sheet.Cells(7, 5)) = "(^o^)"
(5,4)"D5"から(7,5)"E7"までにテキストをセット
TODO:値の参照
GAS + スプレッドシート
getRange(row, column, numRows, numColumns)を使います。
sheet.getRange(5, 4, 3, 2).setValue("(^o^)");
値の参照は二次元配列。
データが以下の場合
var vals = sheet.getRange(5, 4, 3, 2).getValues();
for (var i in vals) {
for (var j in vals[i]) {
Logger.log("(" + i + ", " + j + "): " + vals[i][j]);
}
}
このコードを実行すると結果は以下の通り。
[19-09-07 22:12:25:884 JST] (0, 0): D5
[19-09-07 22:12:25:885 JST] (0, 1): E5
[19-09-07 22:12:25:886 JST] (1, 0): D6
[19-09-07 22:12:25:887 JST] (1, 1): E6
[19-09-07 22:12:25:888 JST] (2, 0): D7
[19-09-07 22:12:25:889 JST] (2, 1): E7
最終行の取得
1データ1行として、Excelやスプレッドシートに「追記」していくようなときによく使う。
PowerShell + Excel
A1セルから連続して下方向に入力値がセットされているセルの最終行が取得できる。
空行がもしあれば、そこまでとなる。
$lastrow = $sheet.Range("A1").End([Microsoft.Office.Interop.Excel.XlDirection]::xlDown.value__).Row
いきなり[Microsoft.Office.Interop.Excel.XlDirection]::xlDown.value__という謎の引数がでてきたけど、これです。
XlDirection Enum (Microsoft.Office.Interop.Excel) | Microsoft Docs
「VBAでExcel処理」の記述だとたくさん情報はヒットするけど、その中の「Excelの最終行をVBAで取得」を解説している記事でxlDownなどのキーワードが出てきます。
このキーワード、PowerShellではどう参照するかというと、.NETの値を参照できるのでそれを使えばOK。
で、.NETの値はどうなっているかというと、
.NET API ブラウザー | Microsoft Docs
ここでキーワード入れて検索すれば、ヒットする(…かもしれない)。
今回はMicrosoft.Office.Interop.Excelというわかりやすい名前空間に定義されているのが見つかったので、これを使えばOK。
ちなみにこの「VBAのサンプルコードに出てくるキーワードは、PowerShell(.NET)だとどう参照するのか」は、PowerShellでExcel操作などではとてもよく使うので慣れておくとよいかも。
GAS + スプレッドシート
var last = sheet.getLastRow();
PowerShell + Excelに比べて単純明快で、「そのシートで一番下の行に値が入力されてるセルの行数」が取得できる。
逆に言うと、以下のような手作りだと割とあるあるな感じのシートだとうまくいかないので注意。
| No | 項目 |
|---|---|
| 1 | ビーフカレー |
| 2 | 野菜カレー |
| 3 | シーフードカレー |
| 4 | バターチキンカレー |
| 5 | |
| 6 | |
| 7 | |
| 8 |
このシートで「バターチキンカレーを最終行として取得したい」場合はgetLastRow()は使えない。(「No」のカラムが8の行まで入力済みのため)
セルアクセスや修飾とかまで書いてるとキリがないのでこれくらいで。。
グラフ (PowerShell/概略)
PowerShell + Excel、以下の操作でグラフを作ったりも可能です。
とりあえずざっくりと簡単に。
-
$chart = $book.Charts.Add()でグラフ用シートの作成- グラフシートでなくシート内のオブジェクトであれば
$chart = $sheet.ChartObjects().Add(200, 75, 480, 360).Chartみたいな感じで
- グラフシートでなくシート内のオブジェクトであれば
-
$chart.SetSourceData()でデータソース設定 -
$chart.ChartType = [Microsoft.Office.Interop.Excel.XlChartType]::xlXYScatterSmoothでグラフの種類を設定 -
$chart.Legend.Position = [Microsoft.Office.Interop.Excel.XlLegendPosition]::xlLegendPositionBottomで凡例の位置を設定
などなど。。このあたりはやっぱりVBAの記事が参考になるはず。
保存 (PowerShell + Excel)
GASとスプレッドシートは自動保存なので特になし。
上書き保存
$book.Save()
別名で保存
$book.SaveAs($new_xlsx_file_path)
終了 (PowerShell + Excel)
GASとスプレッドシートはこれも特になし。
ExcelとPowerShellはちょっとややこしくて、きちんと処理しないとプロセスの残骸が残ってしまう。
【Powershell】 Excelのプロセスが残る - topvalue’s blog
$book.Close()
$excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
Excelのブックを閉じてExcel自体も終了しても、それだけだとタスクマネージャで確認するとExcelのプロセスが残ってしまう。
ここからさらにCOMオブジェクトのリリースが必要。
参考