/// <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;
}
