Excel export methods.

I. Method for exporting Execl in asp.net:

There are two ways to export Execl in asp.net: one is to store the exported file under a folder on the server, and then output the file address to the browser; one is to directly write the file output stream to the browser. In Response output, t-separated Data. When execl is exported, n is equivalent to line feed.
1. Output execl all the html

This method outputs all html content, such as buttons, tables, and images, to Execl.
Response. Clear ();
Response. Buffer = true;
Response. AppendHeader ("Content-Disposition", "attachment; filename =" + DateTime. Now. ToString ("yyyyMMdd") + ". xls ");
Response. ContentEncoding = System. Text. Encoding. UTF8;
Response. ContentType = "application/vnd. ms-excel ";
This. EnableViewState = false;

Here, we use the ContentType attribute, which defaults to text/html. At this time, the output is hypertext, that is, our common webpage format is sent to the client, if it is changed to ms-excel, the excel format will be output, that is, the workbook format will be output to the client, then the browser will prompt you to download and save. ContentType attributes include: image/JPEG; text/HTML; image/GIF; vnd. ms-excel/msword. Likewise, we can also output (export) images and Word documents. The following method also uses this attribute.

2. Export the data in the DataGrid Control to Execl

Although the above method achieves the export function, it also imports all the output information in html such as buttons and paging boxes. We generally want to export data on the DataGrid Control.
System. Web. UI. Control ctl = this. DataGrid1;
// DataGrid1 is the control you drag and drop in the form
HttpContext. Current. Response. AppendHeader ("Content-Disposition", "attachment?filename=excel.xls ");
HttpContext. Current. Response. Charset = "UTF-8 ";
HttpContext. Current. Response. ContentEncoding = System. Text. Encoding. Default;
HttpContext. Current. Response. ContentType = "application/ms-excel ";
Ctl. Page. EnableViewState = false;
System. IO. StringWriter tw = new System. IO. StringWriter ();
System. Web. UI. htmltextwriter hW = new system. Web. UI. htmltextwriter (TW );
CTL. rendercontrol (HW );
Httpcontext. Current. response. Write (TW. tostring ());
Httpcontext. Current. response. End ();

If your DataGrid uses paging, it exports the information of the current page, that is, it exports the information displayed in the DataGrid. Instead of all the information of your select statement.

For ease of use, the writing method is as follows:
Public void dgtoexcel (system. Web. UI. Control CTL)
{
Httpcontext. Current. response. appendheader ("content-disposition", "attachment?filename=excel.xls ");
Httpcontext. Current. response. charset = "UTF-8 ";
Httpcontext. Current. response. contentencoding = system. Text. encoding. default;
Httpcontext. Current. response. contenttype = "application/MS-excel ";
CTL. Page. enableviewstate = false;
System. IO. StringWriter tw = new System. IO. StringWriter ();
System. Web. UI. HtmlTextWriter hw = new System. Web. UI. HtmlTextWriter (tw );
Ctl. RenderControl (hw );
HttpContext. Current. Response. Write (tw. ToString ());
HttpContext. Current. Response. End ();
}

Usage: DGToExcel (datagrid1 );

3. Export the data in DataSet to Execl

With the above idea, we can export the exported information to the Response client. Export the data in the DataSet, that is, to export the information of each row in the table in the DataSet in the ms-excel format to the http stream, so that it is OK. Dsds datasdatasdatasdatasdatasdatasdatasdatasdatasdatasdatas: the file name should be full, and the package suffix should be included, for example, execl2006.xls

Public void CreateExcel (DataSet ds, string FileName)
{
HttpResponse resp;
Resp = Page. Response;
Resp. ContentEncoding = System. Text. Encoding. GetEncoding ("GB2312 ");
Resp. AppendHeader ("Content-Disposition", "attachment; filename =" + FileName );
String colHeaders = "", ls_item = "";

// Define the table object and row object, and use DataSet to initialize the value.
DataTable dt = ds. Tables [0];
DataRow [] myRow = dt. Select (); // you can filter data in the form of dt. Select ("id> 10 ").
Int I = 0;
Int cl = dt. Columns. Count;

// Obtain the titles of each column in the data table. The headers are separated by t. A carriage return is followed by the last column title.
For (I = 0; I <cl; I ++)
{
If (I = (cl-1) // The last column, plus n
{
ColHeaders + = dt. Columns [I]. Caption. ToString () + "n ";
}
Else
{
ColHeaders + = dt. Columns [I]. Caption. ToString () + "t ";
}

}
Resp. Write (colHeaders );
// Write the obtained data to the HTTP output stream

// Process data row by row
Foreach (DataRow row in myRow)
{
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
For (I = 0; I <cl; I ++)
{
If (I = (cl-1) // The last column, plus n
{
Ls_item + = row [I]. ToString () + "n ";
}
Else
{
Ls_item + = row [I]. ToString () + "t ";
}

}
Resp. Write (ls_item );
Ls_item = "";

}
Resp. End ();
}