0

I have an Excel file (.xlsx), that I open in C# via Spreadsheet Light.

Some Cells can have the value #N/A (or whichever representation your language version of Excel uses) as a result of an SVERWEIS (in German, I think it is VLOOKUP in English).

What is the proper way to detect this cell error via SL? I currently check, if the string representation of the cell is "#N/A", which is most probably not the best way to do it. Is there any "correct" check, that I missed?

What I currently do:

using(SLDocument doc = new SLDocument(filename))
{
    if(doc.GetCellValueAsString("A11") != "#N/A")
        //Do error handling here
}

What I would like to do would be more like:

using(SLDocument doc = new SLDocument(filename))
{
    if(doc.HasCellError("A11")) //This function doesn't exist (yet?)
        //Do error handling here
}

This would eliminate the hackish looking solution with the magic string #N/A.

MilConDoin
  • 734
  • 6
  • 24

1 Answers1

0

There is a function in excel called IFERROR which allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the looked up value is returned. If VLOOKUP returns the #N/A error, IFERROR takes over and returns the value you supply.

=IFERROR(VLOOKUP(B10,table,2,FALSE),"Not found")

This will return the message "Not found" instead of #N/A, and then you can check for that specific value you set. I'm not sure if this is the best way to check, but i believe it improves your current solution.

Armando Bracho
  • 659
  • 5
  • 21
  • I am on the consumer side of this interaction, I don't produce the Excel file to parse, so your solution doesn't help. – MilConDoin Jul 25 '16 at 12:06
  • Are you able to call the WorksheetFunction of excel from your SL object? for example: [Your Excel Object].WorksheetFunction as that would allow you to run excel verification from the consuming side such as the one i provided or IsError. – Armando Bracho Jul 25 '16 at 12:22