统计报表

Question

要做个统计报表数据大概这样：a   1 0 3 4
a   2 1 1 5
合计：3 1 4 9
b   1 0 3 4
b   1 1 1 4
合计：2 1 4 8应该用什么控件好？

Answer 1

在gridview中添加小计，总计，
/// <summary>
    /// 小计
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="gradColumns">分组</param>
    /// <param name="countColumns">统计</param>
    /// <returns></returns>
    private DataTable CountDataTable(DataTable dt,string [] gradColumns,string [] countColumns)
    {
        DataTable dtResult;
        //复制databale
        dtResult = dt.Clone();        Dictionary<string, object> dicGrad = new Dictionary<string, object>();
        //赋空值
        foreach (string item in gradColumns)
        {
            dicGrad.Add(item, DBNull.Value);
        }
        //小记
        Dictionary<string, decimal> dicCount = new Dictionary<string, decimal>();
        //统计
        Dictionary<string, decimal> dicALLCount = new Dictionary<string, decimal>();        foreach (string item in countColumns)
        {
            dicCount.Add(item, 0.00m);
            dicALLCount.Add(item, 0.00m);
        }
        int isFirst = 1;//第一次统计
        //string name = "";
        bool isLike = true;//判断要统计名称是否相同    
        foreach (DataRow row in dt.Rows)
        {
            isLike = true;
              
            foreach (string col in dicGrad.Keys)
            {
                if (!row[col].ToString().Equals(dicGrad[col] == null ? "" : dicGrad[col].ToString()))
                {
                    isLike = false;
                    break;
                }
            }
            foreach (string col in gradColumns)
            {
                dicGrad[col]=row[col];
                break;
            }
            if (isLike || isFirst==1)
            {
                foreach (string col in countColumns)
                {
                    decimal decTmp=0.00m;
                    //转换为数值型
                    decimal.TryParse(row[col].ToString(),out decTmp);
                    dicCount[col] += decTmp;
                }            }
            else 
            {
                //增加复制行
                DataRow drTmp = dtResult.NewRow();                drTmp[gradColumns[0]] = "小计";
               
                foreach (string col in countColumns)
                {
                    drTmp[col] = dicCount[col];
                    decimal decTmp = 0.00m;
                    decimal.TryParse(row[col].ToString(), out decTmp);
                    dicCount[col] = decTmp;
                }
                drTmp["rowType"] = "1";
                dtResult.Rows.Add(drTmp);
            }            dtResult.Rows.Add(row.ItemArray);
            //给统计赋值
            foreach (string col in dicCount.Keys)
            {
                decimal decTmp = 0.00m;
                decimal.TryParse(row[col].ToString(), out decTmp);
                dicALLCount[col] += decTmp;
            }
            isFirst = 0;  
        }
        //最后加入小计/总计
        DataRow drEndTmp = dtResult.NewRow();
        if (dtResult != null && dtResult.Rows.Count > 0)
        {
            drEndTmp[gradColumns[0]] = "小计";            foreach (string col in countColumns)
            {
                drEndTmp[col] = dicCount[col];
            }
            drEndTmp["rowType"] = "1";//此行为小计行
            dtResult.Rows.Add(drEndTmp);
        }
        drEndTmp = dtResult.NewRow();
        drEndTmp[gradColumns[0]] = "总计";
        foreach (string col in countColumns)
        {
            drEndTmp[col] = dicALLCount[col];
        }
        drEndTmp["rowType"] = "2";
        dtResult.Rows.Add(drEndTmp);
        return dtResult; }
给小计、合计添加背景色
 /// <summary>
    /// 给小计、合计添加背景色
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void gvShow_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if ( ViewState["NoCount"].ToString() == "true")
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                string rowType = (e.Row.DataItem as DataRowView)["rowType"].ToString();
                if (rowType.Equals("1"))
                {
                    e.Row.Style.Add("background", "#D6D6D6");
                }
                else if (rowType.Equals("2"))
                {
                    e.Row.Style.Add("background", "#BABABA");
                }
            }   
        }        
    }

Answer 2

  DataTable dt = ds.Tables[0];//Response.Write(dt.Compute("avg(id)",""));//求查询结果中ID的平均值            //Response.Write(dt.Compute("sum(id)", "id>600")); //求 结果中大于600的id的和            Response.Write(dt.Compute("sum(id)", "id>600 and id<650"));// 大于600小于650的和