LinqToExcel to load Dictionary

Question

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

This is as close as I've gotten, but not right. Error:

Requires a receiver of type IEnumerable 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());

Answer 1

You can filter the values using a sigle LINQ query.This will return the name and number columns in the excel:

    var sampleExcel = new ExcelQueryFactory(@"I:\Book1.xlsx");
    var sampleWorksheet = from workSheet in sampleExcel.Worksheet("Sheet1") select workSheet;
    var selectedValues = from excelRow in sampleExcel.Worksheet()
       select new { name = excelRow[0], number =Convert.ToInt32(excelRow[1]) };
        foreach (var item in selectedValues)
        {
            Console.WriteLine(string.Format("Name is {0} ,number is {1}",item.name,item.number));
        }


        Dictionary<int, string> dict = new Dictionary<int, string>();

        foreach (var item in selectedValues)
        {
            dict.Add(item.number, item.name);
            Console.WriteLine(string.Format("Name is {0} ,number is {1}", item.name, item.number));
        }

Equivalent lambda expression for the above LINQ query:

var selectedValues1 = sampleExcel.Worksheet().Select(x => new { name = x[0], number = x[1] }); 

Answer 2

Have a go at this:

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

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

Or use this implementation:

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