VB.NET中实现写EXCEL文件,并下载到客户端的示例代码.

1. EXCEL文件的生成.
2.EXCEL文件的下载.

1. EXCEL文件的生成.
Private Sub ExcelOutProc()
        Dim printBookPath As String = ""
        Dim printBookName As String = ""
        Dim excelApp As Excel.Application
        Dim excelBook As Excel.Workbook
        Dim excelSheet As Excel.Worksheet
        Dim strTemplateBookName As String
        Dim sheetName As String
        Dim nStartRowNo As Integer = 0
        Dim blnAddHeadFlag As Boolean = True
        Dim nTitleBakColorIndex As Integer = 15
        '临时数组定义
        Dim Field_Value(,) As String
        Dim nDataStartRowNo As String = 10
        strTemplateBookName = System.Configuration.ConfigurationSettings.AppSettings("TEMPLATE_FILE")
        strTemplateBookName += "Test.xls"
        printBookPath = System.Configuration.ConfigurationSettings.AppSettings("DOWNLOAD_FILE")
        printBookName = "Test_" + Date.Now().ToString("yyyyMMddHHmmss") + ".xls"
        sheetName = "TestSheet"
        Try
            '模板文件存在检查
            If System.IO.File.Exists(strTemplateBookName) = False Then
                'Error
                Return
            End If

            '拷贝模板文件
            FileCopy(strTemplateBookName, printBookPath + printBookName)
            System.IO.File.SetAttributes(printBookPath + printBookName, IO.FileAttributes.Normal)
            excelApp = New Excel.Application
            excelBook = excelApp.Workbooks.Open(printBookPath + printBookName)
            excelSheet = excelBook.Sheets(sheetName)
            excelSheet.Range("B1").Value = "Title"
            ReDim Field_Value(10, 10)

            '先给一数组赋值
            For cntRow As Integer = 0 To 10
                For cntCol As Integer = 0 To 10
                    Field_Value(cntRow, cntCol) = CStr(cntRow) + "--" + CStr(cntCol)
                Next
            Next

            '将数组值赋予EXCEL
            excelSheet.Range("B" + CStr(nDataStartRowNo) + ":K" + CStr(nDataStartRowNo + nStartRowNo)).Value = Field_Value
            '设置边框
            excelSheet.Range("B" + CStr(nDataStartRowNo) + ":K" + CStr(nDataStartRowNo + nStartRowNo)).Borders.LineStyle = Excel.XlLineStyle.xlContinuous

            '标题设置
            '设置边框
            excelSheet.Range("B1" + ":K" + CStr(1)).Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone
            '设置背景色
            excelSheet.Range("B1" + ":K" + CStr(1 + 1)).Interior.ColorIndex = nTitleBakColorIndex
        Catch ex As Exception
        Finally
            If Not excelBook Is Nothing Then
                excelBook.Save()
                excelBook.Close()
            End If
            If Not excelApp Is Nothing Then
                excelApp.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
                excelSheet = Nothing
                excelBook = Nothing
                excelApp = Nothing
                GC.Collect()
            End If
        End Try

        '文件下载操作
        DownloadFile(printBookName, Me.Response)
    End Sub
2.EXCEL文件的下载.文件下载需要做二次提交来实现!
Private Sub DownloadFile(ByVal strFileName As String, ByVal Response As HttpResponse)
        Dim filePath As String
        Try
            'Web.config文件定义路径读取
            filePath = System.Configuration.ConfigurationSettings.AppSettings("DOWNLOAD_TEST")
            Response.ContentType = "application/octet-stream"
            Response.AddHeader("Content-Disposition", "attachment; FileName=" & strFileName)
            Response.Buffer = True
            Response.WriteFile(filePath & strFileName)
            Response.Flush()
            Response.Clear()
            Response.Close()
            Response.End()
        Catch ex As Exception
        End Try
    End Sub