C # datatable export Excel advanced multi-row header, merge cells, Chinese file name garbled

Original address of this article: http://blog.csdn.net/ranbolwb/article/details/8083983, reproduced please keep this line.

This example is an advanced step for exporting datatable excel. In addition to the Garbled text processing mentioned in the previous article, this example also adds the ability to process multi-row headers, merge cells, and handle Garbled text file names, it should be able to meet the needs of daily development.

If you don't talk much about it, go directly to the Code:

Using system; using system. collections. generic; using system. web; using system. configuration; using system. data; using system. data. common; using system. data. oledb; using system. web. UI. webcontrols; using system. text. regularexpressions; // <summary> // Summary of common // Author: Li Weibo // time: 2012-10-18 /// </Summary> public class common {public common () {// todo: add the constructor logic here // <summary> // Description: export the datatable content to exc. El and return the client // Author: Li Weibo // time: // </Summary> /// <Param name = "dtdata"> </param> /// <Param name = "Header"> </param> // /<Param name = "FILENAME"> </param> // <Param name = "mergecellnums"> Format of the column index dictionary to be merged: column index-merge mode (merge mode: 1 merge the same items, 2 merge null items, 3 merge the same items and empty items) </param> /// <Param name = "mergekey"> as the index of the Mark column of the merged item </param> Public static void datatable2excel (system. data. datatable dtdata, tablecell [] header, string filename, Dictionary <int, int> mergecellnums, Int? Mergekey) {system. web. UI. webcontrols. gridview gvexport = NULL; // The current Dialog System. web. httpcontext curcontext = system. web. httpcontext. current; // Io is used to export and return the Excel file system. io. stringwriter strwriter = NULL; system. web. UI. htmltextwriter htmlwriter = NULL; If (dtdata! = NULL) {// set the encoding and attachment format curcontext. response. contenttype = "application/vnd. MS-excel "; curcontext. response. contentencoding = system. text. encoding. getencoding ("gb2312"); curcontext. response. charset = "gb2312"; if (! String. isnullorempty (filename) {// process Chinese name garbled problem filename = system. web. httputility. urlencode (filename, system. text. encoding. utf8); curcontext. response. appendheader ("content-disposition", ("attachment; filename =" + (filename. tolower (). endswith (". xls ")? Filename: Filename + ". xls ");} // export the Excel file strwriter = new system. io. stringwriter (); htmlwriter = new system. web. UI. htmltextwriter (strwriter); // redefine a pageless gridview gvexport = new system. web. UI. webcontrols. gridview (); gvexport. datasource = dtdata. defaultview; gvexport. allowpaging = false; // optimizes the display of exported data, such as ID card and 12-1. rowdatabound + = new system. web. UI. webcontrols. gridviewroweventhandle R (dgexport_rowdatabound); gvexport. databind (); // process the header if (header! = NULL & header. length> 0) {gvexport. headerrow. cells. clear (); gvexport. headerrow. cells. addrange (header);} // merge the cell if (mergecellnums! = NULL & mergecellnums. count> 0) {foreach (INT cellnum in mergecellnums. keys) {mergerows (gvexport, cellnum, mergecellnums [cellnum], mergekey) ;}// return the client gvexport. rendercontrol (htmlwriter); curcontext. response. clear (); curcontext. response. write ("<meta http-equiv = \" Content-Type \ "content = \" application/MS-Excel; charset = gb2312 \ "/>" + strwriter. tostring (); curcontext. response. end () ;}/// <Su Mmary> // description: row binding event // </Summary> /// <Param name = "sender"> </param> /// <Param name = "E"> </param> protected static void dgexport_rowdatabound (Object sender, gridviewroweventargs e) {If (E. row. rowtype = datacontrolrowtype. datarow) {foreach (tablecell cell in E. row. cells) {// optimized the display of exported data, such as ID card and 12-1. If (RegEx. ismatch (cell. text. trim (), @ "^ \ D {12,} $") | RegEx. ismatch (cell. text. trim (), @ "^ \ D + [-] \ D + $ ") {Cell. attributes. add ("style", "Vnd. ms-excel.numberformat: @ ") ;}}/// <summary> // Description: Merge the same rows in the gridview column /// Author: Li Weibo // time: /// </Summary> /// <Param name = "gvexport"> column to be merged for the gridview object </param> /// <Param name = "cellnum"> </param> // <Param name = "mergemode"> merge Mode 1: Merge identical items, 2: Merge null items, 3: Merge identical items, and empty items </param> // /<Param name = "mergekey"> as the index of the tag column of the merged item </param> Public static void mergerows (gridview gv Export, int cellnum, int mergemode, Int? Mergekey) {int I = 0, rowspannum = 1; system. drawing. color altercolor = system. drawing. color. lightgray; while (I <gvexport. rows. count-1) {gridviewrow gvr = gvexport. rows [I]; for (++ I; I <gvexport. rows. count; I ++) {gridviewrow gvrnext = gvexport. rows [I]; If ((! Mergekey. hasvalue | (mergekey. hasvalue & (gvr. cells [mergekey. value]. text. equals (gvrnext. cells [mergekey. value]. text) | "". equals (gvrnext. cells [mergekey. value]. text) & (mergemode = 1 & gvr. cells [cellnum]. TEXT = gvrnext. cells [cellnum]. text) | (mergemode = 2 &&"". equals (gvrnext. cells [cellnum]. text. trim () | (mergemode = 3 & (gvr. cells [cellnum]. TEXT = gvrnext. cells [cellnum]. text | "". equals (gvrnext. cells [cellnum]. text. trim () {gvrnext. cells [cellnum]. visible = false; rowspannum ++; gvrnext. backcolor = gvr. backcolor;} else {gvr. cells [cellnum]. rowspan = rowspannum; rowspannum = 1; // adds background color to the interval row to facilitate reading if (mergekey. hasvalue & cellnum = mergekey. value) {If (altercolor = system. drawing. color. white) {gvr. backcolor = system. drawing. color. lightgray; altercolor = system. drawing. color. lightgray;} else {altercolor = system. drawing. color. white ;}} break;} if (I = gvexport. rows. count-1) {gvr. cells [cellnum]. rowspan = rowspannum; If (mergekey. hasvalue & cellnum = mergekey. value) {If (altercolor = system. drawing. color. white) gvr. backcolor = system. drawing. color. lightgray ;}}}}}}

The page call is as follows:

Tablecell [] header = new tablecell [29]; for (INT I = 0; I 

The above Code has not been strictly tested or has errors or omissions. Please refer to or use the code of this article.
The export result is as follows: