PowershellでExcelへデータを書き込む

質問

データを別途クリップボードにコピーしPowershellでExcelに書き込むことは下記の方法で出来ますが
＊コピー元の水平タブ区切りデータ
果物 りんご みかん バナナ
個数 12 25 15

＊ペーストで書き込むるpsコード
<# Excelを操作する為の宣言#>
$excel = New-Object -ComObject Excel.Application
<# 起動直後は非表示（最小化ではない）になっているので表示する#>
$excel.Visible = $true
<#新規ブックを作成する#>
$book = $excel.Workbooks.Add()
<#新規ブックを作成する#>
$sheet = $excel.Worksheets.Item(1)
<#B列にペーストする#>
$sheet.Range("B2").PasteSpecial($exlpasteType::xlPasteValues)
<#メモリを解放#>
$excel = $null
[GC]::Collect()

これに対しデータのコピー等の作業を省き直接配列を使い処理できないかと思い下記のコードの方法まで至りました
＊配列で書き込むるpsコード
<# Excelを操作する為の宣言#>
$excel = New-Object -ComObject Excel.Application
<# 起動直後は非表示（最小化ではない）になっているので表示する#>
$excel.Visible = $true
<#新規ブックを作成する#>
$book = $excel.Workbooks.Add()
<# シートを取得する#>
$sheet = $excel.Worksheets.Item(1)
<# セルへ配列で書き込み#>
$arw = New-Object "object[,]" 2, 4 <# 書き込み用配列定義 2行4列#>
$arw[0, 0] = "果物"; $arw[0, 1] = "りんご"; $arw[0, 2] = "みかん"; $arw[0, 3] = "バナナ"
$arw[1, 0] = "個数"; $arw[1, 1] = 12; $arw[1, 2] = 25; $arw[1, 3] = 15
$sheet.Range("B2:E3") = $arw <# B2:E3 にセット#>
<#メモリを解放#>
$excel = $null
[GC]::Collect()

コピペで使うデータはsqlの集計結果をそのまま利用していますが、このコードの配列に書き直すにはちょっと手間が掛かる等の理由でもっとシンプル方法がないかと思い質問いたしました。
何か良い方法があればお教えください。

回答No.11

UY-Scuti

"array\d+"
より
"^array\d+$"
の方がいいかな？

お礼

UY-Scuti さん大変ありがとうございます。
任意の文字列を含む行を抽出し行数をカウントで出来ました。情けないのですが、私なんか全体の行数のカウントまでしか行き当たりませんでした。下記のコード問題なく出力されました。

<# セルに書き込むためのデータ#>
$array1 = ("果物","リンゴ" ,"ミカン", "バナナ","ぶどう")
$array2 = ("個数","10", "20","5","8")
$array3 = ("売上","1500", "2000","5000","300")
$i = ((gv).Name -match "^array\d+$").Count
$j = $array1.Length
<# セルへ配列で書き込み#>
$arr = New-Object "object[,]" $i,$j
$c = for($a=1;$a -le $i;$a++){for($b=0;$b -le $j;$b++){"`$arr[$($a-1),$($b)] = `$array$a[$b]"}};
$c | iex;
<#配置先のの先頭位置指定#>
$range = $sheet.Range("B2")
<# 範囲を設定しセット#>
$range.Resize($i, $j) = $arr

今回、変更した他の点
１．配列の要素を文字列と数字を混在させる方法は、作る立場でちょっと面倒なのでダブルクォートで囲む方法に統一しました・・・数字をダブルクォートで囲んでも多分Excel側で自動的に数字と認識するので。
２．書き込み範囲の設定は、cells()内の計算からResizeに変えました。

filemaker側での集計結果をどのような形のデータにし、教えて頂いたコードに自動的に組み込む方法がベストなのかこれから試してみます。
まだ、powershellの配列に関し気になるところですが、大変お世話になりました。

補足

配列を
$arw = New-Object "object[,]" 2, 4
$arw[0, 0] = "果物";
$arw[0, 1] = "りんご";
の形式にしない方法として、下記のように1次元配列を繰り返しセットすることも出来るのですが
<#セルに書き込むためのデータ#>
$array1 = @("果物","リンゴ" ,"ミカン", "バナナ","ぶどう")
$array2 = @("個数","10", "20","5","8")
$array3 = @("売上","1500", "2000","5000","300")
<#１次元配列で書き込む#>
$sheet.Range("B2:E2") = $array1
$sheet.Range("B3:E3") = $array2
$sheet.Range("B4:E4") = $array3

この方法でも問題はないか分かりかねています。

回答No.10

UY-Scuti

補足 2023/11/08 17:42
>配列を増やした場合に行数のカウントの仕方が分からないため手入力となる事が残念

gv (Get-Variable) で今、使われている変数の一覧が分かるので、そこから、「array任意の桁の数値」で一致する数をカウントしては？

(gv).Name -match "array\d+"
で

array1
array2
array3

と表示されると思います。これをカウント

((gv).Name -match "array\d+").Count
で、

3

となると思います。

これを変数に入れて使う。

回答No.9

UY-Scuti

ちなみに、下の返信の「お礼2023/11/07 17:32」
・・・で、for で書くとしたら、

for($a=1;$a -le 2;$a++){for($b=0;$b -le 3;$b++){"`$arr[$($a-1),$($b)] = `$array$a[$b]"}};

これで、
$arr[0,0] = $array1[0]
$arr[0,1] = $array1[1]
$arr[0,2] = $array1[2]
$arr[0,3] = $array1[3]
$arr[1,0] = $array2[0]
$arr[1,1] = $array2[1]
$arr[1,2] = $array2[2]
$arr[1,3] = $array2[3]
の文字列が作れます。
これを使うなら、

$excel = New-Object -ComObject Excel.Application;
$excel.Visible = $true;
$book = $excel.Workbooks.Add();
$sheet = $excel.Worksheets.Item(1);
$array1 = ("果物","リンゴ" ,"ミカン", "バナナ");
$array2 = ("個数",10, 20,5);
$arr = New-Object 'object[,]' 2,4;
$c = for($a=1;$a -le 2;$a++){for($b=0;$b -le 3;$b++){"`$arr[$($a-1),$($b)] = `$array$a[$b]"}};
$c | iex;
$sheet.Range("B2:E3") = $arr;
$excel = $null;
[GC]::Collect();

お礼

度々の回答有り難うございます。今回は、余計なつぶやきに対しfor文まで作っていただき申し訳ありませんでした。Split 演算子を使う方法も初めてみたのでついjavascriptの配列との違いに戸惑っているところです。
データを下記のように増やして書き出すことは出来るのですが
$str=@();
$str +="果物　りんご　みかん　バナナ　柿";
$str +="個数 12 25 15 20";
$str +="売上 1000 2000 1500 1200";
$c = ($str[0] -split "　| ").Count;
$r = $str.Count;
$arw = New-Object "object[,]" $r,$c;
$a = @();
foreach($inc in 0..($r-1)){$cnt = 0;$str[$inc] -split "　| " | % {$a +="`$arw[$inc,$cnt]=`"$($_)`"";$cnt++}};
$a | iex;
$sheet.Range("B2:F4") = $arw;
最後のセルの範囲について、拡張を自動化するにはcell()に置き換えればなんとかなると思われますし、今回教えてもらったfor文の法でも要素が増えた場合どう対応するか考えてみます。
取りあえずお礼と報告です。

補足

データ要素を増やした場合、まだ改善の余地があるが
書き込み範囲の設定は、
$sheet.Range($sheet.Cells(2, 2), $sheet.Cells(2+$r-1, 2+$c-1)) = $arw
で問題なく動く
しかしfor文の方は
<# セルに書き込むためのデータ#>
$array1 = ("果物","リンゴ" ,"ミカン", "バナナ","ぶどう")
$array2 = ("個数",10, 20,5,8)
$array3 = ("売上",1500, 2000,5000,300)
$i = 3
$j = $array1.Length
<# セルへ配列で書き込み#>
$arr = New-Object "object[,]" $i,$j
$c = for($a=1;$a -le $i;$a++){for($b=0;$b -le $j;$b++){"`$arr[$($a-1),$($b)] = `$array$a[$b]"}};
$c | iex;
$sheet.Range($sheet.Cells(2, 2), $sheet.Cells(2+$i-1, 2+$j-1)) = $arr <# B2:F4 にセット#>
とすれば出来るが、配列を増やした場合に行数のカウントの仕方が分からないため手入力となる事が残念
$i = 3

回答No.8

UY-Scuti

filemaker は、結局１行にまとめるような感じなのですかね？

１つの命令が１行で完結するように書く。
行の最後に ; を付ける。
１行で書けない命令は使わない。（@"～"@ のヒアドキュメントは複数行でしか使えないので変えました。エラーの原因はこれかな？）

$excel = New-Object -ComObject Excel.Application;
$excel.Visible = $true;
$book = $excel.Workbooks.Add();
$sheet = $excel.Worksheets.Item(1);
$str=@();
$str +="果物　りんご　みかん　バナナ";
$str +="個数 12 25 15";
$c = ($str[0] -split "　| ").Count;
$r = $str.Count;
$arw = New-Object "object[,]" $r,$c;
$a = @();
foreach($inc in 0..($r-1)){$cnt = 0;$str[$inc] -split "　| " | % {$a +="`$arw[$inc,$cnt]=`"$($_)`"";$cnt++}};
$a | iex;
$sheet.Range("B2:E3") = $arw;
$excel = $null;
[GC]::Collect();

お礼

お教え頂いたコードで試したところExcelに書き込まれました。
ありがとうございます。
当方でも
<# セルに書き込むためのデータ#>
$array1 = ("果物","リンゴ" ,"ミカン", "バナナ")
$array2 = ("個数",10, 20,5)
<# セルへ配列で書き込み#>
$arr = New-Object 'object[,]' 2,4
$arr[0,0] = $array1[0]
$arr[0,1] = $array1[1]
$arr[0,2] = $array1[2]
$arr[0,3] = $array1[3]
$arr[1,0] = $array2[0]
$arr[1,1] = $array2[1]
$arr[1,2] = $array2[2]
$arr[1,3] = $array2[3]
$sheet.Range("B2:E3") = $arr <# B12:E13 にセット#>
ならなんとか繰り返し処理すればいけるかなと思っていて、いままでfor文をコピペばかりで利用していたのでこれからどう書けばいいか悩んでいたところです。

回答No.7

UY-Scuti

filemaker の動作に関してはこちらで検証できないので、これ以上はお力になれそうもありません。
ただ、ちょっと気になることが・・・。

"powershell -WindowStyle Hidden -Command \"" &$cp
powershell を使う限りは、5.1 だと思うのですけど・・・。

PoweShell 7.x.x は、pwsh.exe なので、
"pwsh -WindowStyle Hidden -Command \"" &$cp
だと思うのですけど・・・。（この場合は、UTF-8）

1つ下の回答も、7.x.x では、
pwsh -win Hidden -c "'#'+(gc '%~f0' -Raw) | iex" &exit/b

pwsh.exe にパスを通しておく必要がありますが・・・。

お礼

何回もご協力頂きありがとうございます。
PoweShell 7.x.xに切り替えたのは数ヶ月前でそれまではpc購入時のwindows10の時代のままでした。
PoweShell側で古いコードでも対応しているため、"powershell -WindowStyle Hidden -Command \"" &$cpで動くと推測されますが・・・曖昧な記憶ですが確かそんな解説があったような
"pwsh -WindowStyle Hidden -Command \"" &$cpに代えても動くコードは当たり前ですが動くし、動かないコードはfilemaker側の文字コード処理機能がShift_JISに対応していないから今回みたいな場合はお手上げ状態となるのでしょう、なんせフィールド内容のエクスポートでソースファイルとして書き出すときは昔から変わらずUTF-16のままなので注意が必要とされているくらいですから別に驚くほど事もないですし、これからは、集計結果から力業で最初の配列形式を計算式で作ることに挑みます。
本当に有り難うございました。

回答No.6

UY-Scuti

だとしたら、PowerShell のコンソールを開いてコピペで貼り付けたら動くんでしょうかね？

PwerShell 7.2 だと、ps1 ファイルの文字コードは、UTF-8 です。
Windows PowerShell 5.1 だと、Shift_JIS 相当です。

filemaker というのはよく知りませんが、ダブルクリックで開きたいなら、バッチファイルから実行する方法があります。

---------------------------------------------
powershell -win Hidden -c "'#'+(gc '%~f0' -Raw) | iex" &exit/b
<# Excelを操作する為の宣言#>
$excel = New-Object -ComObject Excel.Application
<# 起動直後は非表示（最小化ではない）になっているので表示する#>
$excel.Visible = $true
<#新規ブックを作成する#>
<# シートを取得する#>
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
<# セルへ配列で書き込み#>
$str=@"
果物　りんご　みかん　バナナ
個数 12 25 15
"@ -split "`n"
$c=($str[0] -split "　| ").Count
$r=$str.Count
$arw=New-Object "object[,]" $r,$c <# 書き込み用配列定義 $r行$c列 このスクリプトでは、$r=2,$c=4になる#>
$a=@()
foreach($inc in 0..($r-1)){
$cnt=0
$str[$inc] -split "　| " | % {
$a +="`$arw[$inc,$cnt]=`"$($_)`""
$cnt++
}
}
$a | iex
$sheet.Range("B2:E3") = $arw <# B2:E3 にセット#>
<#メモリを解放#>
$excel = $null
[GC]::Collect()
---------------------------------------------

上記をファイルの拡張子をbatにして保存。実行。
powershell.exe を使うので文字コードは、文字コードは、Shift_JIS相当。
（Windows PowerShell 5.1 を使用。）

お礼

ありがとうございます。前の報告でpowershellのバージョンが間違っていました。現在は、7.3.9でした。
ターミナルを開きコードをコピペしたところ問題なくExcelに書き込まれました。
ご教示のコードでバッチファイル作り7.3.9環境で試したところUTF-8では動作しませんでしたが、Shift_JISでは問題なくExcelに書き込まれました。
となるとEventを送信スクリプト「"powershell -WindowStyle Hidden -Command \"" &$cp」における文字コードの問題と思われますのでShift_JISのままでは直接コマンド処理できないのでしょうね。なんせセミコロンを使ったり、コメントも<#・・#>としないとエラーになるくらいですから

回答No.5

UY-Scuti

下の2023/11/06 14:54 の返信のスクリプトをこちらで実行した限り、こちらでは動きます。コードのミスは無いです。
こちらの環境は、
Windows 10
Excel 2019
Windows PowerShell 5.1
です。

ps1 ファイルとして実行、または、PowerShell のコンソールを開いて貼り付けても動きます。

そちらの環境は何でしょうかね？

お礼

検証していただきありがとうございます。
そうなると環境の問題でなく、こちらの操作法に問題があると思います。その方法とは、その方法とは、filemakerからpsのコードをセミコロンでコードの改行明示的に変換してEventを送信スクリプト「"powershell -WindowStyle Hidden -Command \"" &$cp」としてコードを直接実行させることによりExcel操作する方法です。Vbs
ではソースファイルを作成し動かす方法でも可能でしたが、powershellの場合セキュリティの問題でソースファイルでは直接ダブルクリックで動作できない等の問題を回避するためです。
他の問題としては、全角文字や空白が認識されないためとか・・javascriptではダブルクォート等で囲まないとコードが読み込めないことがあったのでふと思いましたでも検証の結果問題がないからこれも違うか・・・ちなみに動作環境は
Windows 11
Excel 2019
Windows PowerShell 7.2

回答No.4

UY-Scuti

それから、
$arw = New-Object "object[,]" 2, 4
いらないです。下の方に
$arw=New-Object "object[,]" $r,$c
あると思いますが、そこで実行されます。

$c=($str[0] -split "　| ").Count
$r=$str.Count

で、数値がセットされます。

お礼

ありがとうございます
ご指摘の箇所を手直しして下記のコードで試しましたが、やはり駄目でした。
<# Excelを操作する為の宣言#>
$excel = New-Object -ComObject Excel.Application
<# 起動直後は非表示（最小化ではない）になっているので表示する#>
$excel.Visible = $true
<#新規ブックを作成する#>
<# シートを取得する#>
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
$str=@"
果物　りんご　みかん　バナナ
個数 12 25 15
"@ -split "`n"
$c=($str[0] -split "　| ").Count
$r=$str.Count
$arw=New-Object "object[,]" $r,$c
$a=@()
foreach($inc in 0..($r-1)){
$cnt=0
$str[$inc] -split "　| " | % {
$a +="`$arw[$inc,$cnt]=`"$($_)`""
$cnt++
}
}
$a | iex
$sheet.Range("B2:E3") = $arw <# B2:E3 にセット#>
<#メモリを解放#>
$excel = $null
[GC]::Collect()

$str=@"以下の教えてもらったコードを削除するとExcelは起動するので他にスペルミス等が考えられるでしょうか？

回答No.3

UY-Scuti

$a | iex
が抜けています。
$sheet.Range("B2:E3") = $arw
の上に挿入して下さい。

それから、スクリプトに全角文字がある場合、ps1 ファイルとかに保存する時は、文字コードは、ANSI か Shift_JIS 相当で保存して下さい。

仕組みは、$a に

$arw[0,0]="果物"
$arw[0,1]="りんご"
$arw[0,2]="みかん"
$arw[0,3]="バナナ"
$arw[1,0]="個数"
$arw[1,1]="12"
$arw[1,2]="25"
$arw[1,3]="15"

の文字列がセットされます。

その文字列を iex (Invoke-Expression ）にかけて PowerShell の命令と解釈させて実行しています。
https://win.just4fun.biz/?PowerShell/%E6%96%87%E5%AD%97%E5%88%97%E3%82%92%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89%E3%81%A8%E3%81%97%E3%81%A6%E5%AE%9F%E8%A1%8C%E3%81%99%E3%82%8B%E3%83%BBInvoke-Expression

回答No.2

UY-Scuti

9行目 $r から１引いてください。
foreach($inc in 0..($r-1)){

そうすると16行目、2>null 付けなくていいです。
$a | iex

配列の１行目は、1 じゃなくて 0 でしたね。

お礼

ご教示有り難うございます。
早速下記のように組み込んで実行しましたが私の方のPowerShellを動作させる方法のためかExcelの起動すら出来ませんでした。

<# Excelを操作する為の宣言#>
$excel = New-Object -ComObject Excel.Application
<# 起動直後は非表示（最小化ではない）になっているので表示する#>
$excel.Visible = $true
<#新規ブックを作成する#>
<# シートを取得する#>
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)
<# セルへ配列で書き込み#>
$arw = New-Object "object[,]" 2, 4 <# 書き込み用配列定義 2行4列#>
$str=@"
果物　りんご　みかん　バナナ
個数 12 25 15
"@ -split "`n"
$c=($str[0] -split "　| ").Count
$r=$str.Count
$arw=New-Object "object[,]" $r,$c
$a=@()
foreach($inc in 0..($r-1)){
$cnt=0
$str[$inc] -split "　| " | % {
$a +="`$arw[$inc,$cnt]=`"$($_)`""
$cnt++
}
}
$sheet.Range("B2:E3") = $arw <# B2:E3 にセット#>
<#メモリを解放#>
$excel = $null
[GC]::Collect()

何分お示しされたコードの意味が全く理解できないのでどこに問題があるか全く分かりかねています。さらなるご教授お願いいたします。
当方でも下記の方法でなんとかならないかとお思い試してみましたが
<#セルに書き込むためのデータ#>
$array1 = ("果物","リンゴ" ,"ミカン", "バナナ")
<#１次元配列で書き込む#>
for ($i = 0; $i -lt 1; $i++) {
$sheet.Range($sheet.Cells($i + 1, 1), $sheet.Cells($i + 1, $array1.Count)).Value(10) = $array1
}
任意のセルへの配置の法がわかないのと$array2をどう追加すればいいのか分からず悩んでいたところです。