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

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

表二

```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);

```

在C#的DataTable中使用LINQ转换表格,可以这样实现:

1. 获取DataTable对象,例如从数据库查询得到:

csharp
DataTable table = GetTableFromDatabase();

2. 使用table.AsEnumerable()将DataTable转换为IEnumerable,从而可以应用LINQ查询:

csharp
var result = from row in table.AsEnumerable()
             where row.Field<int>("Age") > 30
             select new { 
                 Name = row.Field<string>("Name"), 
                 Age = row.Field<int>("Age") 
             };

3. LINQ查询结果存储在IEnumerable变量中,可以将其转换回DataTable:

csharp 
DataTable resultTable = result.CopyToDataTable();

4. 也可以直接将LINQ查询结果转换为其他集合类型,如List:

csharp
List<SomeClass> resultList = result.ToList();

总结一下,DataTable使用LINQ查询的主要步骤是:

1. 调用table.AsEnumerable()将DataTable转换为IEnumerable
2. 在IEnumerable上应用LINQ查询及转换
3. 将LINQ查询结果转换回DataTable或其他集合类型(如果需要)
   简单示例代码:

csharp
DataTable table = GetTableFromDatabase();

var result = from row in table.AsEnumerable()
             where row.Field<int>("Age") > 30
             select new { 
                 Name = row.Field<string>("Name"), 
                 Age = row.Field<int>("Age") 
             };

DataTable resultTable = result.CopyToDataTable();
List<SomeClass> resultList = result.ToList();