ClosedXML Reader Class with VLOOKUP Formula

Question

im is currently working on a project that reads data from excel using closedXML, but im has errors with my code because there are vlookup formulas in excel cells. is there any way for closedxml to read string values ​​using vlookup formulas? Thanks!

this is where i get the syntax error:

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

Answer 1

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

Answer 2

ClosedXML does not support the VLOOKUP and HLOOKUP formulas. If you need this, you must implement them manually by reading a range of cells and searching for a row or column with a given value.

Answer 3

According to Rydy, VLOOKUPS are not supported in closed XML.

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

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

The recommended solution would be to write a VBA macro for your template file to connect to the event before saving, and insert special values ​​into the hidden worksheet and load from it. See these links for starter at this.

Insert special values ​​programmatically in VBA - How to remove formulas from a sheet, but save their calculated values Attaching to an event before saving - http://www.mrexcel.com/forum/excel-questions/374035-visual-basic-applications-save- event.html

Answer 4

Just found ValueCached gave the correct 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; }