Solved

exporting data table to csv file

Posted on 2011-09-29
15
489 Views
Last Modified: 2012-05-12
I have the two following files. I have the data being exported to a table - Now I need to get it to export to a txt or csv file.
front end file
aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" AutoGenerateColumns="true" runat="server" />
    </div>
    </form>
</body>
</html>

Open in new window

back end aspx.cs
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;


public partial class _Default : System.Web.UI.Page
{
    private const string MDBFILE = "FileUpload2.mdb";



    private string GetConnectionString()
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(MDBFILE) + ";";
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        string SQL = "SELECT * FROM FSUdata ";

        // Create Connection object
        OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

        // Create Command Object
        OleDbCommand dbComm = new OleDbCommand(SQL, dbConn);

        // Open Connection
        dbConn.Open();

        // Execute command and receive DataReader
        OleDbDataReader dbRead = dbComm.ExecuteReader();

        GridView1.DataSource = dbRead;
        GridView1.DataBind();

        dbConn.Close();


    }
}

Open in new window

0
Comment
Question by:Tagom
15 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
Comment Utility
Please do refer http://bytes.com/topic/asp-net/answers/527355-gridview-csv. This contains solution.
0
 
LVL 12

Expert Comment

by:Ramkisan Jagtap
Comment Utility
0
 

Accepted Solution

by:
RaoAnil earned 400 total points
Comment Utility
Write the following code in code behind your problem will be resolved . If you still get an error you can ask me any time. And dont forget to include
using System.IO;
other wise there will be an error.......
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
/// <summary>
/// This event is used to export gridview data to CSV document
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnCSV_Click(object sender, ImageClickEventArgs e)
{
Response.ClearContent();
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
Response.ContentType = "application/text";
gvdetails.AllowPaging = false;
gvdetails.DataBind();
StringBuilder strbldr = new StringBuilder();
for (int i = 0; i < FSUdata.Columns.Count; i++)
{
//separting header columns text with comma operator
strbldr.Append(gvdetails.Columns[i].HeaderText + ',');
}
//appending new line for gridview header row
strbldr.Append("\n");
for (int j = 0; j < FSUdata.Rows.Count; j++)
{
for (int k = 0; k < FSUdata.Columns.Count; k++)
{
//separating gridview columns with comma
strbldr.Append(FSUdata.Rows[j].Cells[k].Text + ',');
}
//appending new line for gridview rows
strbldr.Append("\n");
}
Response.Write(strbldr.ToString());
Response.End();
}

Open in new window

0
 

Author Comment

by:Tagom
Comment Utility
@RaoAnil - I placed your code in the aspx.cs page....got errors...am thinking because I did not declare some of the variables.
here is the new page:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;


public partial class _Default : System.Web.UI.Page
{
    private const string MDBFILE = "FileUpload2.mdb";



    private string GetConnectionString()
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(MDBFILE) + ";";
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        string SQL = "SELECT * FROM FSUdata ";

        // Create Connection object
        OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

        // Create Command Object
        OleDbCommand dbComm = new OleDbCommand(SQL, dbConn);

        // Open Connection
        dbConn.Open();

        // Execute command and receive DataReader
        OleDbDataReader dbRead = dbComm.ExecuteReader();

        GridView1.DataSource = dbRead;
        GridView1.DataBind();

        dbConn.Close();


    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
    /// <summary>
    /// This event is used to export gridview data to CSV document
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnCSV_Click(object sender, ImageClickEventArgs e)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
        Response.ContentType = "application/text";
        gvdetails.AllowPaging = false;
        gvdetails.DataBind();
        StringBuilder strbldr = new StringBuilder();
        for (int i = 0; i < FSUdata.Columns.Count; i++)
        {
            //separting header columns text with comma operator
            strbldr.Append(gvdetails.Columns[i].HeaderText + ',');
        }
        //appending new line for gridview header row
        strbldr.Append("\n");
        for (int j = 0; j < FSUdata.Rows.Count; j++)
        {
            for (int k = 0; k < FSUdata.Columns.Count; k++)
            {
                //separating gridview columns with comma
                strbldr.Append(FSUdata.Rows[j].Cells[k].Text + ',');
            }
            //appending new line for gridview rows
            strbldr.Append("\n");
        }
        Response.Write(strbldr.ToString());
        Response.End();
    }
}

Open in new window

here is the error
Compiler Error Message: CS0103: The name 'gvdetails' does not exist in the current context

Source Error:

Line 58:         Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
Line 59:         Response.ContentType = "application/text";
Line 60:         gvdetails.AllowPaging = false;
Line 61:         gvdetails.DataBind();
Line 62:         StringBuilder strbldr = new StringBuilder();

what am i  missing, please and thank you!
0
 
LVL 2

Assisted Solution

by:yogsoft
yogsoft earned 100 total points
Comment Utility
@Tagom: I can see that your gridview id is "GridView1" not "gvDetails" try with "GridView1" in place of "gvDetails".
0
 

Author Comment

by:Tagom
Comment Utility
@yogsoft - that did indeed fix the above error -
I now get the following and I can forsee two more - simply because it has been so long since i have coded in aspx!
I do not believe I have declared a couple things correctly or converted it to match my code: I appreciate the help
new error
Compiler Error Message: CS0246: The type or namespace name 'StringBuilder' could not be found (are you missing a using directive or an assembly reference?)

Source Error:

Line 60:        GridView1.AllowPaging = false;
Line 61:        GridView1.DataBind();
Line 62:         StringBuilder strbldr = new StringBuilder();
Line 63:         for (int i = 0; i < FSUdata.Columns.Count; i++)
Line 64:         {

issues I am foreseeing
for (int j = 0; j < FSUdata.Rows.Count; j++) with the table name not being declared or referred to properly
and the button I do not believe that I have declared that as well.

0
 

Author Closing Comment

by:Tagom
Comment Utility
Thank you all for your help an tutorial links
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Expert Comment

by:yogsoft
Comment Utility
@Tagom: From the code what I can see is following using statement is missing and replace"FSUdata" with "GridView1"

using System.Text;

Above two changes should resolve your issues...
0
 

Author Comment

by:Tagom
Comment Utility
I did change the FSUdata to GridView1 and received the following error:
Source Error:
Exception Details: System.InvalidOperationException: Invalid attempt to call FieldCount when reader is closed.


Line 61:         Response.ContentType = "application/text";
Line 62:         GridView1.AllowPaging = false;
Line 63:         GridView1.DataBind();
Line 64:         StringBuilder strbldr = new StringBuilder();
Line 65:         for (int i = 0; i < GridView1.Columns.Count; i++)

Source File: \\argofiler\facstaff\Web\hgdeposition\DataCollections\Default.aspx.cs    Line: 63

which I have NO idea how to fix.
0
 
LVL 2

Expert Comment

by:yogsoft
Comment Utility
I think you are using DataReader as a data source. In this case whenever you need to use GridView1.DataBind(); you have to have your DataReader open (which implies SQL connection should be open). Ensure you are setting GridView1.DataSource property whenever you are doing DataBind.

If you could share or email your code behind file, may I can give you exact cause of error.
0
 

Author Comment

by:Tagom
Comment Utility
Here is the code behind:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Text;


public partial class _Default : System.Web.UI.Page
{
    private const string MDBFILE = "FileUpload2.mdb";



    private string GetConnectionString()
        {
            return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(MDBFILE) + ";";
        }

    protected void Page_Load(object sender, EventArgs e)
    {
        string SQL = "SELECT * FROM FSUdata ";

        // Create Connection object
        OleDbConnection dbConn = new OleDbConnection(GetConnectionString());

        // Create Command Object
        OleDbCommand dbComm = new OleDbCommand(SQL, dbConn);

        // Open Connection
        dbConn.Open();

        // Execute command and receive DataReader
        OleDbDataReader dbRead = dbComm.ExecuteReader();

        GridView1.DataSource = dbRead;
        GridView1.DataBind();

        dbConn.Close();


    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
    /// <summary>
    /// This event is used to export gridview data to CSV document
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    
    protected void cmdSend_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "File.csv"));
        Response.ContentType = "application/text";
        GridView1.AllowPaging = false;
        GridView1.DataBind();
        StringBuilder strbldr = new StringBuilder();
        for (int i = 0; i < GridView1.Columns.Count; i++)
        {
            //separting header columns text with comma operator
            strbldr.Append(GridView1.Columns[i].HeaderText + ',');
        }
        //appending new line for gridview header row
        strbldr.Append("\n");
        for (int j = 0; j < GridView1.Rows.Count; j++)
        {
            for (int k = 0; k < GridView1.Columns.Count; k++)
            {
                //separating gridview columns with comma
                strbldr.Append(GridView1.Rows[j].Cells[k].Text + ',');
            }
            //appending new line for gridview rows
            strbldr.Append("\n");
        }
        Response.Write(strbldr.ToString());
        Response.End();
    }
}

Open in new window

0
 
LVL 2

Expert Comment

by:yogsoft
Comment Utility
Please comment line #63 (GridView1.DataBind();). It should work, because no need to bind data again since you are already binding in Page_Load method.
0
 
LVL 2

Expert Comment

by:yogsoft
Comment Utility
I would suggest to use foreach loop over for loop for looping grid view columns and rows.
0
 

Author Comment

by:Tagom
Comment Utility
hmmm, strangely enough it downloaded the file, however there is NO data in the file whatsoever.
Here is the link to the page, what should be in the file is the same data displayed in the datagrid.
I appreciate you helping me with this. I have been working on it sporadically for the last few weeks and this is my last hurdle. I can then turn it over to the students to use!
http://uwf.edu/hgdeposition/DataCollections/Default.aspx
0
 

Author Comment

by:Tagom
Comment Utility
@yogsoft:
I do not understand what you are saying with the loop over for looping...I am going to open another questions since this one is closed, that way you can get the points where deserved.
Thank you!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now