Johny Bravo
asked on
Help with sum in datatable
Hi Experts,
please have a look for the data I am having in datatable.Actually it is pivoted to get the data in required format.
For pivoting I am using code from codeproject by Felipe
/// <summary>
/// Gets a Inverted DataTable
/// </summary>
/// <param name="table">Provided DataTable</param>
/// <param name="columnX">X Axis Column</param>
/// <param name="columnY">Y Axis Column</param>
/// <param name="columnZ">Z Axis Column (values)</param>
/// <param name="columnsToIgnore">Whe
/// provided here</param>
/// <param name="nullValue">null Values to be filled</param>
/// <returns>C# Pivot Table Method - Felipe Sabino</returns>
public static DataTable GetInversedDataTable(DataT
string columnY, string columnZ, string nullValue, bool sumValues)
{
//Create a DataTable to Return
DataTable returnTable = new DataTable();
if (columnX == "")
columnX = table.Columns[0].ColumnNam
//Add a Column at the beginning of the table
returnTable.Columns.Add(co
//Read all DISTINCT values from columnX Column in the provided DataTale
List<string> columnXValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
string columnXTemp = dr[columnX].ToString();
if (!columnXValues.Contains(c
{
//Read each row value, if it's different from others provided, add to
//the list of values and creates a new Column with its value.
columnXValues.Add(columnXT
returnTable.Columns.Add(co
}
}
//Verify if Y and Z Axis columns re provided
if (columnY != "" && columnZ != "")
{
//Read DISTINCT Values for Y Axis Column
List<string> columnYValues = new List<string>();
foreach (DataRow dr in table.Rows)
{
if (!columnYValues.Contains(d
columnYValues.Add(dr[colum
}
//Loop all Column Y Distinct Value
foreach (string columnYValue in columnYValues)
{
//Creates a new Row
DataRow drReturn = returnTable.NewRow();
drReturn[0] = columnYValue;
//foreach column Y value, The rows are selected distincted
DataRow[] rows = table.Select(columnY + "='" + columnYValue + "'");
//Read each row to fill the DataTable
foreach (DataRow dr in rows)
{
string rowColumnTitle = dr[columnX].ToString();
//Read each column to fill the DataTable
foreach (DataColumn dc in returnTable.Columns)
{
if (dc.ColumnName == rowColumnTitle)
{
//If Sum of Values is True it try to perform a Sum
//If sum is not possible due to value types, the value
// displayed is the last one read
if (sumValues)
{
try
{
drReturn[rowColumnTitle] =
Convert.ToDecimal(drReturn
Convert.ToDecimal(dr[colum
}
catch
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
else
{
drReturn[rowColumnTitle] = dr[columnZ];
}
}
}
}
returnTable.Rows.Add(drRet
}
}
else
{
throw new Exception("The columns to perform inversion are not provided");
}
//if a nullValue is provided, fill the datable with it
if (nullValue != "")
{
foreach (DataRow dr in returnTable.Rows)
{
foreach (DataColumn dc in returnTable.Columns)
{
if (dr[dc.ColumnName].ToStrin
dr[dc.ColumnName] = nullValue;
}
}
}
return returnTable;
}
Now I want to display sum horizontally and vertically of each row.
I am not using Gridview so that is not an option.
I need to calculate that in the datatable only.
How can I do this?Your help is really appreciated.Thanks.
grid.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.