Excelを読み込んで他の言語で読めるようにテキストに出力する(powershell)
よく客先常駐等で勝手にOSS入れられない等不便な場合があります。かといってVBAは文法使いにくし、JScript.netを今さらやる気も出ない。Powershellならデフォで入っているが、遅いから他の言語向けに中身だけ抽出したいということがあるかもしれません。
$error.Clear() if($args.Length -ne 2) { Write-Host "./excelsample.ps1 excelfile(fullpath) outputfile" Exit(9) } if((Test-Path $args[0] -PathType Leaf) -eq $false) { Write-Host "file not found:${$args[0]}" } $exl = $null $book = $null $append = $false try { $exl = New-Object -com Excel.Application $exl.Visible = $false $exl.DisplayAlerts = $false $book = $exl.Workbooks.Open($args[0],0,$true) foreach($sheet in $book.Worksheets) { Write-Host $sheet.Name $usedrange = $sheet.UsedRange.Address() $allrange = "" if($usedrange.IndexOf(":") -eq - 1) { $allrange = "`$A`$1:" + $usedrange } else { $allrange = "`$A`$1:" + $usedrange.Split(':')[1] } $cells = $sheet.Range($allrange).Value() $sw = New-Object System.IO.StreamWriter($args[1], $append, [Text.Encoding]::GetEncoding("UTF-8")) $sw.WriteLine("sheet," + $sheet.Name) for($r = 1;$r -le $cells.GetLength(0);$r++) { for($c = 1;$c -le $cells.GetLength(1);$c++) { $line = "$r,$c," + $cells[$r,$c].Length + "," + $cells[$r,$c] $sw.WriteLine($line) } } $sw.Close() $append = $true [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) > $null $sheet = $null } } finally { if($book -ne $null) { $book.Close() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($book) > $null $book = $null } if($exl -ne $null) { $exl.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($exl) > $null $exl = $null } [System.GC]::Collect() if($error.Count -ne 0) { Write-Host $error Exit(9) } }