ASP.NET 导出模板格式的EXCEL

Imports InteropImports System.IOImports System.Web.HttpServerUtilityImports Interop.Excel.ConstantsImports Interop.Excel.XlPasteTypeImports Interop.Excel.XlBordersIndexImports Interop.Excel.XlLineStyleImports Interop.Excel.XlBorderWeightImports Interop.Excel.XlUnderlineStylePublic Class clsCommonExcelTemplate    Inherits System.Web.UI.Page    Public Sub createAndDowloadExcel(ByVal dt As DataTable, _                                     ByVal strTemplateFile As String, _                                     ByVal strExportFile As String, _                                     ByVal strSheetName As String, _                                     ByVal txtFormat As String, _                                     ByVal intPerSheet As Integer)        createExcelFile(dt, strTemplateFile, strExportFile, intPerSheet, txtFormat, strSheetName)        '回收进程        GC.Collect()    End Sub    Protected Sub createExcelFile(ByVal dt As DataTable, _                                  ByVal strTemplateFile As String, _                                  ByVal strExportFile As String, _                                  ByVal intPerSheet As Integer, _                                  ByVal txtFormat As String, _                                  Optional ByVal strSheetName As String = "")        '获取完整的模板文件路径        Dim strTemplateFilePath As String = Server.MapPath("../../TmplateFile/") + strTemplateFile        '获取完整的到处文件路径        Dim strExportFilePath As String = Server.MapPath("../../DownLoadFile/") + strExportFile        Dim intSheetNum As Integer = 0 'sheet数        '计算sheet数        If dt.Rows.Count Mod intPerSheet = 0 Then            intSheetNum = dt.Rows.Count / intPerSheet        Else            intSheetNum = Int(dt.Rows.Count / intPerSheet) + 1        End If        'sheet名        If String.IsNullOrEmpty(strSheetName) Then            strSheetName = "Sheet"        End If        '创建一个Application对象并使其可见        Dim app As New Excel.Application        app.Visible = False        '保存Excel的时候，不弹出是否保存的窗口直接进行保存        app.DisplayAlerts = False        '打开模板文件，得到WorkBook对象        Dim workbook As Excel.Workbook = app.Workbooks.Open(strTemplateFilePath, False)        '得到WorkSheet对象        Dim worksheet As Excel.Worksheet = workbook.Sheets(1)        Dim arr(,) As Object        Dim intArrRow As Integer = 0        '取得数据插入起始行        Dim intStartRow As Integer = 0        '处理worksheet        Select Case strTemplateFile.ToString            Case "账票1.xls"                worksheet.Range("A2").Value = dt.Rows(0).Item("YEAR").ToString + "年"                worksheet.Range("N2").Value = Now.Date.ToString("yyyy年MM月dd日")                dt.Columns.Remove("YEAR")                intStartRow = 4            Case Else        End Select        '复制Sheet        If intSheetNum >= 2 Then            For i As Integer = 2 To intSheetNum                worksheet.Copy(After:=workbook.Sheets(workbook.Sheets.Count))            Next        End If        Dim intRowCount As Integer = dt.Rows.Count '源DataTable行数        Dim intColCount As Integer = dt.Columns.Count '源DataTable列数        Dim intEndRow As Integer        Dim range As Excel.Range        '将数据写入到EXCEL        For intSheetNo As Integer = 1 To intSheetNum            ReDim arr(intPerSheet, dt.Columns.Count - 1)            worksheet = workbook.Sheets(intSheetNo)            worksheet.Name = strSheetName + "_" + intSheetNo.ToString            intArrRow = 0            If intSheetNo = intSheetNum Then                intEndRow = dt.Rows.Count Mod intPerSheet + intStartRow - 1            Else                intEndRow = intPerSheet + intStartRow - 1            End If            For intCurrentRow As Integer = (intSheetNo - 1) * intPerSheet To intSheetNo * intPerSheet - 1                If intCurrentRow < dt.Rows.Count Then                    For intColumn As Integer = 0 To dt.Columns.Count - 1                        If IsDBNull(dt.Rows(intCurrentRow).Item(intColumn)) Then                            arr(intArrRow, intColumn) = ""                        Else                            arr(intArrRow, intColumn) = dt.Rows(intCurrentRow).Item(intColumn)                        End If                    Next                    intArrRow = intArrRow + 1                End If            Next            worksheet.Activate()            '设置文本格式            If String.IsNullOrEmpty(txtFormat) = False Then                Dim arrInx() As String = txtFormat.Split(",")                For i As Integer = 0 To arrInx.Length - 1                    worksheet.Columns(num2letter(worksheet, CInt(arrInx(i))) + ":" + num2letter(worksheet, CInt(arrInx(i)))).Select()                    app.Selection.NumberFormatLocal = "@"                Next            End If            '赋值            worksheet.Range("A" + intStartRow.ToString).Resize(intPerSheet + 1, dt.Columns.Count).Value = arr            '账票1中将值不等于0的数据的背景色设置为黄色，字体为红色            If strTemplateFile = "账票1.xls" Then                For i As Integer = intStartRow To intEndRow                    If worksheet.Range("F" + i.ToString).Value <> 0 Then                        worksheet.Range("F" + i.ToString).Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)                        worksheet.Range("F" + i.ToString).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)                    End If                Next            End If            '设置cell的线条            range = worksheet.Range("A" + intStartRow.ToString + ":" + num2letter(worksheet, intColCount) + intEndRow.ToString)            range.Cells.Borders.LineStyle = Excel.XlLineStyle.xlContinuous            '设置选中的单元格            worksheet.Range("A" + intStartRow.ToString).Select()        Next        workbook.Sheets(1).Select()        Try            '输出Excel文件并退出            workbook.SaveAs(strExportFilePath)            workbook.Close()            '保存Excel的时候，不弹出是否保存的窗口直接进行保存            app.DisplayAlerts = False            app.Workbooks.Close()            app.Application.Quit()            app.Quit()            worksheet = Nothing            workbook = Nothing            app = Nothing        Catch ex As Exception            Throw ex        Finally        End Try    End Sub    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 FunctionEnd Class