vb.net ExcelHelper类（一）

本篇内容来自网络！内容篇幅有点大，现分为三段上传！

Imports System.Collections

Imports System.Diagnostics

Imports System.Reflection

Imports System.Data

Imports System.Text

Imports System.IO

Imports System

'using cfg = System.Configuration;

''' <summary>

''' 说 明：Excel输出打印模块

''' 暂时不提供操作Excel对象样式方法，样式可以在Excel模板中设置好

''' </summary>

Public Class ExcelHelper

#Region "成员变量"

Private templetFile As String = Nothing

Private outputFile As String = Nothing

Private missing As Object = Missing.Value

Private beforeTime As DateTime

'Excel启动之前时间

Private afterTime As DateTime

'Excel启动之后时间

Private app As Excel.Application

Private workBook As Excel.Workbook

Private workSheet As Excel.Worksheet

Private range As Excel.Range

Private range1 As Excel.Range

Private range2 As Excel.Range

Private textBox As Excel.TextBox

Private sheetCount As Integer = 1

'WorkSheet数量

Private sheetPrefixName As String = "页"

#End Region

#Region "公共属性"

''' <summary>

''' WorkSheet前缀名，比如：前缀名为“页”，那么WorkSheet名称依次为“页-1，页-2...”

''' </summary>

Public WriteOnly Property SheetPrefixName() As String

Set

Me.sheetPrefixName = value

End Set

End Property

''' <summary>

''' WorkSheet数量

''' </summary>

Public ReadOnly Property WorkSheetCount() As Integer

Get

Return workBook.Sheets.Count

End Get

End Property

''' <summary>

''' Excel模板文件路径

''' </summary>

Public WriteOnly Property TempletFilePath() As String

Set

Me.templetFile = value

End Set

End Property

''' <summary>

''' 输出Excel文件路径

''' </summary>

Public WriteOnly Property OutputFilePath() As String

Set

Me.outputFile = value

End Set

End Property

#End Region

#Region "公共方法"

#Region "ExcelHelper"

''' <summary>

''' 构造函数，将一个已有Excel工作簿作为模板，并指定输出路径

''' </summary>

''' <param name="templetFilePath">Excel模板文件路径</param>

''' <param name="outputFilePath">输出Excel文件路径</param>

Public Sub New(templetFilePath As String, outputFilePath As String)

If templetFilePath Is Nothing Then

Throw New Exception("Excel模板文件路径不能为空！")

End If

If outputFilePath Is Nothing Then

Throw New Exception("输出Excel文件路径不能为空！")

End If

If Not File.Exists(templetFilePath) Then

Throw New Exception("指定路径的Excel模板文件不存在！")

End If

Me.templetFile = templetFilePath

Me.outputFile = outputFilePath

'创建一个Application对象并使其可见

beforeTime = DateTime.Now

app = New Excel.ApplicationClass()

app.Visible = True

afterTime = DateTime.Now

'打开模板文件，得到WorkBook对象

workBook = app.Workbooks.Open(templetFile, missing, missing, missing, missing, missing, _

missing, missing, missing, missing, missing, missing, _

missing)

'得到WorkSheet对象

workSheet = CType(workBook.Sheets.get_Item(1), Excel.Worksheet)

End Sub

''' <summary>

''' 构造函数，打开一个已有的工作簿

''' </summary>

''' <param name="fileName">Excel文件名</param>

Public Sub New(fileName As String)

If Not File.Exists(fileName) Then

Throw New Exception("指定路径的Excel文件不存在！")

End If

'创建一个Application对象并使其可见

beforeTime = DateTime.Now

app = New Excel.ApplicationClass()

app.Visible = True

afterTime = DateTime.Now

'打开一个WorkBook

workBook = app.Workbooks.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _

Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, _

Type.Missing)

'得到WorkSheet对象

workSheet = CType(workBook.Sheets.get_Item(1), Excel.Worksheet)

End Sub

''' <summary>

''' 构造函数，新建一个工作簿

''' </summary>

Public Sub New()

'创建一个Application对象并使其可见

beforeTime = DateTime.Now

app = New Excel.ApplicationClass()

'app.Visible = true; 不自动打开

afterTime = DateTime.Now

'新建一个WorkBook

workBook = app.Workbooks.Add(Type.Missing)

'得到WorkSheet对象

workSheet = CType(workBook.Sheets.get_Item(1), Excel.Worksheet)

End Sub

#End Region

#Region "Data Export Methods"

''' <summary>

''' 将DataTable数据写入Excel文件（自动分页）

''' </summary>

''' <param name="dt">DataTable</param>

''' <param name="rows">每个WorkSheet写入多少行数据</param>

''' <param name="top">表格数据起始行索引</param>

''' <param name="left">表格数据起始列索引</param>

Public Sub DataTableToExcel(dt As DataTable, rows As Integer, top As Integer, left As Integer)

Dim rowCount As Integer = dt.Rows.Count

'DataTable行数

Dim colCount As Integer = dt.Columns.Count

'DataTable列数

sheetCount = Me.GetSheetCount(rowCount, rows)

'WorkSheet个数

' StringBuilder sb;

'复制sheetCount-1个WorkSheet对象

Dim i As Integer = 1

While i < sheetCount

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Copy(missing, workBook.Worksheets(i))

i += 1

End While

Dim i As Integer = 1

While i <= sheetCount

Dim startRow As Integer = (i - 1) * rows

'记录起始行索引

Dim endRow As Integer = i * rows

'记录结束行索引

'若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数

If i = sheetCount Then

endRow = rowCount

End If

'获取要写入数据的WorkSheet对象，并重命名

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Name = sheetPrefixName + "-" + i.ToString()

'将dt中的数据写入WorkSheet

' for(int j=0;j<endRow-startRow;j++)

' {

' for(int k=0;k<colCount;k++)

' {

' workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();

' }

' }

'利用二维数组批量写入

Dim row As Integer = endRow - startRow

Dim ss As String(,) = New String(row, colCount) {}

Dim j As Integer = 0

While j < row

Dim k As Integer = 0

While k < colCount

ss(j, k) = dt.Rows(startRow + j)(k).ToString()

k += 1

End While

j += 1

End While

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(row, colCount)

'#Region "利用Windwo粘贴板批量拷贝数据（在Web下面行不通）"

'sb = new StringBuilder();

'

' for(int j=0;j<endRow-startRow;j++)

' {

' for(int k=0;k<colCount;k++)

' {

' sb.Append( dt.Rows[startRow + j][k].ToString() );

' sb.Append("\t");

' }

'

' sb.Append("\n");

' }

'

' System.Windows.Forms.Clipboard.SetDataObject(sb.ToString());

'

' range = (Excel.Range)workSheet.Cells[top,left];

' workSheet.Paste(range,false);

#End Region

range.Value = ss

i += 1

End While

End Sub

''' <summary>

''' 将DataTable数据写入Excel文件（不分页）

''' </summary>

''' <param name="dt">DataTable</param>

''' <param name="top">表格数据起始行索引</param>

''' <param name="left">表格数据起始列索引</param>

Public Sub DataTableToExcel(dt As DataTable, top As Integer, left As Integer)

Dim rowCount As Integer = dt.Rows.Count

'DataTable行数

Dim colCount As Integer = dt.Columns.Count

'DataTable列数

'利用二维数组批量写入

Dim arr As String(,) = New String(rowCount, colCount) {}

Dim j As Integer = 0

While j < rowCount

Dim k As Integer = 0

While k < colCount

arr(j, k) = dt.Rows(j)(k).ToString()

k += 1

End While

j += 1

End While

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(rowCount, colCount)

range.Value = arr

End Sub

''' <summary>

''' 将DataTable数据写入Excel文件（自动分页，并指定要合并的列索引）

''' </summary>

''' <param name="dt">DataTable</param>

''' <param name="rows">每个WorkSheet写入多少行数据</param>

''' <param name="top">表格数据起始行索引</param>

''' <param name="left">表格数据起始列索引</param>

''' <param name="mergeColumnIndex">DataTable中要合并相同行的列索引，从0开始</param>

Public Sub DataTableToExcel(dt As DataTable, rows As Integer, top As Integer, left As Integer, mergeColumnIndex As Integer)

Dim rowCount As Integer = dt.Rows.Count

'源DataTable行数

Dim colCount As Integer = dt.Columns.Count

'源DataTable列数

sheetCount = Me.GetSheetCount(rowCount, rows)

'WorkSheet个数

' StringBuilder sb;

'复制sheetCount-1个WorkSheet对象

Dim i As Integer = 1

While i < sheetCount

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Copy(missing, workBook.Worksheets(i))

i += 1

End While

Dim i As Integer = 1

While i <= sheetCount

Dim startRow As Integer = (i - 1) * rows

'记录起始行索引

Dim endRow As Integer = i * rows

'记录结束行索引

'若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数

If i = sheetCount Then

endRow = rowCount

End If

'获取要写入数据的WorkSheet对象，并重命名

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Name = sheetPrefixName + "-" + i.ToString()

'将dt中的数据写入WorkSheet

' for(int j=0;j<endRow-startRow;j++)

' {

' for(int k=0;k<colCount;k++)

' {

' workSheet.Cells[top + j,left + k] = dt.Rows[startRow + j][k].ToString();

' }

' }

'利用二维数组批量写入

Dim row As Integer = endRow - startRow

Dim ss As String(,) = New String(row, colCount) {}

Dim j As Integer = 0

While j < row

Dim k As Integer = 0

While k < colCount

ss(j, k) = dt.Rows(startRow + j)(k).ToString()

k += 1

End While

j += 1

End While

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(row, colCount)

range.Value = ss

'合并相同行

Me.MergeRows(workSheet, left + mergeColumnIndex, top, rows)

i += 1

End While

End Sub

''' <summary>

''' 将二维数组数据写入Excel文件（自动分页）

''' </summary>

''' <param name="arr">二维数组</param>

''' <param name="rows">每个WorkSheet写入多少行数据</param>

''' <param name="top">行索引</param>

''' <param name="left">列索引</param>

Public Sub ArrayToExcel(arr As String(,), rows As Integer, top As Integer, left As Integer)

Dim rowCount As Integer = arr.GetLength(0)

'二维数组行数（一维长度）

Dim colCount As Integer = arr.GetLength(1)

'二维数据列数（二维长度）

sheetCount = Me.GetSheetCount(rowCount, rows)

'WorkSheet个数

'复制sheetCount-1个WorkSheet对象

Dim i As Integer = 1

While i < sheetCount

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Copy(missing, workBook.Worksheets(i))

i += 1

End While

'将二维数组数据写入Excel

Dim i As Integer = sheetCount

While i >= 1

Dim startRow As Integer = (i - 1) * rows

'记录起始行索引

Dim endRow As Integer = i * rows

'记录结束行索引

'若是最后一个WorkSheet，那么记录结束行索引为源DataTable行数

If i = sheetCount Then

endRow = rowCount

End If

'获取要写入数据的WorkSheet对象，并重命名

workSheet = CType(workBook.Worksheets.get_Item(i), Excel.Worksheet)

workSheet.Name = sheetPrefixName + "-" + i.ToString()

'将二维数组中的数据写入WorkSheet

' for(int j=0;j<endRow-startRow;j++)

' {

' for(int k=0;k<colCount;k++)

' {

' workSheet.Cells[top + j,left + k] = arr[startRow + j,k];

' }

' }

'利用二维数组批量写入

Dim row As Integer = endRow - startRow

Dim ss As String(,) = New String(row, colCount) {}

Dim j As Integer = 0

While j < row

Dim k As Integer = 0

While k < colCount

ss(j, k) = arr(startRow + j, k)

k += 1

End While

j += 1

End While

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(row, colCount)

range.Value = ss

i -= 1

End While

End Sub

'end ArrayToExcel

''' <summary>

''' 将二维数组数据写入Excel文件（不分页）

''' </summary>

''' <param name="arr">二维数组</param>

''' <param name="top">行索引</param>

''' <param name="left">列索引</param>

Public Sub ArrayToExcel(arr As String(,), top As Integer, left As Integer)

Dim rowCount As Integer = arr.GetLength(0)

'二维数组行数（一维长度）

Dim colCount As Integer = arr.GetLength(1)

'二维数据列数（二维长度）

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(rowCount, colCount)

range.FormulaArray = arr

End Sub

'end ArrayToExcel

''' <summary>

''' 将二维数组数据写入Excel文件（不分页）

''' </summary>

''' <param name="arr">二维数组</param>

''' <param name="top">行索引</param>

''' <param name="left">列索引</param>

''' <param name="isFormula">填充的数据是否需要计算</param>

Public Sub ArrayToExcel(arr As String(,), top As Integer, left As Integer, isFormula As Boolean)

Dim rowCount As Integer = arr.GetLength(0)

'二维数组行数（一维长度）

Dim colCount As Integer = arr.GetLength(1)

'二维数据列数（二维长度）

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(rowCount, colCount)

'注意：使用range.FormulaArray写合并的单元格会出问题

If isFormula Then

range.FormulaArray = arr

Else

range.Value = arr

End If

End Sub

'end ArrayToExcel

''' <summary>

''' 将二维数组数据写入Excel文件（不分页），合并指定列的相同行

''' </summary>

''' <param name="arr">二维数组</param>

''' <param name="top">行索引</param>

''' <param name="left">列索引</param>

''' <param name="isFormula">填充的数据是否需要计算</param>

''' <param name="mergeColumnIndex">需要合并行的列索引</param>

Public Sub ArrayToExcel(arr As String(,), top As Integer, left As Integer, isFormula As Boolean, mergeColumnIndex As Integer)

Dim rowCount As Integer = arr.GetLength(0)

'二维数组行数（一维长度）

Dim colCount As Integer = arr.GetLength(1)

'二维数据列数（二维长度）

range = CType(workSheet.Cells(top, left), Excel.Range)

range = range.get_Resize(rowCount, colCount)

'注意：使用range.FormulaArray写合并的单元格会出问题

If isFormula Then

range.FormulaArray = arr

Else

range.Value = arr

End If

Me.MergeRows(workSheet, mergeColumnIndex, top, rowCount)

End Sub