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
