C# Array To Pivot DataTable:
Here is the C# array
object:
?
var data = new[] {
new
{ Product = "Product 1", Year = 2009, Sales = 1212 },
new
{ Product = "Product 2", Year = 2009, Sales = 522 },
new
{ Product = "Product 1", Year = 2010, Sales = 1337 },
new
{ Product = "Product 2", Year = 2011, Sales = 711 },
new
{ Product = "Product 2", Year = 2012, Sales = 2245 },
new
{ Product = "Product 3", Year = 2012, Sales = 1000 }
};
On Googling, I found
the following generic method in StackOverflow
thread.
?
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))
});
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;
}
You can create a
static class for extension methods and put it there.
To convert Year values to columns and get Pivot DataTable:
?
var pivotTable =
data.ToPivotTable(
item
=> item.Year,
item
=> item.Product,
items
=> items.Any() ? items.Sum(x=>x.Sales) : 0);
You will get the
following output:
C# Array to Pivot Dynamic Array:
You might want to
get the List<dynamic> or dynamic[] instead of getting DataTable after
converting columns to rows. It is handy in ASP.NET Web API to return JSON response.
To do it, I updated
the extension method to get the dynamic object. use following extension method:
?
public
static dynamic[] ToPivotArray<T, TColumn, TRow, TData>(
this
IEnumerable<T> source,
Func<T,
TColumn> columnSelector,
Expression<Func<T,
TRow>> rowSelector,
Func<IEnumerable<T>,
TData> dataSelector)
{
var
arr = new List<object>();
var
cols = new List<string>();
String
rowName = ((MemberExpression)rowSelector.Body).Member.Name;
var
columns =
source.Select(columnSelector).Distinct();
cols
=(new []{ rowName}).Concat(columns.Select(x=>x.ToString())).ToList();
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))
}).ToArray();
foreach
(var row in rows)
{
var
items = row.Values.Cast<object>().ToList();
items.Insert(0,
row.Key);
var
obj = GetAnonymousObject(cols, items);
arr.Add(obj);
}
return
arr.ToArray();
}
private static
dynamic GetAnonymousObject(IEnumerable<string> columns,
IEnumerable<object> values)
{
IDictionary<string,
object> eo = new ExpandoObject() as IDictionary<string, object>;
int
i;
for
(i = 0; i < columns.Count(); i++)
{
eo.Add(columns.ElementAt<string>(i),
values.ElementAt<object>(i));
}
return
eo;
}
ExpandoObject is used to create dynamic object.
Now, to convert row
to column and get dynamic array:
var pivotArray =
data.ToPivotArray(
item
=> item.Year,
item
=> item.Product,
items
=> items.Any() ? items.Sum(x => x.Sales) : 0);
You can easily
convert in JSON format
?
String json =
JsonConvert.SerializeObject(pivotArray, new KeyValuePairConverter());
Here is the result:
C# DataTable to Pivot DataTable:
Let us have a
DataTable with same data:
?
DataTable
myDataTable = new DataTable();
myDataTable.Columns.AddRange(new
DataColumn[3] { new DataColumn("Product"), new
DataColumn("Year", typeof(int)), new DataColumn("Sales",
typeof(int)) });
myDataTable.Rows.Add("Product
1", 2009, 1212);
myDataTable.Rows.Add("Product
2", 2009, 522);
myDataTable.Rows.Add("Product
1", 2010, 1337);
myDataTable.Rows.Add("Product
2", 2011, 711);
myDataTable.Rows.Add("Product
2", 2012, 2245);
myDataTable.Rows.Add("Product
3", 2012, 1000);
You can use the same
extension method to get Pivot DataTable like below.
?
var data2 =
myDataTable.AsEnumerable().Select(x=> new {
Product
=x.Field<String>("Product"),
Year=
x.Field<int>("Year"),
Sales
= x.Field<int>("Sales") });
DataTable
pivotDataTable =data2.ToPivotTable(
item
=> item.Year,
item
=> item.Product,
items
=> items.Any() ? items.Sum(x => x.Sales) : 0);
Here is the result:
DataTable to List<dynamic>:
If you need to
convert DataTable to List of dynamic object then use following extension
method:
public static
List<dynamic> ToDynamicList(this DataTable dt)
{
var
list = new List<dynamic>();
foreach
(DataRow row in dt.Rows)
{
dynamic
dyn = new ExpandoObject();
list.Add(dyn);
foreach
(DataColumn column in dt.Columns)
{
var
dic = (IDictionary<string, object>)dyn;
dic[column.ColumnName]
= row[column];
}
}
return
list;
}
Here is the result:
Conclusion:
In this post, we
played with C# Array, DataTable and
implemented to convert row to column and get Pivot Array, DataTable and List of
dynamic object.
0 komentar:
Posting Komentar