如何动态添加模板列，且模板列中动态写入lable的ID？

于 2010-08-12 17:11:00 发布
阅读量1.4k 收藏
点赞数
文章标签： string sql object report cmd sql server
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/UnOpenMyCode/article/details/5807468
版权

废话不多说，代码端上来：

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.OracleClient;
using Syncfusion.XlsIO;
/// <summary>
/// 时间分组话务统计报表
/// </summary>
public partial class report_Report4 : System.Web.UI.Page
{
    TemplateField tf;
    public string _kindID;
    public string KindID
    {
        get
        {
            return _kindID;
        }
        set
        {
            _kindID = value;
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
     
      
    }
    public void BuildColumn()
    {
        using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
        {
            string sql = " select top 1 from dc_callskind";        
            sql += " order by Kind";
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            connection.Open(); 
            OracleDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                tf = new TemplateField();
                this.KindID = dr["KIND"].ToString();
                myTemplate aa = new myTemplate();
                tf.HeaderText = dr["KINDNAME"].ToString();
                tf.ItemTemplate = aa;
               
                this.gridItems.Columns.Add(tf);
               
            }


         
        }
   
   
    }
    private DataTable MakeSpansTable()
    {
        // Create a new DataTable titled 'Names.'
        DataTable namesTable = new DataTable("TimeSpan");


        DataColumn fNameColumn = new DataColumn();
        fNameColumn.DataType = System.Type.GetType("System.String");
        fNameColumn.ColumnName = "Span";
        fNameColumn.DefaultValue = "";
        namesTable.Columns.Add(fNameColumn);

        fNameColumn = new DataColumn();
        fNameColumn.DataType = System.Type.GetType("System.String");
        fNameColumn.ColumnName = "ToTal";
        fNameColumn.DefaultValue = "";
        namesTable.Columns.Add(fNameColumn);

        return namesTable;
    }
  
   
    protected void gridItems_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        Label lb;
        if (e.Row.RowType == DataControlRowType.DataRow)
        {
            int total = 0;
            using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
            {
                string sql = " select * from dc_callskind";
                sql += " where dc_callskind.CANUSE=0";
                sql += " order by Kind";
                OracleCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                connection.Open();
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    lb = (Label)e.Row.FindControl("lb_"+dr["KIND"].ToString());
                    lb.Text = this.GetCount(dr["KIND"].ToString(),e.Row.Cells[0].Text);
                    total += int.Parse(lb.Text); 
                }
            }

            e.Row.Cells[1].Text = total.ToString();    

 

        }
    }
    public string GetCount(string kind,string span)
    {
        string rtn;
        string beginTiemSpan = span.ToString() + " 00:00:00";
        string endTiemSpan = span.ToString() + " 23:59:59";
        using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
        {
            connection.Open();
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = "select count(s.kind) from dc_callsanalysis s where s.kind="+kind;
            cmd.CommandText += " AND s.calltime between  TO_DATE('" + beginTiemSpan + "','yyyy-mm-dd hh24:mi:ss') and  ";
            cmd.CommandText += " TO_DATE('" + endTiemSpan + "','yyyy-mm-dd hh24:mi:ss')";
            if (((System.Data.OracleClient.OracleNumber)(cmd.ExecuteOracleScalar())).IsNull == true)
            {
                rtn = "0";
            }
            else
            {
                rtn = cmd.ExecuteOracleScalar().ToString();

            }

        }
        return rtn;
    }
    protected void BtnStatistic_Click(object sender, EventArgs e)
    {
        this.BindByDay();
       // BuildColumn();
    }
    private void BindByDay()
    {
        DataTable table;
        DataRow row;
        table = MakeSpansTable();
        string value = "";
        TimeSpan tp;
        //string shorDate = "";
        if ((!string.IsNullOrEmpty(this.wdate_visitTimeStart.Value) && (!string.IsNullOrEmpty(this.wdate_visitTimeEnd.Value) && (Convert.ToDateTime(this.wdate_visitTimeStart.Value) < Convert.ToDateTime(this.wdate_visitTimeEnd.Value)))))
        {
            DateTime dt_begin = Convert.ToDateTime(this.wdate_visitTimeStart.Value);
            DateTime dt_end = Convert.ToDateTime(this.wdate_visitTimeEnd.Value);
            tp = dt_end - dt_begin;
            if (tp.Days > 1)
            {

                for (int d = 0; d < tp.Days; d++)
                {
                    DateTime time = Convert.ToDateTime(this.wdate_visitTimeStart.Value) + TimeSpan.FromDays(d);
                    value = time.ToShortDateString();
                    row = table.NewRow();
                    row["Span"] = value;
                    table.Rows.Add(row);

                }
            }
        }

 

       ///<--------Rebuild these TemplateFields   防止页面刷新的时候，丢失模板列组！
        if (this.gridItems.Columns.Count > 2)
        {
            int count = this.gridItems.Columns.Count-2;
            for (int i = 0; i < count; i++)
            {
                this.gridItems.Columns.RemoveAt(2);               
            }
        }
        ///-------->Rebuild these TemplateFields    

 

 

        if (this.gridItems.Columns.Count == 2)
        {
            using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
            {
                string sql = " select * from dc_callskind";
                sql += " where dc_callskind.CANUSE=0";
                sql += " order by Kind";  //模板列头皆来自这个表
                //string sql = "select * from (select rownum rn,a.* from dc_callskind a) where rn <=1";   //--juse like sql server statement:selet top 1
                OracleCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                connection.Open();
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    tf = new TemplateField();
                    this.KindID = dr["KIND"].ToString();
                    myTemplate aa = new myTemplate(this.KindID); 构造函数的妙用！
                  
                    tf.HeaderText = dr["KINDNAME"].ToString();
                    tf.ItemTemplate = aa;
                 
                    this.gridItems.Columns.Add(tf);

                }

 

            }

        }
        this.gridItems.DataSource = table.DefaultView;
        this.gridItems.DataBind();

    }
    protected void BtnExcel_Click(object sender, EventArgs e)
    {
        if (this.gridItems.RecordCount != 0)
        {
            string path = Server.MapPath("~/模板/时间分组话务统计报表.xls");
            string sFile = HttpContext.Current.Server.MapPath(HttpContext.Current.Request.ApplicationPath) + "//" + DateTime.Now.ToString("yyyyMMddHHmmssfffffff") + "_RenGongHuaWu.xls";
            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            IWorkbook workbook = excelEngine.Excel.Workbooks.Open(path);
            IWorksheet sheet1 = workbook.Worksheets[0];
            sheet1.Range["A3"].Value2 = "开始日期:" + this.wdate_visitTimeStart.Value + "" + "  结束日期:" + this.wdate_visitTimeEnd.Value;
            string[,] arr=new string[26,2];
            string[,] arr2 = new string[,] { { "A", "0" }, { "B", "1" }, { "C", "2" }, { "D", "3" }, { "E", "4" }, { "F", "5" }, { "G", "6" }, { "H", "7" }, { "I", "8" }, { "J", "9" }, { "K", "10" }, { "L", "11" },{"M","12"},{"N","13"},{"O","14"},{"P","15"},{"Q","16"},{"R","17"},{"S","18"},{"T","19"},{"U","20"},{"V","21"},{"W","22"},{"X","23"},{"Y","24"},{"Z","25"}};

            for (int i = 0; i < this.gridItems.Columns.Count; i++)
            {
               
                sheet1.Range[arr2[i,0].ToString() +"5"].Value2 = this.gridItems.Columns[i].HeaderText;
                sheet1.Range[arr2[i, 0].ToString() + "5"].CellStyle = sheet1.Range[arr2[0, 0].ToString() + "5"].CellStyle;
              
            }
            for (int i = 0; i < this.gridItems.RecordCount; i++)
            {
                  sheet1.Range["A" + Convert.ToString((i + 6))].Value2 = this.gridItems.Rows[i].Cells[0].Text;
                  sheet1.Range["B" + Convert.ToString((i + 6))].Value2 = this.gridItems.Rows[i].Cells[1].Text;

 

 

 

 

 


            //    Lb_GetCount_Queuetime = (Label)this.gridItems.Rows[i].FindControl("Lb_GetCount_Queuetime");
            //    Lb_GetCount_Connect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetCount_Connect");
            //    Lb_GetPercent_connect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetPercent_connect");
            //    Lb_GetCount_lost = (Label)this.gridItems.Rows[i].FindControl("Lb_GetCount_lost");
            //    Lb_GetPercent_ConnectSecond20 = (Label)this.gridItems.Rows[i].FindControl("Lb_GetPercent_ConnectSecond20");
            //    Lb_GetCount_QueuetimeWait = (Label)this.gridItems.Rows[i].FindControl("Lb_GetCount_QueuetimeWait");
            //    Lb_GetCount_QueuetimeGuaJi = (Label)this.gridItems.Rows[i].FindControl("Lb_GetCount_QueuetimeGuaJi");
            //    Lb_GetLenth_QueuetimeToConnect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetLenth_QueuetimeToConnect");
            //    Lb_GetLenth_QueuetimeToNoConnect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetLenth_QueuetimeToNoConnect");
            //    Lb_GetAVERAGELenth_RingToConnect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetAVERAGELenth_RingToConnect");
            //    Lb_GetAVERAGELenth_Connect = (Label)this.gridItems.Rows[i].FindControl("Lb_GetAVERAGELenth_Connect");


            //    sheet1.Range["B" + Convert.ToString((i + 6))].Value2 = Lb_GetCount_Queuetime.Text;
            //    sheet1.Range["C" + Convert.ToString((i + 6))].Value2 = Lb_GetCount_Connect.Text.Replace("&nbsp;", "");
            //    sheet1.Range["D" + Convert.ToString((i + 6))].Value2 = Convert.ToDouble(Lb_GetPercent_connect.Text.Replace("&nbsp;", "").Replace("%", "")) / 100;
            //    sheet1.Range["E" + Convert.ToString((i + 6))].Value2 = Lb_GetCount_lost.Text.Replace("&nbsp;", "");

            //    sheet1.Range["F" + Convert.ToString((i + 6))].Value2 = Convert.ToDouble(Lb_GetPercent_ConnectSecond20.Text.Replace("&nbsp;", "").Replace("%", "")) / 100;
            //    sheet1.Range["G" + Convert.ToString((i + 6))].Value2 = Lb_GetCount_QueuetimeWait.Text.Replace("&nbsp;", "");

            //    sheet1.Range["H" + Convert.ToString((i + 6))].Value2 = Lb_GetCount_QueuetimeGuaJi.Text.Replace("&nbsp;", "");

            //    sheet1.Range["I" + Convert.ToString((i + 6))].Value2 = Lb_GetLenth_QueuetimeToConnect.Text.Replace("&nbsp;", "");

            //    sheet1.Range["J" + Convert.ToString((i + 6))].Value2 = Lb_GetLenth_QueuetimeToNoConnect.Text.Replace("&nbsp;", "");

            //    sheet1.Range["K" + Convert.ToString((i + 6))].Value2 = Lb_GetAVERAGELenth_RingToConnect.Text.Replace("&nbsp;", "");

            //    sheet1.Range["L" + Convert.ToString((i + 6))].Value2 = Lb_GetAVERAGELenth_Connect.Text.Replace("&nbsp;", "");

 


            }

            //sheet1.Range["A" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "合计";
            //sheet1.Range["B" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=SUM(B6:B" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["C" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=SUM(C6:C" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["D" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(D6:D" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["E" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=SUM(E6:E" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["F" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(F6:F" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["G" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=SUM(G6:G" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["H" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=SUM(H6:H" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["I" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(I6:I" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["J" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(J6:J" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["K" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(K6:K" + (this.gridItems.RecordCount + 5).ToString() + ")";
            //sheet1.Range["L" + Convert.ToString((this.gridItems.RecordCount + 6))].Value2 = "=AVERAGE(L6:L" + (this.gridItems.RecordCount + 5).ToString() + ")";

 

            workbook.SaveAs(sFile, ExcelSaveType.SaveAsXLS, HttpContext.Current.Response, ExcelDownloadType.PromptDialog);
            excelEngine.ThrowNotSavedOnDestroy = false;
            excelEngine.Dispose();
        }
    }
    protected void gridItems_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gridItems.PageIndex = e.NewPageIndex;
        //this.Bind();
    }
    ///
    //select count(s.kind),s.kind  from dc_callsanalysis s,dc_callskind k where s.kind=k.kind group by s.Kind
}
public class myTemplate : ITemplate
{
    ArrayList arr;
    public string _kind;
    public  myTemplate()
    {
   
    }
    public myTemplate(string kind)
    {
        this._kind = kind; 
    }
    public  void InstantiateIn(Control container)
    {
         Label lb = new Label();
        lb.ID = "lb_" + this._kind;
        container.Controls.Add(lb);
     
    }
    public void InstantiateIn(Control container, string kind)
    {
       
        Label lb = new Label();
        lb.ID = "lb_" + kind;
        //lb.DataBinding += new EventHandler(this.BindData);
        //lb.Text = GetCount();
        container.Controls.Add(lb);
    }

    private string  GetLbId()
    {
        arr = new ArrayList();
        using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
        {
            string sql = " select * from dc_callskind";
            sql += " order by Kind";
              OracleCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                connection.Open();
                OracleDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    arr.Add(dr["kind"]);
                }
        }
        return arr[0].ToString() ;
    }
    public void BindData(object sender, EventArgs e)
    {
        //Label lbl = (Label)sender;
        //lbl.Text = "Wait Data";
        //string test2 = DataBinder.Eval(((GridViewRow)lbl.NamingContainer).DataItem, "Span").ToString();
   //     Label lbl = (Label)sender;
   // if( DataBinder.Eval( ((DataGridItem)lbl.NamingContainer).DataItem , "HasPayPoint" ).ToString() == "True" ){
   //  lbl.Text = DataBinder.Eval( ((DataGridItem)lbl.NamingContainer).DataItem , "UserName" ) + "<img src='" + FC.CMS.Utility.Path.ApplicationFileHttpUrl + "modules/auto/GroupBuy/IsMember.gif' alt='已支付保证金会员'>";                                    
   // } 
   // else
   //  lbl.Text = DataBinder.Eval( ((DataGridItem)lbl.NamingContainer).DataItem , "UserName" ).ToString();
   //}

    }

  
    public string GetCount()
    {
        string rtn;
        using (OracleConnection connection = new OracleConnection(ConfigurationManager.ConnectionStrings["DVTeConnectionString"].ToString()))
        {
            connection.Open();
            OracleCommand cmd = connection.CreateCommand();
            cmd.CommandText = "select count(s.kind) from dc_callsanalysis s where s.kind=1";// +report_Report4.k;         
            if (((System.Data.OracleClient.OracleNumber)(cmd.ExecuteOracleScalar())).IsNull == true)
            {
                rtn = "0";
            }
            else
            {
                rtn = cmd.ExecuteOracleScalar().ToString();

            }
           
        }
        return rtn;
    }

}

 

 

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值