C # DataTable export Excel advanced multi-row headers and merge Cells

If you don't talk much about it, go directly to the Code:
[Csharp]
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 excel and return it to the client.
/// Author: Li Weibo
/// Time: 2012-10-18
/// </Summary>
/// <Param name = "dtData"> </param>
/// <Param name = "header"> </param>
/// <Param name = "fileName"> </param>
/// <Param name = "mergeCellNums"> the column index dictionary format 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 marked 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;
// Current Dialog
System. Web. HttpContext curContext = System. Web. HttpContext. Current;
// IO is used to export and return an 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 ))
{
// Handle Chinese garbled characters
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 an excel file
StrWriter = new System. IO. StringWriter ();
HtmlWriter = new System. Web. UI. HtmlTextWriter (strWriter );
 
// Redefine a pagination-free GridView
GvExport = new System. Web. UI. WebControls. GridView ();
GvExport. DataSource = dtData. DefaultView;
GvExport. AllowPaging = false;
// Optimized the display of exported data, such as ID card and 12-1.
GvExport. RowDataBound + = new System. Web. UI. WebControls. GridViewRowEventHandler (dgExport_RowDataBound );
 
GvExport. DataBind ();
// Process the header
If (header! = Null & header. Length> 0)
{
GvExport. HeaderRow. Cells. Clear ();
GvExport. HeaderRow. Cells. AddRange (header );
}
// Merge Cells
If (mergeCellNums! = Null & mergeCellNums. Count> 0)
{
Foreach (int cellNum in mergeCellNums. Keys)
{
MergeRows (gvExport, cellNum, mergeCellNums [cellNum], mergeKey );
}
}
 
// Return to 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 ();
}
}
/// <Summary>
/// 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: 2012-10-18
/// </Summary>
/// <Param name = "gvExport"> GridView object </param>
/// <Param name = "cellNum"> columns to be merged </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 marked 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 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:
[Html]
TableCell [] header = new TableCell [29];
For (int I = 0; I {
Header [I] = new TableHeaderCell ();
}
Header [0]. ColumnSpan = 7;
Header [0]. Text = "basic order information ";
Header [1]. ColumnSpan = 4;
Header [1]. Text = "receiver information ";
Header [2]. ColumnSpan = 4;
Header [2]. Text = "courier information ";
Header [3]. ColumnSpan = 3;
Header [3]. Text = "payment information ";
Header [4]. ColumnSpan = 6;
Header [4]. Text = "item Information </th> </tr> <tr> ";
// The second line
Header [5]. Text = "Order No ";
Header [6]. Text = "order type ";
Header [7]. Text = "order status ";
Header [8]. Text = "Order Time ";
Header [9]. Text = "Payment Time ";
Header [10]. Text = "shipping time ";
Header [11]. Text = "Remarks ";
 
Header [12]. Text = "recipient's name ";
Header [13]. Text = "Address ";
Header [14]. Text = "mobile phone number ";
Header [15]. Text = "delivery method ";
 
Header [16]. Text = "logistics company name ";
Header [17]. Text = "logistics Bill of Lading ";
Header [18]. Text = "Freight Revenue ";
Header [19]. Text = "actual delivery fee ";
 
Header [20]. Text = "total order amount ";
Header [21]. Text = "Payment Method ";
Header [22]. Text = "order payment amount ";
 
Header [23]. Text = "item No ";
Header [24]. Text = "Product Name ";
Header [25]. Text = "commodity price ";
Header [26]. Text = "quantity purchased ";
Header [27]. Text = "Total item amount ";
Header [28]. Text = "discount amount </th> ";
 
DataTable dt = Common. DbHelper. DBClass_GetDataToTable (sqlDHD, sqlParam, ref rMsg );
 
Dictionary <int, int> mergeCellNums = new Dictionary <int, int> ();
For (int I = 0; I <dt. Columns. Count; I ++)
{
MergeCellNums. Add (I, 2 );
}
Common. DataTable2Excel (dt, header, "data export" + DateTime. Now. ToString ("yyyyMMdd"), mergeCellNums, 0 );

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: