如何在ASP.NET中导出Excel工作表 [英] how to export excel sheet in asp.net

问题描述

我使用此代码可以正常工作，但是它导出的excel报告少于给定的一条记录

请任何人告诉..


谢谢

i am using this code its work fine but it export excel report less than one record which given

Pleser any one tell ..


thanks

Private Sub ExportReport(ByVal dr As SqlDataReader, ByVal type As Integer, ByVal Header As String)
        Dim iMileStone As Integer = 0
        Try

            sessionBusinessType = Session("BusinessID")
            iMileStone = 1010
            Dim Report As String = sessionBusinessType
            Dim dg As New DataGrid()
            iMileStone = 1011

            Dim dtSchema As System.Data.DataTable = dr.GetSchemaTable()


            iMileStone = 10111
            Dim dt As System.Data.DataTable = New DataTable()
            iMileStone = 10112
            ' You can also use an ArrayList instead of List<>
            Dim listCols As List(Of DataColumn) = New List(Of DataColumn)()
            iMileStone = 10113
            Dim Count As Integer = 0
            Dim remove As Integer = 0
            Dim ColumnValue As Integer = 0
            iMileStone = 10114
            If Not dtSchema Is Nothing Then
                iMileStone = 1012
                For Each drow As DataRow In dtSchema.Rows
                                        Dim columnName As String = System.Convert.ToString(drow("ColumnName"))
                    Select Case columnName

                        Case "ETADisport"
                            columnName = "ETA"

                        Case "Vessel"
                            columnName = "Vessel"
                        Case "Dwt"
                            columnName = "Dwt"
                        Case "Built"
                            columnName = "Built"
                        Case "HullType"
                            columnName = "Hull"
                        Case "Disport"
                            columnName = "Disport"
                        Case "Owner"
                            columnName = "Owner"
                    End Select
                    iMileStone = 1013
                                        Dim column As DataColumn = New DataColumn(columnName)
                    listCols.Add(column)
                    dt.Columns.Add(column)
                                        Count = Count + 1
                    iMileStone = 1014
                Next drow
            End If
            iMileStone = 1015
            ' Read rows from DataReader and populate the DataTable

            Do While dr.Read()
                Dim dataRow As DataRow = dt.NewRow()
                For i As Integer = 0 To listCols.Count - 1
                    dataRow((CType(listCols(i), DataColumn))) = dr(i)
                Next i
                dt.Rows.Add(dataRow)
            Loop
            iMileStone = 1016
            If type = 1 Then
                'for .xls


                Dim dsExport As New DataSet()
                Dim tw As New System.IO.StringWriter()
                Dim hw As New System.Web.UI.HtmlTextWriter(tw)
                Dim dgGrid As New DataGrid()
                dgGrid.DataSource = dt
                iMileStone = 1017
                'Report Header
                hw.WriteLine("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
                                                         hw.WriteLine("<b><font size='4'> " & Header & " </font></b>")
                hw.WriteBreak()
                hw.WriteBreak()
                iMileStone = 1018
                '   Get the HTML for the control.
                dgGrid.HeaderStyle.Font.Bold = True
                dgGrid.HeaderStyle.BackColor = Drawing.Color.DarkSeaGreen
                dgGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Left
                dgGrid.DataBind()
                dgGrid.RenderControl(hw)
                iMileStone = 1019
                Dim strPath As String = Server.MapPath("~") & "\Report\"
                Dim dir As New System.IO.DirectoryInfo(strPath)
                Dim ArrayFile As Array = dir.GetFiles()

              
                For Each File1 In ArrayFile
                    If File1.CreationTime < Now.AddMinutes(-5) Then
                        System.IO.File.Delete(File1.fullname)
                    End If

                Next

                Dim style As String = "<style>.textmode{mso-number-format:\@;}</style>"
                Dim Path As String = DateTime.UtcNow.AddHours(5).AddMinutes(30).ToString("dd-MMM-yyyy-HH-mm-ss") & ".xls"
                System.IO.File.WriteAllText(strPath & Path, style & tw.ToString())


                                ''==========for html to excel==============
                Response.ContentType = "application/vnd.ms-excel"
                iMileStone = 1012
                Me.EnableViewState = False
                Response.Clear()
                Response.Write(tw.ToString())
                            iMileStone = 1021
                HttpContext.Current.ApplicationInstance.CompleteRequest()
                iMileStone = 1022
                Response.[End]()
                iMileStone = 1023


            Else
                iMileStone = 1024  ' For Word
            End If
            dr.Close()


        Catch ex As Exception
            Logs.WriteErrorLog("TankerPositionPreview.aspx.vb | ExportReport " + ex.Message + iMileStone.ToString)
        End Try

    End Sub

推荐答案

您可以使用真正低级的Open XML SDK(您必须是专家才能使用它)...，也可以为此购买一个库或导出Excel能够导入的内容.

You can use Open XML SDK which is really low level (you have to be an expert to use it)... or you could buy a library for that or export something that Excel is able to import.

这篇关于如何在ASP.NET中导出Excel工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！