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

Source: Internet
Author: User

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:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.