I'm pivoting a table using the method ToPivotTable
and it works well
and I want to display the value with thousand separator like this ( 100 000,00) but I can't.
I tried to put [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:N}")]
in the model but didn't fix the problem.
My Model :
public class CompteResultat
{
public int CompteResultatID { get; set; }
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:N}")]
public Nullable<decimal> Value { get; set; }
public int IndicateurID { get; set; }
public Nullable<int> Year { get; set; }
public virtual Indicateur Indicateur { get; set; }
}
the Method :
public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
this IEnumerable<T> source,
Func<T, TColumn> columnSelector,
Expression<Func<T, TRow>> rowSelector,
Func<IEnumerable<T>, TData> dataSelector)
{
DataTable table = new DataTable();
var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
table.Columns.Add(new DataColumn(rowName));
var columns = source.Select(columnSelector).Distinct();
foreach (var column in columns)
table.Columns.Add(new DataColumn(column.ToString()));
var rows = source.GroupBy(rowSelector.Compile())
.Select(rowGroup => new
{
Key = rowGroup.Key,
Values = columns.GroupJoin(
rowGroup,
c => c,
r => columnSelector(r),
(c, columnGroup) => (dataSelector((columnGroup).ToList())))
});
foreach (var row in rows)
{
var dataRow = table.NewRow();
var items = row.Values.Cast<object>().ToList();
items.Insert(0, row.Key);
dataRow.ItemArray = (items.ToArray());
table.Rows.Add(dataRow);
}
return table;
}
In the controller :
public async Task<ActionResult> Index()
{
var res = await context.CompteResultats.ToListAsync();
var pivotTable = res.OrderBy(s=>s.Indicateur.Ordre).ToPivotTable(
item => item.Year,
item => item.Indicateur.Designation,
items => items.Any() ? items.Sum(x =>(x.Value)) :0);
return View(pivotTable);
}
In the view :
@model System.Data.DataTable
@using System.Data
<table class="display table table-striped" id="myDatatable" width="100%" cellspacing="0">
<tr>
@foreach (DataColumn col in Model.Columns)
{
<th>@col.ColumnName</th>
}
</tr>
@foreach (DataRow row in Model.Rows)
{
<tr>
@foreach (DataColumn col in Model.Columns)
{
<td>
@row[col.ColumnName]
</td>
}
</tr>
}
</table>
For me the result is like this:
Indicateurs 2018 2019
Indicateur1 20000,00 68000,00
Indicateur2 50000,00 52000,00
and i want to display it like this :
Indicateurs 2018 2019
Indicateur1 20 000,00 68 000,00
Indicateur2 50 000,00 52 000,00
Can someone help me please ?