Export to Excel user Control

  整理一下ExportExcel 用户控件的写法，写入Excel我们采用OleDb提供的MS provider。本文采用Vb.net编程

(1) 引入命名空间：

Imports System.IO
Imports System.Data.OleDb
Imports System.Data
Imports System.Diagnostics

 

(2) 声明两个代理及代理实例

   ' Declare Event
    Public Delegate Sub BindData()
    Public Event BindDataBeforeExport As BindData

 

    ' Message Event
    Public Delegate Sub SendMessage(ByVal bsR As BSResult)
    Public Event SendResultEvent As SendMessage

 

(3) Property--控件公共属性设置

   ''' <summary>
    '''  Data Source For Export
    ''' </summary>
    ''' <remarks></remarks>
    Private m_dt As DataTable = Nothing

    Public Property DataForExport() As DataTable
        Get
            Return m_dt
        End Get
        Set(ByVal value As DataTable)
            m_dt = value
        End Set
    End Property

 

    ''' <summary>
    ''' Set Sheet Size: 每个Sheet的记录数

    ''' </summary>
    ''' <remarks></remarks>
    Private m_SheetSize As Integer = 10000

    Public Property SheetSize() As Integer
        Get
            Return m_SheetSize
        End Get
        Set(ByVal value As Integer)
            m_SheetSize = value
        End Set
    End Property

    ''' <summary>
    ''' Is Need Zip
    ''' </summary>
    ''' <remarks></remarks>
    Private m_IsNeedZip As Boolean = False --是否需要压缩成ZIP

    Public Property IsNeedZip() As Boolean
        Get
            Return m_IsNeedZip
        End Get
        Set(ByVal value As Boolean)
            m_IsNeedZip = value
        End Set
    End Property

    ''' <summary>
    ''' Is Need Zip
    ''' </summary>
    ''' <remarks></remarks>
    Private m_FileName As String = String.Empty

    Public Property FileName() As String
        Get
            Return m_FileName
        End Get
        Set(ByVal value As String)
            m_FileName = value
        End Set
    End Property

    ''' <summary>
    ''' Export button if enable
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Property IsEnable() As Boolean
        Get
            If (ViewState("ENABLE") Is Nothing) Then
                ViewState("ENABLE") = True
            End If
            Return ViewState("ENABLE")
        End Get
        Set(ByVal value As Boolean)
            ViewState("ENABLE") = value
            btnExportExcel.Enabled = value
        End Set
    End Property

 

(4) Functions

   ''' <summary>
    ''' 利用OleDB,采用insert方式写入Excel中

    ''' </summary>
    ''' <param name="_dt"></param>
    ''' <param name="excelpath"></param>
    ''' <remarks></remarks>
    Public Sub DataTableToExcel(ByRef _dt As DataTable, ByVal excelpath As String, ByVal sheetSize As Integer)
        Dim _oledbconn As OleDbConnection = Nothing
        Dim _objCmd As OleDbCommand = Nothing
        Dim _rows As Integer = 0
        Dim _col As Integer = 0

        Dim _connstring As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & excelpath & ";Extended Properties=Excel 8.0;"

        ' Get Sheet Count
        Dim sheetCount As Integer = 0
        sheetCount = Integer.Parse(_dt.Rows.Count) / sheetSize
        If (Integer.Parse(_dt.Rows.Count) Mod sheetSize) > 0 Then
            sheetCount += 1
        End If

        For iCount As Integer = 0 To sheetCount - 1
            Dim _sb As StringBuilder = New StringBuilder()
            _rows = _dt.Rows.Count
            _col = _dt.Columns.Count
            '_connstring = string.Format(ConnectionString,excelPath);

            _sb.Append("CREATE TABLE ")
            _sb.Append("Sheet" + (iCount + 1).ToString + " ( ")

            For i As Integer = 0 To _col - 1
                If (i < _col - 1) Then
                    _sb.Append(String.Format("[{0}]  varchar, ", _dt.Columns(i).ColumnName))
                Else
                    _sb.Append(String.Format("[{0}] varchar)", _dt.Columns(i).ColumnName))
                End If

            Next
            _oledbconn = New OleDbConnection(_connstring)
            _objCmd = New OleDbCommand()
            _objCmd.Connection = _oledbconn
            _objCmd.CommandText = _sb.ToString()
            Try
                _oledbconn.Open()
                _objCmd.ExecuteNonQuery()
            Catch ex As Exception
                Dim err As String
                err = "在Excel中创建表失败，错误信息：" + ex.Message
            End Try
            Try

                Dim _sql As String
                _sql = "INSERT INTO Sheet" + (iCount + 1).ToString + "("
                For i As Integer = 0 To _col - 1
                    If (i < _col - 1) Then
                        _sql += "[" + _dt.Columns(i).ColumnName + "],"
                    Else
                        _sql += "[" + _dt.Columns(i).ColumnName + "]) values ("
                    End If
                Next
                Dim iStart As Integer = (iCount) * sheetSize
                For i As Integer = iStart To (iCount + 1) * sheetSize - 1
                    Dim s As String = ""
                    For j As Integer = 0 To _col - 1
                        If (j < _col - 1) Then
                            s += "'" + _dt.Rows(i)(_dt.Columns(j)).ToString().Replace("'", "''") + "',"
                        Else
                            s += "'" + _dt.Rows(i)(_dt.Columns(j)).ToString().Replace("'", "''") + "')"
                        End If
                    Next
                    s = _sql + s
                    _objCmd.CommandText = s
                    _objCmd.ExecuteNonQuery()

                    If i = _dt.Rows.Count - 1 Then
                        Exit For
                    End If

                    If i <> 0 And (i + 1) Mod (sheetSize) = 0 Then
                        Exit For
                    End If
                Next

            Catch ex As Exception
                Return
            Finally
                _objCmd.Dispose()
                _oledbconn.Close()
                _oledbconn = Nothing
            End Try
        Next
    End Sub

    ''' <summary>
    ''' Down File
    ''' </summary>
    ''' <param name="filename"></param>
    ''' <param name="path"></param>
    ''' <remarks></remarks>
    Private Sub DownFile(ByVal filename As String, ByVal path As String)
        Try
            Response.ClearHeaders()
            Response.Clear()
            Response.Expires = 0
            Response.Buffer = True
            Response.AddHeader("Accept-Language", "zh-tw")
            '文件名称
            Response.AddHeader("content-disposition", "attachment; filename=" & Chr(34) & System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) & Chr(34))
            Response.ContentType = "Application/octet-stream"
            '文件内容
            Dim fs As FileStream = New FileStream(path, FileMode.Open, FileAccess.Read)
            Dim br As BinaryReader = New BinaryReader(fs)
            Dim buffer(0 To fs.Length - 1) As Byte
            br.Read(buffer, 0, fs.Length)
            br.Close()
            fs.Close()
            Response.BinaryWrite(buffer)
            Response.Flush()
            Response.Close()

            'Delete the template file on the server
            If File.Exists(path) Then
                Dim fFile As New FileInfo(path)
                fFile.Delete()
            End If
        Catch ex As Exception

        End Try

    End Sub

    ''' <summary>
    ''' Compress File
    ''' </summary>
    ''' <param name="strSrcFile"></param>
    ''' <param name="strDestPath"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CompressFile(ByVal strSrcFile As String, ByVal strDestPath As String) As Integer
        Try
            Dim myProcess As New Process
            myProcess.StartInfo.WindowStyle = ProcessWindowStyle.Hidden
            myProcess.StartInfo.FileName = "zip.exe"
            '-j -r
            myProcess.StartInfo.Arguments = "-j -r " + strDestPath + " " + strSrcFile
            myProcess.Start()
            myProcess.WaitForExit()
            If myProcess.ExitCode = 0 Then
                myProcess.Close()
                Return 1
            Else
                myProcess.Close()
                Return 0
            End If
        Catch ex As Exception
            Return 0
        End Try
        Return 1
    End Function

    'Page Load
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If (IsEnable = False) Then
            btnExportExcel.Enabled = False
        Else
            btnExportExcel.Enabled = True
        End If
    End Sub

 

(5) Export event

  ''' <summary>
    '''  Export Excel To File
    ''' </summary>
    ''' <param name="sender"></param>
    ''' <param name="e"></param>
    ''' <remarks></remarks>
    Protected Sub btnExportExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportExcel.Click
        Dim bsr As BSResult = New BSResult()
        bsr.MsgModule = "IM"
        Try
            ' Call Event
            RaiseEvent BindDataBeforeExport()

            ' Get Export File Path
            Dim strFilePath As String = Server.MapPath("~/") & System.Configuration.ConfigurationManager.AppSettings("TEMPDIR") + "ExportExcel/"

            'check if exist this file uploading directory?
            If Not Directory.Exists(strFilePath) Then
                Directory.CreateDirectory(strFilePath)
            End If

            Dim strFileName As String = String.Empty
            Dim strCompressFile As String = String.Empty

            ' DownLoad Path And Name
            Dim FileDownName As String = String.Empty
            Dim FileDownPath As String = String.Empty

            ' Check User Is Set FileName
            If FileName = String.Empty Then
                strFileName = System.Guid.NewGuid.ToString() + ".xls"
                strCompressFile = System.Guid.NewGuid.ToString() + ".zip"
            Else
                strFileName = FileName + ".xls"
                strCompressFile = FileName + ".zip"
            End If

            If Me.DataForExport.Rows.Count = 0 Then
                'Show Error Message
                bsr.ResultID = -1
                bsr.MsgType = BSResult.MessageType.ErrorMsg
                bsr.MsgModule = "IM"
                bsr.MessageID = "W00020"
            Else
                ' Down Load Excel File
                DataTableToExcel(Me.DataForExport, strFilePath + strFileName, SheetSize)

                If IsNeedZip = True Then
                    If CompressFile(strFilePath + strFileName, strFilePath + strCompressFile) = 0 Then
                        'Haven't send Data now.
                        bsr.ResultID = -1
                        bsr.MsgType = BSResult.MessageType.ErrorMsg
                        bsr.MessageID = "SH0055"
                    End If
                    FileDownName = strCompressFile
                    FileDownPath = strFilePath

                    'Delete the template file on the server
                    If File.Exists(strFilePath + strFileName) Then
                        Dim fFile As New FileInfo(strFilePath + strFileName)
                        fFile.Delete()
                    End If

                Else
                    FileDownName = strFileName
                    FileDownPath = strFilePath
                End If

                ' Down Load File
                DownFile(FileDownName, FileDownPath + FileDownName)
            End If
        Catch ex As Exception
            'Haven't send Data now.
            bsr.ResultID = -1
            bsr.MsgType = BSResult.MessageType.ErrorMsg
            bsr.MessageID = "SH0055"
        End Try
        RaiseEvent SendResultEvent(bsr)
    End Sub

 

其中代理中公布的方法：BindDataBeforeExport 是用来在主调页面用来获取该UserControl的数据源dt，而代理实例SendResultEvent 是用来在主调页面上显示该UserControl中传出的消息；