How to export DataGrid data to an Excel file and download it to the client)

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. receivettings ["DownLoadURL"]. tostring () + datetime. Today. tostring ("yyyymmdd") + new
Random (datetime. Now. millisecond). Next (10000). tostring () + ". CSV"; // save
The path specified by DownLoadURL in Web. 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.

If the file name needs Chinese characters, modify the file name accordingly.
Response. addheader ("content-disposition", "attachment;
Filename = "+ system. Web. httputility. urlencode (" Chinese ",
System. Text. encoding. utf8) + ". xls ");