VB.NETレポート印刷のループサンプル

プログラム初心者には、以外に難しいと思われる印刷レポートのループの組み方を例にクラスの継承、オーバーライド等の使いの方を記載します。

レポートの仕様

レポートヘッダー（ReportHeader）

・印刷帳票で最初に１度印刷します。

ページヘッダー（PageHeader）

・ページ毎に最初に印刷します。（見出し等）

詳細部（Detail）

・実際の項目の内容等を印刷します。

ページフッター（PageFooter）

・ページ毎に最後に印刷します。

レポートフッター（ReportFooter）

・印刷帳票で最後に印刷します。

基本となるクラス

Public Class ReportClass

#Region "ReportControlクラス"
    ''' <summary>
    ''' レポートの管理クラス
    ''' </summary>
    Public Class ReportControl
        'ページの最大行数
        Public PageMaxRows As Short

        'レポートヘッダ行数
        Public ReportHeaderRows As Short
        'レポートフッタ行数
        Public ReportFooterRows As Short

        'ページヘッダ行数
        Public PageHeaderRows As Short
        'ページフッタ行数
        Public PageFooterRows As Short
    End Class
#End Region

#Region "列挙体"
    ''' <summary>
    ''' 行を取得する列挙体
    ''' </summary>
    Public Enum DetailsRowType
        Middle = 0
        First
        Final
    End Enum

#End Region

#Region "クラス内のみで使用する変数"

    '１ページの最大の行数
    Private _RowMax As Integer

    '現在の印刷行位置
    Private _RowPoint As Integer

#End Region

    'ページ番号
    Private _PageNo As Integer

    '行番号（最初からの位置）
    Private _LineNo As Integer

    'レポート印刷制御データ
    Private _ReportCtrl As New ReportControl

    ''' <summary>
    ''' コンストラクタ
    ''' </summary>
    Sub New()
        _PageNo = 1
        _LineNo = 1
    End Sub

    ''' <summary>
    ''' ページ番号を取得する（１－）
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property PageNo() As Integer
        Get
            Return _PageNo
        End Get
    End Property

    ''' <summary>
    ''' 先頭行からの行番号を取得する（１－）
    ''' </summary>
    ''' <returns></returns>
    Public ReadOnly Property LineNo() As Integer
        Get
            Return _LineNo
        End Get
    End Property

    ''' <summary>
    ''' レポートの情報等を設定または取得します。
    ''' </summary>
    ''' <returns></returns>
    Public Property ReportCtrl() As ReportControl
        Get
            Return _ReportCtrl
        End Get
        Set(ByVal value As ReportControl)
            _ReportCtrl = value
        End Set
    End Property

    ''' <summary>
    ''' ページ行数を取得する
    ''' </summary>
    ''' <param name="mode">0:通常ページ 1:開始ページ 2:終了ページ</param>
    ''' <returns></returns>
    Protected Function setDetailRows(Optional mode As DetailsRowType = DetailsRowType.Middle)
        If mode = DetailsRowType.First Then        '開始ページ
            _RowMax = ReportCtrl.PageMaxRows - (ReportCtrl.PageHeaderRows + ReportCtrl.PageFooterRows + ReportCtrl.ReportHeaderRows)
            _RowPoint = 0
        ElseIf mode = DetailsRowType.Final Then    '最終ページ
            _RowMax = ReportCtrl.PageMaxRows
            _RowPoint = 0
        Else
            _RowMax = ReportCtrl.PageMaxRows - (ReportCtrl.PageHeaderRows + ReportCtrl.PageFooterRows)
            _RowPoint = 0
        End If

        Return True
    End Function

    ''' <summary>
    ''' 改ページをする 
    ''' </summary>
    Protected Sub Pr_NextPage()
        _PageNo += 1
        Print_NextPage()
        setDetailRows()
    End Sub

    ''' <summary>
    ''' ページヘッダが必要なら出力する
    ''' </summary>
    Protected Sub Pr_NeedPageHeader()
        If _RowPoint = 0 Then
            Pr_PageHeader()
        End If
    End Sub

    ''' <summary>
    ''' 次の行に進めページフッターが必要なら出力し改ページする
    ''' </summary>
    Protected Sub Pr_NextLine()
        _RowPoint += 1
        _LineNo += 1
        If _RowMax <= _RowPoint Then        '改ページ
            Pr_PageFooter()
            Pr_NextPage()
        End If
    End Sub

    ''' <summary>
    ''' レポートヘッダを印刷する
    ''' </summary>
    Protected Sub Pr_ReportHeader()
        If ReportCtrl.ReportHeaderRows <= 0 Then Return
        Print_ReportHeader()
        _LineNo += ReportCtrl.ReportHeaderRows
    End Sub

    ''' <summary>
    ''' レポートフッターを印刷する
    ''' </summary>
    Protected Sub Pr_ReportFooter()
        If ReportCtrl.ReportFooterRows <= 0 Then Return

        Print_ReportFooter()
        _LineNo += ReportCtrl.ReportFooterRows

        Dim n As Integer = _RowMax - _RowPoint
        If n < ReportCtrl.ReportFooterRows Then
            Pr_NextPage()
        End If
    End Sub

    ''' <summary>
    ''' ページヘッダを印刷する
    ''' </summary>
    Protected Sub Pr_PageHeader()
        If ReportCtrl.PageHeaderRows <= 0 Then Return
        Print_PageHeader()
        _LineNo += ReportCtrl.PageHeaderRows
    End Sub

    ''' <summary>
    ''' ページフッターを印刷する
    ''' </summary>
    Protected Sub Pr_PageFooter()
        If ReportCtrl.PageFooterRows <= 0 Then Return
        Print_PageFooter()
        _LineNo += ReportCtrl.PageFooterRows
    End Sub

    ''' <summary>
    ''' １行印刷する
    ''' </summary>
    ''' <param name="data"></param>
    Protected Sub Pr_LinePrint(data As Object)
        Print_LinePrint(data)
    End Sub

    ''' <summary>
    ''' 詳細内容を印刷する
    ''' </summary>
    Protected Sub Pr_Detail(datas As Object)
        Print_Detail(datas)
    End Sub

    Protected Sub Pr_Detail(table As DataTable)
        Print_Detail(table)
    End Sub

    ''' <summary>
    ''' レポート印刷
    ''' </summary>
    Public Sub Pr_Report(datas As Object)
        Print_Report(datas)
    End Sub

#Region "継承側で実際に出力するコードを書く関数"

    Protected Overridable Sub Print_NextPage()
    End Sub
    Protected Overridable Sub Print_ReportHeader()
    End Sub
    Protected Overridable Sub Print_ReportFooter()
    End Sub
    Protected Overridable Sub Print_PageHeader()
    End Sub
    Protected Overridable Sub Print_PageFooter()
    End Sub
    Protected Overridable Sub Print_LinePrint(data As Object)
    End Sub

    Protected Overridable Sub Print_Detail(datas As Object)
        Dim dataList As List(Of Object) = DirectCast(datas, List(Of Object))

        If dataList.Count = 0 Then Return

        For Each o As Object In dataList
            Pr_NeedPageHeader()
            Pr_LinePrint(o)
            Pr_NextLine()
        Next

        If 0 < _RowPoint Then        '改ページ
            Pr_PageFooter()
        End If
    End Sub

    Protected Overridable Sub Print_Detail(table As DataTable)
        If table.Rows.Count = 0 Then Return

        For Each row As DataRowCollection In table.Rows
            Pr_NeedPageHeader()
            Pr_LinePrint(row)
            Pr_NextLine()
        Next

        If 0 < _RowPoint Then        '改ページ
            Pr_PageFooter()
        End If
    End Sub

    Protected Overridable Sub Print_Report(datas As Object)
        Pr_ReportHeader()
        Pr_Detail(datas)
        Pr_ReportFooter()
    End Sub

#End Region

End Class

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

Public Class ReportClass

#Region "ReportControlクラス"

''' <summary>

''' レポートの管理クラス

''' </summary>

Public Class ReportControl

'ページの最大行数

Public PageMaxRows As Short

'レポートヘッダ行数

Public ReportHeaderRows As Short

'レポートフッタ行数

Public ReportFooterRows As Short

'ページヘッダ行数

Public PageHeaderRows As Short

'ページフッタ行数

Public PageFooterRows As Short

End Class

#End Region

#Region "列挙体"

''' <summary>

''' 行を取得する列挙体

''' </summary>

Public Enum DetailsRowType

Middle = 0

First

Final

End Enum

#End Region

#Region "クラス内のみで使用する変数"

'１ページの最大の行数

Private _RowMax As Integer

'現在の印刷行位置

Private _RowPoint As Integer

#End Region

'ページ番号

Private _PageNo As Integer

'行番号（最初からの位置）

Private _LineNo As Integer

'レポート印刷制御データ

Private _ReportCtrl As New ReportControl

''' <summary>

''' コンストラクタ

''' </summary>

Sub New()

_PageNo = 1

_LineNo = 1

End Sub

''' <summary>

''' ページ番号を取得する（１－）

''' </summary>

''' <returns></returns>

Public ReadOnly Property PageNo() As Integer

Get

Return _PageNo

End Get

End Property

''' <summary>

''' 先頭行からの行番号を取得する（１－）

''' </summary>

''' <returns></returns>

Public ReadOnly Property LineNo() As Integer

Get

Return _LineNo

End Get

End Property

''' <summary>

''' レポートの情報等を設定または取得します。

''' </summary>

''' <returns></returns>

Public Property ReportCtrl() As ReportControl

Get

Return _ReportCtrl

End Get

Set(ByVal value As ReportControl)

_ReportCtrl = value

End Set

End Property

''' <summary>

''' ページ行数を取得する

''' </summary>

''' <param name="mode">0:通常ページ 1:開始ページ 2:終了ページ</param>

''' <returns></returns>

Protected Function setDetailRows(Optional mode As DetailsRowType = DetailsRowType.Middle)

If mode = DetailsRowType.First Then '開始ページ

_RowMax = ReportCtrl.PageMaxRows - (ReportCtrl.PageHeaderRows + ReportCtrl.PageFooterRows + ReportCtrl.ReportHeaderRows)

_RowPoint = 0

ElseIf mode = DetailsRowType.Final Then '最終ページ

_RowMax = ReportCtrl.PageMaxRows

_RowPoint = 0

Else

_RowMax = ReportCtrl.PageMaxRows - (ReportCtrl.PageHeaderRows + ReportCtrl.PageFooterRows)

_RowPoint = 0

End If

Return True

End Function

''' <summary>

''' 改ページをする

''' </summary>

Protected Sub Pr_NextPage()

_PageNo += 1

Print_NextPage()

setDetailRows()

End Sub

''' <summary>

''' ページヘッダが必要なら出力する

''' </summary>

Protected Sub Pr_NeedPageHeader()

If _RowPoint = 0 Then

Pr_PageHeader()

End If

End Sub

''' <summary>

''' 次の行に進めページフッターが必要なら出力し改ページする

''' </summary>

Protected Sub Pr_NextLine()

_RowPoint += 1

_LineNo += 1

If _RowMax <= _RowPoint Then '改ページ

Pr_PageFooter()

Pr_NextPage()

End If

End Sub

''' <summary>

''' レポートヘッダを印刷する

''' </summary>

Protected Sub Pr_ReportHeader()

If ReportCtrl.ReportHeaderRows <= 0 Then Return

Print_ReportHeader()

_LineNo += ReportCtrl.ReportHeaderRows

End Sub

''' <summary>

''' レポートフッターを印刷する

''' </summary>

Protected Sub Pr_ReportFooter()

If ReportCtrl.ReportFooterRows <= 0 Then Return

Print_ReportFooter()

_LineNo += ReportCtrl.ReportFooterRows

Dim n As Integer = _RowMax - _RowPoint

If n < ReportCtrl.ReportFooterRows Then

Pr_NextPage()

End If

End Sub

''' <summary>

''' ページヘッダを印刷する

''' </summary>

Protected Sub Pr_PageHeader()

If ReportCtrl.PageHeaderRows <= 0 Then Return

Print_PageHeader()

_LineNo += ReportCtrl.PageHeaderRows

End Sub

''' <summary>

''' ページフッターを印刷する

''' </summary>

Protected Sub Pr_PageFooter()

If ReportCtrl.PageFooterRows <= 0 Then Return

Print_PageFooter()

_LineNo += ReportCtrl.PageFooterRows

End Sub

''' <summary>

''' １行印刷する

''' </summary>

''' <param name="data"></param>

Protected Sub Pr_LinePrint(data As Object)

Print_LinePrint(data)

End Sub

''' <summary>

''' 詳細内容を印刷する

''' </summary>

Protected Sub Pr_Detail(datas As Object)

Print_Detail(datas)

End Sub

Protected Sub Pr_Detail(table As DataTable)

Print_Detail(table)

End Sub

''' <summary>

''' レポート印刷

''' </summary>

Public Sub Pr_Report(datas As Object)

Print_Report(datas)

End Sub

#Region "継承側で実際に出力するコードを書く関数"

Protected Overridable Sub Print_NextPage()

End Sub

Protected Overridable Sub Print_ReportHeader()

End Sub

Protected Overridable Sub Print_ReportFooter()

End Sub

Protected Overridable Sub Print_PageHeader()

End Sub

Protected Overridable Sub Print_PageFooter()

End Sub

Protected Overridable Sub Print_LinePrint(data As Object)

End Sub

Protected Overridable Sub Print_Detail(datas As Object)

Dim dataList As List(Of Object) = DirectCast(datas, List(Of Object))

If dataList.Count = 0 Then Return

For Each o As Object In dataList

Pr_NeedPageHeader()

Pr_LinePrint(o)

Pr_NextLine()

If 0 < _RowPoint Then '改ページ

Pr_PageFooter()

End If

End Sub

Protected Overridable Sub Print_Detail(table As DataTable)

If table.Rows.Count = 0 Then Return

For Each row As DataRowCollection In table.Rows

Pr_NeedPageHeader()

Pr_LinePrint(row)

Pr_NextLine()

If 0 < _RowPoint Then '改ページ

Pr_PageFooter()

End If

End Sub

Protected Overridable Sub Print_Report(datas As Object)

Pr_ReportHeader()

Pr_Detail(datas)

Pr_ReportFooter()

End Sub

#End Region

End Class

継承をしたクラス

Public Class MyReport
    Inherits ReportClass

    Dim _list As ListBox

    Sub New(list As ListBox)
        _list = list

        Dim RCtrl As ReportControl = New ReportControl()

        RCtrl.ReportHeaderRows = 1
        RCtrl.ReportFooterRows = 1
        RCtrl.PageHeaderRows = 1
        RCtrl.PageFooterRows = 1
        RCtrl.PageMaxRows = 10

        ReportCtrl = RCtrl
        setDetailRows(DetailsRowType.First)
    End Sub

    Protected Overrides Sub Finalize()
    End Sub

    Protected Overrides Sub Print_NextPage()
        _list.Items.Add("--------------")
    End Sub

    Protected Overrides Sub Print_ReportHeader()
        _list.Items.Add("ReportHeader")
    End Sub

    Protected Overrides Sub Print_ReportFooter()
        _list.Items.Add("ReportFooter")
    End Sub

    Protected Overrides Sub Print_PageHeader()
        _list.Items.Add("PageHeader")
    End Sub

    Protected Overrides Sub Print_PageFooter()
        _list.Items.Add("PageFooter")
    End Sub

    Protected Overrides Sub Print_LinePrint(data As Object)
        Dim s As String = DirectCast(data, String)
        _list.Items.Add(s)
    End Sub

End Class

Public Class MyReport

Inherits ReportClass

Dim _list As ListBox

Sub New(list As ListBox)

_list = list

Dim RCtrl As ReportControl = New ReportControl()

RCtrl.ReportHeaderRows = 1

RCtrl.ReportFooterRows = 1

RCtrl.PageHeaderRows = 1

RCtrl.PageFooterRows = 1

RCtrl.PageMaxRows = 10

ReportCtrl = RCtrl

setDetailRows(DetailsRowType.First)

End Sub

Protected Overrides Sub Finalize()

End Sub

Protected Overrides Sub Print_NextPage()

_list.Items.Add("--------------")

End Sub

Protected Overrides Sub Print_ReportHeader()

_list.Items.Add("ReportHeader")

End Sub

Protected Overrides Sub Print_ReportFooter()

_list.Items.Add("ReportFooter")

End Sub

Protected Overrides Sub Print_PageHeader()

_list.Items.Add("PageHeader")

End Sub

Protected Overrides Sub Print_PageFooter()

_list.Items.Add("PageFooter")

End Sub

Protected Overrides Sub Print_LinePrint(data As Object)

Dim s As String = DirectCast(data, String)

_list.Items.Add(s)

End Sub

End Class

呼び出し側

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.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

    Dim myrep As New MyReport(ListBox1)
    myrep.Pr_Report(dtList)
End Sub

Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.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)

Dim myrep As New MyReport(ListBox1)

myrep.Pr_Report(dtList)

End Sub

上のサンプルは、リストボックスに表示するようにしています。実際は、継承を行ったクラスで印刷部分を書きます。

継承してEXCELに出力するサンプル

Imports Microsoft.Office.Interop

Public Class MyExcelReport
    Inherits ReportClass
    Implements IDisposable

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim olSheet As Excel.Worksheet

    Dim ReportHeader_Range As Excel.Range
    Dim ReportFooter_Range As Excel.Range
    Dim PageHeader_Range As Excel.Range
    Dim PageFooter_Range As Excel.Range
    Dim Detail_Range As Excel.Range

    Dim wRange As Excel.Range

    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
                ' TODO: 他の状態を解放します (マネージ オブジェクト)。
            End If

            ComObjectFree(wRange, True)

            ComObjectFree(Detail_Range, True)
            ComObjectFree(ReportHeader_Range, True)
            ComObjectFree(ReportFooter_Range, True)
            ComObjectFree(PageHeader_Range, True)
            ComObjectFree(PageFooter_Range, True)

            ComObjectFree(olSheet, True)
            ComObjectFree(xlSheet, True)
            ComObjectFree(xlBook, True)
            ComObjectFree(xlApp, True)
        End If
        Me.disposedValue = True
    End Sub

#Region "IDisposable Support"

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

#End Region

#End Region

    Protected Overrides Sub Finalize()
        MyBase.Finalize()

        ' Disposeが呼ばれてなかったら呼び出す
        If disposedValue = False Then
            Me.Dispose()
        End If

    End Sub

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

        xlApp = New Excel.Application()
        xlApp.Workbooks.Open(filtpath)

        Dim FileName As String = System.IO.Path.GetFileName(filtpath)

        xlBook = xlApp.Workbooks(FileName)
        xlSheet = xlBook.Worksheets("SheetOrg")
        olSheet = xlBook.Worksheets("Sheet2")

        Dim RCtrl As ReportControl = New ReportControl()

        ReportHeader_Range = xlSheet.Range("ReportHeader")
        RCtrl.ReportHeaderRows = ReportHeader_Range.Rows.Count

        ReportFooter_Range = xlSheet.Range("ReportFooter")
        RCtrl.ReportFooterRows = ReportFooter_Range.Rows.Count

        PageHeader_Range = xlSheet.Range("PageHeader")
        RCtrl.PageHeaderRows = PageHeader_Range.Rows.Count

        PageFooter_Range = xlSheet.Range("PageFooter")
        RCtrl.PageFooterRows = PageFooter_Range.Rows.Count

        Detail_Range = xlSheet.Range("Detail")

        Dim xRange As Excel.Range = xlSheet.Range("PageRows")
        RCtrl.PageMaxRows = xRange.Rows.Count
        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xRange)

        ReportCtrl = RCtrl
        setDetailRows(DetailsRowType.First)
    End Sub

    ''' <summary>
    ''' COMの解放
    ''' </summary>
    ''' <typeparam name="T"></typeparam>
    ''' <param name="objCom"></param>
    ''' <param name="force"></param>
    Public Shared Sub ComObjectFree(Of T As Class)(ByRef objCom As T, Optional ByVal force As Boolean = False)
        If objCom Is Nothing Then
            Return
        End If

        If Not System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then
            Return
        End If

        Try
            If force Then
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom)
                objCom = Nothing
            Else
                System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)
            End If
        Finally
        End Try
    End Sub

    Protected Overrides Sub Print_ReportHeader()
        wRange = olSheet.Cells(LineNo, ReportHeader_Range.Column)
        '  SyncLock wRange
        ReportHeader_Range.Copy(wRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)
        ' End SyncLock
    End Sub

    Protected Overrides Sub Print_ReportFooter()
        wRange = olSheet.Cells(LineNo, ReportFooter_Range.Column)
        ReportFooter_Range.Copy(wRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)
    End Sub

    Protected Overrides Sub Print_PageHeader()
        wRange = olSheet.Cells(LineNo, PageHeader_Range.Column)
        PageHeader_Range.Copy(wRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)
    End Sub

    Protected Overrides Sub Print_PageFooter()
        wRange = olSheet.Cells(LineNo, PageFooter_Range.Column)
        PageFooter_Range.Copy(wRange)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)
    End Sub

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

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

        wRange = olSheet.Cells(LineNo, Detail_Range.Column)
        Detail_Range.Copy(wRange)

        System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wRange)
    End Sub

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

        xlApp.Visible = True
        System.Threading.Thread.Sleep(10000)
        xlApp.Quit()

        Clipboard.Clear()
    End Sub

End Class

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

Imports Microsoft.Office.Interop

Public Class MyExcelReport

Inherits ReportClass

Implements IDisposable

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim olSheet As Excel.Worksheet

Dim ReportHeader_Range As Excel.Range

Dim ReportFooter_Range As Excel.Range

Dim PageHeader_Range As Excel.Range

Dim PageFooter_Range As Excel.Range

Dim Detail_Range As Excel.Range

Dim wRange As Excel.Range

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

' TODO: 他の状態を解放します (マネージオブジェクト)。

End If

ComObjectFree(wRange, True)

ComObjectFree(Detail_Range, True)

ComObjectFree(ReportHeader_Range, True)

ComObjectFree(ReportFooter_Range, True)

ComObjectFree(PageHeader_Range, True)

ComObjectFree(PageFooter_Range, True)

ComObjectFree(olSheet, True)

ComObjectFree(xlSheet, True)

ComObjectFree(xlBook, True)

ComObjectFree(xlApp, True)

End If

Me.disposedValue = True

End Sub

#Region "IDisposable Support"

Public Sub Dispose() Implements IDisposable.Dispose

Dispose(True)

GC.SuppressFinalize(Me)

End Sub

#End Region

Protected Overrides Sub Finalize()

MyBase.Finalize()

' Disposeが呼ばれてなかったら呼び出す

If disposedValue = False Then

Me.Dispose()

End If

End Sub

''' <summary>

''' コンストラクタ

''' </summary>

''' <param name="filtpath"></param>

Sub New(filtpath As String)

MyBase.New()

xlApp = New Excel.Application()

xlApp.Workbooks.Open(filtpath)

Dim FileName As String = System.IO.Path.GetFileName(filtpath)

xlBook = xlApp.Workbooks(FileName)

xlSheet = xlBook.Worksheets("SheetOrg")

olSheet = xlBook.Worksheets("Sheet2")

Dim RCtrl As ReportControl = New ReportControl()

ReportHeader_Range = xlSheet.Range("ReportHeader")

RCtrl.ReportHeaderRows = ReportHeader_Range.Rows.Count

ReportFooter_Range = xlSheet.Range("ReportFooter")

RCtrl.ReportFooterRows = ReportFooter_Range.Rows.Count

PageHeader_Range = xlSheet.Range("PageHeader")

RCtrl.PageHeaderRows = PageHeader_Range.Rows.Count

PageFooter_Range = xlSheet.Range("PageFooter")

RCtrl.PageFooterRows = PageFooter_Range.Rows.Count

Detail_Range = xlSheet.Range("Detail")

Dim xRange As Excel.Range = xlSheet.Range("PageRows")

RCtrl.PageMaxRows = xRange.Rows.Count

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xRange)

ReportCtrl = RCtrl

setDetailRows(DetailsRowType.First)

End Sub

''' <summary>

''' COMの解放

''' </summary>

''' <typeparam name="T"></typeparam>

''' <param name="objCom"></param>

''' <param name="force"></param>

Public Shared Sub ComObjectFree(Of T As Class)(ByRef objCom As T, Optional ByVal force As Boolean = False)

If objCom Is Nothing Then

Return

End If

If Not System.Runtime.InteropServices.Marshal.IsComObject(objCom) Then

Return

End If

Try

If force Then

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objCom)

objCom = Nothing

Else

System.Runtime.InteropServices.Marshal.ReleaseComObject(objCom)

End If

Finally

End Try

End Sub

Protected Overrides Sub Print_ReportHeader()

wRange = olSheet.Cells(LineNo, ReportHeader_Range.Column)

' SyncLock wRange

ReportHeader_Range.Copy(wRange)

System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)

' End SyncLock

End Sub

Protected Overrides Sub Print_ReportFooter()

wRange = olSheet.Cells(LineNo, ReportFooter_Range.Column)

ReportFooter_Range.Copy(wRange)

System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)

End Sub

Protected Overrides Sub Print_PageHeader()

wRange = olSheet.Cells(LineNo, PageHeader_Range.Column)

PageHeader_Range.Copy(wRange)

System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)

End Sub

Protected Overrides Sub Print_PageFooter()

wRange = olSheet.Cells(LineNo, PageFooter_Range.Column)

PageFooter_Range.Copy(wRange)

System.Runtime.InteropServices.Marshal.ReleaseComObject(wRange)

End Sub

Protected Overrides Sub Print_LinePrint(data As Object)

Dim s As String = DirectCast(data, String)

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

wRange = olSheet.Cells(LineNo, Detail_Range.Column)

Detail_Range.Copy(wRange)

System.Runtime.InteropServices.Marshal.FinalReleaseComObject(wRange)

End Sub

Protected Overrides Sub Print_Report(datas As Object)

MyBase.Print_Report(datas)

xlApp.Visible = True

System.Threading.Thread.Sleep(10000)

xlApp.Quit()

Clipboard.Clear()

End Sub

End Class

呼び出し側

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.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 MyExcelReport("C:\work\ExcelTemplate.xls")
        myrep.Pr_Report(dtList)
    End Using
End Sub

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.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)

Using myrep As New MyExcelReport("C:\work\ExcelTemplate.xls")

myrep.Pr_Report(dtList)

End Using

End Sub

上記のサンプルはEXCELファイルをテンプレートとして読み込みテンプレートに従って、印刷レポートを作成します。

シートSheetOrgがテンプレートシートで出力はSheet2にレポート印刷するようになっています。

exceltemplate

またSheetOrgは、①ReportHeader　②PageHeader　③Detail　④PageFooter　⑤ReportFooter　⑥PageRows　とゆうようにセルに名前が付けられており③にお置いては、item1,item2,item3とセル毎に名前がつけられています。