债券总计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 BonusAsia ex-Japan Bonds 10000 10000 10000Asia ex-Japan Bonds 20000 20000 20000Asia ex-Japan Bonds 30000 30000 30000Asia ex-Japan Bonds 40000 40000 40000Asia ex-Japan Cash Equities 11500 11500 11500Asia ex-Japan Cash Equities 20500 20500 20500Asia ex-Japan Cash Equities 16200 16200 16200Asia ex-Japan Convertibles 3000 3000 3000Asia ex-Japan Convertibles 2000 2000 2000Americas Cash Equities 18000 18000 18000Americas Cash Equities 17000 17000 17000Americas Bonds 6600 6600 6600Americas Bonds 5700 5700 5700Japan Convertibles 14000 14000 14000Japan Convertibles 14500 14500 14500What we need is the total for each group & sub-group within the above DataTable as shown below :Region Asset Class Dividends PnL BonusAsia ex-Japan Bonds 10000 10000 10000Asia ex-Japan Bonds 20000 20000 20000Asia ex-Japan Bonds 30000 30000 30000Asia ex-Japan Bonds 40000 40000 40000 Bonds Total 100000 100000 100000Asia ex-Japan Cash Equities 11500 11500 11500Asia ex-Japan Cash Equities 20500 20500 20500Asia ex-Japan Cash Equities 16200 16200 16200 Cash Equities Total 48200 48200 48200Asia ex-Japan Convertibles 3000 3000 3000Asia ex-Japan Convertibles 2000 2000 2000 Convertibles Total 5000 5000 5000Asia ex-Japan Total 153200 153200 153200Americas Cash Equities 18000 18000 18000Americas Cash Equities 17000 17000 17000 Cash Equities Total 35000 35000 35000Americas Bonds 6600 6600 6600Americas Bonds 5700 5700 5700 Bonds Total 12300 12300 12300Americas Total 47300 47300 47300Japan Convertibles 14000 14000 14000Japan Convertibles 14500 14500 14500 Convertibles Total 28500 28500 28500Japan Total 43000 43000 43000Also, 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 Thisvar _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 codevar _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); } 这篇关于计算数据表中的总和。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持! 上岸,阿里云!
08-21 14:13