CODEWITHSUNDEEP
c#excelclosedxml
Page F.P.T
Page F.P.T

Reputation: 671

ClosedXML read row with VLOOKUP formula

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

Upvotes: 4

Views: 4672

Answers (4)

Chukiat
Chukiat

Reputation: 61

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

Upvotes: 1

Francois Botha
Francois Botha

Reputation: 4839

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

Upvotes: 2

Adam Diament
Adam Diament

Reputation: 4840

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

Upvotes: 0

Raidri
Raidri

Reputation: 17550

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.

Upvotes: 0

Related Questions