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.XlUnderlineStylePublic Class clsCommonExcel2Inherits 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.ApplicationDim workbook As Excel.WorkbookDim worksheet As Excel.WorksheetDim arr(,) As ObjectDim cntSheet As IntegerDim m As Integer'删除既存文件System.IO.File.Delete(Server.MapPath("../../DownLoadFile/" + newFileName))'引用Excel对象app = New Excel.Applicationapp.Visible = Falseworkbook = app.Workbooks.Add(1)app.DisplayAlerts = Falseworkbook.SaveAs(Server.MapPath("../../DownLoadFile/" + newFileName))'计算sheet数If table.Rows.Count Mod cntPerSheet = 0 ThencntSheet = table.Rows.Count / cntPerSheetElsecntSheet = Int(table.Rows.Count / cntPerSheet) + 1End 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 - 1arr(0, j) = table.Columns(j).ColumnNameNext'定位到当前sheetworkbook.Sheets(workbook.Sheets.Count).Select()'添加新的sheetworksheet = workbook.Sheets.Add()'设置sheet的名称worksheet.Name = sheetName + "_" + (k + 1).ToString'给每个sheet填充值m = 1For i As Integer = k * cntPerSheet To (k + 1) * cntPerSheet - 1If i < table.Rows.Count ThenFor 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("=")) Thenarr(m, j) = "'" + table.Rows(i).Item(j)Elsearr(m, j) = table.Rows(i).Item(j)End IfNextm = m + 1End IfNext'设置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 = xlAutomaticEnd With.Range("A1:" + num2letter(worksheet, table.Columns.Count) + "1").Select()With app.Selection.Interior.ColorIndex = 6.Pattern = xlSolidEnd Withapp.Selection.Font.Bold = TrueWith app.Selection.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenter.WrapText = False.Orientation = 0.AddIndent = False.IndentLevel = 0.ShrinkToFit = False.ReadingOrder = xlContext.MergeCells = FalseEnd 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 = "@"Nextworksheet.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 Withworksheet.Range("A1").Select()Nextworkbook.Sheets(workbook.Sheets.Count).Select()app.ActiveWindow.SelectedSheets.Delete()workbook.Sheets(1).Select()app.DisplayAlerts = Falseworkbook.Save()workbook.Close()  'add 2011.11.1app.Quit()app = NothingEnd 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 StringIf n >= 1 And n <= 256 Thennum2letter = IIf(n <= 26, Mid(worksheet.Cells(1, n).Address, 2, 1), Mid(worksheet.Cells(1, n).Address, 2, 2))Elsenum2letter = ""End IfEnd Function
End Class