使用 Response.Output 导出 Excel 数据

Question

我有以下代码要保存用于Excel导出：

'Export to Excel
        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=excelExport.xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Dim sw As New StringWriter()
        Dim hw As New HtmlTextWriter(sw)
        Dim grdView = New GridView
        grdView.AllowPaging = False
        'LOAD DATA
        Dim Sql As String = "SELECT * " & _
             "FROM bobSettings AS bobSettings " & _
              "INNER JOIN bobData AS bobData " & _
             "ON bobSettings.linkTobobData = bobData.linkTobobSettings " & _
             "WHERE linkTobobSettings = '1110450005' "
        Dim checkFormName As New SqlCommand(Sql, myCONN)
        myCONN.Open()
        Dim objCmd As SqlCommand
        objCmd = New SqlCommand(Sql, myCONN)
        Dim da As New SqlDataAdapter(objCmd)
        Dim ds As New DataSet()
        da.Fill(ds)
        grdView.DataSource = ds
        grdView.DataBind()
        myCONN.Close()
        'END LOADING DATA
        grdView.DataBind()
        grdView.HeaderRow.Style.Add("background-color", "#FFFFFF")
        grdView.HeaderRow.Cells(0).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(1).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(2).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(3).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(4).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(5).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(6).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(7).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(8).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(9).Style.Add("background-color", "#DFB")
        grdView.HeaderRow.Cells(10).Style.Add("background-color", "#DFB")
        For i As Integer = 0 To grdView.Rows.Count - 1
            Dim row As GridViewRow = grdView.Rows(i)
            row.BackColor = System.Drawing.Color.White
            row.Attributes.Add("class", "textmode")
            If i Mod 2 <> 0 Then
                row.Cells(0).Style.Add("background-color", "#C2D69B")
                row.Cells(1).Style.Add("background-color", "#C2D69B")
                row.Cells(2).Style.Add("background-color", "#C2D69B")
                row.Cells(3).Style.Add("background-color", "#C2D69B")
                row.Cells(4).Style.Add("background-color", "#C2D69B")
                row.Cells(5).Style.Add("background-color", "#C2D69B")
                row.Cells(6).Style.Add("background-color", "#C2D69B")
                row.Cells(7).Style.Add("background-color", "#C2D69B")
                row.Cells(8).Style.Add("background-color", "#C2D69B")
                row.Cells(9).Style.Add("background-color", "#C2D69B")
                row.Cells(10).Style.Add("background-color", "#C2D69B")
            End If
        Next
        grdView.RenderControl(hw)
        Dim style As String = "<style>.textmode{mso-number-format:@;}</style>"
        Response.Write(style)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
        ds = Nothing
        da = Nothing

它可以毫无问题或错误地执行，但我似乎找不到它放置文件的位置，或者它是否弹出一个框要求我保存它等。

我错过了什么？

Answer 1

你写

grdView.DataSource = ds

然后

ds = Nothing

然后

grdView.DataBind()

编辑：

为避免"线程正在中止"，您可以将Response.End()放入 Try Catch 块中以处理 ThreadAbort 异常。

Try
    Response.End()
Catch e As ThreadAbortException
End Try