Asp.Net中C#实现的DataGrid小计,合计和纵向合并的通用方法

来源：互联网发布：python运维脚本编辑：程序博客网时间：2024/05/16 11:56

//方法定义

/// <summary>
/// 映射计数器
/// </summary>
public class MapCounter
{
 protected decimal _counter;
 protected int _mapIndex;
 public MapCounter(int mapIndex)
 {
 this._mapIndex = mapIndex;
 this._counter = 0;
 }
 public int MapIndex{get{return this._mapIndex;}}
 public void Add(decimal value)
 {
 this._counter += value;
 }
 public void Reset()
 {
 this._counter = 0;
 }
 public decimal Value{get{return this._counter;}}
}
public class PresentUtil
{
 public static void PreparePresent(DataGrid target,DataTable dataSource,int keyColumnIndex,string keyColumnDataSourceName,int totalColumnColSpan,int subTotalColumnColSpan,int[] redundancyColumns,int[] totalColumns,int[] subTotalColumns,params int[] mergeColumns)
 {
 //重构数据源,该数据源已插入小计和合计的空行
 target.DataSource = PrepareNewDataSource(dataSource,keyColumnDataSourceName,dataSource.Columns.Count);
 target.CurrentPageIndex = 0;
 target.DataBind();
 //填充小计和合计行
 FillSummationRow(target,keyColumnIndex,totalColumnColSpan,subTotalColumnColSpan,redundancyColumns,totalColumns,subTotalColumns);

//合并列以美化效果
 MergeTextCellAtColumn(target,mergeColumns);
 }
 protected static void FillSummationRow(DataGrid curGrid,int keyColumnIndex,int totalColumnColSpan,int subTotalColumnColSpan,int[] redundancyColumns,int[] totalColumns,int[] subTotalColumns)
 {
 //获取绑定数据源
 DataTable curTable = (DataTable)curGrid.DataSource;
 int collectRowIndex = curTable.Rows.Count - 1;
 MapCounter[] totalCounter = new MapCounter[totalColumns.Length];
 for(int i = 0; i < totalColumns.Length; i ++)
 {
 totalCounter[i] = new MapCounter(totalColumns[i]);
 }
 MapCounter[] subTotalCounter = new MapCounter[subTotalColumns.Length];
 for(int i = 0; i < subTotalColumns.Length; i ++)
 {
 subTotalCounter[i] = new MapCounter(subTotalColumns[i]);
 }
 MapCounter[] redundancyCounter = new MapCounter[redundancyColumns.Length];
 for(int i = 0; i < redundancyCounter.Length; i ++)
 {
 redundancyCounter[i] = new MapCounter(redundancyColumns[i]);
 }
 for( int i = 0; i < curGrid.Items.Count - 1; i ++)
 {
 string keyText = curGrid.Items[i].Cells[keyColumnIndex].Text.Trim();
 bool same = false;
 //通过判断关键文本是否相同来确定是否需要进行累加
 if(i - 1 >= 0)
 {
 same = curGrid.Items[i].Cells[keyColumnIndex].Text == curGrid.Items[i - 1].Cells[keyColumnIndex].Text;
 }
 //关键文本不同的,不包括小计行,对相关数值进行累加
 if(!same && !keyText.Trim().Equals(" "))
 {
 //冗余累加:所谓冗余,指出当前列所在的多行会因为内容相同而被合并,其参与计算的方式是以最终合并的单元值参与计算,如,2个值为40的单元被合并为一个40的rowspan = 2 的单元,那么仅有当前的40参与累计运算
 foreach(MapCounter counter in redundancyCounter)
 {
 counter.Add(SafeConvertToDecimal(curGrid.Items[i].Cells[counter.MapIndex].Text));
 }
 }
 //明细数据始终需要累计,但不包括小计行
 if(!keyText.Trim().Equals(" "))
 {
 //合计累加
 foreach(MapCounter counter in totalCounter)
 {
 counter.Add(SafeConvertToDecimal(curGrid.Items[i].Cells[counter.MapIndex].Text));
 }
 //小计累加
 foreach(MapCounter counter in subTotalCounter)
 {
 counter.Add(SafeConvertToDecimal(curGrid.Items[i].Cells[counter.MapIndex].Text));
 }

}
 //构造小计行
 if(keyText.Trim().Equals(" "))
 {
 curGrid.Items[i].Cells[0].Visible = true;
 curGrid.Items[i].Cells[0].ColumnSpan = subTotalColumnColSpan;
 curGrid.Items[i].Cells[0].Text = "小计:";
 for(int j = 1; j <= subTotalColumnColSpan; j ++)
 {
 curGrid.Items[i].Cells[j].Visible = false;
 }
 foreach(MapCounter counter in subTotalCounter)
 {
 curGrid.Items[i].Cells[counter.MapIndex].Text = "" + counter.Value.ToString() +"";
 }
 //清空小计计数器
 foreach(MapCounter counter in subTotalCounter)
 {
 counter.Reset();
 }
 }

 }

 //构造最终合计行
 curGrid.Items[collectRowIndex].Cells[0].Visible = true;
 curGrid.Items[collectRowIndex].Cells[0].ColumnSpan = totalColumnColSpan;
 curGrid.Items[collectRowIndex].Cells[0].Text = "合计:";
 for(int j = 1; j <= totalColumnColSpan; j ++)
 {
 curGrid.Items[collectRowIndex].Cells[j].Visible = false;
 }
 foreach(MapCounter counter in redundancyCounter)
 {
 curGrid.Items[collectRowIndex].Cells[counter.MapIndex].Text = "" + counter.Value.ToString() +"";
 }
 foreach(MapCounter counter in totalCounter)
 {
 curGrid.Items[collectRowIndex].Cells[counter.MapIndex].Text = "" + counter.Value.ToString() +"";
 }

 }
 protected static void MergeTextCellAtColumn(DataGrid curGrid,int columnIndex)
 {
 DataGridItem curItem = null;
 DataGridItem nextItem = null;
 for (int r = 0; r < curGrid.Items.Count; r++)
 {
 ///当前单元格
 curItem = curGrid.Items[r];
 ///其下单元格
 if(r + 1 < curGrid.Items.Count)
 nextItem = curGrid.Items[r + 1];
 else
 nextItem = null;
 string curValue = curItem == null ? string.Empty:curItem.Cells[columnIndex].Text;
 string nextValue = nextItem == null ? string.Empty:nextItem.Cells[columnIndex].Text;
 ///如果单元格内容相同
 if( curValue != string.Empty && nextValue != string.Empty && curValue == nextValue)
 {
 ///设置其下单元格不可见
 if(nextValue != null)nextItem.Cells[columnIndex].Visible = false;
 ///如果当前单元格可见,则将其RowSpan + 1
 if(curItem.Cells[columnIndex].Visible == true)
 {
 curItem.Cells[columnIndex].RowSpan += 1;
 }
 ///如果当前单元格不可见,则直接回溯到其上第一个可见单元格为止
 else if(curItem.Cells[columnIndex].Visible == false)
 {
 if(curItem.Cells[0].ColumnSpan > 1)
 {
 //证明是合计行,则需要跳过
 }
 else
 {
 int preRowIndex = r;
 while(preRowIndex >= 0 && curGrid.Items[preRowIndex].Cells[columnIndex].Visible == false)
 {
 preRowIndex -= 1;
 }
 string preValue = curGrid.Items[preRowIndex].Cells[columnIndex].Text;
 if(curValue == preValue)
 {
 ///将其上第一个可见单元格的RowSpan + 1
 curGrid.Items[preRowIndex].Cells[columnIndex].RowSpan += 1;
 }
 }
 }
 }
 ///如果单元格内容不同
 else if(curValue != string.Empty && nextValue != string.Empty && curValue != nextValue)
 {
 ///判断是否是临界行:上面的一行或多行与下面的一行或多行的对应单元格内容不同
 if(curItem.Cells[columnIndex].Visible == false)
 {
 if(curItem.Cells[0].ColumnSpan > 1 && curValue.Trim().Equals(" "))
 {
 //证明是合计行,则需要跳过
 }
 else
 {
 int preRowIndex = r;
 while(preRowIndex >= 0 && curGrid.Items[preRowIndex].Cells[columnIndex].Visible == false)
 {
 preRowIndex -= 1;
 }
 string preValue = curGrid.Items[preRowIndex].Cells[columnIndex].Text;
 if(curValue == preValue)
 {
 ///将其上第一个可见单元格的RowSpan + 1
 curGrid.Items[preRowIndex].Cells[columnIndex].RowSpan += 1;
 }
 }
 }
 else
 {
 ///非临界
 }
 }
 ///数据列表的最后一行
 else if(r == curGrid.Items.Count - 1 && curItem.Cells[columnIndex].Visible == false)
 {
 int preRowIndex = r;
 while(preRowIndex >= 0 && curGrid.Items[preRowIndex].Cells[columnIndex].Visible == false)
 {
 preRowIndex -= 1;
 }
 string preValue = curGrid.Items[preRowIndex].Cells[columnIndex].Text;
 if(curValue == preValue)
 {
 ///将其上第一个可见单元格的RowSpan + 1
 curGrid.Items[preRowIndex].Cells[columnIndex].RowSpan += 1;
 }
 }
 }

 }

 protected static void MergeTextCellAtColumn(DataGrid curGrid,params int[] columns)
 {
 foreach(int column in columns)
 {
 MergeTextCellAtColumn(curGrid,column);
 }
 }
 protected static DataTable PrepareNewDataSource(DataTable dataSource,string keyColumnDataSourceName,int columnCount)
 {
 DataTable copy = dataSource.Copy();
 for(int i = 0; i < copy.Rows.Count; i ++)
 {
 string curText = copy.Rows[i][keyColumnDataSourceName].ToString();
 string preText = i - 1 > 0 ? copy.Rows[i - 1][keyColumnDataSourceName].ToString():string.Empty;
 if(curText == preText)
 {
 continue;
 }
 else if(curText != preText && i - 1 > 0)
 {
 //小计行
 DataRow subtotal = dataSource.NewRow();
 subtotal.ItemArray = PrepareItemArray(columnCount);
 dataSource.Rows.InsertAt(subtotal,i);
 }
 }
 //最后小计行
 DataRow lastSubtotal = dataSource.NewRow();
 lastSubtotal.ItemArray = PrepareItemArray(columnCount);
 dataSource.Rows.Add(lastSubtotal);
 //最终合计行
 DataRow totalRow = dataSource.NewRow();
 totalRow.ItemArray = PrepareItemArray(columnCount);
 dataSource.Rows.Add(totalRow);
 return dataSource;
 }
 protected static object[] PrepareItemArray(int columnCount)
 {
 object[] objs = new object[columnCount];
 for(int i = 0; i < objs.Length; i ++)
 {
 objs[i] = DBNull.Value;
 }
 return objs;
 }
 protected static decimal SafeConvertToDecimal(object value)
 {
 try
 {
 return Convert.ToDecimal(value);
 }
 catch
 {
 return 0;
 }
 }

}

//UI调用方式
//获取数据源
DataTable curDt = objPlan.QueryByYearDeptidProjcet(currYear,deptID,project);
//调用实现
PresentUtil.PreparePresent(DGList,curDt,1,"project",4,9,new int[]{5,6,7,8},new int[]{10,11,12,13,14},new int[]{10,11,12,13,14},new int[]{0,1,3,4,5,6,7,8});

//数据源结构

//输出效果展示