Solved

Export data from Gridview to an Excel file using C# and LINQ

Posted on 2008-10-22
3
4,127 Views
Last Modified: 2013-11-11
I devoleping web application in asp.net 2.0 and LINQ, I nedd to Export large data from Gridviewto an Excel file using C#.
0
Comment
Question by:enrique_aeo
3 Comments
 
LVL 4

Accepted Solution

by:
novynov earned 200 total points
Comment Utility
I may need some clarification on exactly what help you need, but given what you've said, here's how I'd tackle it, assuming that you just need to get the data into excel - you are not looking at preserving the formatting of the gridview in the excel version:

- Presumably, your gridview is getting populated through the results of a LINQ query, either through a LinqDataSource, or a manual DataBind() against the query.
- However your query is getting processed, you have access to it in your code behind, and could execute it on a button click for export (or similar). That way, you are processing the raw data...not parsing through the html formatted table that makes up the rendered gridview
- You could then take the query and iterate it, open a file, and for each row of data, you could write a line into the file, separating each column value with a comma...and then of course close your file. This would produce a CSV file which will open nicely in Excel.

Since I don't have your schema or query at hand, my code snippet is from a test db that I have.

Is this what you need, or are you looking for something else, more detail?
 try

 {

     using (TestDataContext context = new TestDataContext() )

     {             

         using (StreamWriter sw = new StreamWriter(@"c:\temp\persons.csv"))                

         {
 

             foreach (person p in context.persons.OrderBy(p => p.lastname) )                

             {

                 string line = String.Format("{0},{1},{2}", p.pid, p.firstname,p.lastname);

                 sw.WriteLine(line);                    

             }               

         }

                               

     }
 

}

catch (Exception exc)

{

    MessageBox.Show(exc.Message);
 
 

}

Open in new window

0
 
LVL 6

Assisted Solution

by:M3mph15
M3mph15 earned 50 total points
Comment Utility
Ok here is a function i used:

private void ExportToExcel(string strFileName, GridView gv)
{
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
// Tells website that what is going to be outputted can be handled by Excel
Response.AddHeader("content-disposition", "attachment;filename=" + strFileName);
//Set filename
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new StringWriter();
System.Web.UI.HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
 
Basically you pass in a file name and the gridview into this function. You will also need to override the VerifyRederingInServerForm function by placing this in your code behind.


public override void VerifyRenderingInServerForm(Control control)
{
return;
}
In addition to this you may need to disable event Validation for this page by placing the following line in the page directive. EnableEventValidation = "false" so it'll look like this:

<%@ Page Language="C#" EnableEventValidation = "false" AutoEventWireup="true"
 CodeFile="ExportGridView.aspx.cs" Inherits="ExportGridView" %>

and that should cause a popup to say wether you want to just open or save the excel spreadsheet.

-M3mph15
0
 

Author Comment

by:enrique_aeo
Comment Utility
Hello novynov and M3mph15, this interesting code that you provide to me, I review at night it, thanks for your time. Greetings
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQl help with selection 14 43
Code enhancement 5 12
C# Application Local DB Connection String 23 57
Visual Studio 2013 debugging 2 5
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…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

11 Experts available now in Live!

Get 1:1 Help Now