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.
Also
If the file name needs Chinese characters, modify response. addheader ("content-disposition ",
"Attachment;
Filename = "+ system. Web. httputility. urlencode ("
Text ", system. Text. encoding. utf8) +". xls ");