[Reprint] How to export data from the DataGrid to an Excel file and download it to the client

Source: Internet
Author: User
Tags file separator
Reference: http://www.cnblogs.com/lovecherry/archive/2005/03/25/125519.html

Method 1: export to a CSV file, store it in any path on the server, and download the file to the client.

Advantages:
1. You can perform identity authentication and then download it to the customer. If you put it in a non-web directory, there will be no corresponding URL and the customer will not be able to download it at any time.
2. Because files are generated, the server space is occupied. However, you can store the file names in the database. You do not need to generate files repeatedly when downloading the files again.
3. CSV files are text files separated by commas (,). Press enter to separate rows to facilitate data import and export.

Implementation Method:
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Sqldataadapter da = new sqldataadapter ("select * From tb1", Conn );
Dataset DS = new dataset ();
Da. Fill (DS, "Table1 ");
Datatable dt = Ds. Tables ["Table1"];
String name = system. configuration. configurationsettings. appsettings ["DownLoadURL"]. tostring () + datetime. today. tostring ("yyyymmdd") + new random (datetime. now. millisecond ). next (1, 10000 ). tostring () + ". CSV "; // save it to the Web. the path specified by DownLoadURL in config. The file format is the current date + 4-digit random number.
Filestream FS = new filestream (name, filemode. Create, fileaccess. Write );
Streamwriter Sw = new streamwriter (FS, system. Text. encoding. getencoding ("gb2312 "));
Sw. writeline ("automatic number, name, age ");
Foreach (datarow DR in DT. Rows)
{
Sw. writeline (Dr ["ID"] + "," + Dr ["vname"] + "," + Dr ["iage"]);
}
Sw. Close ();
Response. addheader ("content-disposition", "attachment; filename =" + server. urlencode (name ));
Response. contenttype = "application/MS-excel"; // specify that the returned stream cannot be read by the client and must be downloaded.
Response. writefile (name); // send the file stream to the client
Response. End ();

Method 2: export to a CSV file and output the file stream directly to the browser without storing it on the server.

Advantages:
1. Generate at any time without occupying Resources
2. Identity Authentication
3. data exchange is also beneficial.

Implementation Method:
Sqlconnection conn = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Sqldataadapter da = new sqldataadapter ("select * From tb1", Conn );
Dataset DS = new dataset ();
Da. Fill (DS, "Table1 ");
Datatable dt = Ds. Tables ["Table1"];
Stringwriter Sw = new stringwriter ();
Sw. writeline ("automatic number, name, age ");
Foreach (datarow DR in DT. Rows)
{
Sw. writeline (Dr ["ID"] + "," + Dr ["vname"] + "," + Dr ["iage"]);
}
Sw. Close ();
Response. addheader ("content-disposition", "attachment; filename=test.csv ");
Response. contenttype = "application/MS-excel ";
Response. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Response. Write (SW );
Response. End ();

Add one or more points to method 1 and method 2. If you want to export the xls file separator, use \ t instead of commas.

The code is modified as follows:
Sw. writeline ("automatic ID \ t name \ t age ");
Foreach (datarow DR in DT. Rows)
{
Sw. writeline (Dr ["ID"] + "\ t" + Dr ["vname"] + "\ t" + Dr ["iage"]);
}
In addition, modify the output file extension to XLS.

Method 3: Export HTML code from the DataGrid, generate an Excel file, and download it to the client

Advantages:
1. There is a fixed format that looks nice (the DataGrid looks nice)

Limitations:
1. It is not suitable for data exchange. It contains HTML code, which is messy and has no fixed format.
2. The DataGrid cannot contain paging or sorting; otherwise, an error occurs.

Implementation Method:
Response. Clear ();
Response. Buffer = false;
Response. charset = "gb2312 ";
Response. appendheader ("content-disposition", "attachment=filename=test.xls ");
Response. contentencoding = system. Text. encoding. getencoding ("gb2312"); response. contenttype = "application/MS-excel"; this. enableviewstate = false;
System. Io. stringwriter ostringwriter = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );
This. datagrid1.rendercontrol (ohtmltextwriter );
Response. Write (ostringwriter. tostring ());
Response. End ();

It is explained here that some netizens report errors such as "No Dr [" ID "]" in the Code. This code is written according to my data structure, then you need to replace the relevant fields with your own.

In addition, if the file name needs Chinese characters, modify response. addheader ("content-disposition", "attachment; filename =" + system. web. httputility. urlencode ("Chinese", system. text. encoding. utf8) + ". xls ");

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.