//方法定义
/// <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 = "<b><font color ='#0033FF'>小计:</font></b>";
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 = "<b><font color ='#0033FF'>" + counter.Value.ToString() +"</font></b>";
}
//清空小计计数器
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 = "<b><font color ='#ff0066'>合计:</font></b>";
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 = "<b><font color ='#ff0066'>" + counter.Value.ToString() +"</font></b>";
}
foreach(MapCounter counter in totalCounter)
{
curGrid.Items[collectRowIndex].Cells[counter.MapIndex].Text = "<b><font color ='#ff0066'>" + counter.Value.ToString() +"</font></b>";
}
}
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});
//数据源结构
//输出效果展示