PowerShell로 엑셀파일 생성하기

엑셀을 생성하는 스크립트가 필요할 것 같아 정리하다가 아래의 사이트를 참고하여 스크립트를 작성하였다.

$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$objWorkBook = $objExcel.WorkBooks.Add()
$objWorkBook.WorkSheets.Item(3).Delete()
$objWorkBook.WorkSheets.Item(2).Delete()
$objWorkBook.WorkSheets.Item(1).Name = "XLS1"

$typLineStyle    = "microsoft.office.interop.excel.xlLineStyle"    -As [type]
$typColorIndex   = "microsoft.office.interop.excel.xlColorIndex"   -As [type]
$typBorderWeight = "microsoft.office.interop.excel.xlBorderWeight" -As [type]
$typCharType     = "microsoft.office.interop.excel.xlChartType"    -As [type]

$objSheet = $objWorkBook.WorkSheets.Item("XLS1")

For($i = 1; $i -le 2; $i++)
{
    $objSheet.Cells.Item(1, $i).Font.Bold = $true
    $objSheet.Cells.Item(1, $i).Borders.LineStyle  = $typLineStyle::xlDashDot
    $objSheet.Cells.Item(1, $i).Borders.ColorIndex = $typColorIndex::xlColorIndexAutomatic
    $objSheet.Cells.Item(1, $i).Borders.Weight     = $typBorderWeight::xlMedium
}

$objSheet.Cells.Item(1, 1) = "Data1"
$objSheet.Cells.Item(1, 2) = "Data2"
$objSheet.Cells.Item(2, 1) = "Magic"
$objSheet.Cells.Item(2, 2) = "Wonderful"

$objRange = $objSheet.UsedRange
$objRange.EntireColumn.AutoFit() | Out-Null

$strXLSFile = "D:\TestBed\PowerShell\test.xlsx"

If(Test-Path $strXLSFile)
{
    Remove-Item $strXLSFile
    $objExcel.ActiveWorkbook.SaveAs($strXLSFile)
}
else
{
    $objExcel.ActiveWorkbook.SaveAs($strXLSFile)
}

$objWorkBook.Close()
$objExcel.Quit()

ExcelHandle.ps1

참고)
http://blog.pages.kr/584
http://social.technet.microsoft.com/Forums/en-US/winserverpowershell/thread/0318449a-ac57-4678-989f-44c4d4bb4228