Export excel. net

All of the following is transferred from http://blog.csdn.net/tzjly/article/details/6952952

 

Using system;
Using system. collections;
Using system. configuration;
Using system. Data;
Using system. LINQ;
Using system. Web;
Using system. Web. Security;
Using system. Web. UI;
Using system. Web. UI. htmlcontrols;
Using system. Web. UI. webcontrols;
Using system. Web. UI. webcontrols. webparts;
Using system. xml. LINQ;
Using system. IO;
Using system. text;
Using system. Data. sqlclient;

Public partial class _ default: system. Web. UI. Page
{
Protected void page_load (Object sender, eventargs E)
{

}
Protected void button#click (Object sender, eventargs E)
{

}
Protected void button2_click (Object sender, eventargs E)
{
Export ("application/MS-excel", ".xls ");
}

// Set the followingCodeCopy to. CS file to view

# Region gridview export the current page to excel by tzjly.20111108
/// <Summary>
/// Export excel in the gridview
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "E"> </param>

// The method called by executing the "button" of the Export command
Protected void btndc_click (Object sender, eventargs E)
{
Export ("application/MS-excel", ".xls ");
}
// Export the current Excel page

Public void binddatatogridview ()
{

String STR = system. configuration. configurationmanager. connectionstrings ["kyj2008connectionstring2"]. connectionstring;
Sqlconnection con = new sqlconnection (STR );
Sqlcommand cmd = new sqlcommand ("Exec proc_tzj_zzctj 'tang', '2017-10-1 ', '2017-12-31'", con );
Sqldataadapter SDA = new sqldataadapter ();
SDA. selectcommand = cmd;
Dataset DS = new dataset ();
SDA. Fill (DS );
// This. gridview1.datasource = Ds;
This. gridview1.databind ();
}

Private void Export (string filetype, string filename)

{
Response. Clear ();
Response. Buffer = true;
Response. charset = "gb2312 ";
Response. appendheader ("content-disposition", "attachment?filename=filename.xls"); // The parameter is not used here because the generated Excel file name is garbled when it is in Chinese
Response. contentencoding = system. Text. encoding. utf7; // if it is set to getencoding ("gb2312"); the exported file will contain garbled characters !!!
Response. contenttype = filetype; // set the output file type to an Excel file.
System. Io. stringwriter ostringwriter = new system. Io. stringwriter ();
System. Web. UI. htmltextwriter ohtmltextwriter = new system. Web. UI. htmltextwriter (ostringwriter );

# Region remove the style of the gridview before exporting the Excel file, but it is complicated to remove the style of all related elements, therefore, if you want to remove all styles and then export them, you can create a page containing the gridview (excluding any styles), bind the required data, and then export the data.
This. gridview1.columns [This. gridview1.columns. Count-1]. Visible = false; // you can hide the columns you do not want to export (the last column "delete" in the gridview is hidden)
// This. gridview1.headerstyle. Reset (); // remove the Header style in the gridview before exporting the Excel file
// This. gridview1.rowstyle. Reset (); // remove the row style in the gridview before exporting the Excel file.
This. gridview1.pagerstyle. Reset (); // remove the pager style from the gridview before exporting the Excel file.
// This. gridview1.controlstyle. Reset (); // remove the control style in the gridview before exporting the Excel file [that is, the applied gridview format]
# Endregion

This. gridview1.rendercontrol (ohtmltextwriter); // you can export the information in the Repeater control.
This. gridview1.allowpaging = false;
Response. Output. Write (ostringwriter. tostring ());
Response. Flush ();
Response. End ();
// This. gridview1.allowpaging = true;
}
# Endregion

# Region gridview export all pages to excel by tzjly.20111109 ----
Private void export2 (string filetype, string filename)

{< br> // clear page
gridview1.allowpaging = false;
binddatatogridview ();
response. clear ();
response. buffer = true;
// set the output character set
response. charset = "gb2312";
// solve the problem of garbled characters exported to excel2007
response. write (" ");

// The exported file name is the inventory result table. xls
Response. appendheader ("content-disposition", "attachment; filename =" +
Httputility. urlencode (filename, encoding. utf8). tostring ());
// Solve the problem of garbled data exported to excel2007
Response. contentencoding = system. Text. encoding. getencoding ("gb2312 ");

// Set the format of the exported file
Response. contenttype = filetype;
// Close viewstate
This. enableviewstate = false;
Stringwriter = new stringwriter ();
Htmltextwriter textwriter = new htmltextwriter (stringwriter );
Gridview1.rendercontrol (textwriter );
// Write the HTML back to the browser
Response. Write (stringwriter. tostring ());
Response. Flush ();
Response. End ();
Gridview1.allowpaging = true; // restore the page
// Re-bind the data source to the gridview
Binddatatogridview ();

}

// The following method is required to export the gridview to excel. Otherwise, an error is reported. You can remove the error message.
Public override void verifyrenderinginserverform (Control)
{

}< br> // The exported cell contains long numbers (such as ID card) does not display scientific notation
protected void gridview1_rowdatabound (Object sender, gridviewroweventargs e)
{< br> If (E. row. rowtype = datacontrolrowtype. datarow)
{< br> E. row. cells [4]. attributes. add ("style", "Vnd. ms-excel.numberformat: @ ");
E. row. cells [5]. attributes. add ("style", "Vnd. ms-excel.numberformat: @ ");
E. row. cells [6]. attributes. add ("style", "Vnd. ms-excel.numberformat: @ ");
}< BR >#endregion

Protected void btndc2_click (Object sender, eventargs E)
{
Export2 ("application/MS-excel", "all cards .xls ");
}
}