PowerShell Excel セルの背景を変更

PowerShellでセルの背景を変更するサンプルです。

# エクセルファイル作成
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $False

# ワークブック作成
$book = $excel.Workbooks.Add()
$sheet = $excel.Worksheets.Item(1)

# セルの背景色変更
for ( $count = 1; $count -lt 15; $count++ )
{
    $sheet.Cells.Item($count, 1) = $count
    $sheet.Cells.Item($count, 2).Interior.ColorIndex = $count
}

for ( $count = 15; $count -lt 29; $count++ )
{
    $sheet.Cells.Item($count - 14, 3) = $count
    $sheet.Cells.Item($count - 14, 4).Interior.ColorIndex = $count
}

for ( $count = 29; $count -lt 43; $count++ )
{
    $sheet.Cells.Item($count - 28, 5) = $count
    $sheet.Cells.Item($count - 28, 6).Interior.ColorIndex = $count
}

for ( $count = 43; $count -lt 57; $count++ )
{
    $sheet.Cells.Item($count - 42, 7) = $count
    $sheet.Cells.Item($count - 42, 8).Interior.ColorIndex = $count
}

# 列を自動幅調整
$null = $sheet.Columns.AutoFit()

# 日時に応じて名前をつけて保存
# C直下にpowershellフォルダを手動で作成して下さい。
$date = Get-Date
$book.SaveAs("c:\powershell\" + $date.ToString("yyyyMMdd_HHmmss_") + "test.xlsx")
$excel.Quit()
$excel = $null
[GC]::Collect()

実行時のイメージ

1

 

 

 

 

 

 

 

 


Bookmark this on Yahoo Bookmark
Bookmark this on Google Bookmarks
Share on LinkedIn
LINEで送る
Pocket