CODEWITHSUNDEEP
c#linqimport-from-excellinq-to-excel
alsafoo
alsafoo

Reputation: 788

LinqToExcel returns null

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

Upvotes: 0

Views: 2451

Answers (3)

Jawad Sabir
Jawad Sabir

Reputation: 167

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.

Upvotes: 0

alsafoo
alsafoo

Reputation: 788

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

Upvotes: 7

Veena
Veena

Reputation: 256

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

Upvotes: 0

Related Questions