C#使用LINQ將datable整理匯入到EXCEL

c# linq vsto

canon760d 2023-05-16 16:27:31 ‧ 1303 瀏覽

分享至

以下表格請問在C#的datable中請問如何用LINQ把表一整理變成表二並且輸入呈現在EXCEL上呢? 嘗試好一段時間無果不知道有沒有大神可以協助QQ
表一

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

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

緯大啊緯大人

iT邦好手 1 級 ‧ 2023-05-16 17:58:42

沒太仔細看你內容，如果是MSSQL我感覺會用到PIVOT?

你可以看看是不是往這方向找答案

回應 3
分享
檢舉

player iT邦大師 1 級 ‧ 2023-05-16 18:06:36 檢舉

PIVOT
https://blog.darkthread.net/blog/linq-pivot/

不過我真的認為用SQL會比較容易做
https://blog.darkthread.net/blog/tips-using-pivot-in-sql-2005/

如果要輸出成Excel檔的話
還需要NPOI或OpenXML

kw6732 iT邦研究生 4 級 ‧ 2023-05-16 18:43:03 檢舉

用SQL會比較容易做我也認同 linq要稍微繞一下

canon760d iT邦新手 5 級 ‧ 2023-05-16 19:59:32 檢舉

先感謝大大回覆，不過因為是需要動態跑的，SQL好像是需要動態語法來執行，所以想說研究LINQ的看看~

登入發表回應

kw6732

iT邦研究生 4 級 ‧ 2023-05-16 18:40:49

這是一種資料的群組和轉置，作法如下，做完轉貼Excel即可，轉 npoi 當然也可以，但手上暫時沒有例子：

這樣囉~~

解法採用 Webform架構 aspx.cs 寫成函式~~ 因為簡單，我就懶得分段了

protected void Pre_Click(object sender, EventArgs e)
{
        List<Dt1> ListDt1 = new List<Dt1>();
        
        ListDt1.Add(new Dt1(1, "廠1", "A", 100, "Anthony", "W2320"));
        ListDt1.Add(new Dt1(1, "廠1", "A", 150, "Anthony", "W2321"));
        ListDt1.Add(new Dt1(1, "廠2", "A", 150, "Anthony", "W2322"));
        ListDt1.Add(new Dt1(1, "廠2", "B", 150, "Anthony", "W2324"));
        ListDt1.Add(new Dt1(1, "廠2", "B", 200, "Anthony", "W2325"));
        ListDt1.Add(new Dt1(2, "廠1", "A", 200, "Anthony", "W2322"));
        ListDt1.Add(new Dt1(2, "廠1", "A", 250, "Anthony", "W2320"));
        ListDt1.Add(new Dt1(2, "廠2", "A", 300, "Anthony", "W2321"));
        ListDt1.Add(new Dt1(2, "廠1", "B", 100, "Anthony", "W2320"));

        var mWk = from d in ListDt1 group d by new { mWk = d.mWeek } ;

        DataTable DataTab01 = new DataTable();

        List<DataColumn> cols = new List<DataColumn>() {
            new DataColumn("mps", typeof(int)),
            new DataColumn("fab", typeof(String)),
            new DataColumn("pc", typeof(String))
        };
        foreach (var mk in mWk)
        {
            cols.Add(new DataColumn(mk.Key.mWk, typeof(String)));
        }

        var ListGDt1 = from d in ListDt1 group d by new { mps = d.mps, fab = d.fab, pc = d.pc};

        DataTab01.Columns.AddRange(cols.ToArray());
        DataRow dR;
        foreach (var it in ListGDt1)
        {
            dR = DataTab01.NewRow();
            dR["mps"] = it.Key.mps;
            dR["fab"] = it.Key.fab;
            dR["pc"] = it.Key.pc;
            var ListDt2 = from d in ListDt1 where d.mps == it.Key.mps && d.fab == it.Key.fab && d.pc == it.Key.pc select d;
            foreach (Dt1 itm in ListDt2)
            {
                dR[itm.mWeek] = itm.demand;
            }
            DataTab01.Rows.Add(dR);
        }

        Pre_Gv.DataSource = DataTab01;
        Pre_Gv.Width = Unit.Percentage(60);
        Pre_Gv.DataBind();
}

自訂物件

public class Dt1
{
    public Dt1()
    {
        
    }

    public Dt1(int mps_, string fab_, string pc_, int demand_, string User_, string Wek_)
    {
        mps = mps_;
        fab = fab_;
        pc = pc_;
        demand = demand_;
        User = User_;
        mWeek = Wek_;
    }

    public int mps { get; set; }
    public string fab { get; set; }
    public string pc { get; set; }
    public int demand { get; set; }
    public string User { get; set; }
    public string mWeek { get; set; }
}

aspx

<asp:Button ID="Pre_bt" runat="server" Text="轉置清單" OnClick="Pre_Click" />
<asp:GridView ID="Pre_Gv" runat="server"></asp:GridView>

回應 2
分享
檢舉

canon760d iT邦新手 5 級 ‧ 2023-05-16 22:42:25 檢舉

謝謝大大回覆，大大的感覺很簡短!，目前是用下面的寫出來不過不知道對不對，手邊沒資料驗證，自己創的資料是可以跑

        var worksheet = Globals.Sheet1;
        worksheet.Select();
        var dates = dt.AsEnumerable().Select(r => r.Field<string>("Date")).Distinct().OrderBy(d => d).ToList();

        worksheet.Cells[1, 1].Value = "Version";
        worksheet.Cells[1, 2].Value = "FAB";
        worksheet.Cells[1, 3].Value = "Product";

        int startColumn = 4;
        foreach (var date in dates)
        {
            worksheet.Cells[1, startColumn].Value = date;
            startColumn++;
        }

        var groupedData = dt.AsEnumerable()
            .GroupBy(r => new { Version = r.Field<string>("Version"), FAB= r.Field<string>("FAB"), Product = r.Field<string>("Product") })
            .OrderBy(g => g.Key.Version) // 先按 Version 升序排列
            .ThenBy(g => g.Key.FAB) // 再按 Product 升序排列
            .ThenBy(g => g.Key.Product) // 再按 Product 升序排列
            .Select(g => new
            {
                Version = g.Key.Version,
                FAB = g.Key.FAB,
                Product = g.Key.Product,
                Quantities = dates.Select(date => g.Where(r => r.Field<string>("Date") == date).Sum(r => r.Field<int>("Quantity"))).ToList()
            })
            .ToList();

        int rowIndex = 2;
        foreach (var group in groupedData)
        {
            worksheet.Cells[rowIndex, 1].Value = group.Version;
            worksheet.Cells[rowIndex, 2].Value = group.FAB;
            worksheet.Cells[rowIndex, 3].Value = group.Product;


            startColumn = 4;
            foreach (var quantity in group.Quantities)
            {
                worksheet.Cells[rowIndex, startColumn].Value = quantity;
                startColumn++;
            }

            rowIndex++;
        }

謝謝大大回覆，大大的感覺很簡短!，目前是用下面的寫出來不過不知道對不對，手邊沒資料驗證，自己創的資料是可以跑

var worksheet = Globals.Sheet1;
            worksheet.Select();
            var dates = dt.AsEnumerable().Select(r => r.Field<string>("Date")).Distinct().OrderBy(d => d).ToList();

worksheet.Cells[1, 1].Value = "Version";
            worksheet.Cells[1, 2].Value = "FAB";
            worksheet.Cells[1, 3].Value = "Product";

int startColumn = 4;
            foreach (var date in dates)
            {
                worksheet.Cells[1, startColumn].Value = date;
                startColumn++;
            }

var groupedData = dt.AsEnumerable()
                .GroupBy(r => new { Version = r.Field<string>("Version"), FAB= r.Field<string>("FAB"), Product = r.Field<string>("Product") })
                .OrderBy(g => g.Key.Version) // 先按 Version 升序排列
                .ThenBy(g => g.Key.FAB) // 再按 Product 升序排列
                .ThenBy(g => g.Key.Product) // 再按 Product 升序排列
                .Select(g => new
                {
                    Version = g.Key.Version,
                    FAB = g.Key.FAB,
                    Product = g.Key.Product,
                    Quantities = dates.Select(date => g.Where(r => r.Field<string>("Date") == date).Sum(r => r.Field<int>("Quantity"))).ToList()
                })
                .ToList();

int rowIndex = 2;
            foreach (var group in groupedData)
            {
                worksheet.Cells[rowIndex, 1].Value = group.Version;
                worksheet.Cells[rowIndex, 2].Value = group.FAB;
                worksheet.Cells[rowIndex, 3].Value = group.Product;

startColumn = 4;
                foreach (var quantity in group.Quantities)
                {
                    worksheet.Cells[rowIndex, startColumn].Value = quantity;
                    startColumn++;
                }

rowIndex++;
            }

修改

kw6732 iT邦研究生 4 級 ‧ 2023-05-17 11:11:56 檢舉

把你所提及的實際資料手動帶入後，得到的圖如下：

這應該就是正確的資料吧~~