asked on
ASP.NET GridView to Excel, CSV or Access
Hello Experts!
I am trying to convert the data that is in a GridView to either Excel or a CSV file. Ultimately, the data is going to be imported into Microsoft Access. Essentially, it doesn't matter how this is done, as long as the data is legible, correct and able to be imported into an Access database.
I have tried the code below, and it does work. But when I try to load the data into excel or import the data into Access I get the following error message:
"The file you are trying to open is in a different format than specified by the extension."
Also, if you open the file in notepad, the data is in HTML format, not CSV.
Any help in getting the data from the gridview into either Excel, CSV or Access would be greatly appreciated!
I am trying to convert the data that is in a GridView to either Excel or a CSV file. Ultimately, the data is going to be imported into Microsoft Access. Essentially, it doesn't matter how this is done, as long as the data is legible, correct and able to be imported into an Access database.
I have tried the code below, and it does work. But when I try to load the data into excel or import the data into Access I get the following error message:
"The file you are trying to open is in a different format than specified by the extension."
Also, if you open the file in notepad, the data is in HTML format, not CSV.
Any help in getting the data from the gridview into either Excel, CSV or Access would be greatly appreciated!
Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)
Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=" & strFileName)
Response.Charset = ""
Me.EnableViewState = False
Dim oStringWriter As New System.IO.StringWriter
Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter)
gvHistory.RenderControl(oHtmlTextWriter)
Response.Write(oStringWriter.ToString())
Response.[End]()
End Sub
Protected Sub btnCSV_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCSV.Click
ExportToExcel("Report.xls", gvHistory)
End Sub
.NET ProgrammingASP.NETMicrosoft Excel
Last Comment
nmarun
Can't you export the datasource to excel or a csv instead?
there are already a lot of questions related to such export on the EE and here is one of them
https://www.experts-exchange.com/questions/24175131/gridview-exporting-to-excel.html
https://www.experts-exchange.com/questions/24175131/gridview-exporting-to-excel.html
ASKER
Sorry for my late response but I've been on vacation. THank you for all the links that you posted but they are exactly what I have listed above. The code above does NOT export to csv, it exports to HTML which can be read by excel. THis is not what I want. I would like code to export my datagrid to a csv file. Any ideas?
ASKER
Ok, I tried this but now I've run into another issue - it is trying to save my file on the server. I need the file to be saved on the users machine. Any help would be appreciated!
Dim objStreamWriter As IO.StreamWriter
'Pass the file path and the file name to the StreamWriter constructor.
'make sure this is a path that you have permissions to save in
objStreamWriter = New IO.StreamWriter(strFileName)
'Write text.
Dim Str As String
Dim i As Integer
Dim j As Integer
Dim headertext = "field1,field2,field3,field4,field5,field5,field6"
objStreamWriter.WriteLine(headertext)
For i = 0 To (dg.Rows.Count - 1)
For j = 0 To (dg.Columns.Count - 1)
'this IF statement stops it from adding a comma after the last field
If j = (dg.Columns.Count - 1) Then
Str = (dg.Rows(i).Cells(j).Text.ToString)
Else
Str = (dg.Rows(i).Cells(j).Text.ToString & ",")
End If
objStreamWriter.Write(Str)
Next
objStreamWriter.WriteLine()
Next
'Close the file.
objStreamWriter.Close()
ASKER
That's the same as what I have in my original post.
You will have to persist that file on to the server before it is available for download. Here's something else you could do:
Export all data to the file, save it to the server and then display a hyper link to the user which points to this file. So when the user clicks on the link, it'll prompt them to download the file.
Export all data to the file, save it to the server and then display a hyper link to the user which points to this file. So when the user clicks on the link, it'll prompt them to download the file.
Why don't you just clear the Response and write the file to it instead of another StreamWriter? Be sure to set the right ContentType though!
ASKER
I'm sorry Alex but I'm not sure what you mean - do you have a code snippet?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
No offense, CityGrl, but since we gave you the solution, I think it would be fair to award the points (split them to the people who have helped you), not delete the question. Wouldn't you agree?
I completely agree with Alex. Also, this thread has some valid posts, deleting this question would not help the others who are in search of similar answers.
ASKER
Sorry, I thought that I did award the points. I awarded all the points to Alex as everyone elses posts pointed to the same thing that I posted originally. I will try this again... this is the first time that I've tried to do this... again sorry!
ASKER
Ok, I'm confused but will do as you say. My answer really IS the solution to the question I asked. It is the solution that I implemented and perfectly answers my question. Alex assisted me with that solution, so I'm not sure why I can't choose my solution and award him the points. Anyway, thank you everyone for your help and I apologize for not understanding how this system works.
ASKER
Just to clarify... what I tried to do was Accept my post as the solution and award all the points to Alex. I'm not sure if that is what actually happened but I definitely clicked the Accept and Award Points button. Again, I apologize and hope that everything is ok now! :)
That IS the right process CityGrl.