ASP.NET 导出模板格式的EXCEL

Imports Interop
Imports System.IO
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 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 Function
End Class