Asp.net (C #) Export Excel -- Common header, multi-layer header.

At work, use Asp.net (C #) to export an Excel file from the page. Complex table Header

So I tried several methods and listed them for your reference:

The first method is mainly for exporting a simple Excel file with only one row of column names and data.

There is an export button on the page. Only the code of the button event is written below:

Try {system. data. datatable table = binddata ("trdate DESC"); // Add a row to the datatable and merge all columns string [] arr = new string [10] {"printed card number ", "Serial Number", "Merchant name", "Merchant number", "POS number", "date and time", "occurrence", "operator", "type ", "Remarks"}; For (INT I = 0; I <table. columns. count; I ++) {table. columns [I]. columnname = arr [I]. tostring ();} dataview dv1 = table. defaultview; dv1.sort = "trdate DESC"; // sorts datatable by POS number (Branch) and date and time by inverted order table = dv1.t Otable (); excelhelper. datatable1excel (table);} catch {response. Write ("<SCRIPT> function window. onload () {alert ('export failed! ') ;}</SCRIPT> ");}

In the excelhelper class:

  public static void DataTable1Excel(System.Data.DataTable dtData)        {            try             {             GridView gvExport = null;             HttpContext curContext = HttpContext.Current;             StringWriter strWriter = null;            HtmlTextWriter htmlWriter = null;            if (dtData != null)            {                curContext.Response.ContentType = "application/vnd.ms-excel";                curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");                curContext.Response.Charset = "utf-8";                strWriter = new StringWriter();                htmlWriter = new HtmlTextWriter(strWriter);                gvExport = new GridView();                gvExport.DataSource = dtData.DefaultView;                gvExport.AllowPaging = false;                gvExport.DataBind();                gvExport.RenderControl(htmlWriter);                curContext.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=gb2312\"/>" + strWriter.ToString());                curContext.Response.End();            }            }            catch (Exception e)            {                 throw e;            }        }

Method 2: Reference of myxls is required. Org. in2bits. myxls

Try {Org. in2bits. myxls. xlsdocument TZ = new Org. in2bits. myxls. xlsdocument (); tz. filename = datetime. now. tostring (). replace ("-",""). replace (":",""). replace ("", "") + ". xls "; // Excel file name Org. in2bits. myxls. worksheet sheet = tz. workbook. worksheets. addnamed ("36524 recharge details"); // Excel worksheet name Org. in2bits. myxls. cells = sheet. cells; int colnum = gridview_data.columns.count; // obtain the number of columns in the gridview for (INT I = 0; I <colnum; I ++) {cells. addvaluecell (1, (I + 1), gridview_data.columns [I]. headertext); // export the gridview column name} datatable table = binddata ("trdate DESC"); For (INT I = 0; I <table. rows. count; I ++) {for (Int J = 0; j <colnum; j ++) {cells. addvaluecell (I + 2), (J + 1), table. rows [I] [J]. tostring () ;}} tz. send (); // output the prepared Excel file to the client} catch {}

Method 3: ----------------- complex Header

Datatable table = binddata ("trdate DESC"); string [] arr = new string [10] {"card printing number", "Serial Number", "Merchant name ", "Merchant number", "POS number", "date and time", "occurrence", "operator", "type", "Remarks"}; For (INT I = 0; I <table. columns. count; I ++) {table. columns [I]. columnname = arr [I]. tostring ();} dataview dv1 = table. defaultview; dv1.sort = "trdate DESC"; // sorts datatable by POS number (Branch) and date and time by inverted order table = dv1.totable (); string summary = labelsumls. text; exportbu36524czmx ("recharge details", summary, table );

# Region export Excel -- Method 3 protected void exportbu36524czmx (string titlename, string summary, datatable table) {try {string temp_path = environment. getfolderpath (environment. specialfolder. desktop); // The generated file storage path exceloperate = new exceloperate (); // create an Excel file. the new process Microsoft. office. interOP. excel. application APP = new Microsoft. office. interOP. excel. application (); If (APP = NULL) {retu Rn;} app. visible = false; app. usercontrol = true; Microsoft. office. interOP. excel. workbooks workbooks = app. workbooks; Microsoft. office. interOP. excel. _ workbook = workbooks. add (Microsoft. office. interOP. excel. xlwbatemplate. xlwbatworksheet); // The parameter in the add method here is equivalent to inheriting an empty template (for now, let's take a look) Microsoft. office. interOP. excel. sheets sheets = workbook. worksheets; Microsoft. office. interOP. excel. _ worksheet workshee T = (Microsoft. office. interOP. excel. _ worksheet) sheets. get_item (1); If (worksheet = NULL) {return;} worksheet. rows. numberformatlocal = "@"; // set all cells to the text format // The title worksheet of the first line. get_range (worksheet. cells [1, 1], worksheet. cells [1, table. columns. count]). merge (missing. value); // horizontally merged worksheet. get_range (worksheet. cells [1, 1], worksheet. cells [1, 1]). value2 = titlename; // sets the format of exceloperate. sethaligncenter (Worksheet, worksheet. cells [1, 1], worksheet. cells [1, 1]); // center exceloperate. setfontsize (worksheet, worksheet. cells [1, 1], worksheet. cells [1, 1], 12); // font size worksheet. get_range (worksheet. cells [1, 1], worksheet. cells [1, table. columns. count]). borders. color = system. drawing. colortranslator. toole (system. drawing. color. black); // black continuous border // a review of worksheet in the second row. get_range (worksheet. cells [2, 1], worksheet. cells [2, Table. columns. count]). merge (missing. value); // horizontally merged worksheet. get_range (worksheet. cells [2, 1], worksheet. cells [2, 1]). value2 = Summary; // sets the format of exceloperate. sethaligncenter (worksheet, worksheet. cells [2, 1], worksheet. cells [2, 1]); // center exceloperate. setfontsize (worksheet, worksheet. cells [2, 1], worksheet. cells [2, 1], 9); // font size worksheet. get_range (worksheet. cells [2, 1], worksheet. cells [1, table. column S. count]). borders. color = system. drawing. colortranslator. toole (system. drawing. color. black); // black continuous border for (INT I = 0; I <table. columns. count; I ++) {worksheet. cells [3, I + 1] = table. columns [I]. columnname. tostring (); exceloperate. setfontsize (worksheet, worksheet. cells [3, I + 1], worksheet. cells [3, I + 1], 9); // font size exceloperate. setbold (worksheet, worksheet. cells [3, I + 1], worksheet. cells [3, I + 1] ); //} Worksheet. get_range (worksheet. cells [3, 1], worksheet. cells [3, table. columns. count]). borders. color = system. drawing. colortranslator. toole (system. drawing. color. black); For (INT I = 0; I <table. rows. count; I ++) {for (Int J = 0; j <table. columns. count; j ++) {string data = table. rows [I] [J]. tostring (); worksheet. cells [4 + I, j + 1] = data; exceloperate. setfontsize (worksheet, worksheet. cell S [4 + I, j + 1], worksheet. cells [4 + I, j + 1], 9); // font size // If (j = 6) // {// worksheet. cells [4 + I, j + 1]. //} worksheet. get_range (worksheet. cells [4 + I, 1], worksheet. cells [4 + I, 10]). borders. color = system. drawing. colortranslator. toole (system. drawing. color. black); // set the border color. Otherwise, the preview will be very unsightly} worksheet. name = titlename; worksheet. columns. entirecolumn. autofit (); // adaptive string tick = datetime for column width. now. Tostring (). replace ("-",""). replace (":",""). replace ("", "") + ". xls "; // Excel file name string save_path = temp_path +" \ "+ tick + ". xls "; workbook. saveas (save_path, missing. value, missing. value, missing. value, missing. value, missing. value, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, missing. value, missing. value, missing. value, missing. value, missing. value); exceloperate. dispose (WO Rksheet, workbook, APP); // close the Excel process response. Write ("<SCRIPT> function window. onload () {alert ('export the Excel file to the desktop! ') ;}</SCRIPT> ") ;}catch {response. Write (" <SCRIPT> function window. onload () {alert (' export failed! ') ;}</SCRIPT> ") ;}# endregion

Result:

Method 4 ------------------------ complex table header, same as the result of method 3. --------------------------- The last 4th methods I used.

String exceltile = label1.text; string summary = labelsumls. text; tablecell [] header = new tablecell [12]; for (INT I = 0; I 
The common class code is as follows:
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; namespace contractsystem. webui. dataanalyse {// <summary> // Summary of common /// Author: Li Weibo // time: 2012-10-18 /// </Summary> public class common {public common () {// Todd O: add the constructor logic here /// <summary> /// description: export the datatable content to excel and return the client // </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, tablecel L [] 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) {// optimizes the display of exported data, for example, if (RegEx. ismatch (cell. text. trim (), @ "^ \ D {12, }$") {Cell. attributes. add ("style", "Vnd. MS-exce L. numberformat: @ ") ;}}/// <summary> // description: merge the same rows in the gridview column /// </Summary> /// <Param name = "gvexport"> gridview object </param> /// <Param name = "cellnum "> columns to be merged </param> // <Param name =" mergemode "> merge mode: 1 merge the same items, 2 merge null items, 3 merge the same items, and null items </param> /// <Param name = "mergekey"> as the index of the tag column of the merged item </param> Public static void mergerows (gridview gvexport, 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 for ease of 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 method reference link in 4th is as follows: {in my program, the line interval color is removed. In common, the comment-out statement is a line at each interval, and the background of the line is grayed out}
Http://blog.csdn.net/ranbolwb/article/details/8083983