PowerShellでExcelを操作してみよう_サンプルコード

task007.ps1

# Excel COMオブジェクト作成
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $true

# 新しいブック作成
$workbook = $excel.Workbooks.Add()
$excel.ActiveWindow.Zoom = 145

# シート取得
$sheet = $workbook.Sheets.Item(1)
$sheet.Name = "請求書"
$sheet.Columns.ColumnWidth = 5
$sheet.Rows.RowHeight = 15
$sheet.Cells.Font.Name = "Meiryo UI"
$sheet.Cells.Borders.ColorIndex = 2

$range1 = $sheet.Range("H2:K2")
$range1.MergeCells = $true
$range1.HorizontalAlignment = -4152 # xlHAlignLeft(-4131)/xlHAlignCenter(-4108)/xlHAlignRight(-4152)
# $range1.Value = "令和7年4月1日"
$range1.Value = Get-Date
$range1.NumberFormat = "ggge年m月d日"

$range2 = $sheet.Range("B3:K4")
$range2.MergeCells = $true
$range2.HorizontalAlignment = -4108 # xlHAlignLeft(-4131)/xlHAlignCenter(-4108)/xlHAlignRight(-4152)
$range2.Value = "請　求　書"
$range2.Font.Size = 18

$row1 = $sheet.Rows(5)
$row1.RowHeight = 8

$range3 = $sheet.Range("B6:G6")
$range3.MergeCells = $true
$range3.RowHeight = 18.5
# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xllinestyle
# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xlbordersindex
$range3.borders.Item(9).LineStyle = 1
$range3.Borders.Item(9).ColorIndex = 1

$range4 = $sheet.Range("h6")
# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xllinestyle
# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xlbordersindex
$range4.borders.Item(9).LineStyle = 1
$range4.Borders.Item(9).ColorIndex = 1
$range4.Value = "御中"
$range4.HorizontalAlignment = -4108 # xlHAlignLeft(-4131)/xlHAlignCenter(-4108)/xlHAlignRight(-4152)

$row2 = $sheet.Rows(7)
$row2.RowHeight = 6.5

$range5 = $sheet.Range("B8")
$prevMonthStr = (Get-Date).AddMonths(-1).ToString("yyyy年M月分")
$range5.Value2 = "件名：" + $prevMonthStr + "について"
$range5.Font.Size = 9
$range5.RowHeight = 13

$range6 = $sheet.Range("B9")
$range6.Value2 = "下記のとおり、ご請求申し上げます。"
$range6.Font.Size = 9
$range6.RowHeight = 13.5

$row2 = $sheet.Rows(10)
$row2.RowHeight = 5

$range7 = $sheet.Range("B11")
$range7.Value2 = "ご請求金額"
$range7.Font.Size = 10
$range7.RowHeight = 19.5

$range8 = $sheet.Range("B11:F11")
$range8.borders.Item(9).LineStyle = 1
$range8.Borders.Item(9).ColorIndex = 1

$row3 = $sheet.Rows(12)
$row3.RowHeight = 5

$range9 = $sheet.Range("B13")
$range9.Value2 = "お支払い期限"
$range9.Font.Size = 9
$range9.RowHeight = 15

$range10 = $sheet.Range("B13:F13")
$range10.borders.Item(9).LineStyle = 1
$range10.Borders.Item(9).ColorIndex = 1

$range11 = $sheet.Range("K13")
$range11.Value2 = "(株)Excel-Fun.xls*  "
$range11.HorizontalAlignment = -4152 # xlHAlignLeft(-4131)/xlHAlignCenter(-4108)/xlHAlignRight(-4152)
$range11.Font.Size = 10

$shape1 = $sheet.Shapes.AddShape(5,340,140,31,32) # msoShapeRoundedRectangle(5)	角丸四角形/
$shape1.Fill.Visible = $false
$shape1.Line.ForeColor.RGB = 255
$shape1.Line.Weight = 0.75
$shape1.TextFrame.HorizontalAlignment = -4108  # xlCenter
$shape1.TextFrame.VerticalAlignment = -4108    # xlCenter
$shape1.TextFrame.MarginBottom = 0 
$shape1.TextFrame.MarginLeft = 0 
$shape1.TextFrame.MarginRight = 0 
$shape1.TextFrame.MarginTop = 0 

$shape1.TextFrame.Characters().Text = "Excel`n-Fun`n.xls*"
$shape1.TextFrame.Characters().Font.Name = "HG行書体"
$shape1.TextFrame.Characters().Font.Size = 7
$shape1.TextFrame.Characters().Font.ColorIndex = 3
$shape1.TextFrame.Characters().Font.Bold = $true

# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xllinestyle
# https://learn.microsoft.com/ja-jp/office/vba/api/excel.xlbordersindex
$range12 = $sheet.Range("B15:K15")
$range12.borders.LineStyle = 1
$range12.Borders.ColorIndex = 1
$range12.borders.Item(11).LineStyle = -4115

$range13 = $sheet.Range("B15")
$range14 = $sheet.Range("C15:F15")
$range15 = $sheet.Range("G15:H15")
$range16 = $sheet.Range("I15:K15")

$range13.Merge()
$range14.Merge()
$range15.Merge()
$range16.Merge()

$range13.HorizontalAlignment = -4108  # xlCenter
$range14.HorizontalAlignment = -4108  # xlCenter
$range15.HorizontalAlignment = -4108  # xlCenter
$range16.HorizontalAlignment = -4108  # xlCenter

$range13.Value2() = "No"
$range14.Value2() = "種別"
$range15.Value2() = "件数"
$range16.Value2() = "金額"

for ($rowCnt = 1; $rowCnt -le 5; $rowCnt++) {

    $range13 = $range13.Offset(1,0)
    $range14 = $range14.Offset(1,0).Resize(1,$range14.Columns.Count)
    $range15 = $range15.Offset(1,0).Resize(1,$range15.Columns.Count)
    $range16 = $range16.Offset(1,0).Resize(1,$range16.Columns.Count)
    
    # セルを結合
    $range13.Merge()
    $range14.Merge()
    $range15.Merge()
    $range16.Merge()

    $range13.RowHeight = 18
}

$range17 = $sheet.Range("B16:K20")
$range17.borders.LineStyle = 1
$range17.Borders.ColorIndex = 1
$range17.borders.Item(11).LineStyle = -4115
$range17.borders.Item(12).LineStyle = -4115

$valsFormulas = @(
    @("合計(税別)", "=SUM(I16:I20)"),
    @("消費税", "=INT(I21*0.08)"),
    @("合計(税込)", "=I21+I22")
);

$range18 = $sheet.Range("G21:H21")
$range19 = $sheet.Range("I21:K21")

for ($rowCnt = 0; $rowCnt -le 2; $rowCnt++) {
    # セルを結合
    $range18.Merge()
    $range19.Merge()
    $range18.HorizontalAlignment = -4108  # xlCenter
    $range19.HorizontalAlignment = -4152  # xlRight	
    $range18.Value2 = $valsFormulas[$rowCnt][0]
    $range19.Formula = $valsFormulas[$rowCnt][1]
    $range19.NumberFormat = "\#,##0_);[赤](\#,##0)"
    $range18.Font.Size = 10
    $range19.Font.Size = 11

    $range18.RowHeight = 18

    $range18 = $range18.Offset(1,0).Resize(1,$range18.Columns.Count)
    $range19 = $range19.Offset(1,0).Resize(1,$range19.Columns.Count)
}

$range20 = $sheet.Range("G21:K23")
$range20.borders.LineStyle = 1
$range20.Borders.ColorIndex = 1
$range20.borders.Item(11).LineStyle = -4115
$range20.borders.Item(12).LineStyle = -4115

$row4 = $sheet.Rows(24)
$row4.RowHeight = 9

$range21 = $sheet.Range("B25:C25")
$range21.Merge()
$range21.HorizontalAlignment = -4108  # xlCenter
$range21.borders.LineStyle = 1
$range21.Borders.ColorIndex = 1
$range21.Value = "摘要"
$range21.Font.Size = 10

$range22 = $sheet.Range("B26:K28")
$range22.Merge()
$range22.borders.LineStyle = 1
$range22.Borders.ColorIndex = 1
$range22.HorizontalAlignment = -4131  # xlLeft	
$range22.VerticalAlignment = -4160 # xlTop	
$range22.Font.Size = 10