LinqToExcel加载字典 [英] LinqToExcel to load Dictionary

问题描述

我得出了以下结论，但还不够优雅.如果可能的话，我想和ToDictionary一起解决这个问题.谢谢您的帮助，因为我还很新.

I have the following worked out but quite less than elegant. I'd like to work this out with ToDictionary if possible. Thank you for any help as I'm pretty new.

var excel = new ExcelQueryFactory(@"E:\MAHipotCepaStationProgram.xlsx");

//get list of program names
List<string> testNames = new List<string>();
testNames.AddRange(excel.Worksheet().ToList()
            .Where(s => s["Program #"].Value.ToString() == "Program Title")
            .Select(s => s[1].Value.ToString()));


//get list of program numbers
List<int> testNumbers = new List<int>();
testNumbers.AddRange(excel.Worksheet().ToList()
            .Where(s => s["Program #"].Value.ToString() == "Program #")
            .Select(s => Convert.ToInt32(s[1].Value)));

//combine them
Dictionary<int, string> programs = new Dictionary<int, string>();
for (int x = 0; x < testNames.Count-1; x++)
{
    if (!programs.ContainsKey(Convert.ToInt32(testNumbers[x])))
    {
        programs.Add(Convert.ToInt32(testNumbers[x]), testNames[x]);
    }
    else
    {
        testNumbers[x].Dump("Duplicate Found");
    }
}

programs.Dump("Dict");

这与我所获得的接近，但不正确.错误:需要不与我一起计算的IEnumberable字符串类型的接收器:

This is as close as I've gotten, but not right. Error: "Requires a receiver of type IEnumberable string which isn't computing with me:

var excel = new ExcelQueryFactory(@"E:\MAHipotCepaStationProgram.xlsx");

Dictionary<string, string> programsDict = excel.Worksheet().ToDictionary<string, string>(
                                        e => e["Program #"].Value.ToString() == "Program Title")
                                            .Select(s => s[1].Value.ToString()),
                                        f => f.Where(d => d.Value.ToString() == "Program #").ToString());

推荐答案

可以尝试一下:

Dictionary<int, string> programsDict =
    excel
        .Worksheet()
        .Select(x => new { A = x[0].ToString(), B = x[1].ToString() })
        .ToArray()
        .Where(x => new [] { "Program #", "Program Title" }.Contains(x.A))
        .Buffer(2)
        .Select(x => new { title = x[0].B, number = int.Parse(x[1].B) })
        .ToDictionary(x => x.number, x => x.title);

您只需要NuGet"System.Interactive"即可获取.Buffer(int)运算符.

You just need to NuGet "System.Interactive" to get the .Buffer(int) operator.

或使用此实现:

public static IEnumerable<T[]> Buffer<T>(this IEnumerable<T> source, int count)
    =>
        source
            .Select((t, i) => new { t, i })
            .GroupBy(x => x.i / count)
            .Select(x => x.Select(y => y.t).ToArray());

这篇关于LinqToExcel加载字典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！