/// <summary>
/// 给DataTable添加序号 /// </summary> /// <param name="dt"></param> /// <param name="colXuHao"></param> public static void dbDataTableAddXuHao(DataTable dt,string colXuHao) { for (int i = 0; i < dt.Rows.Count; i++) { dt.Rows[i][colXuHao] = i + 1; } } /// <summary> /// 在DataTable末尾添加合计行,后两参数设置需要合计的行的范围,DataTableSumMultiCol(dt,1,2,5) //合计第3、4、5、6列 /// </summary> /// <param name="dt"></param> /// <param name="nColHeJi">需要显示‘合计’字段的列</param> /// <param name="nMinColHeJi">起始合计的列</param> /// <param name="nMaxColHeJi">结束合计的列,-1表示全部</param> public static DataRow dbDataTableSumRowsWithColRange(DataTable dt, int nColHeJi, int nMinColHeJi, int nMaxColHeJi) { if (nMaxColHeJi == -1) nMaxColHeJi = dt.Columns.Count-1; if (nMaxColHeJi < nMinColHeJi) custError("合计列时,结束列要比起始列大!"); int[] ix = new int[nMaxColHeJi - nMinColHeJi + 1]; for (int i = 0; i < ix.Length; i++) { ix[i] = i + nMinColHeJi; } return dbDataTableSumRowsWithColList(dt, nColHeJi, ix); //int[] i = nmaxcolhe } /// <summary> /// 在DataTable末尾添加合计行,最后一个参数设置需要合计的列,DataTableSumMultiCol(dt,1,{2,6,8}) //合计第3、7、9列 /// </summary> /// <param name="dt"></param> /// <param name="nColHeJi">需要显示‘合计’字段的列</param> /// <param name="colsHeJi">需要合计的列</param> public static DataRow dbDataTableSumRowsWithColList(DataTable dt, int nColHeJi, int[] colsHeJi) { DataRow dr = dt.NewRow(); dr[nColHeJi] = "合计"; dt.Rows.Add(dr); //初始化合计数组 decimal[] arrDec = new decimal[colsHeJi.Length]; for (int i = 0; i < colsHeJi.Length; i++) { arrDec[i] = decimal.Zero; } //合计 for (int i = 0; i < dt.Rows.Count - 1; i++) { for(int j=0;j<colsHeJi.Length;j++){ int c = colsHeJi[j]; if (Convert.IsDBNull(dt.Rows[i][c])) continue; arrDec[j] += Tools.toDec(dt.Rows[i][c] + ""); } } //赋值 for (int i = 0; i < colsHeJi.Length; i++) { int c = colsHeJi[i]; if (arrDec[i] == decimal.Zero) dr[c] = DBNull.Value; else dr[c] = arrDec[i]; } return dr; } /// <summary> /// 在DataTable末尾添加合计行,最后一个参数设置需要合计的 列,Tools.dbDataTableSumRowsWithColList(dt,"车号",new string[]{"个人产值","节油(元)","超油(元)"}); /// </summary> /// <param name="dt"></param> /// <param name="nColHeJi">需要显示‘合计’字段的列</param> /// <param name="colsHeJi">需要合计的列</param> public static DataRow dbDataTableSumRowsWithColList(DataTable dt, string sColHeJi, string[] colsHeJi) { DataRow dr = dt.NewRow(); dr[sColHeJi] = "合计"; dt.Rows.Add(dr); //初始化合计数组 decimal[] arrDec = new decimal[colsHeJi.Length]; for (int i = 0; i < colsHeJi.Length; i++) { arrDec[i] = decimal.Zero; } //合计 for (int i = 0; i < dt.Rows.Count - 1; i++) { for (int j = 0; j < colsHeJi.Length; j++) { string cName = colsHeJi[j]; if (Convert.IsDBNull(dt.Rows[i][cName])) continue; arrDec[j] += Tools.toDec(dt.Rows[i][cName] + ""); } } //赋值 for (int i = 0; i < colsHeJi.Length; i++) { string cName = colsHeJi[i]; if (arrDec[i] == decimal.Zero) dr[cName] = DBNull.Value; else dr[cName] = arrDec[i]; } return dr; } /// <summary> /// 分类小计,并有合计行, /// </summary> /// <param name="dt"></param> /// <param name="nColHeJi">需要显示‘合计’字段的列</param> /// <param name="colsHeJi">需要合计的列</param> public DataRow dbDataTableSubSumRowsWithColList(DataTable dt, string sColHeJi, string[] colsGroup, string[] colsHeJi) { DataRow dr = dt.NewRow(); dr[sColHeJi] = "合计"; dt.Rows.Add(dr); //初始化合计数组 decimal[] arrDec = new decimal[colsHeJi.Length]; for (int i = 0; i < colsHeJi.Length; i++) { arrDec[i] = decimal.Zero; } //合计 for (int i = 0; i < dt.Rows.Count - 1; i++) { for (int j = 0; j < colsHeJi.Length; j++) { string cName = colsHeJi[j]; if (Convert.IsDBNull(dt.Rows[i][cName])) continue; arrDec[j] += Tools.toDec(dt.Rows[i][cName] + ""); } } for (int i = 0; i < colsHeJi.Length; i++) { string cName = colsHeJi[i]; if (arrDec[i] == decimal.Zero) dr[cName] = DBNull.Value; else dr[cName] = arrDec[i]; } if (dt.Rows.Count <= 1) return dr; //小计 string sRate = ""; int currSubSumCol = dt.Rows.Count - 2; ArrayList ar = new ArrayList(); for (int i = dt.Rows.Count - 2; i >= 0; i--) { string currRate = dt.Rows[i]["税率(%)"] + ""; if (sRate != currRate) { if (i != dt.Rows.Count - 2) { dr = dt.NewRow(); dr[sColHeJi] = "小计"; for (int j = 0; j < colsHeJi.Length; j++) { string cName = colsHeJi[j]; if (arrDec[j] == decimal.Zero) dr[cName] = DBNull.Value; else dr[cName] = arrDec[j]; } dt.Rows.InsertAt(dr, currSubSumCol + 1); } currSubSumCol = i; sRate = currRate; for (int j = 0; j < colsHeJi.Length; j++) { //归零 arrDec[j] = decimal.Zero; } } for (int j = 0; j < colsHeJi.Length; j++) { string cName = colsHeJi[j]; if (Convert.IsDBNull(dt.Rows[i][cName])) continue; arrDec[j] += Tools.toDec(dt.Rows[i][cName] + ""); } if (i == 0) { dr = dt.NewRow(); dr[sColHeJi] = "小计"; for (int j = 0; j < colsHeJi.Length; j++) { string cName = colsHeJi[j]; if (arrDec[j] == decimal.Zero) dr[cName] = DBNull.Value; else dr[cName] = arrDec[j]; } dt.Rows.InsertAt(dr, currSubSumCol + 1); } } return dr; |
|