VB.NET导出Excel 轻松实现Excel的服务器与客户端交换 服务器不安装Office

说来VB.Net这个也是之前的一个项目中用到的。今天拿来总结下用途，项目需求，不让在服务器安装Office办公软件。这个也是煞费了一顿.

主要的思路就是 在导出的时候，利用DataTable做中间变量导出

下边这个是使用时候的代码 getData()方法是我获取的DataTable "defaultPickUpLocation" 是表的名字

    '導出Excel
    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnExport.Click
        Dim dt As DataTable = getData()
        GridViewHelper.ExportToExcel(dt, "defaultPickUpLocation_")
    End Sub

下边接下来是导出的方法 GridView 是自定义的类文件 

        Shared Sub ExportToExcel(ByVal dataTable As Data.DataTable, ByVal fileName As String)
            For Each dataRow As DataRow In dataTable.Rows
                For i As Integer = 0 To dataRow.ItemArray.Count - 1
                    If Not IsDBNull(dataRow.Item(i)) Then
                        dataRow.Item(i) = removeHTMLTagat(CType(dataRow.Item(i), String))
                    End If
                Next
            Next
            Export2Excel(dataTable, fileName)

因为数据库中总有一些是Table的字节 现在要删除

        Private Shared Function removeHTMLTagat(ByVal HTML As String) As String
            Dim Codehtml() As KeyValuePair(Of String, String) = New KeyValuePair(Of String, String)() {
         New KeyValuePair(Of String, String)("<", "<"),
         New KeyValuePair(Of String, String)(">", ">"),
         New KeyValuePair(Of String, String)(" ", " ")
         }
            For Each keyValuePair As KeyValuePair(Of String, String) In Codehtml
                Dim regex As New Regex(keyValuePair.Key)
                HTML = regex.Replace(HTML, keyValuePair.Value)
            Next
            Dim aryReg() As String = New String() {
                      "<[^>]*>"}
            For Each pattern As String In aryReg
                Dim regex As New Regex(pattern)
                HTML = regex.Replace(HTML, "")
            Next
            Return HTML
        End Function

还有Export2Excel的具体导出Excel的方法

        Private Shared Sub Export2Excel(ByVal _dataTable As DataTable, ByVal _filename As String)
            Dim fileName = String.Format("{0}{1}.xls", _filename, DateTime.Now.ToString("yyMMddHHmmssffff"))
            Dim sNewFullFile = String.Format("{0}Downfiles/{1}", HttpContext.Current.Request.PhysicalApplicationPath, fileName)
            File.Copy(String.Format("{0}format_files/{1}format.xls", HttpContext.Current.Request.PhysicalApplicationPath, _filename), sNewFullFile)
            Dim strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=0'"
            Dim conn = New OleDbConnection(strConn)
            Dim cmd As OleDbCommand = Nothing
            Dim bRet = False
            conn.Open()
            Dim insertDataStr = "INSERT INTO [Sheet1$] ("
            Dim insertValueStr = "values("
            For i As Integer = 0 To _dataTable.Columns.Count - 1
                insertDataStr = String.Format("{0} [{1}],", insertDataStr, _dataTable.Columns(i).ColumnName)
                insertValueStr = String.Format("{0}{1},", insertValueStr, "?")
            Next
            insertValueStr = String.Format("{0})", insertValueStr.Trim(","c))
            insertDataStr = String.Format("{0}) {1}", insertDataStr.Trim(","c), insertValueStr)

            cmd = New OleDbCommand(insertDataStr, conn)
            For i As Integer = 0 To _dataTable.Columns.Count - 1
                cmd.Parameters.Add(i.ToString(), OleDbType.LongVarChar)
            Next
            For Each row As DataRow In _dataTable.Rows
                For i As Integer = 0 To _dataTable.Columns.Count - 1
                    Dim value = row(_dataTable.Columns(i).ColumnName).ToString()
                    If value.Length > 100 Then
                        value = String.Format("{0}……", value.Substring(0, 100))
                    End If

                    cmd.Parameters(i).Value = value
                Next
                cmd.ExecuteNonQuery()
            Next
            bRet = True

            cmd.Dispose()
            conn.Dispose()
            If bRet Then
                HttpContext.Current.Response.Redirect(String.Format("Downfiles/{0}", fileName))
            End If

        End Sub

现在就写了这个文件，等把导入再写。如有问题 请回复  祝君成功。

转载于:https://www.cnblogs.com/kangzi/p/4127060.html