ClosedXMLレポート印刷サンプル

ReportClassを継承してClosedXMLでEXCELファイルを作成するサンプルです。

Public Class ClosedXMLReport
    Inherits ReportClass
    Implements IDisposable

    Dim xlBook As XLWorkbook = Nothing
    Dim xlSheet As IXLWorksheet = Nothing
    Dim olSheet As IXLWorksheet = Nothing

    Dim ReportHeader_Range As IXLRange = Nothing
    Dim ReportFooter_Range As IXLRange = Nothing
    Dim PageHeader_Range As IXLRange = Nothing
    Dim PageFooter_Range As IXLRange = Nothing
    Dim Detail_Range As IXLRange = Nothing

    Dim _DataTable As DataTable

#Region "Dispose"

    Private disposedValue As Boolean = False        '重複する呼び出しの制御

    Protected Overridable Sub Dispose(ByVal disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If Not xlBook Is Nothing Then xlBook.Dispose()
                If Not xlSheet Is Nothing Then xlSheet.Dispose()
                If Not olSheet Is Nothing Then olSheet.Dispose()
                If Not ReportHeader_Range Is Nothing Then ReportHeader_Range.Dispose()
                If Not ReportFooter_Range Is Nothing Then ReportFooter_Range.Dispose()
                If Not PageHeader_Range Is Nothing Then PageHeader_Range.Dispose()
                If Not PageFooter_Range Is Nothing Then PageFooter_Range.Dispose()
                If Not Detail_Range Is Nothing Then Detail_Range.Dispose()

                xlBook = Nothing
                xlSheet = Nothing
                olSheet = Nothing
                ReportHeader_Range = Nothing
                ReportFooter_Range = Nothing
                PageHeader_Range = Nothing
                PageFooter_Range = Nothing
                Detail_Range = Nothing
            End If
        End If

        Me.disposedValue = True
    End Sub

#End Region

#Region "IDisposable Support"

    Public Sub Dispose() Implements IDisposable.Dispose
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub

#End Region

    ''' <summary>
    ''' デストラクタ
    ''' </summary>
    Protected Overrides Sub Finalize()
        MyBase.Finalize()

        Me.Dispose(False)
    End Sub

    ''' <summary>
    ''' コンストラクタ
    ''' </summary>
    ''' <param name="filtpath"></param>
    Sub New(filtpath As String)
        MyBase.New()

        xlBook = New XLWorkbook(filtpath)

        xlSheet = Nothing
        xlBook.TryGetWorksheet("SheetOrg", xlSheet)

        olSheet = Nothing
        xlBook.TryGetWorksheet("Sheet2", olSheet)

        Dim RCtrl As ReportControl = New ReportControl()

        ReportHeader_Range = xlSheet.Range("ReportHeader")
        RCtrl.ReportHeaderRows = ReportHeader_Range.RowCount

        ReportFooter_Range = xlSheet.Range("ReportFooter")
        RCtrl.ReportFooterRows = ReportFooter_Range.RowCount

        PageHeader_Range = xlSheet.Range("PageHeader")
        RCtrl.PageHeaderRows = PageHeader_Range.RowCount

        PageFooter_Range = xlSheet.Range("PageFooter")
        RCtrl.PageFooterRows = PageFooter_Range.RowCount

        Detail_Range = xlSheet.Range("Detail")

        Dim xRange As IXLRange = xlSheet.Range("PageRows")
        RCtrl.PageMaxRows = xRange.RowCount

        ReportCtrl = RCtrl
        setDetailRows(DetailsRowType.First)
    End Sub

    Protected Overrides Sub Print_ReportHeader()
        Dim x As Integer = ReportHeader_Range.Cell(1, 1).Address.ColumnNumber
        Dim cell As IXLCell = olSheet.Cell(LineNo, x)
        ReportHeader_Range.CopyTo(cell)
    End Sub

    Protected Overrides Sub Print_ReportFooter()
        Dim x As Integer = ReportFooter_Range.Cell(1, 1).Address.ColumnNumber
        Dim cell As IXLCell = olSheet.Cell(LineNo, x)
        ReportFooter_Range.CopyTo(cell)
    End Sub

    Protected Overrides Sub Print_PageHeader()
        Dim x As Integer = PageHeader_Range.Cell(1, 1).Address.ColumnNumber
        Dim cell As IXLCell = olSheet.Cell(LineNo, x)
        PageHeader_Range.CopyTo(cell)
    End Sub

    Protected Overrides Sub Print_PageFooter()
        Dim x As Integer = PageFooter_Range.Cell(1, 1).Address.ColumnNumber
        Dim cell As IXLCell = olSheet.Cell(LineNo, x)
        PageFooter_Range.CopyTo(cell)
    End Sub

    Protected Overrides Sub Print_LinePrint(data As Object)
        Dim s As String = DirectCast(data, String)

        xlSheet.Cell("item1").Value = s

        Dim x As Integer = Detail_Range.Cell(1, 1).Address.ColumnNumber
        Dim cell As IXLCell = olSheet.Cell(LineNo, x)

        Detail_Range.CopyTo(cell)
    End Sub

    Protected Overrides Sub Print_Report(datas As Object)
        MyBase.Print_Report(datas)

        xlBook.SaveAs("c:\work\test.xlsx")
    End Sub

End Class

呼び出し側

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim dtList As List(Of Object) = New List(Of Object)

    For i As Integer = 0 To 100
        Dim s As String = ""
        s = i.ToString()
        dtList.Add(s)
    Next

    Using myrep As New ClosedXMLReport("C:\work\ExcelTemplate.xlsx")
        myrep.Pr_Report(dtList)
    End Using
End Sub