← 返回所有博客文章

 

最近因为施工业的物料管理系统,其中涉及大量的物料需要管理和汇总,数据量非常庞大,之前尝试自己通过将原始数据,加工处理建模,在后台代码中通过分组,转置再显示到Web页面中,但自己编写的代码量非常大,而且性能简直无法忍受。后来使用矩表非常好的解决了需求,本文主要介绍之前如何通过代码将数据展现在页面中,以及使用矩表控件创建行列转置以及动态列的表格,并显示在网页中。

1. 行列转置代码片段

public static DataTable GetCrossTable(DataTable dt)
{
  if (dt == null || dt.Columns.Count != 3 || dt.Rows.Count == 0)
  {
   return dt;
  }
  else
  {
   DataTable result = new DataTable();
   result.Columns.Add(dt.Columns[0].ColumnName);
   DataTable dtColumns = dt.DefaultView.ToTable("dtColumns", true, dt.Columns[1].ColumnName);
   for (int i = 0; i < dtColumns.Rows.Count; i++)
   {
    string colName;
    if (dtColumns.Rows[1][0] is DateTime)
    {
     colName = Convert.ToDateTime(dtColumns.Rows[i][0]).ToString();
    }
    else
    {
     colName = dtColumns.Rows[i][0].ToString();
    }
    result.Columns.Add(colName);
    result.Columns[i + 1].DefaultValue = "0";
   }
   DataRow drNew = result.NewRow();
   drNew[0] = dt.Rows[0][0];
   string rowName = drNew[0].ToString();
   foreach (DataRow dr in dt.Rows)
   {
    string colName = dr[1].ToString();
    double dValue = Convert.ToDouble(dr[2]);
    if (dr[0].ToString().Equals(rowName, StringComparison.CurrentCultureIgnoreCase))
    {
     drNew[colName] = dValue.ToString();
    }
    else
    {
     result.Rows.Add(drNew);
     drNew = result.NewRow();
     drNew[0] = dr[0];
     rowName = drNew[0].ToString();
     drNew[colName] = dValue.ToString();
    }
   }
   result.Rows.Add(drNew);
   return result;
  }
}

 

2. 实现多级分组逻辑示例代码:

public void GroupByClassLeverl()
{
      var query =
        from inforCode in ClassReportDetail group InfoCode by BigClassName into newGroup
        orderby newGroup.Key
        select newGroup;

    foreach (var BigClassName in ClassReportDetail )
    {
        Console.WriteLine("Key: {0}", nameGroup.Key);
        foreach (var ClassName in nameGroup)
        {
            Console.WriteLine("\t{0}, {1}", student.BigClassName , student.ClassName );
…………        
 
}
    }
}
 
 

3. SQL语句实现中实现汇总分级功能,进行7张表的复杂连接和汇总:

 
 每一张表中包含多列,需要做出多层连接和排序,并根据用户输入对数据进行过滤
select a.*,b.SupplyMode,h.ClassName,g.ClassName BigClassName,e.ReceiveDepName,f.W_ReceiveDepName,
isnull(b.reQuantity,0)reQuantity,isnull(b.reBookSum,0) reBookSum,isnull(d.mQuantity,0) mQuantity,isnull(d.mBookSum,0)
…,
isnull(f.W_allQuantity,0) W_allQuantity,isnull(f.W_allAllotSum,0) W_allAllotSum,isnull(f.W_AllotBookSum,0) W_AllotBookSum
from (select a.InfoCode,a.InfoName,a.InfoModel,a.InfoUnit,a.ClassNodebh,a.Rate,a.NonRatePrice,a.BookPrice,a.Manufacturer,a.BatchNo,a.BarCode,a.Storeroom,a.InfoRemark 
from (
        select  b.InfoCode,b.InfoName,b.InfoModel,b.InfoUnit,b.ClassNodebh,b.Rate,b.NonRatePrice,b.BookPrice,b.Manufacturer,b.BatchNo,b.BarCode,b.Storeroom,b.InfoRemark 
        from dbo.M_MonthStore b
        where left(b.ProjectID,LEN(@projectid))=@projectid and b.Month>=@startmonth and b.Month<=@endmonth  and b.MonthBalanceNum!=0
        group by  b.InfoCode,b.InfoName,b.InfoModel,b.InfoUnit,b.ClassNodebh,b.Rate,b.NonRatePrice,b.BookPrice,b.Manufacturer,b.BatchNo,b.BarCode,b.Storeroom,b.InfoRemark 
        union all
               select InfoCode,InfoName,InfoModel,InfoUnit,ClassNodebh,Rate,NonRatePrice,BookPrice,Manufacturer,BatchNo,BarCode,b.Storeroom,InfoRemark 
        from dbo.M_ReceiveOrder a inner join dbo.M_ReceiveOrderItem b
        on a.ProjectID=b.ProjectID
        and a.OrderID=b.OrderID
        where left(a.ProjectID,LEN(@projectid))=@projectid  and a.OrderDate>=@startdate AND a.OrderDate<=@enddate
        AND a.IsAudit=1
        union all
        
            ……) b
        on a.InfoCode=b.InfoCode
        and a.InfoName=b.InfoName
        and a.InfoModel=b.InfoModel
        and a.InfoUnit=b.InfoUnit
        and a.ClassNodebh=b.ClassNodebh
        and a.StoreRoom=b.StoreRoom
      ……….
order by supplymode,ReceiveDepName,W_ReceiveDepName desc
 
    
    
 
 
最终只能将数据单一的呈现出来,而且样式非常简单的,将近上万条的数据呈现起来根本无法忍耐,尝尝会导致页面崩溃死掉。
 
 
 
 
使用报表提供的矩表控件实现行列转置,就不需要再写那么复杂的行列转置和分组代码,而且能共根据物料的供应方式来自动生成列,将数据展现在最终页面中,
 
使用矩表控件实现步骤:
 

1. 添加RDL报表

2. 为报表添加数据源

 
clip_image003
 

3. 添加数据集

 

在数据集窗口中输入SQL 语句:

select * From ClassReportDataTable.
 

获取字段

 
image
 

4. 添加矩表控件

clip_image005

5. 搭建报表结构

5.1 首先添加按照一级类别和二级类别添加行分组

选中行分组单元格,添加行分组-》子分组

 

image

5.2 添加供应方式动态列

右键单击列分组单元格,选择插入列分组

 

image

 

5.3 最后将所有数据绑定到矩表中,这样大功就告成了。

 

image

再也不用痛苦的写前端显示和超复杂的性能优化代码,在之前页面刷半个小时都没有办法刷出来,用户是真的无法忍耐,所以考虑用现有的比较成熟的报表控件,果然矩表控件拖拖拽拽就可以解决,