问题

我遇到了一些问题，找不到将我的Excel电子表格转换为C#模型的理想方式。

我有一些使用EPPlus库的MVP代码，但它非常简单，只是打开文件并从左上到右下读取单元格中的内容。

我正在寻找关于最适合这种数据的模型结构的指导，我认为一旦我能够找到一种创建模型的方法，代码实现就应该很简单。

像这样的模型结构是否理想？

public class DataModel
{
    public Dictionary<string, Region> Regions { get; set; }
}

public class Region
{
    public Dictionary<string, Category> Categories { get; set; }
}

public class Category
{
    public Dictionary<string, Subcategory> Subcategories { get; set; }
}

public class Subcategory
{
    public decimal UK { get; set; }
    public decimal England { get; set; }
    public decimal NorthEast { get; set; }
    public decimal NorthWest { get; set; }
    ...
}

Excel文档大致如下：

| | | | |UK|England|North East|North West|Yorkshire and the Humber|East Midlands|West Midlands|East|London|South East|South West|Wales|Scotland|Northern Ireland|
|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|:----|
|1|Food & non-alcoholic drinks| | |60.30|60.80|55.20|56.40|54.40|61.20|58.00|62.20|65.80|66.20|61.00|56.10|57.00|63.80|
| | | | | | | | | | | | | | | | | | |
|1.1|Food| | |55.30|55.80|50.30|51.80|49.90|56.00|53.00|57.10|60.60|61.00|56.10|51.60|51.60|58.00|
| |1.1.1|Bread, rice and cereals| |5.40|5.40|4.90|5.10|4.90|5.50|5.40|5.40|5.90|5.80|5.20|5.00|5.20|5.90|
| |1.1.2|Pasta products| |0.40|0.40|0.40|0.40|0.40|0.40|0.40|0.40|0.50|0.50|0.40|0.40|0.50|0.50|
| |1.1.3|Buns, cakes, biscuits etc.| |3.80|3.80|3.70|3.60|3.30|4.00|3.80|3.90|3.90|4.10|4.10|3.50|3.70|4.60|
| |1.1.4|Pastry (savoury)| |0.90|0.90|0.90|0.90|0.90|1.00|0.80|1.00|0.90|1.00|0.90|0.90|0.90|0.90|
|1.2|Non-alcoholic drinks| | |5.00|5.00|4.90|4.50|4.40|5.20|5.00|5.10|5.30|5.20|4.80|4.50|5.50|5.80|
| |1.2.1|Coffee| |0.90|1.00|0.90|0.90|0.80|1.10|0.80|1.00|0.80|1.20|1.00|0.80|0.90|0.90|
| |1.2.2|Tea| |0.50|0.50|0.40|0.40|0.40|0.50|0.50|0.50|0.50|0.50|0.50|0.40|0.40|0.60|
| |1.2.3|Cocoa and powdered chocolate| |0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|0.10|
| |1.2.4|Fruit and vegetable juices (inc. fruit squash)| |1.10|1.10|0.90|1.00|0.90|1.00|1.00|1.10|1.20|1.20|1.00|1.00|1.00|1.00|
| |1.2.5|Mineral or spring waters| |0.40|0.40|0.30|0.40|0.30|0.30|0.40|0.40|0.70|0.40|0.30|0.40|0.30|0.50|
| |1.2.6|Soft drinks (inc. fizzy and ready to drink fruit drinks)| |2.00|2.00|2.30|1.80|1.90|2.10|2.20|2.00|1.90|1.80|1.90|2.00|2.70|2.70|
| | | | | | | | | | | | | | | | | | |
|2|Alcoholic drink, tobacco & narcotics| | |12.50|12.30|12.90|13.60|11.90|13.60|11.60|12.20|10.90|12.50|12.40|12.00|14.00|14.70|
| | | | | | | | | | | | | | | | | | |
|2.1|Alcoholic drinks| | |8.70|8.80|8.20|9.50|8.30|9.30|8.20|8.90|7.90|9.10|9.10|8.10|8.50|7.70|
| |2.1.1|Spirits and liqueurs (brought home)| |2.00|1.90|2.20|1.90|1.50|2

public class DataModel
{
    public Dictionary<string, Region> Regions { get; set; }
}

public class Region
{
    public Dictionary<string, Category> Categories { get; set; }
}

public class Category
{
    public Dictionary<string, Subcategory> Subcategories { get; set; }
}

public class Subcategory
{
    public decimal UK { get; set; }
    public decimal England { get; set; }
    public decimal NorthEast { get; set; }
    public decimal NorthWest { get; set; }
    ...
}

答案1

得分: 1

模型设计在很大程度上取决于您将如何访问和进一步处理您的数据，所以很难说什么是适合您提供的数据集的合适数据模型。

从我的角度来看，您可以在这种情况下创建一个类似树形结构的模型：

public class Node
{
    public Node ParentNode { get; set; }
    public Dictionary<string, double> Values { get; set; }
    public string Name { get; set; }
    public string NodeAlternativeData { get; set; }
}

示例构建：

var rootNode = new Node()
{
    Name = "食品与非酒精饮料",
    Values = new Dictionary<string, double>()
    {
        {"英国", 1}, {"英格兰", 2}
    },
    ParentNode = null,
    NodeAlternativeData = "1"
};

var foodNode = new Node()
{
    Name = "食品",
    Values = new Dictionary<string, double>()
    {
        {"英国", 1}, {"英格兰", 2}
    },
    ParentNode = rootNode,
    NodeAlternativeData = "1.1"
};

var drinkNode = new Node()
{
    Name = "饮料",
    Values = new Dictionary<string, double>()
    {
        {"英国", 1}, {"英格兰", 2}
    },
    ParentNode = rootNode,
    NodeAlternativeData = "1.2"
};

public class Node
{
    public Node ParentNode { get; set; }
    public Dictionary<string, double> Values { get; set; }
    public string Name { get; set; }
    public string NodeAlternativeData { get; set; }
}

var rootNode = new Node()
{
    Name = "Food & non-alcoholic drinks",
    Values = new Dictionary<string, double>()
    {
        {"UK",1}, {"England", 2}
    },
    ParentNode = null,
    NodeAlternativeData = "1"
};

var foodNode = new Node()
{
    Name = "Food",
    Values = new Dictionary<string, double>()
    {
        {"UK",1}, {"England", 2}
    },
    ParentNode = rootNode,
    NodeAlternativeData = "1.1"
};

var drinkNode = new Node()
{
    Name = "Drink",
    Values = new Dictionary<string, double>()
    {
        {"UK",1}, {"England", 2}
    },
    ParentNode = rootNode,
    NodeAlternativeData = "1.2"
};