C# WinForm DataTable 导出 Excel 多行表头、合并单元格

C# 代码 同时被 2 个专栏收录
63 篇文章 0 订阅
14 篇文章 0 订阅

本次的代码是根据以前的asp.net DataTable2Excel 类修改而来,并进行了优化,使用方法与asp.net版相同。

本例用到了Microsoft.Office.Interop.Excel.dll,版本 12.0.4518.1014

asp.net DataTable2Excel 类的文章链接:C# DataTable 导出 Excel 进阶 多行表头、合并单元格、中文文件名乱码


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;
using System.IO;
using System.Text;
using System.Web.UI;

namespace DataTable2Excel
{
    /// <summary>
    /// Common 的摘要说明
    /// 作者:李伟波
    /// 时间:2016-4-8
    /// </summary>
    public class Common
    {
        public Common()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 描述:把DataTable内容导出excel并返回客户端 
        /// 作者:李伟波
        /// 时间:2016-4-8
        /// </summary>
        /// <param name="dtData"></param>
        /// <param name="header"></param>
        /// <param name="fileName"></param>
        /// <param name="mergeCellNums">要合并的列索引字典 格式:列索引-合并模式(合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项)</param>
        /// <param name="mergeKey">作为合并项的标记列索引</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;
            // IO用于导出并返回excel文件 
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                // 导出excel文件 
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);

                // 重新定义一个无分页的GridView 
                gvExport = new System.Web.UI.WebControls.GridView();
                gvExport.DataSource = dtData.DefaultView;
                gvExport.AllowPaging = false;
                //优化导出数据显示,如身份证、12-1等显示异常问题
                gvExport.RowDataBound += new System.Web.UI.WebControls.GridViewRowEventHandler(dgExport_RowDataBound);

                gvExport.DataBind();
                //处理表头
                if (header != null && header.Length > 0)
                {
                    gvExport.HeaderRow.Cells.Clear();
                    gvExport.HeaderRow.Cells.AddRange(header);
                }
                //合并单元格
                if (mergeCellNums != null && mergeCellNums.Count > 0)
                {
                    foreach (int cellNum in mergeCellNums.Keys)
                    {
                        MergeRows(gvExport, cellNum, mergeCellNums[cellNum], mergeKey);
                    }
                }

                // 返回客户端 
                gvExport.RenderControl(htmlWriter);

                StreamWriter sw = new StreamWriter(fileName, false, Encoding.GetEncoding("gb2312"));
                sw.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
                sw.Flush();
                sw.Close();
                sw.Dispose();

                strWriter.Close();
                strWriter.Dispose();
                //保存的文件并不是真正的Excel格式,用Excel打开会有格式不一致的提示,为了更好的体验,保存为Excel2003格式。
                SaveAsExcel2003(fileName);
            }
        }
        /// <summary>
        /// 描述:行绑定事件
        /// </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)
                {
                    //优化导出数据显示,如身份证、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>   
        /// 描述:合并GridView列中相同的行
        /// 作者:李伟波
        /// 时间:2016-4-8
        /// </summary>   
        /// <param   name="gvExport">GridView对象</param>   
        /// <param   name="cellNum">需要合并的列</param>   
        /// <param name="mergeMode">合并模式 1 合并相同项、2 合并空项、3 合并相同项及空项</param>
        /// <param name="mergeKey">作为合并项的标记列索引</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;
                        if (alterColor == System.Drawing.Color.White)
                            gvrNext.BackColor = System.Drawing.Color.LightGray;
                    }
                    else
                    {
                        gvr.Cells[cellNum].RowSpan = rowSpanNum;
                        rowSpanNum = 1;
                        //间隔行加底色,便于阅读
                        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;
                        }
                    }
                }
            }
        }
        /// <summary>
        /// 另存为2003格式Excel
        /// </summary>
        /// <param name="filePath"></param>
        public static void SaveAsExcel2003(string filePath)
        {
            Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
            app.DisplayAlerts = false; //取消所有提示窗口
            Microsoft.Office.Interop.Excel.Workbooks wbs = app.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook wb = wbs.Open(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            //另存为Excel2003格式
            wb.SaveAs(filePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, null, null, false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);
            wb.Close(Type.Missing, Type.Missing, Type.Missing);
            wbs.Close();
            app.Quit();
            wb = null;
            wbs = null;
            app = null;
            GC.Collect();
        }
    }
}




  • 0
    点赞
  • 0
    评论
  • 4
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值