自己写了一个对Excel操作的类, 封装了Excel的通常的操作,以简化生成 Excel 报表的代码量。
代码基于 .net 2.0, Excel 2003
该 Assembly 共有两个类:
1. XlsReport.vb
Imports
System
Imports
Excel
=
Microsoft.Office.Interop.Excel
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlCopyAction
Enum XlCopyAction
Paste
Insert
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlBorders
Enum XlBorders
xlDiagonalDown = Excel.XlBordersIndex.xlDiagonalDown
xlDiagonalUp = Excel.XlBordersIndex.xlDiagonalUp
xlEdgeLeft = Excel.XlBordersIndex.xlEdgeLeft
xlEdgeTop = Excel.XlBordersIndex.xlEdgeTop
xlEdgeBottom = Excel.XlBordersIndex.xlEdgeBottom
xlEdgeRigth = Excel.XlBordersIndex.xlEdgeRight
xlInsideHorizontal = Excel.XlBordersIndex.xlInsideHorizontal
xlInsideVertical = Excel.XlBordersIndex.xlInsideVertical
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlLineStyle
Enum XlLineStyle
xlContinuous = Excel.XlLineStyle.xlContinuous
xlDash = Excel.XlLineStyle.xlDash
xlDashDot = Excel.XlLineStyle.xlDashDot
xlDashDotDot = Excel.XlLineStyle.xlDashDotDot
xlDot = Excel.XlLineStyle.xlDot
xlDouble = Excel.XlLineStyle.xlDouble
xlLineStyleNone = Excel.XlLineStyle.xlLineStyleNone
xlSlantDashDot = Excel.XlLineStyle.xlSlantDashDot
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlBorderWight
Enum XlBorderWight
xlHairline = Excel.XlBorderWeight.xlHairline
xlMedium = Excel.XlBorderWeight.xlMedium
xlThick = Excel.XlBorderWeight.xlThick
xlThin = Excel.XlBorderWeight.xlThin
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlFontStyle
Enum XlFontStyle
xlStrikethrough
xlSuperscript
xlSubscript
xlOutlineFont
xlShadow
xlBold
xlItalic
xlUnderlineDouble
xlUnderlineSingle
xlNone
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlCellFormat
Enum XlCellFormat
xlWrapTest
xlShrinkToFit
xlNone
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlHAlign
Enum XlHAlign
xlCenter = Excel.XlHAlign.xlHAlignCenter
xlCenterAcrossSelection = Excel.XlHAlign.xlHAlignCenterAcrossSelection
xlDistributed = Excel.XlHAlign.xlHAlignDistributed
xlFill = Excel.XlHAlign.xlHAlignFill
xlGeneral = Excel.XlHAlign.xlHAlignGeneral
xlJustify = Excel.XlHAlign.xlHAlignJustify
xlLeft = Excel.XlHAlign.xlHAlignLeft
xlRight = Excel.XlHAlign.xlHAlignRight
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlVAlign
Enum XlVAlign
xlBottom = Excel.XlVAlign.xlVAlignBottom
xlCenter = Excel.XlVAlign.xlVAlignCenter
xlDistributed = Excel.XlVAlign.xlVAlignDistributed
xlJustify = Excel.XlVAlign.xlVAlignJustify
xlTop = Excel.XlVAlign.xlVAlignTop
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Enum XlFillPattern
Enum XlFillPattern
xlNone = Excel.XlPattern.xlPatternNone
xlSolid = Excel.XlPattern.xlPatternSolid
xlAuto = Excel.XlPattern.xlPatternAutomatic
xlChecker = Excel.XlPattern.xlPatternChecker
xlCrissCross = Excel.XlPattern.xlPatternCrissCross
xlDown = Excel.XlPattern.xlPatternDown
xlUp = Excel.XlPattern.xlPatternUp
xlHorizontal = Excel.XlPattern.xlPatternHorizontal
xlVertical = Excel.XlPattern.xlPatternVertical
xlGrid = Excel.XlPattern.xlPatternGrid
xlGray8 = Excel.XlPattern.xlPatternGray8
xlGray16 = Excel.XlPattern.xlPatternGray16
xlGray25 = Excel.XlPattern.xlPatternGray25
xlGray50 = Excel.XlPattern.xlPatternGray50
xlGray75 = Excel.XlPattern.xlPatternGray75
xlLightDown = Excel.XlPattern.xlPatternLightDown
xlLightHorizontal = Excel.XlPattern.xlPatternLightHorizontal
xlLightUp = Excel.XlPattern.xlPatternLightUp
xlLightVertical = Excel.XlPattern.xlPatternLightVertical
xlSemiGray75 = Excel.XlPattern.xlPatternSemiGray75
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Class XlsReport
Class XlsReport
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
プロパティ#Region "プロパティ"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Private mxlsApp As Excel.ApplicationClass ' Excelオブジェクト
Private mxbkBook As Excel._Workbook ' 表テンプレートとするExcelワークブック
Private mxstSheet As Excel._Worksheet ' 表テンプレートとするExcelワークシート
Private mxstTmplSheet As Excel._Worksheet ' テンプレートワークシート
Private mstrKeyWord As String = "**" ' 変数名の先頭キーワード文字列
Private mhtKeyCellContainer As Hashtable ' セルのHash
Private mlstTmplSheetName As List(Of String) ' テンプレートワークシート名のリスト
Private mblnIsWorkbookClosed As Boolean
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROPERTY]
' * 変数名の先頭キーワード文字列を設定する。 IN
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public WriteOnly Property KeyWord()Property KeyWord() As String
Set(ByVal value As String)
mstrKeyWord = value
End Set
End Property
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROPERTY]
' * Excelワークシートを設定する IO
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Property ActiveSheet()Property ActiveSheet() As String
Get
Return mxstSheet.Name
End Get
Set(ByVal Value As String)
Try
mxstSheet = CType(mxbkBook.Worksheets(Value), Excel.Worksheet)
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errSheet)
End Try
End Set
End Property
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
Reportの操作#Region "Reportの操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * clsWorkSheetのインスタンスを初期化する
' *
' * [ARGUMENT]
' * strFilePath IN 要求経路
' * strPasswd IN 要求パスワード
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub New()Sub New(ByVal strFilePath As String, Optional ByVal strPasswd As String = Nothing)
Try
Me.mxlsApp = New Excel.ApplicationClass
Me.mxlsApp.Visible = False
Me.mxbkBook = Me.mxlsApp.Workbooks.Open(strFilePath, Password:=strPasswd)
Me.mxstSheet = mxbkBook.Worksheets(1)
Me.mxstTmplSheet = mxstSheet
Me.mhtKeyCellContainer = New Hashtable()
Me.mlstTmplSheetName = New List(Of String)
Me.mblnIsWorkbookClosed = False
Catch ex As Exception
Me.Dispose()
Throw New XlsReportException(ex, XlErrorNo.errOpen)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * ワークシートを添加する
' *
' * [ARGUMENT]
' * strTemplateSheetName IN 要求Excelテンプレートワークシート名
' * strNewSheetName IN 添加のワークシートの名
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SheetAdd()Sub SheetAdd(ByVal strTemplateSheetName As String, Optional ByVal strNewSheetName As String = "")
Try
Me.mxstTmplSheet = Me.mxbkBook.Worksheets(strTemplateSheetName)
If mlstTmplSheetName.IndexOf(strTemplateSheetName) < 0 Then
mlstTmplSheetName.Add(strTemplateSheetName)
End If
Me.mxstTmplSheet.Copy(After:=Me.mxstSheet)
mxstSheet = mxbkBook.ActiveSheet
If strNewSheetName <> "" Then
mxstSheet.Name = strNewSheetName
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAddSheet)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * Excelシート保存
' *
' * [ARGUMENT]
' * strXlsPath IN 要求保存経路
' * strPasswd IN 要求パスワード
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SaveAs()Sub SaveAs(ByVal strXlsPath As String, Optional ByVal strPasswd As String = Nothing)
Try
' 保存するファイル形式が「.xls」でなければエラーとする
If StrConv(Microsoft.VisualBasic.Right(strXlsPath, 4), VbStrConv.Lowercase) <> ".xls" Then
Return
End If
' 保存時の確認メッセージを表示しないように変更
mxlsApp.DisplayAlerts = False
' テンプレートワークシートを削除する
For Each strSheetName As String In mlstTmplSheetName
CType(Me.mxbkBook.Worksheets(strSheetName), Excel._Worksheet).Delete()
Next
mxbkBook.Password = strPasswd
' ワークシートをExcel形式で保存する
mxbkBook.SaveAs(Filename:=strXlsPath, FileFormat:=Excel.XlFileFormat.xlWorkbookNormal, _
ReadOnlyRecommended:=False, CreateBackup:=False)
mxbkBook.Close()
mblnIsWorkbookClosed = True
' 保存時の確認メッセージを表示するように変更
mxlsApp.DisplayAlerts = True
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errSave)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
'' *====================================================================================================
'' *
'' * [PROCEDURE]
'' * ワークブックを閉じる。 True -- 保存, False -- 保存ない
'' *
'' * [ARGUMENT]
'' * blnSave IN 要求True、False
'' *
'' *====================================================================================================
'Public Sub Close(Optional ByVal blnSave As Boolean = True)
' Try
' ' 保存時の確認メッセージを表示しないように変更
' mxlsApp.DisplayAlerts = False
' If blnSave Then
' If Me.mblnMultiSheets Then
' Me.mxstTmplSheet.Visible = Excel.XlSheetVisibility.xlSheetHidden
' End If
' Me.mxbkBook.Close(SaveChanges:=Excel.XlSaveAction.xlSaveChanges)
' Else
' Me.mxbkBook.Close(SaveChanges:=Excel.XlSaveAction.xlDoNotSaveChanges)
' End If
' ' 保存時の確認メッセージを表示するように変更
' mxlsApp.DisplayAlerts = True
' Catch ex As Exception
' Throw New XlsReportException(ex, XlErrorNo.errClose)
' End Try
'End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * COMオブジェクトのすべてをリリースする
' *
' * [ARGUMENT]
' * なし
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub Dispose()Sub Dispose()
If mxstSheet IsNot Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(mxstSheet)
mxstSheet = Nothing
End If
If mxstTmplSheet IsNot Nothing Then
System.Runtime.InteropServices.Marshal.ReleaseComObject(mxstTmplSheet)
mxstTmplSheet = Nothing
End If
If mxbkBook IsNot Nothing Then
mxlsApp.DisplayAlerts = False
If Not mblnIsWorkbookClosed Then
mxbkBook.Close()
End If
System.Runtime.InteropServices.Marshal.ReleaseComObject(mxbkBook)
mxbkBook = Nothing
mxlsApp.DisplayAlerts = True
End If
If mxlsApp IsNot Nothing Then
mxlsApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(mxlsApp)
mxlsApp = Nothing
GC.Collect()
End If
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
セルの操作#Region "セルの操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * セルを取得する
' *
' * [RETURN]
' * セル
' *
' * [ARGUMENT]
' * strCell IN セル名
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Function GetCell()Function GetCell(ByVal strCell As String) As Excel.Range
Dim cell As Excel.Range = Nothing
Try
If strCell.StartsWith(mstrKeyWord) Then
If Me.mhtKeyCellContainer.Contains(strCell) Then
cell = Me.mxstSheet.Range(mhtKeyCellContainer(strCell))
Else
cell = Me.mxstSheet.Cells.Find(strCell)
Me.mhtKeyCellContainer(strCell) = cell.Address
End If
Else
cell = Me.mxstSheet.Range(strCell)
End If
Catch ex As Exception
End Try
If cell Is Nothing Then
Dim msg As String = String.Format("セル({0})ではありません。", strCell)
Throw New XlsReportException(Nothing, XlErrorNo.errCell, msg)
End If
Return cell
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * セルを取得する
' *
' * [RETURN]
' * セル
' *
' * [ARGUMENT]
' * intRow IN セルの行(1~)
' * intCol IN セルの列(1~)
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Function GetCell()Function GetCell(ByVal intRow As Integer, ByVal intCol As Integer) As Excel.Range
Dim cell As Excel.Range = Nothing
Try
cell = CType(Me.mxstSheet.Cells(intRow, intCol), Excel.Range)
Catch ex As Exception
End Try
If cell Is Nothing Then
Dim msg As String = String.Format("セル({0}, {1})ではありません。", intRow, intCol)
Throw New XlsReportException(Nothing, XlErrorNo.errCell, msg)
End If
Return cell
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セルに公式を設定
' *
' * [ARGUMENT]
' * cell IN セル
' * strFormula IN 公式
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Sub SetCellFormula()Sub SetCellFormula(ByVal cell As Excel.Range, ByVal strFormula As String)
Try
cell.Formula = strFormula
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errFormula, "セルに公式を設定エラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セルに公式を設定
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' * strFormula IN 公式
' * 例えば: XlsReport1.SetFormula("A1", "=SUM(A2:A3)")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetFormula()Sub SetFormula(ByVal strCell As String, ByVal strFormula As String)
Dim cell As Excel.Range = GetCell(strCell)
SetCellFormula(cell, strFormula)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セルに公式を設定
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' * intOffsetX IN strCellで指定したセル位置から、対象となる範囲を左、または、
' * 右への移動量を整数型で指定します。
' * intOffsetY IN strCellで指定したセル位置から、対象となる範囲を上、または、
' * 下への移動量を整数型で指定します。
' * strFormula IN 公式
' * 例えば: XlsReport1.SetFormula("A1", 1, 1, "=SUM(A2:A3)")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetFormula()Sub SetFormula(ByVal strCell As String, ByVal intOffsetX As Integer, ByVal intOffsetY As Integer, _
ByVal strFormula As String)
Dim cell As Excel.Range = GetCell(strCell)
cell = GetCell(cell.Row + intOffsetX, cell.Column + intOffsetY)
SetCellFormula(cell, strFormula)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セルに公式を設定
' *
' * [ARGUMENT]
' * intRow IN セルの行(1~)
' * intCol IN セルの列(1~)
' * strFormula IN 公式
' * 例えば: XlsReport1.SetFormula("A1", "=SUM(A2:A3)")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetFormula()Sub SetFormula(ByVal intRow As Integer, ByVal intCol As Integer, ByVal strFormula As String)
Dim cell As Excel.Range = GetCell(intRow, intCol)
SetCellFormula(cell, strFormula)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セル値の差し込み
' *
' * [ARGUMENT]
' * cell IN セル
' * objValue IN 値
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Sub SetCellValue()Sub SetCellValue(ByVal cell As Excel.Range, ByVal objValue As Object)
Try
If TypeOf objValue Is Array Then
Dim arr As Array = CType(objValue, Array)
If arr.Rank > 1 Then
cell.Resize(arr.GetLength(1), arr.GetLength(0)).Value = arr
Else
cell.Resize(1, arr.GetLength(0)).Value = arr
End If
Else
cell.Value = objValue
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errValue, "値の差し込みエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セル値の差し込み
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' * objValue IN 値
' * 例えば: XlsReport1.SetValue("A1", "アドバンスソフトウェア")
' * XlsReport1.SetValue("**Cell", "アドバンスソフトウェア")
' * XlsReport1.SetValue("PostCell", "アドバンスソフトウェア")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetValue()Sub SetValue(ByVal strCell As String, ByVal objValue As Object)
Dim cell As Excel.Range = GetCell(strCell)
SetCellValue(cell, objValue)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セル値の差し込み
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' * intOffsetX IN strCellで指定したセル位置から、対象となる範囲を左、または、
' * 右への移動量を整数型で指定します。
' * intOffsetY IN strCellで指定したセル位置から、対象となる範囲を上、または、
' * 下への移動量を整数型で指定します。
' * objValue IN 値
' * 例えば: XlsReport1.SetValue("A1", 1, 1, "アドバンスソフトウェア")
' * XlsReport1.SetValue("**Cell", 1, 1, "アドバンスソフトウェア")
' * XlsReport1.SetValue("PostCell", 1, 1, "アドバンスソフトウェア")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetValue()Sub SetValue(ByVal strCell As String, ByVal intOffsetX As Integer, ByVal intOffsetY As Integer, _
ByVal objValue As Object)
Dim cell As Excel.Range = GetCell(strCell)
cell = GetCell(cell.Row + intOffsetX, cell.Column + intOffsetY)
SetCellValue(cell, objValue)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 座標形式による値の差し込み
' *
' * [ARGUMENT]
' * intRow IN セルの行(1~)
' * intCol IN セルの列(1~)
' * strValue IN 値
' * 例えば: XlsReport1.SetCellValue(1, 1, "アドバンスソフトウェア")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetValue()Sub SetValue(ByVal intRow As Integer, ByVal intCol As Integer, ByVal objValue As Object)
Dim cell As Excel.Range = GetCell(intRow, intCol)
SetCellValue(cell, objValue)
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * A1 参照形式による値を取得
' *
' * [RETURN]
' * セルの値
' *
' * [ARGUMENT]
' * strCell IN セル名
' * 例えば: XlsReport1.GetCellValue("A1")
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Function GetValue()Function GetValue(ByVal strCell As String) As Object
Return GetCell(strCell).Value
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * 座標形式による値を取得
' *
' * [RETURN]
' * セルの値
' *
' * [ARGUMENT]
' * intRow IN セルの行(1~)
' * intCol IN セルの列(1~)
' * 例えば: XlsReport1.GetCellValue(1, 1)
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Function GetValue()Function GetValue(ByVal intRow As Integer, ByVal intCol As Integer) As Object
Return GetCell(intRow, intCol).Value
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * セルをクリアする。
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub CellClear()Sub CellClear(ByVal strCell As String)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Clear()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errClear, "セルクリアエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
行の操作#Region "行の操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * 行を取得する
' *
' * [RETURN]
' * 行
' *
' * [ARGUMENT]
' * intRow IN 開始行番号 (1 ~) の値を設定します。
' * intCount IN 行数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Function GetRow()Function GetRow(ByVal intRow As Integer, Optional ByVal intCount As Integer = 1) As Excel.Range
Dim row As Excel.Range = Nothing
Try
row = Me.mxstSheet.Rows(String.Format("{0}:{1}", intRow, intRow + intCount - 1))
Catch ex As Exception
End Try
If row Is Nothing Then
Dim msg As String = String.Format("行({0}:{1})ではありません。", intRow, intRow + intCount - 1)
Throw New XlsReportException(Nothing, XlErrorNo.errRow, msg)
End If
Return row
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行をクリアする。
' *
' * [ARGUMENT]
' * intRow IN 開始行番号 (1 ~) の値を設定します。
' * intCount IN 行数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowClear()Sub RowClear(ByVal intRow As Integer, Optional ByVal intCount As Integer = 1)
Dim row As Excel.Range = GetRow(intRow, intCount)
Try
row.Clear()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errClear, "行をクリアエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行を削除する。
' *
' * [ARGUMENT]
' * intRow IN 開始行番号 (1 ~) の値を設定します。
' * intCount IN 行数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowDelete()Sub RowDelete(ByVal intRow As Integer, Optional ByVal intCount As Integer = 1)
Dim row As Excel.Range = GetRow(intRow, intCount)
Try
row.Delete()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errDelete, "行を削除エラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行を隠す。
' *
' * [ARGUMENT]
' * intRow IN 開始行番号 (1 ~) の値を設定します。
' * intCount IN 行数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowHide()Sub RowHide(ByVal intRow As Integer, Optional ByVal intCount As Integer = 1)
Dim row As Excel.Range = GetRow(intRow, intCount)
Try
row.Hidden = True
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errHide, "行を隠すエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行をコピーする。 intSRow行目をコピーからintDRow行目に貼り付けます、挿入します。
' *
' * [ARGUMENT]
' * intSRow IN コピー元の行番号 (1 ~) の値を設定します。
' * intDRow IN コピー先の行番号 (1 ~) の値を設定します。
' * enmAction IN 貼り付け/挿入。省略時は「貼り付け」になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowCopy()Sub RowCopy(ByVal intSRow As Integer, ByVal intDRow As Integer, _
Optional ByVal enmAction As XlCopyAction = XlCopyAction.Paste)
Dim srow As Excel.Range = GetRow(intSRow)
Dim drow As Excel.Range = GetRow(intDRow)
Try
srow.Copy()
drow.Select()
If enmAction = XlCopyAction.Paste Then
Me.mxstSheet.Paste()
ElseIf enmAction = XlCopyAction.Insert Then
drow.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftDown)
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errCopy, "行をコピーエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行をコピーする。 intSRow行目からintCount行数をコピー、intDRow行目に貼り付けます、また挿入します。
' *
' * [ARGUMENT]
' * intSRow IN コピー元の行番号 (1 ~) の値を設定します。
' * intCount IN コピーの行数 (1 ~) の値を設定します。
' * intDRow IN コピー先の行番号 (1 ~) の値を設定します。
' * enmAction IN 貼り付け/挿入。省略時は「貼り付け」になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowCopy()Sub RowCopy(ByVal intSRow As Integer, ByVal intCount As Integer, _
ByVal intDRow As Integer, Optional ByVal enmAction As XlCopyAction = XlCopyAction.Paste)
Dim srow As Excel.Range = GetRow(intSRow, intCount)
Dim drow As Excel.Range = GetRow(intDRow)
Try
srow.Copy()
drow.Select()
If enmAction = XlCopyAction.Paste Then
Me.mxstSheet.Paste()
ElseIf enmAction = XlCopyAction.Insert Then
drow.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftDown)
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errCopy, "行をコピーエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行を挿入する。
' *
' * [ARGUMENT]
' * intRow IN 開始行番号 (1 ~) の値を設定します。
' * intCount IN 行数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowInsert()Sub RowInsert(ByVal intRow As Integer, Optional ByVal intCount As Integer = 1)
Dim row As Excel.Range = GetRow(intRow)
Try
For i As Integer = 0 To intCount
row.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftDown)
Next
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errInsert, "行を挿入エラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * intSRowからintDRowまで範囲の行高を調整する
' *
' * [ARGUMENT]
' * intSCol IN 調整元行番号 (1 ~) の値を設定します。
' * intDRow IN 調整先行番号 (1 ~) の値を設定します。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowFit()Sub RowFit(ByVal intSRow As Integer, ByVal intDRow As Integer)
Try
Dim strRange As String
Dim xrgRows As Excel.Range
strRange = intSRow.ToString() + ":" + intDRow.ToString()
xrgRows = CType(Me.mxstSheet.Rows(strRange), Excel.Range).AutoFit()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errFormat)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 行PageBreakを添加する
' *
' * [ARGUMENT]
' * intRow IN 行番号 (1 ~) の値を設定します。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub RowPageBreakAdd()Sub RowPageBreakAdd(ByVal intRow As Integer)
Dim row As Excel.Range = GetRow(intRow)
Try
mxstSheet.HPageBreaks.Add(Before:=row)
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errPageBreak)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
列の操作#Region "列の操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * 列を取得する
' *
' * [RETURN]
' * 列
' *
' * [ARGUMENT]
' * intCol IN 開始列番号 (1 ~) の値を設定します。
' * intCount IN 列数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Private Function GetCol()Function GetCol(ByVal intCol As Integer, Optional ByVal intCount As Integer = 1) As Excel.Range
Dim col As Excel.Range = Nothing
Dim sColAddress As String = ""
Dim eColAddress As String = ""
Try
sColAddress = CType(Me.mxstSheet.Columns(intCol), Excel.Range).Address.Split(":")(0)
eColAddress = CType(Me.mxstSheet.Columns(intCol + intCount - 1), Excel.Range).Address.Split(":")(0)
col = Me.mxstSheet.Columns(String.Format("{0}:{1}", sColAddress, eColAddress))
Catch ex As Exception
End Try
If col Is Nothing Then
Dim msg As String = String.Format("列({0}:{1})ではありません。", sColAddress, eColAddress)
Throw New XlsReportException(Nothing, XlErrorNo.errColumn, msg)
End If
Return col
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列をクリアする。
' *
' * [ARGUMENT]
' * intCol IN 開始列番号 (1 ~) の値を設定します。
' * intCount IN 列数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnClear()Sub ColumnClear(ByVal intCol As Integer, Optional ByVal intCount As Integer = 1)
Dim col As Excel.Range = GetCol(intCol, intCount)
Try
col.Clear()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errClear, "列をクリアエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列を削除する。
' *
' * [ARGUMENT]
' * intCol IN 開始列番号 (1 ~) の値を設定します。
' * intCount IN 列数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnDelete()Sub ColumnDelete(ByVal intCol As Integer, Optional ByVal intCount As Integer = 1)
Dim col As Excel.Range = GetCol(intCol)
Try
col.Delete()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errDelete, "列を削除エラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列を隠す。
' *
' * [ARGUMENT]
' * intCol IN 開始列番号 (1 ~) の値を設定します。
' * intCount IN 列数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnHide()Sub ColumnHide(ByVal intCol As Integer, Optional ByVal intCount As Integer = 1)
Dim col As Excel.Range = GetCol(intCol, intCount)
Try
col.Hidden = True
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errHide, "列を隠すエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列をコピーする。 intSCol列目をコピーからintDCol列目に貼り付けます、挿入します。
' *
' * [ARGUMENT]
' * intSCol IN コピー元の列番号 (1 ~) の値を設定します。
' * intDCol IN コピー先の列番号 (1 ~) の値を設定します。
' * enmAction IN 貼り付け/挿入。省略時は「貼り付け」になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnCopy()Sub ColumnCopy(ByVal intSCol As Integer, ByVal intDCol As Integer, _
Optional ByVal enmAction As XlCopyAction = XlCopyAction.Paste)
Dim scol As Excel.Range = GetCol(intSCol)
Dim dcol As Excel.Range = GetCol(intDCol)
Try
scol.Copy()
dcol.Select()
If enmAction = XlCopyAction.Paste Then
Me.mxstSheet.Paste()
ElseIf enmAction = XlCopyAction.Insert Then
dcol.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftToRight)
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errCopy, "列をコピーエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列をコピーする。 intSCol列目からintCount列数をコピー、intDCol列目に貼り付けます、また挿入します。
' *
' * [ARGUMENT]
' * intSCol IN コピー元の列番号 (1 ~) の値を設定します。
' * intCount IN コピーの列数 (1 ~) の値を設定します。
' * intDCol IN コピー先の列番号 (1 ~) の値を設定します。
' * enmAction IN 貼り付け/挿入。省略時は「貼り付け」になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnCopy()Sub ColumnCopy(ByVal intSCol As Integer, ByVal intCount As Integer, _
ByVal intDCol As Integer, Optional ByVal enmAction As XlCopyAction = XlCopyAction.Paste)
Dim scol As Excel.Range = GetCol(intSCol, intCount)
Dim dcol As Excel.Range = GetCol(intDCol)
Try
scol.Copy()
dcol.Select()
If enmAction = XlCopyAction.Paste Then
Me.mxstSheet.Paste()
ElseIf enmAction = XlCopyAction.Insert Then
dcol.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftToRight)
End If
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errCopy, "列をコピーエラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列を挿入する。
' *
' * [ARGUMENT]
' * intCol IN 開始列番号 (1 ~) の値を設定します。
' * intCount IN 列数 (1 ~) の値を設定します。省略時は 1 になります。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnInsert()Sub ColumnInsert(ByVal intCol As Integer, Optional ByVal intCount As Integer = 1)
Dim col As Excel.Range = GetCol(intCol)
Try
For i As Integer = 0 To intCount
col.Insert(Shift:=Excel.XlInsertShiftDirection.xlShiftToRight)
Next
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errInsert, "列を挿入エラー。")
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * intSColからintDColまで範囲の列を調整する
' *
' * [ARGUMENT]
' * intSCol IN 調整元列番号 (1 ~) の値を設定します。
' * intDCol IN 調整先列番号 (1 ~) の値を設定します。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnFit()Sub ColumnFit(ByVal intSCol As Integer, ByVal intDCol As Integer)
Try
Dim strRange As String
Dim xrgRows As Excel.Range
strRange = intSCol.ToString() + ":" + intDCol.ToString()
xrgRows = CType(Me.mxstSheet.Columns(strRange), Excel.Range).AutoFit()
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errFormat)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * 列PageBreakを添加する
' *
' * [ARGUMENT]
' * intCol IN 行番号 (1 ~) の値を設定します。
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub ColumnPageBreakAdd()Sub ColumnPageBreakAdd(ByVal intCol As Integer)
Dim col As Excel.Range = GetCol(intCol)
Try
mxstSheet.VPageBreaks.Add(Before:=col)
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errPageBreak)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
属性の操作#Region "属性の操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrBorderStyle()Sub AttrBorderStyle(ByVal strCell As String, ByVal enmBorder As XlBorders, _
ByVal enmLineStyle As XlLineStyle)
Dim cell As Excel.Range = GetCell(strCell)
cell.Borders(enmBorder).LineStyle = enmLineStyle
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrBorderWeight()Sub AttrBorderWeight(ByVal strCell As String, ByVal enmBorder As XlBorders, _
ByVal enmBorderWeight As XlBorderWight)
Dim cell As Excel.Range = GetCell(strCell)
cell.Borders(enmBorder).Weight = enmBorderWeight
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrBorderColor()Sub AttrBorderColor(ByVal strCell As String, ByVal enmBorder As XlBorders, _
ByVal intColor As Integer)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Borders(enmBorder).Color = intColor
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrFontFamily()Sub AttrFontFamily(ByVal strCell As String, ByVal strFontFamily As String)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Font.Name = strFontFamily
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrFontSize()Sub AttrFontSize(ByVal strCell As String, ByVal intSize As Integer)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Font.Size = intSize
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrHorizontalAlign()Sub AttrHorizontalAlign(ByVal strCell As String, ByVal enmAlign As XlHAlign)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.HorizontalAlignment = enmAlign
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrVerticalAlign()Sub AttrVerticalAlign(ByVal strcell As String, ByVal enmAlign As XlVAlign)
Dim cell As Excel.Range = GetCell(strcell)
Try
cell.VerticalAlignment = enmAlign
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrCellFormat()Sub AttrCellFormat(ByVal strCell As String, ByVal enmCellFormat As XlCellFormat)
Dim cell As Excel.Range = GetCell(strCell)
Try
Select Case enmCellFormat
Case XlCellFormat.xlShrinkToFit
cell.WrapText = True
Case XlCellFormat.xlShrinkToFit
cell.ShrinkToFit = True
Case Else
cell.WrapText = False
cell.ShrinkToFit = False
End Select
Catch ex As Exception
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrFontStyle()Sub AttrFontStyle(ByVal strCell As String, ByVal enmFontStyle As XlFontStyle)
Dim cell As Excel.Range = GetCell(strCell)
Try
Select Case enmFontStyle
Case XlFontStyle.xlOutlineFont
cell.Font.OutlineFont = True
Case XlFontStyle.xlShadow
cell.Font.Shadow = True
Case XlFontStyle.xlStrikethrough
cell.Font.Strikethrough = True
Case XlFontStyle.xlSubscript
cell.Font.Subscript = True
Case XlFontStyle.xlSuperscript
cell.Font.Superscript = True
Case XlFontStyle.xlBold
cell.Font.Bold = True
Case XlFontStyle.xlItalic
cell.Font.Italic = True
Case XlFontStyle.xlUnderlineDouble
cell.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleDouble
Case XlFontStyle.xlUnderlineSingle
cell.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
Case Else
cell.Font.OutlineFont = False
cell.Font.Shadow = False
cell.Font.Strikethrough = False
cell.Font.Subscript = False
cell.Font.Superscript = False
cell.Font.Bold = False
cell.Font.Italic = False
cell.Font.Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone
End Select
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrFontColor()Sub AttrFontColor(ByVal strCell As String, ByVal intColor As Integer)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Font.ColorIndex = intColor
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub AttrBackColor()Sub AttrBackColor(ByVal strCell As String, ByVal intColor As Integer, _
Optional ByVal enmFillPattern As XlFillPattern = XlFillPattern.xlAuto)
Dim cell As Excel.Range = GetCell(strCell)
Try
cell.Interior.ColorIndex = intColor
cell.Interior.Pattern = enmFillPattern
Catch ex As Exception
Throw New XlsReportException(ex, XlErrorNo.errAttr)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/7ff8d92cded7e0ce15e7ca1acc870052.gif)
データの操作#Region "データの操作"
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [PROCEDURE]
' * データ貼り付け範囲のクリップボードペースト
' *
' * [ARGUMENT]
' * strCell IN セル範囲
' * セル範囲は、セル位置 (A1 参照形式) / 変数名 / セル名による指定ができます。
' * strData IN データ
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub SetRangeData()Sub SetRangeData(ByVal strCell As String, ByVal strData As String)
Dim strHorizonArray As String()
Dim strVerticalArray As String()
Dim intRowCnt As Integer
Dim intColCnt As Integer
Dim cell As Excel.Range = GetCell(strCell)
Try
strVerticalArray = strData.Split(ControlChars.Cr)
strHorizonArray = strVerticalArray(0).Split(ControlChars.Tab)
Dim strDataArray(strVerticalArray.Length - 1, strHorizonArray.Length - 1) As Object
For intRowCnt = 0 To strVerticalArray.Length - 1
For intColCnt = 0 To strHorizonArray.Length - 1
strDataArray(intRowCnt, intColCnt) = ""
Next
Next
For intRowCnt = 0 To strVerticalArray.Length - 1
For intColCnt = 0 To strVerticalArray(intRowCnt).Split(ControlChars.Tab).Length - 1
strDataArray(intRowCnt, intColCnt) = strVerticalArray(intRowCnt).Split(ControlChars.Tab)(intColCnt)
Next
Next
If strVerticalArray.Length - 1 > 0 Then
cell.Resize(strVerticalArray.Length - 1, strHorizonArray.Length).Value = strDataArray
End If
Catch ex As Exception
Me.Dispose()
Throw New XlsReportException(ex, XlErrorNo.errData)
End Try
End Sub
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
' *====================================================================================================
' *
' * [FUNCTION]
' * 範囲のデータを取得
' *
' * [RETURN]
' * DataTable
' *
' * [ARGUMENT]
' * intSRow IN 開始行番号
' * intSCol IN 開始列番号
' * blnFirstRowIsHeader IN 範囲の第一行はヘーダの設定
' *
' *====================================================================================================
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Function GetRangeData()Function GetRangeData(ByVal intSRow As Integer, ByVal intSCol As Integer, _
Optional ByVal blnFirstRowIsHeader As Boolean = False) As DataTable
Dim objDataTable As DataTable = New DataTable(Me.mxstSheet.Name) 'テーブル
Dim intBeginRow As Integer = 1 '開始行番号
Dim intBeginCol As Integer = 1 '開始列番号
Dim intEndRow As Integer = 1 '結束行番号
Dim intEndCol As Integer = 1 '結束列番号
Dim objData As Object(,)
Try
'開始行
If intSRow < 1 Then
intBeginRow = Me.mxstSheet.UsedRange.Row
Else
intBeginRow = intSRow
End If
'開始列
If intSCol < 1 Then
intBeginCol = Me.mxstSheet.UsedRange.Column
Else
intBeginCol = intSCol
End If
'結束行
If blnFirstRowIsHeader Then
intEndRow = intBeginRow + Me.mxstSheet.UsedRange.Rows.Count - 1
Else
intEndRow = intBeginRow + Me.mxstSheet.UsedRange.Rows.Count - 2
End If
'結束列
intEndCol = intBeginCol + Me.mxstSheet.UsedRange.Columns.Count - 1
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
'テープル中の列数の設定
For i As Integer = intBeginCol To intEndCol
Dim objDataColumn As System.Data.DataColumn
objDataColumn = New System.Data.DataColumn
objDataColumn.DataType = Type.GetType("System.String")
objDataColumn.DefaultValue = ""
If blnFirstRowIsHeader Then
objDataColumn.ColumnName = Me.mxstSheet.Cells(intBeginRow, intBeginCol + i).Value.ToString()
End If
objDataTable.Columns.Add(objDataColumn)
Next
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
If blnFirstRowIsHeader Then
intBeginRow += 1
End If
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
'テープル中のデータの設定
objData = Me.mxstSheet.Range(Me.mxstSheet.Cells(intBeginRow, intBeginCol), _
Me.mxstSheet.Cells(intEndRow, intEndCol)).Value
For i As Integer = 1 To objData.GetUpperBound(0)
Dim objDataRow As System.Data.DataRow = objDataTable.NewRow()
For j As Integer = 1 To objData.GetUpperBound(1)
objDataRow(j - 1) = objData(i, j)
Next
objDataTable.Rows.Add(objDataRow)
Next
Catch ex As Exception
Me.Dispose()
Throw New XlsReportException(ex, XlErrorNo.errData)
End Try
Return objDataTable
End Function
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
#End Region
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
End Class
2. XlsReportException.vb
Public
Enum XlErrorNo
Enum XlErrorNo
errNoError = 0
errOpen
errSave
errClose
errSheet
errAddSheet
errValue
errFormula
errClear
errCopy
errPaste
errInsert
errDelete
errHide
errFormat
errPageBreak
errCell
errRow
errColumn
errData
errAttr
errParam
errFileType
errAppError
errVersion
errOther
End Enum
![](https://i-blog.csdnimg.cn/blog_migrate/6810355c2f78c12e91b7997a8e8c583a.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/a41954a27d6ad96fa2c2cf816e677448.gif)
Public
Class XlsReportException
Class XlsReportException
Inherits Exception
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Private menmErrorNo As XlErrorNo
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
Private mstrErrorMessage As String
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Property ErrorNo()Property ErrorNo() As XlErrorNo
Get
Return menmErrorNo
End Get
Set(ByVal value As XlErrorNo)
menmErrorNo = value
End Set
End Property
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Property ErrorMsg()Property ErrorMsg() As String
Get
Return mstrErrorMessage
End Get
Set(ByVal value As String)
mstrErrorMessage = value
End Set
End Property
![](https://i-blog.csdnimg.cn/blog_migrate/6a9c071a08f1dae2d3e1c512000eef41.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/37c8bf68cdc3cc81759c34160776bc53.gif)
Public Sub New()Sub New(ByVal ex As Exception, Optional ByVal errorNo As XlErrorNo = XlErrorNo.errOther, _
Optional ByVal errorMsg As String = "")
menmErrorNo = errorNo
mstrErrorMessage = errorMsg
If errorMsg = "" AndAlso ex IsNot Nothing Then
mstrErrorMessage = ex.Message
End If
End Sub
End Class