ASP.NET 4 (VB) export to Excel issue freezing screen so other messages don't show.

[DougP]

I have code to export to Excel, in Green highlight below. this works perfect.
I added message in a label (yellow highlight) to show how many records to expect. This does nothing. Something with the page refresh and the Save or Open box showing at the bottom of the page will not let anything on screen occur.
If I skip the green code using the "Exit sub" then the message in the yellow code works just fine.
Can someone guide me ?
TIA

       'fill grid with data based on options choosen
        Dim TheMonth As String = ""
        TheMonth = Trim(Left(Me.ddlChooseMonth.Text, InStr(1, Me.ddlChooseMonth.Text, " -") - 1))

        Dim TheVendor As String = ""
        Dim SOWTracker As String = ""
        Dim queryTrans As String

        If Me.ddlChooseVendor.Text <> "Choose..." Then
            TheVendor = Trim(Left(Me.ddlChooseVendor.Text, InStr(1, Me.ddlChooseVendor.Text, " -") - 1))
            SOWTracker = Trim(Right(Me.ddlChooseVendor.Text, Len(Me.ddlChooseVendor.Text) - InStr(1, Me.ddlChooseVendor.Text, "- ")))
        End If
        Dim TheYear As String = Me.ddlChooseYear.Text
        If TheMonth = 13 Then
           queryTrans = YearToDate()
        Else
            queryTrans = CreateExportToExcelSQLString(TheVendor, SOWTracker, TheMonth, TheYear)
[highlight #FCE94F]            Me.lblRecordsExported.Text = CountRecords(TheVendor, SOWTracker, TheMonth, TheYear)
            Me.lblRecordsExported.Visible = True[/highlight]
        End If

        'Exit Sub

        Dim tbl As DataTable
        tbl = PopulateGridwithSQL(queryTrans)
        Dim pck As ExcelPackage = New ExcelPackage()
        Dim Vendorname As String = Right(ddlChooseVendor.Text, Len(ddlChooseVendor.Text) - InStr(1, ddlChooseVendor.Text, "- "))
        If Vendorname = "Choose..." Then
            Vendorname = ""
        Else
            Vendorname = Vendorname & "_"
        End If
        Dim Monthname As String = Trim(Right(Me.ddlChooseMonth.Text, Len(Me.ddlChooseMonth.Text) - InStr(1, Me.ddlChooseMonth.Text, "- ")))
        Dim MonthYear As String = Monthname & "_" & Me.ddlChooseYear.Text
        Dim ExcelFilename As String = Vendorname & MonthYear & " SOW Time Reporting"
        Dim Tabname As String = Vendorname & MonthYear

        'Create the worksheet
[highlight #8AE234]        Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add(Tabname)

        'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
        ws.Cells("A1").LoadFromDataTable(tbl, True)

        'Write it back to the client
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + ExcelFilename + ".xlsx")
        HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray())
        HttpContext.Current.Response.End()

        pck = Nothing[/highlight]

DougP

 

[DougP]

I was using this code to get the results from a datagrid to Excel. But when there were around 18,000 rows it crashed the whole page.
Is there a way to combine the top with this and pass the Datatable to this code below? somehow?

        Dim sw As New StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(sw)
        Dim frm As HtmlForm = New HtmlForm()

        'sample file name PT12B009_Sept_2012_SOW Time Reporting
        Dim Vendorname As String = Right(ddlChooseVendor.Text, Len(ddlChooseVendor.Text) - InStr(1, ddlChooseVendor.Text, "- "))
        Dim Monthname As String = Trim(Right(Me.ddlChooseMonth.Text, Len(Me.ddlChooseMonth.Text) - InStr(1, Me.ddlChooseMonth.Text, "- ")))
        Dim MonthYear As String = Monthname & "_" & Me.ddlChooseYear.Text
        Dim ExcelFilename As String = Vendorname & "_" & MonthYear & "_SOW Time Reporting"

        Page.Response.AddHeader("content-disposition", "attachment;filename=" & ExcelFilename & ".xls")
        Page.Response.ContentType = "application/vnd.ms-excel"
        Page.Response.Charset = ""
        Page.EnableViewState = False
        frm.Attributes("runat") = "server"
        Controls.Add(frm)
        frm.Controls.Add(GridView1)
        frm.RenderControl(hw)
        Response.Write(sw.ToString())
        Response.End()

DougP