current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to be able to generate a basic CSV file of the results returned from an SqlDataSource/GridView upon the user clicking a button, then allow them to save this file.

Is this simple to do?

Thanks

share|improve this question
    
Do you actually want a .csv, or are you settling for a .csv because spreadsheets are so complex? –  MAW74656 Nov 30 '10 at 17:03

3 Answers 3

up vote 1 down vote accepted

Since you are using a SqlDataSource then you can get a DataTable out of it like this:

var dv = new DataView();
var dt = new DataTable();
dv = (DataView)mySQLDataSource.Select(DataSourceSelectArguments.Empty);
dt = dv.ToTable();

I wrote a set of extension methods to do DataTable to CSV, which you should be able to use easily.

The rest of your code would be pretty simple then:

var csv = dt.ToCSV();

Here is a full example using this very method.

The Markup:

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
    CodeBehind="Default.aspx.cs" Inherits="SO.WebUI._Default" %>

<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="EmployeeId"
        DataSourceID="SqlDataSourceLocal">
        <Columns>
            <asp:BoundField DataField="EmployeeId" HeaderText="EmployeeId" InsertVisible="False"
                ReadOnly="True" SortExpression="EmployeeId" Visible="False" />
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
            <asp:BoundField DataField="Birthdate" HeaderText="Birthdate" SortExpression="Birthdate" />
        </Columns>
    </asp:GridView>

    <!-- This is the button to export CSV! -->
    <asp:Button ID="btnToCSV" runat="server" Text="Export to CSV" OnClick="GetCSV" />

    <asp:SqlDataSource ID="SqlDataSourceLocal" runat="server" 
    ConnectionString="<%$ ConnectionStrings:StackOverflowExamplesConnectionString %>"
        SelectCommand="SELECT * FROM [Employee]"></asp:SqlDataSource>
</asp:Content>

And the code-behind:

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    public void GetCSV(object sender, EventArgs e)
    {
        DataView dv = (DataView)SqlDataSourceLocal.Select(DataSourceSelectArguments.Empty);
        var dt = dv.ToTable();

        var csv = dt.ToCSV();

        WriteToOutput(csv, "export.csv", "text/csv");
    }

    private void WriteToOutput(String csv, String fileName, String mimeType)
    {
        Response.Clear();
        Response.ContentType = mimeType;
        Response.AddHeader("Content-Disposition", String.Format("attachment;filename={0}", fileName));
        Response.Write(csv);
        Response.End();
    }
}

There you go. The only thing not included here is the extension method source, but since I've linked to it, you should have no problems getting that into a static class.

share|improve this answer
up vote 0 down vote

For a GridView you could use this:

            StreamWriter writer = new StreamWriter("file.csv");

            // Write columns
            writer.Write(myGridView.Columns[0].HeaderText);
            for (int i = 1; i < myGridView.Columns.Count; i++)
                writer.Write("," + myGridView.Columns[i].HeaderText);

            writer.Write("\n");

            // Write values
            for (int x = 0; x < myGridView.Rows.Count; x++)
            {
                writer.Write(myGridView.Rows[x].Cells[0].Text);
                for (int i = 1; i < myGridView.Rows[x].Cells.Count; i++)
                    writer.Write("," + myGridView.Rows[x].Cells[i].Text);

                writer.Write("\n");
            }

            writer.Close();
share|improve this answer
up vote 0 down vote

private void button13_Click(object sender, EventArgs e) // export to .csv { //OnExportGridToCSV(); 

       StreamWriter writer = new StreamWriter("C:\\scripts\\file.csv");

       // Write columns
       writer.Write(dataGridView1.Columns[0].HeaderText);
       for (int i = 1; i < dataGridView1.Columns.Count; i++)
           writer.Write("," + dataGridView1.Columns[i].HeaderText);

       writer.Write("\n");

       // Write values
       for (int x = 0; x < dataGridView1.Rows.Count; x++)
       {
           writer.Write(dataGridView1.Rows[x].Cells[0].FormattedValue.ToString());
           for (int i = 1; i < dataGridView1.Rows[x].Cells.Count; i++)
               writer.Write("," + dataGridView1.Rows[x].Cells[i].FormattedValue.ToString());

           writer.Write("\n");

           textBox5.Text = ("Row " + (x + 1).ToString() + " of " + dataGridView1.Rows.Count + " exported."); // progress indicator
       }


       writer.Close();



       // open up the newly created file in excel 

       Process proc = new Process();
       proc.StartInfo = new ProcessStartInfo("excel.exe", "C:\\scripts\\file.csv");
       proc.Start();
   }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.