asp.net中数据倒入EXCEL方法

   

1、在asp.net项目站点下建立ExcelMod和TEMP目录，ExcelMod用于存放打印模板文件,TEMP用于存放生成的临时文件。

2、建立EXCEL倒出类。

 

    Public Class ExcelWork

        Dim cServer As HttpServerUtility

        Public Sub New(ByVal pServer As HttpServerUtility)

            cServer = pServer

        End Sub

 

‘ 参数说明：

‘pExcelModName 模板文件名称

‘pBeginRow EXCEL文件输出时，起始行

‘pBeginCol EXCEL文件输出时，起始列

‘pTable     输出内容

‘pRows      行数

‘pCols      列数

‘pPara      标头及其他参数

        Public Function CreateExcel(ByVal pExcelModName As String, ByVal pBeginRow As Integer, ByVal pBeginCol As Integer, _

        ByVal pTable As String(,), ByVal pRows As Integer, ByVal pCols As Integer, Optional ByVal pPara(,) As String = Nothing) As String

            '生成报

            Dim pExcel As Excel.Application

            Dim pSheetActive As Worksheet

            Dim pCell As Range

            Dim myFs As Object

            Dim filePos As String

            Dim fileName As String

            Dim i As Integer

            Dim j As Integer

            Try

 

                pExcel = New Excel.Application

 

                pExcel.Workbooks.Open(GetExcelModDir & pExcelModName) '打开Excel模板

                pSheetActive = pExcel.Sheets(1)

 

                '设置二级表头及其他行列外数据

                If Not pPara Is Nothing Then

                    Dim h As Int16 = 0

                    For h = 0 To pPara.Length / 2 - 1

                        pCell = pSheetActive.Range(pPara(h, 0))

                        pCell.Value = pPara(h, 1)

                    Next

                End If

 

                For i = 0 To pRows

                    For j = 0 To pCols

                        pCell = pSheetActive.Cells(i + pBeginRow, j + pBeginCol)

                        pCell.Value = pTable(i, j)

                        pCell.BorderAround(1)

                    Next

                Next

 

                myFs = CreateObject("scripting.FileSystemObject")

 

                filePos = GetTempDir '要存放打印临时文件的临时目录

 

                fileName = getTemporaryFile(myFs) '取得一个临时文件名

 

                Try

                    myFs.DeleteFile(filePos & "*.xls") '删除该目录下所有原先产生的临时打印文件

                Catch ex As Exception

 

                End Try

 

                myFs = Nothing

 

                pSheetActive = Nothing

                pExcel.ActiveWorkbook.SaveAs(filePos & fileName)

                pExcel.ActiveWorkbook.Close()

 

                pExcel.Quit()

                pExcel = Nothing

 

                GC.Collect()

 

                Return fileName

            Catch ex As Exception

                MsgBox(ex.Message.ToString, MsgBoxStyle.Information, "运行错误")

                Return ""

            End Try

        End Function

 

        Private ReadOnly Property GetTempDir()

            Get

                Return cServer.MapPath("\ProduceManage") & "\Temp\"

            End Get

        End Property

 

        Private ReadOnly Property GetExcelModDir()

            Get

                Return cServer.MapPath("\ProduceManage") & "\ExcelMod\"

            End Get

        End Property

 

        Private Function getTemporaryFile(ByVal myFileSystem) As String

            Dim pTmpf As String = ""

            Dim pFile, pPos As String

            Try

                pFile = myFileSystem.getTempName

                pPos = InStr(1, pFile, ".")

                pTmpf = Mid(pFile, 1, pPos) & "xls"

            Catch ex As Exception

 

            End Try

            Return pTmpf

        End Function

 

        Private Function GetServerIP() As String

            Dim pIPHostEntry As Net.IPHostEntry

            Dim ip As Net.IPAddress

            Dim pMyIp As String = ""

            Try

                pIPHostEntry = Net.Dns.GetHostByName(cServer.MachineName)

                For Each ip In pIPHostEntry.AddressList

                    pMyIp = ip.ToString

                Next

            Catch ex As Exception

            Finally

                ip = Nothing

                pIPHostEntry = Nothing

            End Try

            Return pMyIp

        End Function

    End Class

（3）在页面中调用CreateExcel函数，生成对应EXCEL文件，并根据返回的文件名称，下载生成的文件到客户端，并显示。

 

可在项目中，加入一个公用页面，用于下载/显示倒出的EXCEL文件页面加载时，加入如下方法      

               Dim pExcelFile = Me.Session("excelfile")

                Dim pFs As FileStream

                pFs = File.OpenRead(Me.Server.MapPath("\ProduceManage\temp\" & pExcelFile))

                Dim pBt(pFs.Length) As Byte

                pBt(pFs.Length) = New Byte

                pFs.Read(pBt, 0, pBt.Length)

                pFs.Close()

                Response.AddHeader("Content-Disposition", "attachment; filename=" & pExcelFile)

                Response.BinaryWrite(pBt)

                Response.ContentType = "application/ms-excel"