LINQ出现ToDictionary错误

Question

我在将这些表达式转换为lambdas时遇到了问题，没有错误(无法转换类型错误)：

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

我们应该看一下下面这样的代码，但无论我怎么尝试，它都不会编译：

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

任何帮助都是非常感谢的。另外，这是我们试图从excel表格中提取数据的图片，程序标题和程序编号。

​

​

Answer 1

下面是创建数据透视表的代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {

            List<string> columnHeaders = excel.Worksheet.Select(x => (string)x[0]).ToList();
            columnHeaders = columnHeaders.Distinct().ToList();

            DataTable pivotTable = new DataTable();
            foreach (string columnHeader in columnHeaders)
            {
                if(columnHeader != "")
                   pivotTable.Columns.Add(columnHeader, typeof(string));
            }

            DataRow newRow = null;
            foreach (var row in excel.Worksheet)
            {
                if ((string)row[0] == "Program #")
                {
                    newRow = pivotTable.Rows.Add();
                }
                if((row[0] != null) && ((string)row[0] != ""))
                   newRow[(string)row[0]] = (row[1] == null) ? "" : row[1];  
            }
        }
    }
}

Answer 2

尝试以下操作：

            //first get programs and numbers
            var rows = excel.Worksheet.Where(x => (x[0].ToString() == "Program #") || (x[0].ToString() == "Program Title")).Select(x => new { ColA = x[0].ToString(), ColB = x[1].ToString() }).ToList();

            // now even entries are the numbers and the odd are the titles
            var programTitle = rows.Select((x, i) => new { itemNumber = i / 2, program = x }).GroupBy(x => x.itemNumber).Select(x => new { number = int.Parse(x.FirstOrDefault().program.ColB), title = x.LastOrDefault().program.ColB }).ToList();

            //now create your dictionary
            Dictionary<int, string> programs = programTitle.GroupBy(x => x.number, y => y.title)
                .ToDictionary(x => x.Key, y => y.FirstOrDefault());