与LinqToExcel导入数据 [英] Importing data with LinqToExcel

问题描述

我有一个XLSX US preadsheet，我试图导入到的IList（FinancialResults的）使用VB.NET 集合。

在S preadsheet是在下面的格式，其中我需要从相应列的单元格映射到一个类。

取值preadsheet

型号

 公共类FinancialResults
    朋友地产MonthBudget单
    朋友地产MonthActual单
    朋友地产YearToDateBudget单
    朋友地产YearToDateActual单
    朋友地产FullYearForcastBudget单
    朋友地产FullYearForcastActual单
    朋友地产NextMonthBudget单
    朋友地产NextMonthActual单
末级
 

code

我的初步意见是使用 LinqToExcel这是我经过的NuGet但我安装我有问题，使用该库加载数据。从阅读网站上的文档，我发现，此功能可由 AddMapping 方法

  ExcelEngine.AddMapping（中FinancialResults）（功能（M）m.MonthBudget，D）
ExcelEngine.AddMapping（中FinancialResults）（功能（M）m.MonthActual，E）
ExcelEngine.AddMapping（中FinancialResults）（功能（米）m.YearToDateBudget，H）
ExcelEngine.AddMapping（中FinancialResults）（功能（M）m.YearToDateActual，我）
ExcelEngine.AddMapping（中FinancialResults）（功能（米）m.FullYearForcastBudget，L）
ExcelEngine.AddMapping（中FinancialResults）（功能（米）m.FullYearForcastActual中，M）
ExcelEngine.AddMapping（中FinancialResults）（功能（米）m.NextMonthBudget，P）
ExcelEngine.AddMapping（中FinancialResults）（功能（M）m.NextMonthActual，Q）
 

然后用下面的LINQ的声明中加载数据。

 暗淡VAR =（来自R在ExcelEngine.Worksheet（FinancialResults中）（excelPageName）
           选择R）.ToList
 

结果

在code编译和运行，但结果不是从S preadsheet加载。在FinacialResults类的所有实例都填充了0.0

盘算，列映射也许错我质疑的列集合 ExcelQueryFactory 用

 暗淡COLS = ExcelEngine.GetColumnNames（excelPageName）
 

这返回的下方。

我改变了列映射，以反映这些，但结果又一次回来0.0所有实例。

  ExcelEngine.AddMapping（FinancialResults中）（功能（M）m.MonthBudget，F4）
ExcelEngine.AddMapping（中FinancialResults）（功能（M）m.MonthActual，F5）
 

问题

这可能使用LinqToExcel给出的这个S preadsheet格式？如果是的话我究竟做错了什么？我应该使用 WorksheetRange 方法或单独加载每个单元<？ / P>

有另一种preferable的选择吗？

解决方案

这是不可能的LinqToExcel阅读的S preadsheet你有它的布局，因为

读它最简单的办法是改变布局，使第一行包含如下面所示的图片

列名

I have an XLSX spreadsheet that I am trying to import into a IList(of FinancialResults) collection using VB.NET.

The spreadsheet is in the below format where I need to map the cells from the appropriate columns into a class.

Spreadsheet

Model

Public Class FinancialResults
    Friend Property MonthBudget As Single
    Friend Property MonthActual As Single
    Friend Property YearToDateBudget As Single
    Friend Property YearToDateActual As Single
    Friend Property FullYearForcastBudget As Single
    Friend Property FullYearForcastActual As Single
    Friend Property NextMonthBudget As Single
    Friend Property NextMonthActual As Single
End Class

Code

My initial view was to use LinqToExcel which I installed via NuGet but I am having problems loading the data using the library. From reading the documentation on the website I found that this functionality can be provided by the AddMapping method

ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.MonthBudget, "D")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.MonthActual, "E")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.YearToDateBudget, "H")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.YearToDateActual, "I")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.FullYearForcastBudget, "L")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.FullYearForcastActual, "M")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.NextMonthBudget, "P")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.NextMonthActual, "Q")

Then loading the data using the below Linq statement.

Dim var = (From r In ExcelEngine.Worksheet(Of FinancialResults)(excelPageName)
           Select r).ToList

Results

The code compiles and runs but the results are not loaded from the spreadsheet. All instances of the FinacialResults class are populated with 0.0

Figuring that the column mapping maybe at fault I queried the columns collection of the ExcelQueryFactory using

Dim cols = ExcelEngine.GetColumnNames(excelPageName)

Which returned the below.

I changed the column mappings to reflect these but again the results come back as 0.0 for all instances.

ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.MonthBudget, "F4")
ExcelEngine.AddMapping(Of FinancialResults)(Function(m) m.MonthActual, "F5")

Question

Is this possible using LinqToExcel given the format of this spreadsheet? If so what am I doing wrong? Should I be using the WorksheetRange methods or loading each cell individually?

Is there another preferable alternative?

解决方案

It's not possible for LinqToExcel to read the spreadsheet you have because of its layout.

The easiest way to read it is to change the layout so the first row contains the column names like the picture below shows

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