接上两条内容!
''' <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