CODEWITHSUNDEEP
c#excellinqlinq-to-excel
T. H.
T. H.

Reputation: 79

linqtoexcel c# make blank fields null or 0 after fetching them?

Basically I have an excel file with a lot of worksheets, whenever one of these worksheets have a reference to another worksheet, but the reference is an empty cell, e.g cell 'C5' in the 'Example' worksheet has a reference to cell 'J10' in the 'RefEx' worksheet, if cell 'J10' is empty, cell 'C5' will be blank, but won't be empty because it still contains the reference, and thus I can't insert it to my database because I need to insert it as a decimal (empty rows are inserted as 0,00).

It works and will insert 0,00 on blank cells with references as long as I change the formatting of the columns that have those references, but it's a lot of manual work with the amount of worksheets I have, and will be having.

Is there a way to make those fields null or at least just make them 0 without touching the excel file?

The only thing I kind of find weird is that I can easily read the cells as strings, they will just appear to be empty, but as soon as I try to change them to decimals, the error occurs.

This is how I connect and query the excel file:

string pathToExcelFile = @"D:\Programming Tools\Visual Studio\Projects\KPIimport\SampleXLSFile.xls";
ConnectionExcel ConObject = new ConnectionExcel(pathToExcelFile);
var query2 = from a in ConObject.UrlConnection.Worksheet<Product>("Sample-spreadsheet-file") select a;

And my class:

class Product {
    public string ItemName {
        get;
        set;
    }
    public string DogName {
        get;
        set;
    }
    public string FatDog {
        get;
        set;
    }

And where the error occurs in my code, because it expects a string that it can convert to decimals, but the reference messes with that:

command.Parameters.AddWithValue("@ItemName", item.ItemName);
command.Parameters.AddWithValue("@DogName", item.DogName);
command.Parameters.AddWithValue("@FatDog", Convert.ToDecimal(item.FatDog));

Thanks to Caius Jard it has been fixed with:

private string _fatDog;
public string FatDog {
  get {
    if(_fatDog == null) {
       return "0";
    } else if(_fatDog == "") {
       return "0";
    } else if(_fatDog == "-"))
       return "0";
    } else {
       return _fatDog;
    }
  }
  set {
     _fatDog = value;
  }
}

Upvotes: 0

Views: 740

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

You can add a body to the get,that tests the current value for a variety of undesirables, and returns “0” instead, for example

private string _fatDog;
public string FatDog(
  if(_fatDog == null)
    return "0";
  else if(_fatDog == "")
    return "something else";
  else if(_fatDog.StartsWith("'Data")) //ensure _fatDog isn't null before you do this - this code here does that in the "if(_fatDog == null)" part
    return "123";
  else
    return _fatDog;
)

As an aside, there are occasionally good reasons to avoid using AddWithValue - https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/

Upvotes: 1

Related Questions