Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Solved
Simple Code to export plain text only from Gridview to CSV (no formatting)
Posted on 2013-01-04
I have the following code in Test.vb:
This code accesses GridViewExportUtil.cs which has the following code:
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...
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
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);
}
}
}
}
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...
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
5
3
2- +3
13 Comments
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>
Would you have a sample? I presume you mean why didn't I export directly from the datasource rather than the gridview??
Active today
If you just need plain text you need to remove the style code below used in the header in Export method:
Regarding second issue: Remove the first column of the datatable using
RemoveAt method
for (int j = 0; j < gv.Columns.Count; j++)
{
gv.HeaderRow.Cells[j].Style.Add("background-color", "cornflowerblue");
}
Regarding second issue: Remove the first column of the datatable using
RemoveAt method
table.Columns.RemoveAt(0);
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();
}
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...
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...
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();
}
Hi,
I created a sample code for you -
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...
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>
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();
}
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...
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.NullReferenceExcept
Source Error:
Line 293: Dim lt As Literal = TryCast(gvCA.Rows(i).FindC
Line 294: 'add separator
Line 295: sb.Append(lt.Text + ","c)
Any reason you can think of?
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.NullReferenceExcept
Source Error:
Line 293: Dim lt As Literal = TryCast(gvCA.Rows(i).FindC
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]()
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.
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.
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 -
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 -
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...
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)
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" />
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...
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.
Sorry, for not explaining more in my first post. As far as the export is concerned, you can do away with the SqlDataSource completely. (I would say don't use a SqlDataSource control period - but that's another topic). Anyway, code should look similar to this:
Html Markup:
<asp:Button runat="server" Text="Button" ID="ExportButton" OnClick="ExportButton_Click" />
Code Behind:
public void ExportButton_Click(object sender, EventArgs e)
{
var connection = new SqlConnection("Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;");
var command = new SqlCommand("SELECT * FROM [TableName]", connection);
var reader = GetDataReader(command);
var csv = ExportToCsv(reader);
Response.Clear();
Response.AppendHeader("content-disposition", "attachment; filename=" + DateTime.Now + ".csv");
Response.ContentType = "text/csv";
Response.Write(csv);
Response.Flush();
Response.End();
}
private SqlDataReader GetDataReader(SqlCommand command)
{
SqlDataReader reader = null;
try
{
command.CommandType = CommandType.Text;
command.Connection.Open();
reader = command.ExecuteReader();
}
catch (Exception exception)
{
//log exception and/or rethrow
if (reader != null)
{
reader.Dispose();
reader = null;
}
command.Connection.Close();
command.Dispose();
}
return reader;
}
private 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();
}
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster.
To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month10 days, 12 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.