在DotNet中对Excel文档的Cell进行格式化(VB.net)
'***************************************************************************
'实在是懒得写注释,有任何建议、问题、指正欢迎留言
'***************************************************************************
Imports System
Imports Excel
Public Class ExcelClass
'***************************************************************************
'Function Name : FormatCellsByRows
'Writer : Gujs
'Date : 2005/08/26
'***************************************************************************
Public Shared Function FormatCellsByRows(ByVal iFormatStartRow As Integer, _
ByVal iFormatStartCol As Integer, _
ByVal iFormatEndCol As Integer, _
ByVal sExcelPath As String, _
ByVal saCellFormat() As String, _
Optional ByVal iBaseIndex As Integer = 1, _
Optional ByVal iRowsCount As Object = 0) As Boolean
If iBaseIndex = 0 Then
iFormatStartRow += 1
iFormatStartCol += 1
iFormatEndCol += 1
End If
Dim i As Integer
Dim j As Integer
Dim iFormatCount As Integer = saCellFormat.Length
Dim xlsbook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsApp As Excel.Application
Try
xlsApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlsbook = xlsApp.Workbooks.Open(sExcelPath)
xlsSheet = CType(xlsbook.Worksheets.Item(1), Excel.Worksheet)
For i = iFormatStartRow To xlsSheet.UsedRange.Rows.Count Step iFormatCount
For j = 0 To iFormatCount - 1
xlsSheet.Range(xlsSheet.Cells(i + j, iFormatStartCol), xlsSheet.Cells(i + j, iFormatEndCol)).NumberFormat = saCellFormat(j)
Next
Next
xlsSheet.Range(xlsSheet.Cells(iFormatStartRow, iFormatStartCol), xlsSheet.Cells(xlsSheet.UsedRange.Rows.Count, iFormatEndCol)).FormulaR1C1 = _
xlsSheet.Range(xlsSheet.Cells(iFormatStartRow, iFormatStartCol), xlsSheet.Cells(xlsSheet.UsedRange.Rows.Count, iFormatEndCol)).FormulaR1C1
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).Select()
xlsbook.Save()
Return True
Catch ex As Exception
Return False
Finally
xlsbook.Saved = True
xlsbook.Close()
xlsApp.Quit()
xlsSheet = Nothing
xlsbook = Nothing
xlsApp = Nothing
GC.Collect()
End Try
End Function
Public Shared Function FormatCellsByCols(ByVal iFormatStartRow As Integer, _
ByVal iFormatStartCol As Integer, _
ByVal iFormatEndCol As Integer, _
ByVal sExcelPath As String, _
ByVal saCellFormat() As String, _
Optional ByVal iBaseIndex As Integer = 1, _
Optional ByVal iRowsCount As Object = 0) As Boolean
If iBaseIndex = 0 Then
iFormatStartRow += 1
iFormatStartCol += 1
iFormatEndCol += 1
End If
Dim i As Integer
Dim j As Integer
Dim iFormatCount As Integer = saCellFormat.Length
Dim xlsbook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet
Dim xlsApp As Excel.Application
Try
xlsApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlsbook = xlsApp.Workbooks.Open(sExcelPath)
xlsSheet = CType(xlsbook.Worksheets.Item(1), Excel.Worksheet)
For i = iFormatStartCol To iFormatEndCol Step iFormatCount
For j = 0 To iFormatCount - 1
xlsSheet.Range(xlsSheet.Cells(iFormatStartRow, i + j), xlsSheet.Cells(xlsSheet.UsedRange.Rows.Count + 1, i + j)).NumberFormat = saCellFormat(j)
Next
Next
xlsSheet.Range(xlsSheet.Cells(iFormatStartRow, iFormatStartCol), xlsSheet.Cells(xlsSheet.UsedRange.Rows.Count + 1, iFormatEndCol)).FormulaR1C1 = _
xlsSheet.Range(xlsSheet.Cells(iFormatStartRow, iFormatStartCol), xlsSheet.Cells(xlsSheet.UsedRange.Rows.Count + 1, iFormatEndCol)).FormulaR1C1
xlsSheet.Range(xlsSheet.Cells(1, 1), xlsSheet.Cells(1, 1)).Select()
xlsbook.Save()
Return True
Catch ex As Exception
Return False
Finally
xlsbook.Saved = True
xlsbook.Close()
xlsApp.Quit()
xlsSheet = Nothing
xlsbook = Nothing
xlsApp = Nothing
GC.Collect()
End Try
End Function
End Class
Public Class CellFormat
' example -222,345,778
Public Const IntNumber = "#,##0"
' example -344,456.25
Public Const FloatNumber = "#,##0.00"
' example ▲344,456
Public Const IntNumberHasTran = "#,##0;""▲ ""#,##0"
' example ▲344,456.25
Public Const FloatNumberHasTran = "#,##0.00;""▲ ""#,##0.00"
' example -344,45625%
Public Const IntPercent = "#,##0%"
' example -344,45625.00%
Public Const FloatPercent = "#,##0.00%"
' example ▲344,45625%
Public Const IntPercentHasTran = "#,##0%;""▲ ""#,##0%"
' example ▲344,45625.00%
Public Const FloatPercentHasTran = "#,##0.00%;""▲ ""#,##0.00%"
'
Public Const TextFormat = "@"
End Class