vb.net ExcelHelper类(三)

接上两条内容！

''' <summary>

''' 插行（在指定行上面插入指定数量行）

''' </summary>

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

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

Public Sub InsertRows(rowIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

range = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 0

While i < count

range.Insert(Excel.XlDirection.xlDown)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 插行（在指定WorkSheet指定行上面插入指定数量行）

''' </summary>

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

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

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

Public Sub InsertRows(sheetIndex As Integer, rowIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

range = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 0

While i < count

range.Insert(Excel.XlDirection.xlDown)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 复制行（在指定行下面复制指定数量行）

''' </summary>

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

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

Public Sub CopyRows(rowIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

range1 = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 1

While i <= count

range2 = CType(workSheet.Rows(rowIndex + i, Me.missing), Excel.Range)

range1.Copy(range2)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 复制行（在指定WorkSheet指定行下面复制指定数量行）

''' </summary>

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

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

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

Public Sub CopyRows(sheetIndex As Integer, rowIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

range1 = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 1

While i <= count

range2 = CType(workSheet.Rows(rowIndex + i, Me.missing), Excel.Range)

range1.Copy(range2)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 删除行

''' </summary>

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

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

Public Sub DeleteRows(rowIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

range = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 0

While i < count

range.Delete(Excel.XlDirection.xlDown)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 删除行

''' </summary>

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

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

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

Public Sub DeleteRows(sheetIndex As Integer, rowIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

range = CType(workSheet.Rows(rowIndex, Me.missing), Excel.Range)

Dim i As Integer = 0

While i < count

range.Delete(Excel.XlDirection.xlDown)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

#End Region

#Region "Column Methods"

''' <summary>

''' 插列（在指定列右边插入指定数量列）

''' </summary>

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

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

Public Sub InsertColumns(columnIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

range = CType(workSheet.Columns(Me.missing, columnIndex), Excel.Range)

Dim i As Integer = 0

While i < count

range.Insert(Excel.XlDirection.xlDown)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 插列（在指定WorkSheet指定列右边插入指定数量列）

''' </summary>

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

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

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

Public Sub InsertColumns(sheetIndex As Integer, columnIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

range = CType(workSheet.Columns(Me.missing, columnIndex), Excel.Range)

Dim i As Integer = 0

While i < count

range.Insert(Excel.XlDirection.xlDown)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 复制列（在指定列右边复制指定数量列）

''' </summary>

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

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

Public Sub CopyColumns(columnIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

' range1 = (Excel.Range)workSheet.Columns[columnIndex,this.missing];

range1 = CType(workSheet.get_Range(Me.IntToLetter(columnIndex) + "1", Me.IntToLetter(columnIndex) + "10000"), Excel.Range)

Dim i As Integer = 1

While i <= count

' range2 = (Excel.Range)workSheet.Columns[this.missing,columnIndex + i];

range2 = CType(workSheet.get_Range(Me.IntToLetter(columnIndex + i) + "1", Me.IntToLetter(columnIndex + i) + "10000"), Excel.Range)

range1.Copy(range2)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 复制列（在指定WorkSheet指定列右边复制指定数量列）

''' </summary>

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

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

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

Public Sub CopyColumns(sheetIndex As Integer, columnIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

' range1 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex];

range1 = CType(workSheet.get_Range(Me.IntToLetter(columnIndex) + "1", Me.IntToLetter(columnIndex) + "10000"), Excel.Range)

Dim i As Integer = 1

While i <= count

' range2 = (Excel.Range)workSheet.Columns[Type.Missing,columnIndex + i];

range2 = CType(workSheet.get_Range(Me.IntToLetter(columnIndex + i) + "1", Me.IntToLetter(columnIndex + i) + "10000"), Excel.Range)

range1.Copy(range2)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 删除列

''' </summary>

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

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

Public Sub DeleteColumns(columnIndex As Integer, count As Integer)

Try

Dim n As Integer = 1

While n <= Me.WorkSheetCount

workSheet = CType(workBook.Worksheets(n), Excel.Worksheet)

range = CType(workSheet.Columns(Me.missing, columnIndex), Excel.Range)

Dim i As Integer = 0

While i < count

range.Delete(Excel.XlDirection.xlDown)

i += 1

End While

n += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 删除列

''' </summary>

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

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

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

Public Sub DeleteColumns(sheetIndex As Integer, columnIndex As Integer, count As Integer)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

workSheet = CType(workBook.Worksheets(sheetIndex), Excel.Worksheet)

range = CType(workSheet.Columns(Me.missing, columnIndex), Excel.Range)

Dim i As Integer = 0

While i < count

range.Delete(Excel.XlDirection.xlDown)

i += 1

End While

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

#End Region

#Region "Range Methods"

''' <summary>

''' 将指定范围区域拷贝到目标区域

''' </summary>

''' <param name="sheetIndex">WorkSheet索引</param>

''' <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>

''' <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>

''' <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>

Public Sub RangeCopy(sheetIndex As Integer, startCell As String, endCell As String, targetCell As String)

If sheetIndex > Me.WorkSheetCount Then

Me.KillExcelProcess()

Throw New Exception("索引超出范围，WorkSheet索引不能大于WorkSheet数量！")

End If

Try

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

range1 = workSheet.get_Range(startCell, endCell)

range2 = workSheet.get_Range(targetCell, Me.missing)

range1.Copy(range2)

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 将指定范围区域拷贝到目标区域

''' </summary>

''' <param name="sheetName">WorkSheet名称</param>

''' <param name="startCell">要拷贝区域的开始Cell位置（比如：A10）</param>

''' <param name="endCell">要拷贝区域的结束Cell位置（比如：F20）</param>

''' <param name="targetCell">目标区域的开始Cell位置（比如：H10）</param>

Public Sub RangeCopy(sheetName As String, startCell As String, endCell As String, targetCell As String)

Try

Dim sheet As Excel.Worksheet = Nothing

Dim i As Integer = 1

While i <= Me.WorkSheetCount

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

If workSheet.Name = sheetName Then

sheet = workSheet

End If

i += 1

End While

If sheet IsNot Nothing Then

Dim i As Integer = sheetCount

While i >= 1

range1 = sheet.get_Range(startCell, endCell)

range2 = sheet.get_Range(targetCell, Me.missing)

range1.Copy(range2)

i -= 1

End While

Else

Me.KillExcelProcess()

Throw New Exception("名称为""" + sheetName + """的工作表不存在")

End If

Catch e As Exception

Me.KillExcelProcess()

Throw e

End Try

End Sub

''' <summary>

''' 自动填充

''' </summary>

Public Sub RangAutoFill()

Dim rng As Excel.Range = workSheet.get_Range("B4", Type.Missing)

rng.Value2 = "星期一 "

rng.AutoFill(workSheet.get_Range("B4", "B9"), Excel.XlAutoFillType.xlFillWeekdays)

rng = workSheet.get_Range("C4", Type.Missing)

rng.Value = "一月"

rng.AutoFill(workSheet.get_Range("C4", "C9"), Excel.XlAutoFillType.xlFillMonths)

rng = workSheet.get_Range("D4", Type.Missing)

rng.Value2 = "1"

rng.AutoFill(workSheet.get_Range("D4", "D9"), Excel.XlAutoFillType.xlFillSeries)

rng = workSheet.get_Range("E4", Type.Missing)

rng.Value2 = "3"

rng = workSheet.get_Range("E5", Type.Missing)

rng.Value2 = "6"

rng = workSheet.get_Range("E4", "E5")

rng.AutoFill(workSheet.get_Range("E4", "E9"), Excel.XlAutoFillType.xlFillSeries)

End Sub

''' <summary>

''' 应用样式

''' </summary>

Public Sub ApplyStyle()

Dim missingValue As Object = Type.Missing

Dim rng As Excel.Range = workSheet.get_Range("B3", "L23")

Dim style As Excel.Style

Try

style = workBook.Styles("NewStyle")

' Style doesn't exist yet.

Catch

style = workBook.Styles.Add("NewStyle", missingValue)

style.Font.Name = "Verdana"

style.Font.Size = 12

style.Font.Color = 255

style.Interior.Color = (200 << 16) Or (200 << 8) Or 200

style.Interior.Pattern = Excel.XlPattern.xlPatternSolid

End Try

rng.Value2 = "'Style Test"

rng.Style = "NewStyle"

rng.Columns.AutoFit()

End Sub

#End Region

#Region "ExcelHelper Kit"

''' <summary>

''' 将Excel列的字母索引值转换成整数索引值

''' </summary>

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

''' <returns></returns>

Public Function LetterToInt(letter As String) As Integer

Dim n As Integer = 0

If letter.Trim().Length = 0 Then

Throw New Exception("不接受空字符串！")

End If

If letter.Length >= 2 Then

Dim c1 As Char = letter.ToCharArray(0, 2)(0)

Dim c2 As Char = letter.ToCharArray(0, 2)(1)

If Not Char.IsLetter(c1) OrElse Not Char.IsLetter(c2) Then

Throw New Exception("格式不正确，必须是字母！")

End If

c1 = Char.ToUpper(c1)

c2 = Char.ToUpper(c2)

Dim i As Integer = Convert.ToInt32(c1) - 64

Dim j As Integer = Convert.ToInt32(c2) - 64

n = i * 26 + j

End If

If letter.Length = 1 Then

Dim c1 As Char = letter.ToCharArray()(0)

If Not Char.IsLetter(c1) Then

Throw New Exception("格式不正确，必须是字母！")

End If

c1 = Char.ToUpper(c1)

n = Convert.ToInt32(c1) - 64

End If

If n > 256 Then

Throw New Exception("索引超出范围，Excel的列索引不能超过256！")

End If

Return n

End Function

''' <summary>

''' 将Excel列的整数索引值转换为字符索引值

''' </summary>

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

''' <returns></returns>

Public Function IntToLetter(n As Integer) As String

If n > 256 Then

Throw New Exception("索引超出范围，Excel的列索引不能超过256！")

End If

Dim i As Integer = Convert.ToInt32(n / 26)

Dim j As Integer = n Mod 26

Dim c1 As Char = Convert.ToChar(i + 64)

Dim c2 As Char = Convert.ToChar(j + 64)

If n > 26 Then

Return c1.ToString() + c2.ToString()

ElseIf n = 26 Then

Return "Z"

Else

Return c2.ToString()

End If

End Function

#End Region

#Region "Output File(注意：如果目标文件已存在的话会出错)"

''' <summary>

''' 输出Excel文件并退出

''' </summary>

Public Sub OutputExcelFile()

If Me.outputFile Is Nothing Then

Throw New Exception("没有指定输出文件路径！")

End If

Try

workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 输出指定格式的文件（支持格式：HTML，CSV，TEXT，EXCEL）

''' </summary>

''' <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>

Public Sub OutputFile(format As String)

If Me.outputFile Is Nothing Then

Throw New Exception("没有指定输出文件路径！")

End If

Try

Select Case format

Case "HTML"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "CSV"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "TEXT"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case Else

' case "XML":

' {

' workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,

' Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,

' Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

' break;

'

' }

If True Then

workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

End Select

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 保存文件

''' </summary>

Public Sub SaveFile()

Try

workBook.Save()

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 另存文件

''' </summary>

Public Sub SaveAsFile()

If Me.outputFile Is Nothing Then

Throw New Exception("没有指定输出文件路径！")

End If

Try

workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 将Excel文件另存为指定格式

''' </summary>

''' <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>

Public Sub SaveAsFile(format As String)

If Me.outputFile Is Nothing Then

Throw New Exception("没有指定输出文件路径！")

End If

Try

Select Case format

Case "HTML"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "CSV"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "TEXT"

If True Then

workBook.SaveAs(outputFile, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case Else

' case "XML":

' {

' workBook.SaveAs(outputFile,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,

' Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,

' Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

' break;

' }

If True Then

workBook.SaveAs(outputFile, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

End Select

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 另存文件

''' </summary>

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

Public Sub SaveFile(fileName As String)

Try

workBook.SaveAs(fileName, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

''' <summary>

''' 将Excel文件另存为指定格式

''' </summary>

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

''' <param name="format">HTML，CSV，TEXT，EXCEL，XML</param>

Public Sub SaveAsFile(fileName As String, format As String)

Try

Select Case format

Case "HTML"

If True Then

workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "CSV"

If True Then

workBook.SaveAs(fileName, Excel.XlFileFormat.xlCSV, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case "TEXT"

If True Then

workBook.SaveAs(fileName, Excel.XlFileFormat.xlHtml, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

Case Else

' case "XML":

' {

' workBook.SaveAs(fileName,Excel.XlFileFormat.xlXMLSpreadsheet, Type.Missing, Type.Missing,

' Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,

' Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

' break;

' }

If True Then

workBook.SaveAs(fileName, missing, missing, missing, missing, missing, _

Excel.XlSaveAsAccessMode.xlExclusive, missing, missing, missing, missing)

Exit Select

End If

End Select

Catch e As Exception

Throw e

Finally

Me.Dispose()

End Try

End Sub

#End Region

#End Region

#Region "私有方法"

''' <summary>

''' 合并单元格，并赋值，对指定WorkSheet操作

''' </summary>

''' <param name="beginRowIndex">开始行索引</param>

''' <param name="beginColumnIndex">开始列索引</param>

''' <param name="endRowIndex">结束行索引</param>

''' <param name="endColumnIndex">结束列索引</param>

''' <param name="text">合并后Range的值</param>

Private Sub MergeCells(sheet As Excel.Worksheet, beginRowIndex As Integer, beginColumnIndex As Integer, endRowIndex As Integer, endColumnIndex As Integer, text As String)

If sheet Is Nothing Then

Return

End If

range = sheet.get_Range(sheet.Cells(beginRowIndex, beginColumnIndex), sheet.Cells(endRowIndex, endColumnIndex))

range.ClearContents()

'先把Range内容清除，合并才不会出错

range.MergeCells = True

range.Value = text

range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

End Sub

''' <summary>

''' 将指定索引列的数据相同的行合并，对指定WorkSheet操作

''' </summary>

''' <param name="columnIndex">要合并的列索引</param>

''' <param name="beginRowIndex">合并开始行索引</param>

''' <param name="rows">要合并的行数</param>

Private Sub MergeRows(sheet As Excel.Worksheet, columnIndex As Integer, beginRowIndex As Integer, rows As Integer)

Dim beginIndex As Integer = beginRowIndex

Dim count As Integer = 0

Dim text1 As String

Dim text2 As String

If sheet Is Nothing Then

Return

End If

Dim j As Integer = beginRowIndex

While j < beginRowIndex + rows

range1 = CType(sheet.Cells(j, columnIndex), Excel.Range)

range2 = CType(sheet.Cells(j + 1, columnIndex), Excel.Range)

text1 = range1.Text.ToString()

text2 = range2.Text.ToString()

If text1 = text2 Then

System.Threading.Interlocked.Increment(count)

Else

If count > 0 Then

Me.MergeCells(sheet, beginIndex, columnIndex, beginIndex + count, columnIndex, text1)

End If

beginIndex = j + 1

'设置开始合并行索引

'计数器清0

count = 0

End If

j += 1

End While

End Sub

''' <summary>

''' 计算WorkSheet数量

''' </summary>

''' <param name="rowCount">记录总行数</param>

''' <param name="rows">每WorkSheet行数</param>

Public Function GetSheetCount(rowCount As Integer, rows As Integer) As Integer

Dim n As Integer = rowCount Mod rows

'余数

If n = 0 Then

Return rowCount / rows

Else

Return Convert.ToInt32(rowCount / rows) + 1

End If

End Function

''' <summary>

''' 结束Excel进程

''' </summary>

Public Sub KillExcelProcess()

Dim myProcesses As Process()

Dim startTime As DateTime

myProcesses = Process.GetProcessesByName("Excel")

'得不到Excel进程ID，暂时只能判断进程启动时间

For Each myProcess As Process In myProcesses

startTime = myProcess.StartTime

If startTime > beforeTime AndAlso startTime < afterTime Then

myProcess.Kill()

End If

Next

End Sub

Private Sub Dispose()

workBook.Close(Nothing, Nothing, Nothing)

app.Workbooks.Close()

app.Quit()

If range IsNot Nothing Then

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

range = Nothing

End If

If range1 IsNot Nothing Then

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

range1 = Nothing

End If

If range2 IsNot Nothing Then

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

range2 = Nothing

End If

If textBox IsNot Nothing Then

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

textBox = Nothing

End If

If workSheet IsNot Nothing Then

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

workSheet = Nothing

End If

If workBook IsNot Nothing Then

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

workBook = Nothing

End If

If app IsNot Nothing Then

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

app = Nothing

End If

GC.Collect()

Me.KillExcelProcess()

End Sub

'end Dispose

#End Region

End Class