Please help me export a GridView to Excel and then programmatically save the output to disk.

Hi Nelson3000,

You have to use Response.Write first to push the content to the file/stream.

Response.WriteFile accepts a file name as a parameter not the content.
https://docs.microsoft.com/en-us/dotnet/api/system.web.httpresponse.writefile?redirectedfrom=MSDN&view=netframework-4.7.2#System_Web_HttpResponse_WriteFile_System_String_

In which folder on your web server the files are stored? If you are storing them under a predetermined folder, you can just use

string folder ="YOUR VALID FOLDER PATH";
Response.Write(swReport.ToString())
Response.WriteFile(string.Concat(folder, "//", UNIQUEFILENAME);

Select allOpen in new window

Regards,
Chinmay.

Hi Paul,

Thanks for your recommendation.

I incorporated it into my routine along with a change to use a StreamWriter to write the file to disk
I also added: Response.AddHeader("X-Download-Options", "noopen")
The XLS file is created and I can open it in Excel.
But as you warned, I still get a file "Save or Open" prompt.


The newly revised routine is as follows:


 Sub ExportSpreadsheet(dtExport As DataTable)

        Dim gvReport As New GridView()

        gvReport.AllowPaging = False

        '-- Load the GridView
        gvReport.DataSource = dtExport
        gvReport.DataBind()

        Response.Clear()
        Response.Buffer = True

        dim strExportPathAndFilename as string = "C:\Allied\TestExport_" & Now().ToString("yyyyMMdd-HHmmss") & ".xls"

        Response.AddHeader("content-disposition", "attachment;filename=" & Path.GetFileName(strExportPathAndFilename))
        Response.AddHeader("X-Download-Options", "noopen")
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"

        Dim swReport As New StringWriter()
        Dim hwReport As New HtmlTextWriter(swReport)

        For intRow As Integer = 0 To gvReport.Rows.Count - 1
            '-- Apply text style to each Row
            gvReport.Rows(intRow).Attributes.Add("class", "textmode")
        Next

        gvReport.RenderControl(hwReport)

        '-- Format numbers to string
        Dim strStyle As String = "<style> .textmode{mso-number-format:\@;}</style>"
        Response.Write(strStyle)


        '-- New Method: Write XLS-file to disk
        Dim streamReport As New StreamWriter(strExportPathAndFilename)
        streamReport.Write(swReport.ToString())
        streamReport.Close()
        Response.Flush()

    End Sub


So, I am getting closer, but now need to suppress the "Save or Open" window.

Also, thanks for the write-up on EPPlus.
I recognize that this HTML method is not really an Excel file.
However, I am dealing with a situation where I cannot install any software on the server.
I originally wanted to install Interop.Office.Excel, but my request was rejected.
Hence, I needed a simple workaround.

Meanwhile, I would appreciate if you could please elaborate on your comment about how I can eliminate the "Save or Open" prompt.

Thanks Very Much,

Nelson

Paul and Chinmay,

I need to rethink my approach to this matter.

Thanks again for your help.  Your recommendations were very enlightening.

Best Regards,

Nelson