我有一個這樣的列表：internal class Order{ public Order() { } public string ProductID { get; set; } public int Amount { get; set; } public string Date { get; set; }}List<Order> orderList = new List<Order>() { new Order(){ ProductID="12345", Amount=300, Date = "2018-12-19"}, new Order(){ ProductID="12345", Amount=0, Date = "2018-12-20"}, new Order(){ ProductID="12345", Amount=200, Date = "2018-12-21"}, new Order(){ ProductID="12345", Amount=250, Date = "2018-12-22"}, new Order(){ ProductID="12345", Amount=30, Date = "2018-12-23"}, new Order(){ ProductID="67898", Amount=20, Date = "2018-12-20"}, new Order(){ ProductID="67898", Amount=30, Date = "2018-12-21"}, new Order(){ ProductID="67898", Amount=40, Date = "2018-12-22"}, new Order(){ ProductID="67898", Amount=50, Date = "2018-12-23"}, new Order(){ ProductID="67898", Amount=130, Date = "2018-12-24"}};在這種情況下，當我將列表轉換DataTable為 Excel 并將其導出時，我得到以下信息：我想從Date值創建新列并按ProductID列出它們。如何將此列表轉換DataTable為如下所示：
2 回答
墨色風雨
TA貢獻1853條經驗 獲得超6個贊
GroupBy您可以通過使用and來實現這一點ExpandoObject：
var result = new List<ExpandoObject>();
foreach (var orders in orderList.GroupBy(obj => obj.ProductID))
{
var record = new ExpandoObject();
foreach (var order in orders.AsEnumerable())
{
((IDictionary<string, object>)record).Add(order.Date, order.Amount);
}
result.Add(record);
}
Cats萌萌
TA貢獻1805條經驗 獲得超9個贊
你想要一個像 excel 一樣的數據透視表。下面的代碼創建了兩個數據表。您列出的第一個基礎。然后它需要第一個表并創建第二個數據透視表
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication93
{
class Program
{
static void Main(string[] args)
{
Order order = new Order();
DataTable dt = order.MakeTable();
//now create piviot table
string[] dates = dt.AsEnumerable().Select(x => x.Field<string>("Date")).Distinct().ToArray();
DataTable pivot = new DataTable();
pivot.Columns.Add("ProductID", typeof(string));
foreach (string date in dates)
{
pivot.Columns.Add(date, typeof(int));
}
var groups = dt.AsEnumerable().GroupBy(x => x.Field<string>("ProductID")).ToList();
foreach(var group in groups)
{
DataRow newRow = pivot.Rows.Add();
newRow["ProductID"] = group.Key;
foreach (var col in group)
{
newRow[col.Field<string>("Date")] = col.Field<int>("Amount");
}
}
}
}
internal class Order
{
public Order()
{
}
public string ProductID { get; set; }
public int Amount { get; set; }
public string Date { get; set; }
public DataTable MakeTable()
{
DataTable dt = new DataTable();
List<Order> orderList = new List<Order>() {
new Order(){ ProductID="12345", Amount=300, Date = "2018-12-19"},
new Order(){ ProductID="12345", Amount=0, Date = "2018-12-20"},
new Order(){ ProductID="12345", Amount=200, Date = "2018-12-21"},
new Order(){ ProductID="12345", Amount=250, Date = "2018-12-22"},
new Order(){ ProductID="12345", Amount=30, Date = "2018-12-23"},
new Order(){ ProductID="67898", Amount=20, Date = "2018-12-20"},
new Order(){ ProductID="67898", Amount=30, Date = "2018-12-21"},
new Order(){ ProductID="67898", Amount=40, Date = "2018-12-22"},
new Order(){ ProductID="67898", Amount=50, Date = "2018-12-23"},
new Order(){ ProductID="67898", Amount=130, Date = "2018-12-24"}
};
foreach (var prop in this.GetType().GetProperties())
{
string typeName = prop.PropertyType.FullName;
Type systemType = System.Type.GetType(typeName);
dt.Columns.Add(prop.Name, systemType);
}
foreach (Order row in orderList)
{
object[] data = row.GetType().GetProperties().Select(x => x.GetValue(row, null)).ToArray();
dt.Rows.Add(data);
}
return dt;
}
}
}
- 2 回答
- 0 關注
- 166 瀏覽
添加回答
舉報
0/150
提交
取消