We help IT Professionals succeed at work.

Simple Code to export plain text only from Gridview to CSV (no formatting)

badzster
badzster asked
on
13 Comments
1 Solution
2,720 Views
Last Modified: 2013-01-26
I have the following code in Test.vb:

    Protected Sub ibExportPlain_Click(ByVal sender As Object, ByVal args As EventArgs) Handles ibExportPlain.Click, lbExportPlain.Click
        GridViewExportUtil.Export("Export.csv", Me.gvCA)

    End Sub

Open in new window



This code accesses GridViewExportUtil.cs which has the following code:

using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 
/// </summary>
public class GridViewExportUtil
{
    /// <summary>
    /// 
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="gv"></param>
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "text/plain";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                    for (int j = 0; j < gv.Columns.Count; j++)
                    {
                        gv.HeaderRow.Cells[j].Style.Add("background-color", "cornflowerblue");
                    }
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                    
                }
                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is Image)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as Image).AlternateText));
            }
            else if (current is Label)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as Label).Text));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }
}

Open in new window





I need help with the following:
(1)  The export works great except the output still includes the background color of the last column - I can't remove it for some reason.

(2) Since the GridViewExportUtil.cs removes the images from the first column and replaces it with the Alt text, the first column is blank.  Is there a way to remove this column?

I just want a plain csv file which opens in excel...
Comment
Watch Question

View Solution Only

badzster

Author

Commented:
Here is the Gridview code in Test.aspx:

<asp:GridView ID="gvCA" runat="server" AutoGenerateColumns="False" 
            CellPadding="4" DataKeyNames="ID" DataSourceID="SQLTable" 
            ForeColor="#333333" GridLines="None" CssClass="GridView" AllowSorting="True" Width="100%" >
            <AlternatingRowStyle BackColor="White" />
            <Columns>
                <asp:TemplateField>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox6" runat="server" Text='<%# Eval("STATUSURL") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Image ID="Image1" runat="server" ImageUrl='<%# Eval("STATUSURL") %>' />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ID" SortExpression="ID">
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="DESCRIPTION" >
                    <ItemTemplate>
                        <asp:LinkButton ID="LinkButton2" runat="server" onclick="LinkButton2_Click" 
                            Text='<%#Eval("DESCRIPTION")%>'></asp:LinkButton>
                    </ItemTemplate>
                </asp:TemplateField>  
                <asp:TemplateField HeaderText="SOURCE" SortExpression="SOURCE">
                    <ItemTemplate>
                        <asp:Label ID="Label2" runat="server" Text='<%# Bind("SOURCE") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("SOURCE") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemStyle Width="200px" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="ASSIGNED_TO" SortExpression="ASSIGNED_TO">
                    <ItemTemplate>
                        <asp:Label ID="Label3" runat="server" Text='<%# Bind("ASSIGNED_TO") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("ASSIGNED_TO") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="DUE_DATE" SortExpression="DUE_DATE">
                    <ItemTemplate>
                        <asp:Label ID="lblDueDate" runat="server" Text='<%# Bind("DUE_DATE", "{0:d}") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("DUE_DATE") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="STATUS" SortExpression="STATUS">
                    <ItemTemplate>
                        <asp:Label ID="Label5" runat="server" Text='<%# Bind("STATUS") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("STATUS") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="FUTURE_POTENTIAL_RISK" 
                    SortExpression="FUTURE_POTENTIAL_RISK">
                    <ItemTemplate>
                        <asp:Label ID="Label6" runat="server" 
                            Text='<%# Bind("FUTURE_POTENTIAL_RISK") %>'></asp:Label>
                    </ItemTemplate>
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox5" runat="server" 
                            Text='<%# Bind("FUTURE_POTENTIAL_RISK") %>'></asp:TextBox>
                    </EditItemTemplate>
                </asp:TemplateField>
            </Columns>
            <EditRowStyle BackColor="#2461BF" />
            <EmptyDataTemplate>
                Empty
            </EmptyDataTemplate>
            <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" 
                CssClass="Gridview HeaderStyle" Width="100%" HorizontalAlign="Left" />
            <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#EFF3FB" />
            <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#F5F7FB" />
            <SortedAscendingHeaderStyle BackColor="#6D95E1" />
            <SortedDescendingCellStyle BackColor="#E9EBEF" />
            <SortedDescendingHeaderStyle BackColor="#4870BE" />

        </asp:GridView>

Open in new window

dj_alik
Top Expert 2011

Commented:
Why did not work with underlying data source of the grid view?
badzster

Author

Commented:
Would you have a sample?  I presume you mean why didn't I export directly from the datasource rather than the gridview??
Miguel OzSenior Software Engineer
CERTIFIED EXPERT
Top Expert 2009

Commented:
If you just need plain text you need to remove the style code below used in the header in Export method:
for (int j = 0; j < gv.Columns.Count; j++)
                    {
                        gv.HeaderRow.Cells[j].Style.Add("background-color", "cornflowerblue");
                    }

Open in new window


Regarding second issue: Remove the first column of the datatable using
RemoveAt method
table.Columns.RemoveAt(0);

Open in new window

before render the table into the htmlwriter.
P1ST0LPETE

Commented:
Would you have a sample?  I presume you mean why didn't I export directly from the datasource rather than the gridview??
I agree that you should do the export from the underlying data source.  Attempting to export from the GridView itself just seems dirty.

Here is an example of exporting to CSV format from a SqlDataReader:

public String ExportToCsv(SqlDataReader reader)
{
    var csv = new StringBuilder();
            
    if (reader != null && reader.HasRows)
    {
        var count = reader.FieldCount;

        for (int i = 0; i < count; i++)
        {
            csv.AppendFormat("{0}{1}", reader.GetName(i), i < count - 1 ? "," : "\r\n");
        }

        while (reader.Read())
        {
            for (int i = 0; i < count; i++)
            {
                csv.AppendFormat("{0}{1}", Convert.ToString(reader[i]), i < count - 1 ? "," : "\r\n");
            }
        }
    }

    return csv.ToString();
}

Open in new window

Roopesh ReddyIT Analyst
Top Expert 2012

Commented:
Hi,

Check this article - http://www.aspsnippets.com/Articles/Export-GridView-To-Word-Excel-PDF-CSV-Formats-in-ASP.Net.aspx

Well explained all the possible formats in which you can export the data!

Hope it helps u...
badzster

Author

Commented:
Tried the following code from the suggested link (http://www.aspsnippets.com/Articles/Export-GridView-To-Word-Excel-PDF-CSV-Formats-in-ASP.Net.aspx) but only the headers appear - no rows...definitely rows in gridview though??


protected void btnExportCSV_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=GridViewExport.csv");
    Response.Charset = "";
    Response.ContentType = "application/text";
 
    GridView1.AllowPaging = false;
    GridView1.DataBind();
 
    StringBuilder sb = new StringBuilder();
    for (int k = 0; k < GridView1.Columns.Count; k++)
    {
        //add separator
        sb.Append(GridView1.Columns[k].HeaderText + ',');
    }
    //append new line
    sb.Append("\r\n");
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        for (int k = 0; k < GridView1.Columns.Count; k++)
        {
            //add separator
            sb.Append(GridView1.Rows[i].Cells[k].Text + ',');
        }
        //append new line
        sb.Append("\r\n");
    }
    Response.Output.Write(sb.ToString());
    Response.Flush();
    Response.End();
}

Open in new window

Roopesh ReddyIT Analyst
Top Expert 2012

Commented:
Hi,

I created a sample code for you - 

<asp:GridView runat="server" ID="gridView"  AutoGenerateColumns="false">
        <Columns>
            <asp:TemplateField HeaderText="A" ShowHeader="true" >
                <ItemTemplate>
                    <asp:Literal Text='<%#Eval("Name")%>' ID="txt1" runat="server" />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="B">
                <ItemTemplate>
                    <asp:Literal runat="server" ID="txt2" Text='<%#Eval("Company")%>'  />
                </ItemTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="C">
                <ItemTemplate>
                    <asp:Literal runat="server" ID="txt3" Text='<%#Eval("Category")%>' />
                </ItemTemplate>
            </asp:TemplateField>
        </Columns>
    </asp:GridView>

Open in new window


protected void Page_Load(object sender, EventArgs e)
        {
            var rows = new []
            {
                new
                {
                    Name="Bill Gates", Company="Microsoft",Category = "Software"
                },
                new
                {
                    Name="Steve Jobs",Company="Apple",Category = "Software"
                },
                new
                {
                    Name="Larry Page",Company="Google",Category = "Software"
                }

            };

            gridView.DataSource = rows;
            gridView.DataBind();

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition",
             "attachment;filename=GridViewExport.csv");
            Response.Charset = "";
            Response.ContentType = "application/text";

            

            StringBuilder sb = new StringBuilder();
            for (int k = 0; k < gridView.Columns.Count; k++)
            {
                //add separator
                sb.Append(gridView.Columns[k].HeaderText + ',');
            }
            //append new line
            sb.Append("\r\n");
            for (int i = 0; i < gridView.Rows.Count; i++)
            {
                    Literal lt =  gridView.Rows[i].FindControl("txt1") as Literal;
                    //add separator
                    sb.Append(lt.Text + ',');

                    lt = gridView.Rows[i].FindControl("txt2") as Literal;
                    //add separator
                    sb.Append(lt.Text + ',');

                    lt = gridView.Rows[i].FindControl("txt3") as Literal;
                    //add separator
                    sb.Append(lt.Text + ',');

                    //append new line
                    sb.Append("\r\n");

            }
                           
            Response.Output.Write(sb.ToString());
            Response.Flush();
            Response.End();

        }

Open in new window


The above is a sample code, which it shows how to export the data as CSV format!

Moreover, you would have been using Templates instead of Auto Generated columns and hence it is not exporting the data!

http://forums.asp.net/t/1736121.aspx/1

Hope it helps u...
badzster

Author

Commented:
Thanks for the effort - I've tried the code below (converted to vb) but keep getting the error:

Object reference not set to an instance of an object.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 293:            Dim lt As Literal = TryCast(gvCA.Rows(i).FindControl("Label1"), Literal)
Line 294:            'add separator
Line 295:            sb.Append(lt.Text + ","c)


Any reason you can think of?


        gvCA.DataBind()

        Response.Clear()
        Response.Buffer = True
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.csv")
        Response.Charset = ""
        Response.ContentType = "application/text"



        Dim sb As New StringBuilder()
        For k As Integer = 0 To gvCA.Columns.Count - 1
            'add separator
            sb.Append(gvCA.Columns(k).HeaderText + ","c)
        Next
        'append new line
        sb.Append(vbCr & vbLf)
        For i As Integer = 0 To gvCA.Rows.Count - 1
            Dim lt As Literal = TryCast(gvCA.Rows(i).FindControl("Label1"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("LinkButton2"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("Label2"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("Label3"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("lblDueDate"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("Label5"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)

            lt = TryCast(gvCA.Rows(i).FindControl("Label6"), Literal)
            'add separator
            sb.Append(lt.Text + ","c)
            'append new line

            sb.Append(vbCr & vbLf)
        Next

        Response.Output.Write(sb.ToString())
        Response.Flush()
        Response.[End]()

Open in new window

Manoj PatilTechnical Lead
CERTIFIED EXPERT

Commented:
badzster

Author

Commented:
Already tried that as per my post on 2013-01-06 at 09:19:14  ID: 38748197

I just wish there was a way to export directly from the SQLDataSource - however the sample given by: P1ST0LPETEPosted on 2013-01-06 at 01:30:03ID: 38747792 doesn't seem to specify/allow you to input the specific SQLDataSource...maybe I'm missing something.
Roopesh ReddyIT Analyst
Top Expert 2012

Commented:
Hi,

Yeah! It's because you have used  asp:Label control and while casting you have used asp:Literal control, which leads to the NullReference Exception!

Consider the following statement in your code -

Dim lt As Literal = TryCast(gvCA.Rows(i).FindControl("Label1"), Literal)

Open in new window


You have asp:Label in the GridView, but you are casting it to asp:Literal which is not correct.

In my example, i have used Literals, so i have used same in the code behind as well -

<asp:Literal Text='<%#Eval("Name")%>' ID="txt1" runat="server" />

Open in new window


So, you have to use the same control which you have used in the asp:GridView and cast it accordingly!

If you still has issues, do show us the asp:GridView code, so that we will give the equivalent code!

Hope it helps u...
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.

View Solution