如何在C#的datatable用LINQ转换表格

Question

以下表格请问在C#的datable中请问如何用LINQ把表一整理变成表二并且输入呈现在EXCEL上呢?
表一

MPS_VERSION	FAB	PARTNUMBER_CHILD	DEMAND	LM_USER	M_WEEK
1	厂1	A	100	ANTHONY	W2320
1	厂1	A	150	ANTHONY	W2321
1	厂2	A	150	ANTHONY	W2322
1	厂2	B	150	ANTHONY	W2324
1	厂2	B	200	ANTHONY	W2325
2	厂1	A	200	ANTHONY	W2322
2	厂1	A	250	ANTHONY	W2320
2	厂2	A	300	ANTHONY	W2321
2	厂1	B	100	ANTHONY	W2320
3	厂1	A	50	ANTHONY	W2321
3	厂1	B	100	ANTHONY	W2321
3	厂1	A	150	ANTHONY	W2322
3	厂1	B	10	ANTHONY	W2322

表二

MPS_VERSION	FAB	PARTNUMBER_CHILD	W2320	W2321	W2322	W2324	W2325
1	厂1	A	100	150
1	厂2	A			150
1	厂2	B				150
1	厂2	B					200
2	厂1	A	250		200
2	厂2	A		300
2	厂1	B	100
3	厂1	A		50	150
3	厂1	B		100	10

Answer 1

```c#
DataTable table = new DataTable();
            table.Columns.Add("MPS_VERSION", typeof(int));
            table.Columns.Add("FAB", typeof(string));
            table.Columns.Add("PARTNUMBER_CHILD", typeof(string));
            table.Columns.Add("DEMAND", typeof(int));
            table.Columns.Add("LM_USER", typeof(string));
            table.Columns.Add("M_WEEK", typeof(string));

            // 添加数据
            table.Rows.Add(1, "厂1", "A", 100, "ANTHONY", "W2320");
            table.Rows.Add(1, "厂1", "A", 150, "ANTHONY", "W2321");
            table.Rows.Add(1, "厂2", "A", 150, "ANTHONY", "W2322");
            table.Rows.Add(1, "厂2", "B", 150, "ANTHONY", "W2324");
            table.Rows.Add(1, "厂2", "B", 200, "ANTHONY", "W2325");
            table.Rows.Add(2, "厂1", "A", 200, "ANTHONY", "W2322");
            table.Rows.Add(2, "厂1", "A", 250, "ANTHONY", "W2320");
            table.Rows.Add(2, "厂2", "A", 300, "ANTHONY", "W2321");
            table.Rows.Add(2, "厂1", "B", 100, "ANTHONY", "W2320");
            table.Rows.Add(3, "厂1", "A", 50, "ANTHONY", "W2321");
            table.Rows.Add(3, "厂1", "B", 100, "ANTHONY", "W2321");
            table.Rows.Add(3, "厂1", "A", 150, "ANTHONY", "W2322");
            table.Rows.Add(3, "厂1", "B", 10, "ANTHONY", "W2322");

            var columnNames = table.AsEnumerable().Select(row => row.Field<string>("M_WEEK")).Distinct().ToList();

            var dict = table.AsEnumerable()
                    .GroupBy(row => new {
                        MPS_VERSION = row.Field<int>("MPS_VERSION"),
                        FAB = row.Field<string>("FAB"),
                        PARTNUMBER_CHILD = row.Field<string>("PARTNUMBER_CHILD")
                    })
                    .ToDictionary(
                        group => new {
                            group.Key.MPS_VERSION,
                            group.Key.FAB,
                            group.Key.PARTNUMBER_CHILD
                        },
                        group => columnNames.Aggregate(
                            new Dictionary<string, int>(),
                            (acc, curr) => {
                                acc[curr] = group.FirstOrDefault(x => x.Field<string>("M_WEEK") == curr)?.Field<int>("DEMAND") ?? 0;
                                return acc;
                            }
                        )
                    );


            DataTable newTable = new DataTable();
            newTable.Columns.Add("MPS_VERSION", typeof(int));
            newTable.Columns.Add("FAB", typeof(string));
            newTable.Columns.Add("PARTNUMBER_CHILD", typeof(string));
            columnNames.ForEach(name => newTable.Columns.Add(name, typeof(int)));

            // 添加新行和列
            foreach (var kvp in dict)
            {
                var newRow = newTable.NewRow();
                newRow["MPS_VERSION"] = kvp.Key.MPS_VERSION;
                newRow["FAB"] = kvp.Key.FAB;
                newRow["PARTNUMBER_CHILD"] = kvp.Key.PARTNUMBER_CHILD;
                foreach (var cn in columnNames)
                {
                    newRow[cn] = kvp.Value[cn];
                }
                newTable.Rows.Add(newRow);
            }


            Response.Write(newTable);

```