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

Source: Internet
Author: User

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

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.