A Simple Method for querying data from the gridview and exporting Excel

This has always been what I want to write. Hey hey, there's nothing to think of today. It's a very common method. I heard that npoi can be used to export better results. I will try again later! I also hope that someone can recommend me ..........

 

// Link string

String strconn = "Data Source =.; database = hhaspx_gz; uid = sa; Pwd ="; // link to the SQL database

// Export Excel
Protected void button2_click (Object sender, eventargs E)
{
// Query data
Sqlconnection Cn = new sqlconnection (strconn );
CN. open ();
Sqldataadapter SDA = new sqldataadapter ("select * From hhaspx", CN );
Dataset DS = new dataset ();
SDA. Fill (DS, "hhaspx ");
String filename = datetime. Now. ticks + ". xls ";
Createexcel (DS, excelheader (), filename );
}

/// <Summary>
/// Set the field to be exported to be a field in the database.
/// </Summary>
/// <Returns> </returns>
Public dictionary <string, string> excelheader ()
{
Dictionary <string, string> DIC = new dictionary <string, string> ();
Dic. Add ("hhaspx_rq", "Project No ");
Dic. Add ("hhaspx_bh", "Project name ");
Dic. Add ("hhaspx_xm", "valuation objective ");
Dic. Add ("hhaspx_dx", "Valuation method ");
Dic. Add ("hhaspx_kh", "job start date ");
Dic. Add ("hhaspx_jl", "job end date ");
Dic. Add ("hhaspx_jt", "valuation operation period ");
Dic. Add ("hhaspx_jb", "Land Area ");
Dic. Add ("hhaspx_zb", "Building Area ");
Dic. Add ("hhaspx_jx", "estimate total price ");
Dic. Add ("hhaspx_hj", "estimated Object Location (located )");
Return DIC;
}

# Region general export Excel
/// <Summary>
/// Export Excel
/// </Summary>
/// <Param name = "ds"> data source </param>
/// <Param name = "dic"> fields to be displayed </param>
/// <Param name = "FILENAME"> default file name </param>
Public static void createexcel (Dataset ds, Dictionary <string, string> DIC, string filename)
{
Httpresponse resp;
Resp = system. Web. httpcontext. Current. response;
Resp. contentencoding = system. Text. encoding. getencoding ("gb2312 ");
Resp. appendheader ("content-disposition", "attachment; filename =" + filename );
// Define the row and column of the title
String colheaders = "", ls_item = "";
Datatable dt = Ds. Tables [0];
Datarow [] myrow = DT. Select (); // you can filter data in the form of DT. Select ("ID> 10 ").
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.
Foreach (keyvaluepair <string, string> kV in DIC)
{
Colheaders + = kV. Value + "\ t"; // \ t indicates a hop. Similar to the tab key function.
}
Colheaders + = "\ n"; // line feed after the title line is output
Resp. Write (colheaders );
Foreach (datarow onerow in myrow)
{
Foreach (keyvaluepair <string, string> Col in DIC)
{
Ls_item + = onerow [col. Key] + "\ t ";
}
Ls_item + = "\ n ";
// Write the data in the current row to the HTTP output stream, and leave ls_item empty for downstream data
Resp. Write (ls_item );
Ls_item = "";
}
Resp. End ();
}
# Endregion
}