ClosedXML read row with VLOOKUP formula

4

votes

im currently working on a project that reads data from excel using closedXML but im having errors with my code because the excel cells have vlookup formulas in it. is there a way for closedxml to read row values with vlookup formulas? Thanks!

this is where i get the "Syntax Error" error:

if (rowValue.Cell(colnum).HasFormula)
{
    ((IDictionary<String, Object>)item)[field] = rowValue.Cell(colnum).Value.ToString();
}

c#excelclosedxml

2

votes

The develop branch of http://github.com/ClosedXML/ClosedXML now supports VLOOKUP and HLOOKUP.

1

votes

Just found the ValueCached gave the right text. (ClosedXML_v0.76.0.0)

IXLCell JobCell = row.Cells().Where(item => item.Address.ColumnLetter == "B").FirstOrDefault(); }
string Job = JobCell.RichText.Text;
if (string.IsNullOrEmpty(Job))
{
   Job = JobCell.ValueCached;
}

0

votes

ClosedXML doesn't support VLOOKUP and HLOOKUP formulas. If you need them you have to implement them manually by reading the range of cells and searching for the row or column with the given value.

0

votes

As Raidri mentions VLOOKUPS are not supported in Closed XML.

See this discussion on the closedxml codebase forum for updates on VLOOKUP support in closed xml

https://closedxml.codeplex.com/discussions/569497

A suggested workaround would be to write a VBA macro for your template file to hook into the before save event, and paste special values into a hidden worksheet, and upload from there. See these links for a starter on doing that.

Pasting special values programattically in VBA - How to remove formulas from sheet but keep their calculated values Hooking in to the before save event - http://www.mrexcel.com/forum/excel-questions/374035-visual-basic-applications-save-event.html

ClosedXML read row with VLOOKUP formula

4 Answers