面倒なExcel作業はPowerShell、スプレッドシート作業はGASにやらせよう

タイトルは某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

ブックの読み込み

```powershell
  $excel = New-Object -ComObject Excel.Application
  $excel.Visible = $true
  $excel.DisplayAlerts = $true
  $book = $excel.Workbooks.Open($xlsx_file_path)
```

## GAS + スプレッドシート

```javascript
  var ss = SpreadsheetApp.getActiveSpreadsheet();
```

# シートの読み込み

## PowerShell + Excel

```powershell
  $sheet = $book.Sheets.item("sheet_name")
```

## GAS + スプレッドシート

```javascript
  var sheet = spredSheet.getSheetByName("sheet_name");
```

# セルにアクセス

- [VBA入門者の”どっち？” Cells or Range－Cellsでしかできないこと：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug](https://www.moug.net/tech/exvba/0150070.html)
- [【初心者向けGAS】スプレッドシートのセル・セル範囲とその値を取得する方法](https://tonari-it.com/gas-spreadsheet-range-value-values/)

## アドレス指定

`B10`セルの値をゲット/書き込みするには・・・

### PowerShell + Excel

```powershell
  # 参照
  $val = $sheet.Range("B10").Text

  # 更新
  $sheet.Range("B10") = "ここはB10"
```

### GAS + スプレッドシート

```javascript
  // 参照
  var val = sheet.getRange("B10").getValue();

  // 更新
  sheet.getRange("B10").setValue("ここはB10のはず")
```

## 行・列番号の指定

同じく`B10`セルの値を、「何カラム目何行目」という指定で参照するには・・・
ループ処理で1行ずつ番号指定で何度も処理するようなときはこちらが便利。

### PowerShell + Excel

`Cells(row, column)`を使います。
`raw`に行番号(1開始)、`column`に列番号(これも1開始)を指定する。

```powershell
  $val = $sheet.Cells(10, 2).Text
```

### GAS + スプレッドシート

`getRange(row, column)`を使います。
引数はExcel+PowerShellと全く同じ。

```javascript
  var val = sheet.getRange(10, 2).getValue();
```

## 行・列番号の範囲指定

### PowerShell + Excel

`Range()` + `Cell()`を組み合わせます。
範囲の左上のセルと右下のセルを`Cell()`で指定し、それを`Range()`に指定。

```powershell
  $sheet.Range($sheet.Cells(5, 4), $sheet.Cells(7, 5)) = "(^o^)"
```

`(5,4)"D5"`から`(7,5)"E7"`までにテキストをセット

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/b8413bb5-aab9-3ba1-a98f-861188ae3131.png)

TODO:値の参照

### GAS + スプレッドシート

`getRange(row, column, numRows, numColumns)`を使います。

```javascript
  sheet.getRange(5, 4, 3, 2).setValue("(^o^)");
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/2937675d-b14c-155a-11e5-0247d635ca09.png)

値の参照は二次元配列。

データが以下の場合

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/99da40f2-798a-992a-f1c9-6d770dba5ce8.png)

```javascript
  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]);
    }
  }
```

このコードを実行すると結果は以下の通り。

```text
[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`セルから連続して下方向に入力値がセットされているセルの最終行が取得できる。
空行がもしあれば、そこまでとなる。

```powershell
  $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](https://docs.microsoft.com/ja-jp/dotnet/api/microsoft.office.interop.excel.xldirection?view=excel-pia#Microsoft_Office_Interop_Excel_XlDirection_xlDown)

「VBAでExcel処理」の記述だとたくさん情報はヒットするけど、その中の「Excelの最終行をVBAで取得」を解説している記事で`xlDown`などのキーワードが出てきます。

例: [No.8 ワークシートの最終行、最終列を取得する](http://www.niji.or.jp/home/toru/notes/8.html)

このキーワード、PowerShellではどう参照するかというと、.NETの値を参照できるのでそれを使えばOK。
で、.NETの値はどうなっているかというと、

[.NET API ブラウザー | Microsoft Docs](https://docs.microsoft.com/ja-jp/dotnet/api/)

ここでキーワード入れて検索すれば、ヒットする(…かもしれない)。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/256859/ffd0a03a-84f2-67fe-8055-65c1f4cd69be.png)

今回は`Microsoft.Office.Interop.Excel`というわかりやすい名前空間に定義されているのが見つかったので、これを使えばOK。

ちなみにこの「VBAのサンプルコードに出てくるキーワードは、PowerShell(.NET)だとどう参照するのか」は、PowerShellでExcel操作などではとてもよく使うので慣れておくとよいかも。

### GAS + スプレッドシート

```javascript
  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とスプレッドシートは自動保存なので特になし。

上書き保存

```powershell
  $book.Save()
```

別名で保存

```powershell
  $book.SaveAs($new_xlsx_file_path)
```

# 終了 (PowerShell + Excel)

GASとスプレッドシートはこれも特になし。
ExcelとPowerShellはちょっとややこしくて、きちんと処理しないとプロセスの残骸が残ってしまう。

[【Powershell】 Excelのプロセスが残る - topvalue’s blog](http://topvalue.hatenablog.com/entry/2018/01/05/210014)

```powershell
  $book.Close()
  $excel.Quit()
  [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
  [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
```

Excelのブックを閉じてExcel自体も終了しても、それだけだとタスクマネージャで確認するとExcelのプロセスが残ってしまう。
ここからさらにCOMオブジェクトのリリースが必要。

---

参考

- [PowerShell で Excel をどうのこうのすることに興味を持ってくれると嬉しい - Qiita](https://qiita.com/miyamiya/items/161372111b68bad0744a)