Link to home
Create AccountLog in
Avatar of CityGrl
CityGrl

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!
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

Open in new window

Avatar of alexpercsi
alexpercsi
Flag of Romania image

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
Avatar of CityGrl
CityGrl

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?
Avatar of CityGrl

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()

Open in new window

Avatar of CityGrl

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.
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!
Avatar of CityGrl

ASKER

I'm sorry Alex but I'm not sure what you mean - do you have a code snippet?
ASKER CERTIFIED SOLUTION
Avatar of alexpercsi
alexpercsi
Flag of Romania image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
Avatar of CityGrl

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!
Avatar of CityGrl

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.
Avatar of CityGrl

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.