在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