LinqToExcel returns null

Question

I have an excel sheet of xls format, I am using LinqToExcel to read it and then import it to my DB.

This sheet consist of about 3K row and only 6 cols. I am using .addmapping to map my class properties to the column names

The problem i have is: the cells of column "web-code" are SOMETIMES coming back as null although there are data in the cells.

Here is a sample data that is coming as null! enter image description here

My Code watch enter image description here

And here is a sample data where the data coming correct: enter image description here

My Code Watch enter image description here

I have tried applying ExcelColumn attribute for mapping, but no luck!

code:

        var factory = new ExcelQueryFactory(_excelFilePath);
        factory.AddMapping<ExcelPriceEntity>(x => x.WebCode, "WEB-CODE");
        factory.AddMapping<ExcelPriceEntity>(x => x.Type, "TYPE");
        factory.AddMapping<ExcelPriceEntity>(x => x.Style, "STYLE");
        factory.AddMapping<ExcelPriceEntity>(x => x.Qty, "QTY");
        factory.AddMapping<ExcelPriceEntity>(x => x.UnitPrice, "Unit Price");
        factory.AddMapping<ExcelPriceEntity>(x => x.Bucket, "WEBCODE W/BUCKET");

        factory.StrictMapping = StrictMappingType.ClassStrict;
        factory.TrimSpaces = TrimSpacesType.Both;
        factory.ReadOnly = true;
        var prices = factory.Worksheet<ExcelPriceEntity>(_allPricesSheetName).ToList();
        var priccerNP = prices.Where(p => p.Type.Contains("900 ARROW TAPE")).ToList();

My PriceEntity Class:

public class ExcelPriceEntity
{
    //[ExcelColumn("TYPE")] 
    public string Type { get; set; }
    public string WebCode { get; set; }
    //[ExcelColumn("STYLE")] 
    public string Style { get; set; }
    //[ExcelColumn("QTY")] 
    public string Qty { get; set; }
    //[ExcelColumn("Unit Price")] 
    public string UnitPrice { get; set; }
    //[ExcelColumn("WEBCODE W/BUCKET")] 
    public string Bucket { get; set; }
}

Apart from showing the code in textual form you also need to show us some lines that return goods results and some lines that return bad. It's not enough to say "the cells of column "web-code" are SOMETIMES coming back as null". You need to show us. — Enigmativity, Mar 03 '15 at 02:52
Use type field of the returned objects in code to look it up in the excel images. I am on the train now. — alsafoo, Mar 03 '15 at 03:07
@Engimativity In my question you have two excel images, the first is the set where web-code is coming is null. The second image of excel is where web-code column is coming populated. It is clear in the question, read it again. — alsafoo, Mar 03 '15 at 04:44
Are u sure the type of WebCode in ExcelPriceEntity class is string? — Veena, Mar 03 '15 at 04:58

score 7 · Accepted Answer · answered Mar 04 '15 at 18:43

Alternate Solution: I ended up saving the excel sheet as csv file, then import to SQL table.Then i used linq-to-sql to read the data.

Root Cause: After researching i found out the problem was that the first cell of this column(web-code) was interger number, and excel trys to figure out the datatype of the column by looking at the first rows!

So next rows of (web-code) column was some text data. So excel couldn't parse it as integer, and assign null value to it!

What I could've done is, assing text value to the first cell so excel would guess the data type as string. But I didn't test that. For anyone reading this answer, try having text value in you first row if you came across the same problem

Veena · Answer 2 · 2015-03-03T06:06:16.940

0

here, the Contains is not like string contains. It compares a list of cell values to the exact value u give inside the contains method. just try with the full text "900 AMMONIA STOCK OR CUST...)

edited Mar 03 '15 at 06:06

answered Mar 03 '15 at 05:21

Veena

256
1
10

I did, no results. tried with this line var priccerNP = prices.Where(p => p.Type.Equals("900 ARROW TAPE", StringComparison.OrdinalIgnoreCase)).ToList(); – alsafoo Mar 03 '15 at 05:38
then it is data related. but not sure what it is. bcz i know same code on same columns gives me results back – alsafoo Mar 03 '15 at 06:17

score 0 · Answer 3 · answered May 30 '18 at 09:45

Another alternative solution to @alsafoo is to convert the column from "general" to "text".

These are the steps: 1. Right click on any cell in the column. 2. In Number tab, select text. 3. Select Format cell 4. Press Ok.

After then, the library will read all values as string.

LinqToExcel returns null

3 Answers3