asp.net生成excel文件的类

asp.net生成excel文件的类

 

调用：Dim clsExcel As New clsCommonExcel2

            clsExcel.createAndDowloadExcel(table, "sheet名称", "生成的excel名称", "1,3,5", 20000)

 

Imports Interop
Imports System.Web.HttpServerUtility
Imports Interop.Excel.Constants
Imports Interop.Excel.XlPasteType
Imports Interop.Excel.XlBordersIndex
Imports Interop.Excel.XlLineStyle
Imports Interop.Excel.XlBorderWeight
Imports Interop.Excel.XlUnderlineStyle

Public Class clsCommonExcel2
    Inherits System.Web.UI.Page

    ''' <summary>
    ''' web服务器端生成excel文件
    ''' </summary>
    ''' <param name="table">数据集DataTable</param>
    ''' <param name="sheetName">excel的sheet名称</param>
    ''' <param name="newFileName">excel文件名称</param>
    ''' <param name="txtFormat">第1，3，5列要设为文本格式，则传入[1，3，5]</param>
    ''' <param name="cntPerSheet">每sheet的数据件数，超过则新生成sheet</param>
    ''' <remarks></remarks>
    Public Sub createAndDowloadExcel(ByVal table As DataTable, ByVal sheetName As String, _
                                     ByVal newFileName As String, ByVal txtFormat As String, _
                                     ByVal cntPerSheet As Integer)
        createExcelFile(table, sheetName, newFileName, txtFormat, cntPerSheet)
        '回收进程
        GC.Collect()
    End Sub

    Protected Sub createExcelFile(ByVal table As DataTable, ByVal sheetName As String, _
                                  ByVal newFileName As String, ByVal txtFormat As String, _
                                  ByVal cntPerSheet As Integer)
        Dim app As Excel.Application
        Dim workbook As Excel.Workbook
        Dim worksheet As Excel.Worksheet
        Dim arr(,) As Object
        Dim cntSheet As Integer
        Dim m As Integer

        ''删除既存文件
        'System.IO.File.Delete(Server.MapPath("../../DownLoadFile/" + newFileName))

        app = New Excel.Application
        app.Visible = False
        workbook = app.Workbooks.Add(1)

        app.DisplayAlerts = False
        workbook.SaveAs(Server.MapPath("../../DownLoadFile/" + newFileName))

        '计算sheet数
        If table.Rows.Count Mod cntPerSheet = 0 Then
            cntSheet = table.Rows.Count / cntPerSheet
        Else
            cntSheet = Int(table.Rows.Count / cntPerSheet) + 1
        End If
        For k = 0 To cntSheet - 1

            ReDim arr(cntPerSheet, table.Columns.Count - 1)
            For j As Integer = 0 To table.Columns.Count - 1
                arr(0, j) = table.Columns(j).ColumnName
            Next

            workbook.Sheets(workbook.Sheets.Count).Select()
            worksheet = workbook.Sheets.Add()
            worksheet.Name = sheetName + "_" + (k + 1).ToString
            m = 1
            For i As Integer = k * cntPerSheet To (k + 1) * cntPerSheet - 1
                If i < table.Rows.Count Then
                    For j As Integer = 0 To table.Columns.Count - 1
                        'arr(m, j) = table.Rows(i).Item(j)  防止excel单元格中信息以”-，=“开头
                        If Not IsNumeric(table.Rows(i).Item(j)) AndAlso table.Rows(i).Item(j).ToString.Length > 1 AndAlso _
                            (Left(table.Rows(i).Item(j).ToString, 1).Equals("-") OrElse Left(table.Rows(i).Item(j).ToString, 1).Equals("=")) Then
                            arr(m, j) = "'" + table.Rows(i).Item(j)
                        Else
                            arr(m, j) = table.Rows(i).Item(j)
                        End If

                    Next
                    m = m + 1
                End If
            Next

            '格式
            With worksheet
                .Cells.Select()
                With app.Selection.Font
                    .Name = "宋体"
                    .Size = 11
                    .Strikethrough = False
                    .Superscript = False
                    .Subscript = False
                    .OutlineFont = False
                    .Shadow = False
                    .Underline = xlUnderlineStyleNone
                    .ColorIndex = xlAutomatic
                End With

                .Range("A1:" + num2letter(worksheet, table.Columns.Count) + "1").Select()
                With app.Selection.Interior
                    .ColorIndex = 6
                    .Pattern = xlSolid
                End With
                app.Selection.Font.Bold = True
                With app.Selection
                    .HorizontalAlignment = xlCenter
                    .VerticalAlignment = xlCenter
                    .WrapText = False
                    .Orientation = 0
                    .AddIndent = False
                    .IndentLevel = 0
                    .ShrinkToFit = False
                    .ReadingOrder = xlContext
                    .MergeCells = False
                End With

                Dim arrInx() As String = txtFormat.Split(",")
                For i As Integer = 0 To arrInx.Length - 1
                    '设置文本格式
                    .Columns(num2letter(worksheet, CInt(arrInx(i))) + ":" + num2letter(worksheet, CInt(arrInx(i)))).Select()
                    app.Selection.NumberFormatLocal = "@"
                Next

                worksheet.Range("A1").Resize(cntPerSheet + 1, table.Columns.Count).Value = arr

                .Columns("A:" + num2letter(worksheet, table.Columns.Count)).Select()
                .Columns("A:" + num2letter(worksheet, table.Columns.Count)).EntireColumn.AutoFit()

            End With
            worksheet.Range("A1").Select()
        Next
        workbook.Sheets(workbook.Sheets.Count).Select()
        app.ActiveWindow.SelectedSheets.Delete()
        workbook.Sheets(1).Select()

        app.DisplayAlerts = False
        workbook.Save()
        workbook.Close()  'add 2011.11.1
        app.Quit()
        app = Nothing

    End Sub

    'n必须介于1到256之间
    Public Shared Function num2letter(ByVal worksheet As Excel.Worksheet, ByVal n As Integer) As String
        If n >= 1 And n <= 256 Then
            num2letter = IIf(n <= 26, Mid(worksheet.Cells(1, n).Address, 2, 1), Mid(worksheet.Cells(1, n).Address, 2, 2))
        Else
            num2letter = ""
        End If
    End Function
End Class