任何.Net函数或linq查询以取消数据透视 [英] Any .Net function or linq query to unpivot data

问题描述

是否有可用的.net库来取消透视Excel数据?我目前正在使用LinqToExcel框架从电子表格中读取数据，因此不确定是否存在可用于执行取消透视的动态linq查询.感谢您的任何建议.顺便说一句，我正在寻找一种可以处理多列的解决方案.

Is there any .net library available to unpivot excel data? I am currently using LinqToExcel framework to read data from spreadsheets, so not sure if there are dynamic linq queries available to perform the unpivot. Thanks for any suggestions. BTW, I am looking for a solution which could handle multiple columns.

示例 原始表格

Product Location  Customer1   Customer2   Customer3
  A        X          10         20         100

目的地表

Product Location Customer    Demand
  A        X      Customer1    10
  A        X      Customer2    20
  A        X      Customer3    100

推荐答案

尝试如下操作:

var customer1 =
    from c in excel.Worksheet()
    select new
    {
        Product = c["Product"],
        Location = c["Location"],
        Customer = "Customer1",
        Demand = c["Customer1"],
    };

var customer2 =
    from c in excel.Worksheet()
    select new
    {
        Product = c["Product"],
        Location = c["Location"],
        Customer = "Customer2",
        Demand = c["Customer2"],
    };

var customer3 =
    from c in excel.Worksheet()
    select new
    {
        Product = c["Product"],
        Location = c["Location"],
        Customer = "Customer3",
        Demand = c["Customer3"],
    };

var customers = customer1.Union(customer2).Union(customer3);

---

根据您的评论尝试以下操作:

---

Based on your comment try this:

var columns = 4; /* how many columns to unpivot */
var customers =
    from n in Enumerable.Range(2, columns)
    from c in excel.Worksheet()
    select new
    {
        Product = c["Product"].Value,
        Location = c["Location"].Value,
        Column = n.ToString(),
        Demand = c[n].Value,
    };

这篇关于任何.Net函数或linq查询以取消数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！