C#给DataTable添加序号、C#给DataTable添加合计、小计
/// <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;
}
