计算数据表中的总和。 [英] Calculate the sum totals in a datatable.
问题描述
嗨！
我有一个DataTable可以获取以下格式的数据。
地区资产类别股息PnL奖金
亚洲除日本债券10000 10000 10000
亚洲除日本债券20000 20000 20000
亚洲除日本债券30000 30000 30000
亚洲除日本债券40000 40000 40000
亚洲除日本现金股票11500 11500 11500
亚洲（日本除外）现金股票20500 20500 20500
亚洲（日本除外）现金股票16200 16200 16200
亚洲（日本除外）敞篷车3000 3000 3000
亚洲（日本除外）可转换债券2000 2000 2000
美洲现金股票18000 18000 18000
美洲现金股票17000 17000 17000
美洲债券6600 6600 6600
美洲债券5700 5700 5700
日本敞篷车14000 14000 14000
日本C onvertibles 14500 14500 14500
我们需要的是每个组的总和&上述DataTable中的子组如下图所示：
地区资产类别股息PnL奖金
亚洲除日本债券10000 10000 10000
亚洲除日本债券20000 20000 20000
亚洲除日本债券30000 30000 30000
亚洲除日本债券40000 40000 40000
债券总计100000 100000 100000
亚洲（日本除外）现金股票11500 11500 11500
亚洲（日本除外）现金股票20500 20500 20500
亚洲（日本除外）现金股票16200 16200 16200
现金股票合计48200 48200 48200
亚洲（日本除外）敞篷车3000 3000 3000
亚洲日本前可转换债券2000 2000 2000
可转换债券总额5000 5000 5000
亚洲（日本除外）总计153200 153200 153200
美洲现金股票18000 18000 18000
美洲现金股票17 000 17000 17000
现金股票总计35000 35000 35000
美洲债券6600 6600 6600
美洲债券5700 5700 5700
债券总计12300 12300 12300
美洲总计47300 47300 47300
日本敞篷车14000 14000 14000
日本敞篷车14500 14500 14500
敞篷车总计28500 28500 28500
日本总计43000 43000 43000
另外，我们需要打印上面的数据表excel（即我们没有使用任何报告工具，总计和小计可以很容易地计算出来。
我们怎样才能做到这一点？任何帮助将不胜感激。
提前致谢，
Charvi。
我尝试了什么：
我试过了循环通过数据和&计算总和，但这似乎只适用于内部组和&不是外部组。
Hi!
I have a DataTable that fetches the data in the below format.
Region Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500
What we need is the total for each group & sub-group within the above DataTable as shown below :
Region Asset Class Dividends PnL Bonus
Asia ex-Japan Bonds 10000 10000 10000
Asia ex-Japan Bonds 20000 20000 20000
Asia ex-Japan Bonds 30000 30000 30000
Asia ex-Japan Bonds 40000 40000 40000
Bonds Total 100000 100000 100000
Asia ex-Japan Cash Equities 11500 11500 11500
Asia ex-Japan Cash Equities 20500 20500 20500
Asia ex-Japan Cash Equities 16200 16200 16200
Cash Equities Total 48200 48200 48200
Asia ex-Japan Convertibles 3000 3000 3000
Asia ex-Japan Convertibles 2000 2000 2000
Convertibles Total 5000 5000 5000
Asia ex-Japan Total 153200 153200 153200
Americas Cash Equities 18000 18000 18000
Americas Cash Equities 17000 17000 17000
Cash Equities Total 35000 35000 35000
Americas Bonds 6600 6600 6600
Americas Bonds 5700 5700 5700
Bonds Total 12300 12300 12300
Americas Total 47300 47300 47300
Japan Convertibles 14000 14000 14000
Japan Convertibles 14500 14500 14500
Convertibles Total 28500 28500 28500
Japan Total 43000 43000 43000
Also, we need to print the above datatable to excel (i.e. we are not using any reporting tool where the totals and sub-totals could be easily computed).
How could we achieve this? Any help would be much appreciated.
Thanks in advance,
Charvi.
What I have tried:
I have tried looping through the datatble & calculating the sum but this seems to work only for the inner group & not the outer group.
推荐答案
你可以使用使用MS Excel（xls / xlsx）使用MDAC和Oledb [ ^ ]或Microsoft.Office.Interop.Excel namespace（） [ ^ ]如如何：使用COM Interop创建Excel电子表格（ C＃） [ ^ ]
you could use Working with MS Excel(xls / xlsx) Using MDAC and Oledb[^] or the Microsoft.Office.Interop.Excel namespace ()[^] as described in How to: Use COM Interop to Create an Excel Spreadsheet (C#)[^]
//尝试Thi s
//Try This
var _result = from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset"),
Class = r1.Field<string>("Class"),
} into g
select new
{
RegionAsset = g.Key.RegionAsset,
Class = g.Key.Class,
TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))
};
//对于Excel下载
//For Excel Download
using Excel = Microsoft.Office.Interop.Excel;
public static bool ExportDataTableToExcel(DataTable dt, string filepath)
{
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;
try
{
// Start Excel and get Application object.
oXL = new Excel.Application();
// Set some properties
oXL.Visible = true;
oXL.DisplayAlerts = false;
// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);
// Get the Active sheet
oSheet = (Excel.Worksheet)oWB.ActiveSheet;
oSheet.Name = "Data";
int rowCount = 1;
foreach (DataRow dr in dt.Rows)
{
rowCount += 1;
for (int i = 1; i < dt.Columns.Count + 1; i++)
{
// Add the header the first time through
if (rowCount == 2)
{
oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
}
oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
}
}
oWB.SaveAs(filepath, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing,
false, false, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
oWB.Close();
oWB = null;
oXL.Quit();
}
catch
{
throw;
}
finally
{
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();
}
return true;
}
//希望这适用于您之前的代码
//Hope This works with your previous code
var _result1 = (from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset"),
Class = r1.Field<string>("Class"),
} into g
select new
{
RegionAsset = g.Key.RegionAsset,
Class = g.Key.Class,
TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))
}).ToList();
var _result2 = (from r1 in DT.AsEnumerable()
group r1 by new
{
RegionAsset = r1.Field<string>("Region Asset")
} into g
select new
{
RegionAsset = g.Key.RegionAsset,
TotalDividends = g.Sum(x => x.Field<int>("Dividends")),
TotalPnL = g.Sum(x => x.Field<int>("PnL")),
TotalBonus = g.Sum(x => x.Field<int>("Bonus"))
}).ToList();
DataTable DTResult = DT.Clone();
foreach (var r1 in _result1)
{
foreach (DataRow dr in DT.AsEnumerable().Where(p => p.Field<string>("Region Asset") == r1.RegionAsset && p.Field<string>("Class") == r1.Class))
{
DataRow dtrow = DTResult.NewRow();
dtrow.ItemArray = new String[] { r1.RegionAsset, r1.Class, dr.Field<int>("Dividends").ToString(), dr.Field<int>("Dividends").ToString(), dr.Field<int>("Dividends").ToString() };
DTResult.Rows.Add(dtrow);
}
DataRow dtrow1 = DTResult.NewRow();
dtrow1.ItemArray = new String[] { r1.Class + "Total", "", r1.TotalDividends.ToString(), r1.TotalPnL.ToString(), r1.TotalBonus.ToString() };
DTResult.Rows.Add(dtrow1);
}
这篇关于计算数据表中的总和。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
