I have a simple page where there is one Gridview and I am trying to export to CSV, have tried several code snippets around the web and in here and keep having the same result, it will save the file, but will not show the data I just get the header row (comma
separated), then a series of ',,,,' where the data form the gridview should be.
Only major thing I see that may be causing an issue is the underlying Datasource for the Gridview has the sourced of a Stored Procedure on an SQL Server.
Any help figuring out what is going on here greatly appreciated.
Protected Sub btnExportCSV_Click(sender As Object, e As EventArgs) Handles btnExportCSV.Click
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv")
Response.Charset = ""
Response.ContentType = "application/text"
GridView1.AllowPaging = False
GridView1.DataBind()
Dim sb As New StringBuilder()
For k As Integer = 0 To GridView1.Columns.Count - 1
'add separator
sb.Append(GridView1.Columns(k).HeaderText + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
For i As Integer = 0 To GridView1.Rows.Count - 1
For k As Integer = 0 To GridView1.Columns.Count - 1
'add separator
sb.Append(GridView1.Rows(i).Cells(k).Text + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
Next
Response.Output.Write(sb.ToString())
Response.Flush()
Response.End()
End Sub
It would be much more easy if you run your Stored Procedure again and get data from there than deal with Gridview data already bound.
There are many ways to get data from Gridview but it depends, if using Boundfields, Templatefield, AutoGenerated Columns, etc. And you have to explore gridview properties to find out what best fit for you.
In your case, using template fields you have to use FindControl
Dim sb As New StringBuilder
Dim HeaderCSV As String = String.Join(",", (From p As TableCell In GridView1.HeaderRow.Cells Select p.Text).ToArray)
sb.AppendLine(HeaderCSV)
For Each grdRow As GridViewRow In GridView1.Rows
If grdRow.RowType = DataControlRowType.DataRow Then
Dim Payroll_ID As String = CType(grdRow.Cells(0).FindControl("lbl_PayrollID"), Label).Text
Dim Employee As String = CType(grdRow.Cells(1).FindControl("Label1"), Label).Text
Dim Allotment As String = CType(grdRow.Cells(2).FindControl("Label2"), Label).Text
Dim Time_Taken As String = CType(grdRow.Cells(2).FindControl("Label3"), Label).Text
Dim Time_Available As String = CType(grdRow.Cells(2).FindControl("Label4"), Label).Text
sb.AppendLine(Payroll_ID & "," & Employee & "," & Allotment & "," & Time_Taken & "," & Time_Available)
End If
Next
In the click event of button, it is not feasible to get the cell value through "GridView1.Rows(i).Cells(k).Text".
You need to specify the value of a specific control in this row.
Please refer to below codes :
Protected Sub btnExportCSV_Click(sender As Object, e As EventArgs) Handles btnExportCSV.Click
Response.Clear()
Response.Buffer = True
Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv")
Response.Charset = ""
Response.ContentType = "application/text"
GridView1.AllowPaging = False
GridView1.DataBind()
Dim sb As New StringBuilder()
For k As Integer = 0 To GridView1.Columns.Count - 1
'add separator
sb.Append(GridView1.Columns(k).HeaderText + ","c)
Next
'append new line
sb.Append(vbCr & vbLf)
For i As Integer = 0 To GridView1.Rows.Count - 1
sb.Append((CType(GridView1.Rows(i).FindControl("lbl_PayrollID"), Label)).Text + ","c)
sb.Append((CType(GridView1.Rows(i).FindControl("Label1"), Label)).Text + ","c)
sb.Append((CType(GridView1.Rows(i).FindControl("Label2"), Label)).Text + ","c)
'append new line
sb.Append(vbCr & vbLf)
Next
Response.Output.Write(sb.ToString())
Response.Flush()
Response.End()
End Sub
Member
3 Points
6 Posts
Gridview to CSV issue no data
Jul 19, 2019 04:55 PM|Menorel|LINK
I have a simple page where there is one Gridview and I am trying to export to CSV, have tried several code snippets around the web and in here and keep having the same result, it will save the file, but will not show the data I just get the header row (comma separated), then a series of ',,,,' where the data form the gridview should be.
Only major thing I see that may be causing an issue is the underlying Datasource for the Gridview has the sourced of a Stored Procedure on an SQL Server.
Any help figuring out what is going on here greatly appreciated.
Gridview Markup
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" DataSourceID="SQLAllotmentList" ForeColor="Black" GridLines="Vertical" style="text-align: right; background-color: #FFFFFF; font-size: small;"> <AlternatingRowStyle BackColor="#CCCCCC" /> <Columns> <asp:TemplateField HeaderText="Payroll ID"> <ItemTemplate> <asp:Label ID="lbl_PayrollID" runat="server" Text='<%# Eval("PayRollID") %>' style="font-weight: 700"></asp:Label> </ItemTemplate> <HeaderStyle HorizontalAlign ="Left" /> <ItemStyle HorizontalAlign="Left" /> </asp:TemplateField> <asp:TemplateField HeaderText="Employee"> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Employee") %>' style="font-weight: 700"></asp:Label> </ItemTemplate> <HeaderStyle HorizontalAlign ="Left" /> <ItemStyle HorizontalAlign="Left" /> </asp:TemplateField> <asp:TemplateField HeaderText="Allotment"> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Eval("PTO_RoundDown") %>'></asp:Label> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:TemplateField HeaderText="Time Taken"> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Eval("TimeTaken") %>'></asp:Label> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> </asp:TemplateField> <asp:TemplateField HeaderText="Time Available"> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Eval("TimeAvailable_RoundDown") %>'></asp:Label> </ItemTemplate> <ItemStyle HorizontalAlign="Center" /> </asp:TemplateField> </Columns> <FooterStyle BackColor="#CCCCCC" /> <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" /> <SortedAscendingCellStyle BackColor="#F1F1F1" /> <SortedAscendingHeaderStyle BackColor="#808080" /> <SortedDescendingCellStyle BackColor="#CAC9C9" /> <SortedDescendingHeaderStyle BackColor="#383838" /> </asp:GridView>Code Behind
Protected Sub btnExportCSV_Click(sender As Object, e As EventArgs) Handles btnExportCSV.Click Response.Clear() Response.Buffer = True Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv") Response.Charset = "" Response.ContentType = "application/text" GridView1.AllowPaging = False GridView1.DataBind() Dim sb As New StringBuilder() For k As Integer = 0 To GridView1.Columns.Count - 1 'add separator sb.Append(GridView1.Columns(k).HeaderText + ","c) Next 'append new line sb.Append(vbCr & vbLf) For i As Integer = 0 To GridView1.Rows.Count - 1 For k As Integer = 0 To GridView1.Columns.Count - 1 'add separator sb.Append(GridView1.Rows(i).Cells(k).Text + ","c) Next 'append new line sb.Append(vbCr & vbLf) Next Response.Output.Write(sb.ToString()) Response.Flush() Response.End() End SubSample form resulting CSV
Payroll ID,Employee,Allotment,Time Taken,Time Available,
,,,,,
,,,,,
,,,,,
,,,,,
,,,,,
Member
749 Points
531 Posts
Re: Gridview to CSV issue no data
Jul 20, 2019 04:36 PM|jzero|LINK
It would be much more easy if you run your Stored Procedure again and get data from there than deal with Gridview data already bound.
There are many ways to get data from Gridview but it depends, if using Boundfields, Templatefield, AutoGenerated Columns, etc. And you have to explore gridview properties to find out what best fit for you.
In your case, using template fields you have to use FindControl
Dim sb As New StringBuilder Dim HeaderCSV As String = String.Join(",", (From p As TableCell In GridView1.HeaderRow.Cells Select p.Text).ToArray) sb.AppendLine(HeaderCSV) For Each grdRow As GridViewRow In GridView1.Rows If grdRow.RowType = DataControlRowType.DataRow Then Dim Payroll_ID As String = CType(grdRow.Cells(0).FindControl("lbl_PayrollID"), Label).Text Dim Employee As String = CType(grdRow.Cells(1).FindControl("Label1"), Label).Text Dim Allotment As String = CType(grdRow.Cells(2).FindControl("Label2"), Label).Text Dim Time_Taken As String = CType(grdRow.Cells(2).FindControl("Label3"), Label).Text Dim Time_Available As String = CType(grdRow.Cells(2).FindControl("Label4"), Label).Text sb.AppendLine(Payroll_ID & "," & Employee & "," & Allotment & "," & Time_Taken & "," & Time_Available) End If NextContributor
2500 Points
814 Posts
Re: Gridview to CSV issue no data
Jul 22, 2019 07:05 AM|Yang Shen|LINK
Hi Menorel,
In the click event of button, it is not feasible to get the cell value through "GridView1.Rows(i).Cells(k).Text".
You need to specify the value of a specific control in this row.
Please refer to below codes :
Protected Sub btnExportCSV_Click(sender As Object, e As EventArgs) Handles btnExportCSV.Click Response.Clear() Response.Buffer = True Response.AddHeader("content-disposition","attachment;filename=GridViewExport.csv") Response.Charset = "" Response.ContentType = "application/text" GridView1.AllowPaging = False GridView1.DataBind() Dim sb As New StringBuilder() For k As Integer = 0 To GridView1.Columns.Count - 1 'add separator sb.Append(GridView1.Columns(k).HeaderText + ","c) Next 'append new line sb.Append(vbCr & vbLf) For i As Integer = 0 To GridView1.Rows.Count - 1 sb.Append((CType(GridView1.Rows(i).FindControl("lbl_PayrollID"), Label)).Text + ","c) sb.Append((CType(GridView1.Rows(i).FindControl("Label1"), Label)).Text + ","c) sb.Append((CType(GridView1.Rows(i).FindControl("Label2"), Label)).Text + ","c) 'append new line sb.Append(vbCr & vbLf) Next Response.Output.Write(sb.ToString()) Response.Flush() Response.End() End SubBest Regard,
Yang Shen