none
PowershellでExcelの指定したセルの値を取得してCSVに出力させたい RRS feed

 > 

  • 質問

  • Question
    サインインして投票
    0
    サインインして投票

    現在Powershellのスクリプトを作成中です。

    まだまだ素人で自習書やサンプルコードを見ながら試行錯誤を繰り返してますが、解決できない問題があります。

    現在Excelの指定した範囲のセルの値を取得してそれをCSVに出力させるという処理のスクリプト作成をしており、セルの中身をそのままCSVファイルとして出力させたいのですが出力されるのが中身ではなくデータの長さや個数の情報が出力され、セルの中身が出力されないままで困ってます。

    「シート1のA1からC5に格納されているセルの情報を配列に格納してそれをcsv出力させる」という処理で、コードは以下の通りですが、csv出力がそれぞれのセルの中身ではなくデータの長さや個数("Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"...)だけがcsv出力され、詰まってます。

    配列処理は問題なく行われているため、csv出力の際にデータの処理・データ型の処理が正しく行われてない疑いが強いとみていますが有力な手がかりがつかめないままです。

    どなたかヘルプをお願いいたします。

        $fpath = “C:\excelcsvtest\*”
        $hanni = “A1:C5”
        $Fullname = Get-ChildItem $fpath -Include *.xls,*.xlsx
        $excel = new-Object -com excel.application;
        $excel.visible = $false;
        $book = $excel.Workbooks.open($Fullname);
        $sheet = $book.Worksheets.Item(1);
        $range = $sheet.Range($hanni);
        $array = @();
        foreach ($cell in $range){
            $array += $cell.text;
        }
        $book.close();
        $excel.quit();
        $array
        ConvertTo-Csv -InputObject $array -NoTypeInformation
    Export-Csv -Path C:\excelcsvtest\testout.csv -NoTypeInformation -InputObject $array -Encoding Default

    2014年9月19日 5:18
    |

回答

  • Question
    サインインして投票
    0
    サインインして投票

    foreach ($cell in $range){}の処理で、$rangeに含まれているセルを1次元配列（$array）に格納しているのが問題かと思われます。

    シートを行ごとに処理する必要があるのではないでしょうか。

    また、単にXLSX形式のファイルをCSVファイルに変換するというのであれば、Workbook.SaveAsメソッドでも良いかと思います。

    • 回答としてマーク ak1t0 2014年9月19日 7:40
    2014年9月19日 6:23
    |
    モデレータ
  • Question
    サインインして投票
    0
    サインインして投票

    疑似コードではなく、一応動くコードも書いてみました。

    PowerShell 3.0以降で動作します。

    $hanni = "A1:C3"
$Fullname = "C:\SkyDrive\ドキュメント\Book1.xlsx"
$excel = New-Object -com excel.application
$book = $excel.Workbooks.open($Fullname)
$sheet = $book.Worksheets.Item(1)
$range = $sheet.Range($hanni)
$headers = @()
$array = foreach($row in $range.Rows)
{
    if($headers.Length -eq 0)
    {
        $headers = $row.Columns |%{$_.Text}
    }
    else
    {
        $o = [ordered]@{}
        1..$headers.length |%{
            $o[$headers[$_-1]] = $row.Columns.Item($_).Text
        }
        [pscustomobject]$o
    }
}
$book.close()

$array|Export-Csv -Path C:\SkyDrive\ドキュメント\test.csv -NoTypeInformation  -Encoding Default
    オブジェクトのプロパティ名を取得するために、1行目だけ処理を変えるなど、結構めんどくさいですね…。

    なおExport-Csvの-InputObjectパラメータはオブジェクト配列を展開してくれないみたいなので、パイプライン入力に変えてあります。

    • 回答としてマーク ak1t0 2014年9月24日 2:10
    2014年9月19日 11:15
    |
    モデレータ

すべての返信

  • Question
    サインインして投票
    0
    サインインして投票

    foreach ($cell in $range){}の処理で、$rangeに含まれているセルを1次元配列（$array）に格納しているのが問題かと思われます。

    シートを行ごとに処理する必要があるのではないでしょうか。

    また、単にXLSX形式のファイルをCSVファイルに変換するというのであれば、Workbook.SaveAsメソッドでも良いかと思います。

    • 回答としてマーク ak1t0 2014年9月19日 7:40
    2014年9月19日 6:23
    |
    モデレータ
  • Question
    サインインして投票
    0
    サインインして投票

    アドバイスありがとうございます。

    配列処理の箇所を多次元配列の記述に修正して調査します

    2014年9月19日 7:39
    |
  • Question
    サインインして投票
    0
    サインインして投票

    Export-Csvコマンドレットに入力する値としては、多次元配列というよりかは、最終的には「『行に含まれる各セルの値をプロパティとして含むオブジェクト』の一次元配列」にする必要があるかと思います。

    Excelシートの行を一旦はオブジェクトにする過程が入るかと思います。

    疑似コードだと

    $array=@()
    foreach(行 in シート.全行)
    {
    　オブジェクト=new-object psobject
    　foreach(セル in 行)
    　{
    　　オブジェクト.プロパティ名=セル
    　}
    　$array+=オブジェクト
    }

    のような流れになるでしょうか。

    2014年9月19日 7:56
    |
    モデレータ
  • Question
    サインインして投票
    0
    サインインして投票

    疑似コードではなく、一応動くコードも書いてみました。

    PowerShell 3.0以降で動作します。

    $hanni = "A1:C3"
$Fullname = "C:\SkyDrive\ドキュメント\Book1.xlsx"
$excel = New-Object -com excel.application
$book = $excel.Workbooks.open($Fullname)
$sheet = $book.Worksheets.Item(1)
$range = $sheet.Range($hanni)
$headers = @()
$array = foreach($row in $range.Rows)
{
    if($headers.Length -eq 0)
    {
        $headers = $row.Columns |%{$_.Text}
    }
    else
    {
        $o = [ordered]@{}
        1..$headers.length |%{
            $o[$headers[$_-1]] = $row.Columns.Item($_).Text
        }
        [pscustomobject]$o
    }
}
$book.close()

$array|Export-Csv -Path C:\SkyDrive\ドキュメント\test.csv -NoTypeInformation  -Encoding Default
    オブジェクトのプロパティ名を取得するために、1行目だけ処理を変えるなど、結構めんどくさいですね…。

    なおExport-Csvの-InputObjectパラメータはオブジェクト配列を展開してくれないみたいなので、パイプライン入力に変えてあります。

    • 回答としてマーク ak1t0 2014年9月24日 2:10
    2014年9月19日 11:15
    |
    モデレータ
  • Question
    サインインして投票
    0
    サインインして投票
    ありがとうございます、参考にさせていただきます
    2014年9月24日 2:10
    |