web程序生成excel

一、生成EXCEL的DLL文件

        1、先决条件：计算机有安装office。

        2、生成excel2003的Dll文件：将【C:\Program Files\Microsoft Office\OFFICE11\】目录下的EXCEL.EXE文件拷贝到【C:\Visual Studio.Net\SDK\v1.1\Bin\】目录下。

        3、打开Visual Studio 的.net命令提示，运行TlbImp EXCEL.EXE Excel.dll，则在【C:\Visual Studio.Net\SDK\v1.1\Bin\】目录下生成excel.dll文件。

        4、程序中添加对excel.dll文件的引用。

二、服务器端的设置

        1、在服务器上安装office的Excel软件。

        2、在【开始】->【运行】中输入dcomcnfg.exe启动【组件服务】。

        3、依次双击【组件服务】->【计算机】->【我的电脑】->【DCOM配置】。

        4、在【DCOM配置】中找到【Microsoft Excel 应用程序"】,在它上面点击右键,然后点击【属性】,弹出【Microsoft Excel 应用程序属性】对话框。

        5、点击【标识】标签,选择【交互式用户】。

        6、点击【安全】标签,在【启动和激活权限】上点击【自定义】,然后点击对应的【编辑】按钮，在弹出的【安全性】对话框中填加一个【NETWORK SERVICE】用户(注意要选择本计算机名),并给它赋予【本地启动】和【本地激活】权限。

        7、依然是【安全】标签,在【访问权限】上点击【自定义】,然后点击【编辑】，在弹出的【安全性】对话框中也填加一个【NETWORK SERVICE】用户,然后赋予【本地访问】权限。

        注意：关于用户，winxp系统下是ASPNET,win2003系统下是NETWORK SERVICE （有域的情况下位置选本机）

三、相关代码      

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>
    ''' 生成EXCEL文件
    ''' </summary>
    ''' <param name="table">需要导出的datatable</param>
    ''' <param name="sheetName">excel的sheet名</param>
    ''' <param name="newFileName">新文件名</param>
    ''' <param name="txtFormat">需要甚至为文本格式的列</param>
    ''' <param name="cntPerSheet">每个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
    ''' <summary>
    ''' 生成EXCEL文件过程
    ''' </summary>
    ''' <param name="table">需要导出的datatable</param>
    ''' <param name="sheetName">excel的sheet名</param>
    ''' <param name="newFileName">新文件名</param>
    ''' <param name="txtFormat">需要甚至为文本格式的列</param>
    ''' <param name="cntPerSheet">每个sheet显示数据条数</param>
    ''' <remarks></remarks>
    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))

        '引用Excel对象
        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
        '为每个sheet添加值
        For k = 0 To cntSheet - 1

            '设置excel的sheet的列名
            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

            '定位到当前sheet
            workbook.Sheets(workbook.Sheets.Count).Select()
            '添加新的sheet
            worksheet = workbook.Sheets.Add()
            '设置sheet的名称
            worksheet.Name = sheetName + "_" + (k + 1).ToString

            '给每个sheet填充值
            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)
                        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

            '设置sheet格式
            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

    ''' <summary>
    ''' 设置文本列
    ''' </summary>
    ''' <param name="worksheet">excel的sheet名</param>
    ''' <param name="n">n必须是介于1到256之间的有效列号</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    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