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

The example is an advanced step for exporting a DataTable Excel file. In addition to the Garbled text processing mentioned in the previous article, this example also adds the function of processing multiple row headers, merging cells, and processing 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:
[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: