Nelson3000
asked on
Please help me export a GridView to Excel and then programmatically save the output to disk.
I need to export the contents of a GridView to Excel.
The XLS file needs to be saved to disk because I will be emailing it.
Also, all exports are stored on the server for 30-days.
Using the following code, I appear to be able to export the GridView contents, but I am unable to save the file.
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:\TestExport.xls"
Response.AddHeader("conten t-disposit ion", "attachment;filename=" & strExportPathAndFilename)
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).Attr ibutes.Add ("class", "textmode")
Next
gvReport.RenderControl(hwR eport)
'-- Format numbers to string
Dim strStyle As String = "<style> .textmode{mso-number-forma t:\@;}</st yle>"
Response.Write(strStyle)
'-- Write XLS-file to disk
Response.WriteFile(swRepor t.ToString ())
Response.Flush()
End Sub
The routine terminates on my Response.WriteFile() command.
The error message indicates that I am not providing "a valid virtual path"
So how do I specify both the FileName and data to be exported, using Response.WriteFile?
Any suggestions on what I need to do would be greatly appreciated.
Many Thanks,
Nelson
The XLS file needs to be saved to disk because I will be emailing it.
Also, all exports are stored on the server for 30-days.
Using the following code, I appear to be able to export the GridView contents, but I am unable to save the file.
Sub ExportSpreadsheet(dtExport
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:\TestExport.xls"
Response.AddHeader("conten
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).Attr
Next
gvReport.RenderControl(hwR
'-- Format numbers to string
Dim strStyle As String = "<style> .textmode{mso-number-forma
Response.Write(strStyle)
'-- Write XLS-file to disk
Response.WriteFile(swRepor
Response.Flush()
End Sub
The routine terminates on my Response.WriteFile() command.
The error message indicates that I am not providing "a valid virtual path"
So how do I specify both the FileName and data to be exported, using Response.WriteFile?
Any suggestions on what I need to do would be greatly appreciated.
Many Thanks,
Nelson
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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-Down load-Optio ns", "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-H Hmmss") & ".xls"
Response.AddHeader("conten t-disposit ion", "attachment;filename=" & Path.GetFileName(strExport PathAndFil ename))
Response.AddHeader("X-Down load-Optio ns", "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).Attr ibutes.Add ("class", "textmode")
Next
gvReport.RenderControl(hwR eport)
'-- Format numbers to string
Dim strStyle As String = "<style> .textmode{mso-number-forma t:\@;}</st yle>"
Response.Write(strStyle)
'-- New Method: Write XLS-file to disk
Dim streamReport As New StreamWriter(strExportPath AndFilenam e)
streamReport.Write(swRepor t.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
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-Down
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
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-H
Response.AddHeader("conten
Response.AddHeader("X-Down
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).Attr
Next
gvReport.RenderControl(hwR
'-- Format numbers to string
Dim strStyle As String = "<style> .textmode{mso-number-forma
Response.Write(strStyle)
'-- New Method: Write XLS-file to disk
Dim streamReport As New StreamWriter(strExportPath
streamReport.Write(swRepor
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
ASKER
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
I need to rethink my approach to this matter.
Thanks again for your help. Your recommendations were very enlightening.
Best Regards,
Nelson
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
In which folder on your web server the files are stored? If you are storing them under a predetermined folder, you can just use
Open in new window
Regards,
Chinmay.