?
Solved

Export GridView to Excel

Posted on 2011-10-28
21
Medium Priority
?
797 Views
Last Modified: 2012-05-12
Hello Experts,

I need to be able to export the contents of the GridView Control to Excel or .CSV file. Please see my CodeBehind to retrieving the data to my GridView Control. I'm using a DataTable and was wondering if I can somehow take the contents of the DataTable and use those values to Export to Excel.

protected void RetrieveAllPledgeParticipants()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessChoice"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "WellnessChoiceAdmin_RetrieveAllPledgeParticipants";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            DataTable dtPledgeParticipants = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter();

            try
            {
                conn.Open();

                adp.SelectCommand = cmd;
                adp.Fill(dtPledgeParticipants);

                gv_Pledge.DataSource = dtPledgeParticipants;
                gv_Pledge.DataBind();
            }

            catch (Exception ex)
            {
                lblPledgeError.Text = ex.Message.ToString();
            }
        }
    }

Open in new window

0
Comment
Question by:asp_net2
  • 10
  • 5
  • 4
  • +1
21 Comments
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37045770
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 37045791
Not sure if this works when you have paging.  The other way would be to write to a CSV file (looping over your data table by row, column) and then push the file out in your response header.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37046286
Hi ged325,

Any other tutorials that work?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:Miguel Oz
ID: 37048482
You can save your datatable as csv file (Excel can read it), check:
http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv
http://dotnetguts.blogspot.com/2007/01/exporting-datatable-to-csv-file-format.html

There is an option to save to excel, but it will yield the same result:(it will be slow because you are using Excel COM interop)
http://www.daniweb.com/software-development/csharp/threads/213015
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37049839
Hi mas_oz2003,

Not sure how I can implement what you are providing with my code. I'm not retriing the data from a Click Event. The data is getting populated from Page_Load and stored in a DataTable. I would like to use that same DataTable so when user Click the button it exports that data. I have attached my code above, is there any way to use that code and create a Button Click Event?
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 37051174
Where is RetrieveAllPledgeParticipants called? Page_load event and checking IsPostback?
Depending on your implementation, if you want to fetch the Sql data once, you can store it Session at your method, like:
Session["allPledgeParticipants"] = dtPledgeParticipants;

and in your button click:
dtPledgeParticipants = Session["allPledgeParticipants"]  as DataTable;
//then proceed to use the sample csv code on the links.

Note: If your RetrieveAllPledgeParticipants  is called  every time the apge interacts with the server (even whenyou ckick the button), then defien the table as a field in your page and use it in the click implementation.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37053515
>> Where is RetrieveAllPledgeParticipants called
It's called during Page_Load. I don't have IsPostBack setup for  RetrieveAllPledgeParticipants.

RetrieveAllPledgeParticipants will retrieve about 1000 + rows that contain about 4 columns worth of data for each row. Do you think that storing the DataTable into Session might be bad since the data may be rather large?

I will try your solution tonight but wanted your feedback on the above if that's ago. Also not sure what you mean by what you said below, could you allaborate more?

>> Note: If your RetrieveAllPledgeParticipants  is called  every time the apge interacts with the server (even whenyou ckick the button), then defien the table as a field in your page and use it in the click implementation.
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 37055231
So RetrieveAllPledgeParticipants is called by Page_Load all the time, you can use attached code

Note: It seems very demanding that your page is calling SQL server on every postback, unless of course there is so few postback that is not worth worrying about it.
public partial class Default2 : System.Web.UI.Page
{
    DataTable dtPledgeParticipants;
    
    protected void Page_Load(object sender, EventArgs e)
    {
        RetrieveAllPledgeParticipants();
        //other implementation here
    }

    protected void btnExport_Click(object sender, EventArgs e)
    {
      //use dtPledgeParticipants with sample export code
    }
    
    protected void RetrieveAllPledgeParticipants()
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessChoice"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "WellnessChoiceAdmin_RetrieveAllPledgeParticipants";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = conn;
    
                dtPledgeParticipants = new DataTable();
                SqlDataAdapter adp = new SqlDataAdapter();
    
                try
                {
                    conn.Open();
    
                    adp.SelectCommand = cmd;
                    adp.Fill(dtPledgeParticipants);
    
                    gv_Pledge.DataSource = dtPledgeParticipants;
                    gv_Pledge.DataBind();
                }
    
                catch (Exception ex)
                {
                    lblPledgeError.Text = ex.Message.ToString();
                }
            }
    }
}

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 37056007
Hi masoz2003,

I'm not sure what you mean by the following comment below. Could you explain to  me a little clearer so that I may understand?

Note: It seems very demanding that your page is calling SQL server on every postback, unless of course there is so few postback that is not worth worrying about it.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37056548
Hi mas_oz2003,

Your sample did not work. Please see what I had to do to get it to work. Once again, I'm not a fan of storing all that data into "Session". Also, it would be nice to have something popup asking the user where he/she would like to do with the file. It just saves the data to local drive with out showing or displaying anything letting you know what's going on :(


public partial class admin_secure_pledge_view_participants : System.Web.UI.Page
{
    private DataTable dtPledgeParticipants;

    protected void Page_Load(object sender, EventArgs e)
    {
        RetrieveAllPledgeParticipants();
    }

    protected void gv_Pledge_RowDataBound(Object sender, GridViewRowEventArgs e)
    {
        //check if its a data row (not footer or header for example)
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            //find image in our itemtemplate
            Image img = (Image)e.Row.FindControl("ImgDynamic");

            Int32 pldg_complete = 0;

            //get the column value we want to check against (0 or 1)
            if (DataBinder.Eval(e.Row.DataItem, "pldg_complete") != DBNull.Value)
            {
                //get the column value we want to check against (0 or 1)
                pldg_complete = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "pldg_complete"));
            }

            //set our img src based on pldg_complete value (0 or 1)
            if (pldg_complete == 1)
            {
                img.ImageUrl = "../../images/complete.png";
            }
            else
            {
                img.ImageUrl = "../../images/not_complete.png";
            }
        }
    }

    protected void RetrieveAllPledgeParticipants()
    {
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessChoice"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "WellnessChoiceAdmin_RetrieveAllPledgeParticipants";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = conn;

            DataTable dtPledgeParticipants = new DataTable();
            SqlDataAdapter adp = new SqlDataAdapter();

            try
            {
                conn.Open();

                adp.SelectCommand = cmd;
                adp.Fill(dtPledgeParticipants);

                gv_Pledge.DataSource = dtPledgeParticipants;
                gv_Pledge.DataBind();

                Session["allPledgeParticipants"] = dtPledgeParticipants;
            }

            catch (Exception ex)
            {
                lblPledgeError.Text = ex.Message.ToString();
            }
        }
    }

    protected void gv_Pledge_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gv_Pledge.PageIndex = e.NewPageIndex;
        gv_Pledge.DataBind();
    }

    protected void btn_ExportPledgeData_Click(object sender, EventArgs e)
    {
        dtPledgeParticipants = Session["allPledgeParticipants"] as DataTable;
        CreateCSVFile(dtPledgeParticipants, "c:\\Pledge_AllParticipants.csv");
    }

    public void CreateCSVFile(DataTable dtPledgeParticipants, string strFilePath)
    {
        // Create the CSV file to which grid data will be exported.
        StreamWriter sw = new StreamWriter(strFilePath, false);
        // First we will write the headers.
        //DataTable dt = m_dsProducts.Tables[0];
        int iColCount = dtPledgeParticipants.Columns.Count;
        for (int i = 0; i < iColCount; i++)
        {
            sw.Write(dtPledgeParticipants.Columns[i]);
            if (i < iColCount - 1)
            {
                sw.Write(",");
            }
        }

        sw.Write(sw.NewLine);
        // Now write all the rows.
        foreach (DataRow dr in dtPledgeParticipants.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                if (!Convert.IsDBNull(dr[i]))
                {
                    sw.Write(dr[i].ToString());
                }
                if (i < iColCount - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);
        }
        sw.Close();
    }
}

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 37058586
@mas_oz2003:

Also, when I use the code above it does NOT work when I upload it to the server, I get a server application error. Do I need to give certain write permissions since it's outputting data to a file?

I'm beggining to think the links you sent me are not the correct method for this. Please respond or I need to grade based on what was provided and re-ask question.
0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 37060669
You must have an export location on your server in your web site virtual directory. The c drive is not part of your web site, thus it fails because of permissions. ( If it does not fail, it means your server has a big security hole...)

The provided links have the correct code for what you asked for, if you need user interaction and being stored in the client machine, you need to ask/search for uploading an excel file in asp.net.

My note is more a performance check (Nothing to do with your original question - but you may want to have a look if page is slow) : You need to decide based on your page usage. How many times page_load is called in a typical use case in your app (<=3) I won't bother else you need to check if querying the database is more expensive than having a session object storing the datatable so you do not have to query the database again and again.
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37062203
mas_oz2003,

I made a change to the location to the following below but when I execute the page I get the following error message belowl

Changed line below:
CreateCSVFile(dtPledgeParticipants, "../exportdata/pledge/Pledge_AllParticipants.csv");  

Error below:
StreamWriter sw = new StreamWriter(strFilePath, false);

Could not find a part of the path 'C:\Program Files (x86)\Common Files\Microsoft Shared\DevServer\exportdata\pledge\Pledge_AllParticipants.csv'.

Also, nothing is wrong with this example other than I can't get the data to a folder once I upload to website. But it would be nice if user had the option to save to their location. Also, when a user clicks on button to Export data the user has no idea what happens. The page just blinks for a second and the data is exported but end user may not know that causing them to continue to click button.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 37062731
Hello asp_net2, some time ago I offered a solution to export the content of a gridview to an excel work book by using oledb and Microsoft Jet and send it back to the client browser, this is the link to the solution:
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27327904.html

I have made some improvements to the code, here is:
void ExportToExcel(string sheetName, GridView myGridView)
{
    if (string.IsNullOrEmpty(sheetName))
        throw new ArgumentException("sheetName is required.", "sheetName");

    if (myGridView == null)
        throw new ArgumentException("myGridView is required.", "myGridView");
        
    string myTempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() + ".xls");
    try
    {
        using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + myTempFile + "';Extended Properties='Excel 8.0;HDR=Yes'"))
        {
            cn.Open();

            // Get de column names.
            System.Collections.Specialized.StringCollection myColumnNames = new System.Collections.Specialized.StringCollection();
            int colCount = 0;
            foreach (TableCell cell in myGridView.HeaderRow.Cells)
            {
                colCount ++;
                string columName = Server.HtmlDecode(cell.Text).Trim();
                // The column name can't be empty.
                myColumnNames.Add("[" + (string.IsNullOrEmpty(columName) ? "Col" + colCount : columName) + "]");
            }

            // Build Sql for sheet creation                                
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                sbSql.Append(columnName).Append(" string");
            }
            sbSql.Insert(0, "CREATE TABLE [" + sheetName + "] (");
            sbSql.Append(")");

            // Create a work sheet
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn);
            cmd.ExecuteNonQuery();


            // Build Sql for data insertion                                
            sbSql.Length = 0;
            StringBuilder sbSqlParameters = new StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                if (sbSqlParameters.Length > 0)
                    sbSqlParameters.Append(", ");

                sbSql.Append(columnName);
                sbSqlParameters.Append("?");
            }
            sbSql.Insert(0, "INSERT INTO [" + sheetName + "] (");
            sbSql.Append(") VALUES (");
            sbSql.Append(sbSqlParameters.ToString());
            sbSql.Append(")");
            cmd = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn);

            // Create parameters
            foreach (string columnName in myColumnNames)
                cmd.Parameters.Add(columnName, System.Data.OleDb.OleDbType.BSTR);

            // Copy data from your GridView to the Excel file
            foreach (GridViewRow row in myGridView.Rows)
            {
                for (int i = 0; i < myColumnNames.Count; i++)
                    cmd.Parameters[i].Value = row.Cells[i].Text;
                cmd.ExecuteNonQuery();
            }
        }

        // Send the excel file to the client.
        Response.Clear();
        Response.Cache.SetNoStore();
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls");
        Response.ContentType = "application/ms-excel";
        Response.BinaryWrite(System.IO.File.ReadAllBytes(myTempFile));
        Response.End();
    }
    finally
    {
        // Delete the temp file for cleanup purposes
        System.IO.File.Delete(myTempFile);
    }
}

Open in new window


Example of usage:
protected void buttonExport_Click(object sender, EventArgs e)
{
    ExportToExcel("MyData", YourGridViewId);
}

Open in new window

0
 
LVL 17

Accepted Solution

by:
Carlos Villegas earned 2000 total points
ID: 37062854
I have adapted the code to export the content of a DataTable to an Excel file, then send it back to the client browser:
void ExportDataTableToExcel(string sheetName, DataTable myDataTable)
{
    if (string.IsNullOrEmpty(sheetName))
        throw new ArgumentException("sheetName is required.", "sheetName");

    if (myDataTable == null)
        throw new ArgumentException("myDataTable is required.", "myDataTable");

    string myTempFile = System.IO.Path.Combine(System.IO.Path.GetTempPath(), Guid.NewGuid().ToString() + ".xls");
    try
    {
        using (System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + myTempFile + "';Extended Properties='Excel 8.0;HDR=Yes'"))
        {
            cn.Open();

            // Get de column names.
            System.Collections.Specialized.StringCollection myColumnNames = new System.Collections.Specialized.StringCollection();
            int colCount = 0;
            foreach (DataColumn column in myDataTable.Columns)
            {
                colCount++;
                string columName = column.ColumnName.Trim();
                // The column name can't be empty.
                myColumnNames.Add("[" + (string.IsNullOrEmpty(columName) ? "Col" + colCount : columName) + "]");
            }

            // Build Sql for sheet creation                                
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                sbSql.Append(columnName).Append(" string");
            }
            sbSql.Insert(0, "CREATE TABLE [" + sheetName + "] (");
            sbSql.Append(")");

            // Create a work sheet
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn);
            cmd.ExecuteNonQuery();


            // Build Sql for data insertion                                
            sbSql.Length = 0;
            StringBuilder sbSqlParameters = new StringBuilder();
            foreach (string columnName in myColumnNames)
            {
                if (sbSql.Length > 0)
                    sbSql.Append(", ");

                if (sbSqlParameters.Length > 0)
                    sbSqlParameters.Append(", ");

                sbSql.Append(columnName);
                sbSqlParameters.Append("?");
            }
            sbSql.Insert(0, "INSERT INTO [" + sheetName + "] (");
            sbSql.Append(") VALUES (");
            sbSql.Append(sbSqlParameters.ToString());
            sbSql.Append(")");
            cmd = new System.Data.OleDb.OleDbCommand(sbSql.ToString(), cn);

            // Create parameters
            foreach (string columnName in myColumnNames)
                cmd.Parameters.Add(columnName, System.Data.OleDb.OleDbType.BSTR);

            // Copy data from your DataTable to the Excel file
            foreach (DataRow row in myDataTable.Rows)
            {
                for (int i = 0; i < myColumnNames.Count; i++)
                    cmd.Parameters[i].Value = row[i];
                cmd.ExecuteNonQuery();
            }
        }

        // Send the excel file to the client.
        Response.Clear();
        Response.Cache.SetNoStore();
        Response.AddHeader("content-disposition", "attachment; filename=" + Server.UrlEncode(sheetName) + ".xls");
        Response.ContentType = "application/ms-excel";
        Response.BinaryWrite(System.IO.File.ReadAllBytes(myTempFile));
        Response.End();
    }
    finally
    {
        // Delete the temp file for cleanup purposes
        System.IO.File.Delete(myTempFile);
    }
}

Open in new window


How to use (example):
protected void buttonExportDataTable_Click(object sender, EventArgs e)
{
    // Sample data
    DataTable dtt = new DataTable();
    dtt.Columns.Add("Id", typeof(int));
    dtt.Columns.Add("Name", typeof(string));
    dtt.Columns.Add("Column2", typeof(string));
    dtt.Columns.Add("Column3", typeof(string));
    dtt.Rows.Add(1, "Test 1", "Test 2", "Test 3");
    dtt.Rows.Add(2, "Test 1", "Test 2", "Test 3");
    dtt.Rows.Add(3, "Test 1", "Test 2", "Test 3");
    dtt.Rows.Add(4, "Test 1", "Test 2", "Test 3");
        
    ExportDataTableToExcel("MyData", dtt);
}

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 37063062
HI yv989c,

Thank you for the feedback!!! Only problem is that I'm not sure how to implement what you provided to what I'm using for retrieving the data. Please see my original code above, this is what I'm using to retrieve data to DataTable.

Also, the post 37056548 works for me except that when I upload the code to my webserver i'm unable to run the code and can only run the code locally. mas_oz2003 made a comment in npost 37060669 explaining why it's not working on server but not sure how to implement that to work on server end. It works fine locally. Also, this option does not give user where he/she can save the file at which doe snot help them at all :(
0
 
LVL 17

Assisted Solution

by:Carlos Villegas
Carlos Villegas earned 2000 total points
ID: 37063301
Ok, try this, first add the ExportDataTableToExcel method posted above to your code, then:

Add these methods:
private DataTable RetrieveAllPledgeParticipantsData()
{
    DataTable dtPledgeParticipants = new DataTable();

    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WellnessChoice"].ConnectionString))
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "WellnessChoiceAdmin_RetrieveAllPledgeParticipants";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Connection = conn;

        SqlDataAdapter adp = new SqlDataAdapter();

        conn.Open();

        adp.SelectCommand = cmd;
        adp.Fill(dtPledgeParticipants);
    }

    return dtPledgeParticipants;
}

// Asumming this will be your button OnClick handler to export the data
protected void buttonExportDataTable_Click(object sender, EventArgs e)
{
    ExportDataTableToExcel("MyData", RetrieveAllPledgeParticipantsData());
}

Open in new window


And update your RetrieveAllPledgeParticipants method with this:
protected void RetrieveAllPledgeParticipants()
{
    try
    {
        gv_Pledge.DataSource = RetrieveAllPledgeParticipantsData();
        gv_Pledge.DataBind();
    }
    catch (Exception ex)
    {
        lblPledgeError.Text = ex.Message.ToString();
    }
}

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 37063495
Hi yv989c,

Yes, that all worked perfectly and as I needed. Did you create the ExportDataTableToExcel yourself or did you find it somewhere? I would never have thought it would be that involved to copy the data to Excel. Where did you learn that at, i"m very curious so I can learn. Did you get help from a book(s)?
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 37063703
Hello asp_net2, yes, I have used Microsoft Jet for many years to accomplish task that involved ms access, ms excel, etc... Some time ago I worked with a data warehouse team, so data transformation was a daily task (DTS, now SSIS), therefore I'm familiarized with that kind of work, then when you put together that with web programming knowledge the result is that method ;)

You learn by reading and practicing :)
Internet is your best book!
0
 
LVL 4

Author Closing Comment

by:asp_net2
ID: 37063742
Thank you VERY much for that. I just wish there was a book that taught stuff like that such as exporting to excel and pdf. I find alot of tutorials online but it just confusses me that there is more than one way of creating the same output. I guess I try to think that there should be a standard on performing certain tasks like this and when I see more than one way it throws me off :)

Thank you kindly for your help and time.
0
 
LVL 17

Expert Comment

by:Carlos Villegas
ID: 37064176
You are welcome buddy
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Integration Management Part 2
Suggested Courses

864 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