程序博客网 > 微信 代理服务器 知乎

GridView修改表头，并导出Excel(C#版)

来源：互联网 发布：微信 代理服务器 知乎 编辑：程序博客网 时间：2024/05/16 23:36

说明：此类是集合了网上朋友的一些类方法拼写而成，发表出来一便朋友们借鉴

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


/*/时间：2010年2月6日
//文件：ExcelEdit.cs
//功能：定制griedview控件的表头，并能够导出Excel
//作者：门剑勇
//声明：此类是集合了网上的一些资料，并注明出处。加入了部分自己写的东东*/


/// <summary>
/// ExcelEdit 的摘要说明
/// 注意事项一：在调用此类的页添加下列方法
///  public override void VerifyRenderingInServerForm(System.Web.UI.Control control)
///  {
///      //什么都没有，空的函数
///  }
///
/// 注意事项二：
/// SplitTableHeader函数是用在GridView控件的RowCreated事件中，调用方法如下例子：
/// protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
///        {
///            if (e.Row.RowType == DataControlRowType.Header)
///            {
///                ExcelEdit dHelper = new ExcelEdit();
///                string header = "等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值";
///                dHelper.SplitTableHeader(e.Row, header);
///            }
///        }
///
/// 注意事项三：
/// ModifygrdHeader函数，详细看函数说明。
/// </summary>
public class ExcelEdit
{
    /// <summary>
    /// ExcelEdit类构造函数
    /// </summary>
    public ExcelEdit()
    {
    }
   
    /// <summary>
    /// 定义dataset属性
    /// </summary>
    private DataSet _DS;
    public DataSet DS
    {
        set { _DS = value; }
        get { return _DS; }
    }

    /// <summary>
    /// 定义GridView属性
    /// </summary>
    private GridView _GV;
    public GridView GV
    {
        set { _GV = value;}
        get { return _GV; }
    }

    /// <summary>
    /// 绑定数据源，成功返回true，失败返回false。
    /// </summary>
    private bool BindGridView()
    {
        try
        {
            //定义GirdView的格式后，绑定数据源。
            _GV.AllowPaging = false;
            _GV.DataSource = _DS;
            _GV.DataBind();
        }
        catch (Exception e)
        {
            //错误跳转到指定的错误页面
            return false;
            string url = HttpContext.Current.Request.ApplicationPath + "/error.aspx?error=" + e.Message.ToString() + "&strurl=" + HttpContext.Current.Request.Url.ToString();
            HttpContext.Current.Response.Redirect(url);
        }
        //函数成功
        return true;
    }

    /// <summary>
    /// 导出GridView控件中的数据Excel
    /// </summary>
    public void toExcelgrdExcel()
    {
        if (BindGridView())
        {
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.Charset = "GB2312";
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=Qty.xls");
            //如果设置为 GetEncoding("GB2312");导出的文件将会出现乱码！！！
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            //设置输出文件类型为excel文件。
            HttpContext.Current.Response.ContentType = "application/ms-excel";
            //定义文件流，和控件输出流，建立关联。
            System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
            System.Web.UI.Html32TextWriter oHtmlTextWriter = new Html32TextWriter(oStringWriter);
            //将控件的信息写到输出流。
            _GV.RenderControl(oHtmlTextWriter);
            HttpContext.Current.Response.Output.Write(oStringWriter.ToString());
            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
        }
    }

   /// <summary>
   /// 在绑定后导出时可以改变_GV表头
   /// </summary>
    public void ModifygrdHeader()
    {
        try
        {
            _GV.HeaderRow.Cells.Clear();
            TableCell cell = new TableCell();
            cell.Text = "<tr  style='background-color: #006699;font-size: 15px;color: #FFFFFF;padding: 2pt;'><td rowspan='2' style='font-size=15px'>Customer</td><td  rowspan=2  style='font-size=15px'>" +
                         "Size</td><td rowspan='2'  style='font-size=15px'>TAC Film</td><td rowspan='2'  style='font-size=15px'>搭配性产品</td><td rowspan='2'  style='font-size=15px'>T/B</td><td rowspan='2'  style='font-size=15px'>Absorption Angle</td>" +
                         "<td rowspan='2'  style='font-size=15px'>Dimension_X</td><td rowspan='2'  style='font-size=15px'>Dimension_Y</td><td rowspan='2'  style='font-size=15px'>经济幅宽</td><td colspan='2'  style='font-size=15px'>1330mm</td><td colspan='2'  style='font-size=15px'>1475mm</td><td colspan='2'  style='font-size=15px'>2000mm</td>" +
                         "</tr><tr  style='background-color: #006699;font-size: 12px;color: #FFFFFF;padding: 2pt;'><td>片数/M</td><td>利用率</td><td>片数/M</td><td>利用率</td><td>片数/M</td><td>利用率</td>";

            _GV.HeaderRow.Cells.Add(cell);
        }
        catch (Exception e)
        {
            string url = HttpContext.Current.Request.ApplicationPath + "/error.aspx?error=" + e.Message.ToString() + "&strurl=" + HttpContext.Current.Request.Url.ToString();
            HttpContext.Current.Response.Redirect(url);
        }
    }

    #region
    // 用于生成Excel表头的函数块
    // 时间: 23:07 2008/9/28    Author: Andy Lu
    // 文件: AndyGridViewTHeaderHepler.cs
    // 功能: 动态生成复合表头帮助类
    // 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',').
    // 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名
    // 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值
    // 三行时请注意：列头要重复
    // 文件出处：http://www.cnblogs.com/mzoylee/archive/2009/09/24/1573504.html
    public void SplitTableHeader(GridViewRow targetHeader, string newHeaderNames)
    {
        //获得表头元素的实例
        TableCellCollection tcl = targetHeader.Cells;
        //清除元素
        tcl.Clear();
        int row = GetRowCount(newHeaderNames);
        int col = GetColCount(newHeaderNames);
        string[,] nameList = ConvertList(newHeaderNames, row, col);
        int RowSpan = 0;
        int ColSpan = 0;
        for (int k = 0; k < row; k++)
        {
            string LastFName = "";
            for (int i = 0; i < col; i++)
            {
                if (LastFName == nameList[i, k] && k != row - 1)
                {
                    LastFName = nameList[i, k];
                    continue;
                }
                else
                {
                    LastFName = nameList[i, k];
                }
                int bFlag = IsVisible(nameList, k, i, LastFName);
                switch (bFlag)
                {
                    case 0:
                        break;
                    case 1:
                        RowSpan = GetSpanRowCount(nameList, row, k, i);
                        ColSpan = GetSpanColCount(nameList, row, col, k, i);
                        //添加表头控件
                        tcl.Add(new TableHeaderCell());
                        tcl[tcl.Count - 1].RowSpan = RowSpan;
                        tcl[tcl.Count - 1].ColumnSpan = ColSpan;
                        tcl[tcl.Count - 1].HorizontalAlign = HorizontalAlign.Center;
                        tcl[tcl.Count - 1].Text = LastFName;
                        break;
                    case -1:
                        string[] EndColName = LastFName.Split(new char[] { ',' });
                        foreach (string eName in EndColName)
                        {
                            //添加表头控件
                            tcl.Add(new TableHeaderCell());
                            tcl[tcl.Count - 1].HorizontalAlign = HorizontalAlign.Center;
                            tcl[tcl.Count - 1].Text = eName;
                        }
                        break;
                }
            }
            if (k != row - 1)
            {//不是起始行,加入新行标签
                tcl[tcl.Count - 1].Text = tcl[tcl.Count - 1].Text + "</th></tr><tr class=" + targetHeader.CssClass + ">";
            }
        }
    }

    /// <summary>
    /// 如果上一行已经输出和当前内容相同的列头，则不显示
    /// </summary>
    /// <param name="ColumnList">表头集合</param>
    /// <param name="rowIndex">行索引</param>
    /// <param name="colIndex">列索引</param>
    /// <returns>1:显示,-1:含','分隔符,0:不显示</returns>
    private int IsVisible(string[,] ColumnList, int rowIndex, int colIndex, string CurrName)
    {
        if (rowIndex != 0)
        {
            if (ColumnList[colIndex, rowIndex - 1] == CurrName)
            {
                return 0;
            }
            else
            {
                if (ColumnList[colIndex, rowIndex].Contains(","))
                {
                    return -1;
                }
                else
                {
                    return 1;
                }
            }
        }
        return 1;
    }

    /// <summary>
    /// 取得和当前索引行及列对应的下级的内容所跨的行数
    /// </summary>
    /// <param name="ColumnList">表头集合</param>
    /// <param name="row">行数</param>
    /// <param name="rowIndex">行索引</param>
    /// <param name="colIndex">列索引</param>
    /// <returns>行数</returns>
    private int GetSpanRowCount(string[,] ColumnList, int row, int rowIndex, int colIndex)
    {
        string LastName = "";
        int RowSpan = 1;
        for (int k = rowIndex; k < row; k++)
        {
            if (ColumnList[colIndex, k] == LastName)
            {
                RowSpan++;
            }
            else
            {
                LastName = ColumnList[colIndex, k];
            }
        }
        return RowSpan;
    }

    /// <summary>
    /// 取得和当前索引行及列对应的下级的内容所跨的列数
    /// </summary>
    /// <param name="ColumnList">表头集合</param>
    /// <param name="row">行数</param>
    /// <param name="col">列数</param>
    /// <param name="rowIndex">行索引</param>
    /// <param name="colIndex">列索引</param>
    /// <returns>列数</returns>
    private int GetSpanColCount(string[,] ColumnList, int row, int col, int rowIndex, int colIndex)
    {
        string LastName = ColumnList[colIndex, rowIndex];
        int ColSpan = ColumnList[colIndex, row - 1].Split(new char[] { ',' }).Length;
        ColSpan = ColSpan == 1 ? 0 : ColSpan;
        for (int i = colIndex + 1; i < col; i++)
        {
            if (ColumnList[i, rowIndex] == LastName)
            {
                ColSpan += ColumnList[i, row - 1].Split(new char[] { ',' }).Length;
            }
            else
            {
                LastName = ColumnList[i, rowIndex];
                break;
            }
        }
        return ColSpan;
    }

    /// <summary>
    /// 将已定义的表头保存到数组
    /// </summary>
    /// <param name="newHeaders">新表头</param>
    /// <param name="row">行数</param>
    /// <param name="col">列数</param>
    /// <returns>表头数组</returns>
    private string[,] ConvertList(string newHeaders, int row, int col)
    {
        string[] ColumnNames = newHeaders.Split(new char[] { '#' });
        string[,] news = new string[col, row];
        string Name = "";
        for (int i = 0; i < col; i++)
        {
            string[] CurrColNames = ColumnNames[i].ToString().Split(new char[] { ' ' });
            for (int k = 0; k < row; k++)
            {
                if (CurrColNames.Length - 1 >= k)
                {
                    if (CurrColNames[k].Contains(","))
                    {
                        if (CurrColNames.Length != row)
                        {
                            if (Name == "")
                            {
                                news[i, k] = news[i, k - 1];
                                Name = CurrColNames[k].ToString();
                            }
                            else
                            {
                                news[i, k + 1] = Name;
                                Name = "";
                            }
                        }
                        else
                        {
                            news[i, k] = CurrColNames[k].ToString();
                        }
                    }
                    else
                    {
                        news[i, k] = CurrColNames[k].ToString();
                    }
                }
                else
                {
                    if (Name == "")
                    {
                        news[i, k] = news[i, k - 1];
                    }
                    else
                    {
                        news[i, k] = Name;
                        Name = "";
                    }
                }
            }
        }
        return news;
    }

    /// <summary>
    /// 取得复合表头的行数
    /// </summary>
    /// <param name="newHeaders">新表头</param>
    /// <returns>行数</returns>
    private int GetRowCount(string newHeaders)
    {
        string[] ColumnNames = newHeaders.Split(new char[] { '#' });
        int Count = 0;
        foreach (string name in ColumnNames)
        {
            int TempCount = name.Split(new char[] { ' ' }).Length;
            if (TempCount > Count)
                Count = TempCount;
        }
        return Count;
    }

    /// <summary>
    /// 取得复合表头的列数
    /// </summary>
    /// <param name="newHeaders">新表头</param>
    /// <returns>列数</returns>
    private int GetColCount(string newHeaders)
    {
        return newHeaders.Split(new char[] { '#' }).Length;
    }
    #endregion
}

微信 代理服务器 知乎 微信 代理服务器 知乎
原创粉丝点击
热门IT博客
大数据产业园规划方案大数据产业园规划方案
单位网络不能玩游戏单位网络不能玩游戏
mac版国服lol
mssql数据库迁移
plc控制编程 模块化
张国荣气质 知乎
淘宝客返利api
蒂姆邓肯数据
商家管理 源码
网络键盘侠800作文
软件测试行业学费
高音哥 知乎
淘宝没有指纹支付选项
linux 倒序
用文言文说网络流行句
甘肃自助旅游攻略知乎
守望狂鼠数据
北京80越野车数据
linux搭建jenkins环境
艾薇软件下载
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 属相年份 1962年属相 属相与楼层 什么属相好 明年属相 年份属相 属相和年份 哪些属相不适合戴玉 哪些属相适合戴玉 那些属相不适合戴玉 十二属相顺序 2021年哪个属相不适宜生宝宝 2020年哪个属相不适宜生宝宝 属相老鼠今年多大 86年属虎的属相婚配表 蛇与什么属相相配 92年属猴的属相婚配表 94年属狗的属相婚配表 84年属鼠的属相婚配表 90年属马的属相婚配表 81年属鸡的属相婚配表 属蛇的属相婚配表大全 虎相配的属相 男属猪的属相婚配表 婚姻属相匹配 12是什么生肖 二十生肖 44是什么生肖 12个生肖 生肖运 月份生肖 2017生肖 鸡生肖 生肖币 2016生肖 虎生肖 生肖排序 属羊今年 1991年属羊 属羊年份 属羊的年份

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里