在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