プログラミング

PowerShellでDBから取得したデータをExcelに出力する方法

こんにちは。
けいぞうです。

今回は「PowerShellでDBから取得したデータをExcelに出力する方法」についての記事です。

[目次]

サンプルコード – DBから取得したデータをExcelに出力

SQLServerのDBから取得してきたデータをExcelに整形して出力するサンプルコードです。

# ログ出力関数
function outputLog{
  $msg = ""
  for($i=0;$i -lt $args.count;$i++){
    $msg += $args[$i]
  }
  $datetime = Get-Date -format "yyyy/MM/dd HH:mm:ss"
  $val = $datetime + " " + $msg
  add-content -path $LOGFILEPATH -value ( $val ) -encoding UTF8
}

# 接続文字列の取得関数
function GetconnectionString(){
  $connectionString = #ここに接続文字列を記載
  return $connectionString
}

# SELECT処理関数
function SelectFromDB($conn){
    $commandText = "SELECT * FROM Table_1"; # ここにクエリを記載

    $conn.open()
    $dt = New-Object System.Data.DataTable
    $da = New-Object System.Data.SqlClient.SqlDataAdapter($commandText, $conn)
    [void]$da.Fill($dt)
    $da.Dispose()
    $conn.Close()

    return [System.Data.DataTable]$dt
}

#-------
#メイン処理
#-------

# 事前処理
$date = date
$scriptPath = Split-Path $MyInvocation.MyCommand.Path -Parent
$LOGFILEPATH = $scriptPath + "\" + "GetDBData.log"

try{
 # 設定ファイルから接続文字列の取得
 $connectionString = GetconnectionString
 $conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)

 # 開始ログ出力
 outputLog "開始"

 # DBからデータを取得
 $dt = New-Object System.Data.DataTable
 $Result = SelectFromDB $conn
 $dt = $Result[0].Table
 
 # 取得したデータをcsv出力
 $FileName = $scriptPath + "\GetData.csv"
 $dt | Export-Csv -Path $FileName -encoding "UTF8" -notype
 
 # エクセルオブジェクト作成初期化
 $excel = New-Object -ComObject Excel.Application
 $excel.Visible = $false
 $excel.DisplayAlerts = $false
 $book = $excel.Workbooks.Add()
 $xlWBATWorksheet = -4167
 $book = $excel.workbooks.add($xlWBATWorksheet)
 $LineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
 
 $sheet = $book.ActiveSheet
 $sheet.Name = "DBDATA_" + $date.ToString("yyyyMMdd")
 $sheet.Cells.Item(1, 1) = $date.ToString("yyyyMMdd")+"取得 DB情報" #左上セルの文言

 for($i=0;$i -lt $dt.Rows.Count;$i++) 
 {
    for($j=0;$j -lt $dt.Columns.Count;$j++)
    {
        # セルの開始位置をずらす
        $ic = $i + 2
        $jc = $j + 2

        if($i -eq 0)
        {
            $sheet.Cells.Item($ic, $jc) = $dt.Columns[$j].Caption.ToString()
            $sheet.Cells.Item($ic, $jc).Interior.ColorIndex = 37
        }
        else
        {
            $sheet.Cells.Item($ic, $jc) = $dt.Rows[$i][$j].ToString()
        }

        #罫線をひく
        $sheet.cells.item($ic,$jc).borders.LineStyle = $LineStyle::xlContinuous
    }
 }
 
 #列幅を自動調節
 $null = $sheet.Columns.AutoFit()
 $sheet.Columns("A").ColumnWidth = 3 #1列目のみ固定3

 # ファイルを保存
 $ExcelFileName = "DBDATA_" + $date.ToString("yyyyMMdd") + ".xlsx"
 $book.SaveAs("$scriptPath\$ExcelFileName")

 } catch {
     outputLog $error[0]

 } finally {
     $excel.Quit()  # Excel の終了
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) # 変数の破棄
     [System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet) # 変数の破棄
     outputLog "終了" 
 }

エクセルにこんな感じの値が埋め込まれます

DB接続(SQLServer)の方法

PowerShellでSQLServerに接続してDB操作する方法については、「PowerShellでのSQLServerのDB操作方法」の記事にて詳細を説明しています。

サンプルコードでは以下部分が対象の部分です。

# 接続文字列の取得関数
function GetconnectionString(){
  $connectionString = #ここに接続文字列を記載
  return $connectionString
}

# SELECT処理関数
function SelectFromDB($conn){
    $commandText = "SELECT * FROM Table_1"; # ここにクエリを記載

    $conn.open()
    $dt = New-Object System.Data.DataTable
    $da = New-Object System.Data.SqlClient.SqlDataAdapter($commandText, $conn)
    [void]$da.Fill($dt)
    $da.Dispose()
    $conn.Close()

    return [System.Data.DataTable]$dt
}

# 設定ファイルから接続文字列の取得
$connectionString = GetconnectionString
$conn = New-Object System.Data.SqlClient.SqlConnection($connectionString)

# DBからデータを取得
$dt = New-Object System.Data.DataTable
$Result = SelectFromDB $conn
$dt = $Result[0].Table

Excel操作の方法

サンプルコードのExcel操作の部分は以下になります。

# エクセルオブジェクト作成初期化
 $excel = New-Object -ComObject Excel.Application
 $excel.Visible = $false
 $excel.DisplayAlerts = $false
 $book = $excel.Workbooks.Add()
 $xlWBATWorksheet = -4167
 $book = $excel.workbooks.add($xlWBATWorksheet)
 $LineStyle = "microsoft.office.interop.excel.xlLineStyle" -as [type]
 
 $sheet = $book.ActiveSheet
 $sheet.Name = "DBDATA_" + $date.ToString("yyyyMMdd")
 $sheet.Cells.Item(1, 1) = $date.ToString("yyyyMMdd")+"取得 DB情報" #左上セルの文言

 for($i=0;$i -lt $dt.Rows.Count;$i++) 
 {
    for($j=0;$j -lt $dt.Columns.Count;$j++)
    {
        # セルの開始位置をずらす
        $ic = $i + 2
        $jc = $j + 2

        if($i -eq 0)
        {
            $sheet.Cells.Item($ic, $jc) = $dt.Columns[$j].Caption.ToString()
            $sheet.Cells.Item($ic, $jc).Interior.ColorIndex = 37
        }
        else
        {
            $sheet.Cells.Item($ic, $jc) = $dt.Rows[$i][$j].ToString()
        }

        #罫線をひく
        $sheet.cells.item($ic,$jc).borders.LineStyle = $LineStyle::xlContinuous
    }
 }
 
 #列幅を自動調節
 $null = $sheet.Columns.AutoFit()
 $sheet.Columns("A").ColumnWidth = 3 #1列目のみ固定3

 # ファイルを保存
 $ExcelFileName = "DBDATA_" + $date.ToString("yyyyMMdd") + ".xlsx"
 $book.SaveAs("$scriptPath\$ExcelFileName")

RECOMMENDこちらの記事も人気です。