Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.8k views
in Technique[技术] by (71.8m points)

c# - LinqToExcel to load Dictionary

enter image description here

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());
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...