One bad formula results in total report generation failure

[AnonymousNation]

Hello,

Noticed that if our Excel Template had one bad formula in a cell and ClosedXML attempted to process this cell, the whole report would fail and not further processing is done against the report. It would be great if ClosedXML would log any such errors and simply place, "#ERROR#" or similar in the cell in question and continue to process the report.

For example, I have this formula in my cell:

=IF($CY3="Data Condition", "Data Condition", IF(JT3, "True", "False"))

and JT3 contained something like this {{item.mynode.isgood.value}}

This would result in the whole report failing as the evaluation of IF({{item.mynode.isgood.value}}, "True", "False") bombs out given that the '{{ }}' expression doesn't evaluate to a True/False value.

Proposed solution (apologies for not cloning the repo and checking in code have SSL issues when cloning):

Within "ClosedXML.Report.RangeTemplate" update the Parse() method as follows:

private static RangeTemplate Parse(string name, IXLRange range, TempSheetBuffer buff, TemplateErrors errors, IDictionary<string, object> globalVariables)
        {
            var result = new RangeTemplate(name, range, buff,
                range.RowCount(), range.ColumnCount(), errors, globalVariables);

            var innerRanges = GetInnerRanges(range).ToArray();

            var sheet = range.Worksheet;
            for (int iRow = 1; iRow <= result._rowCnt; iRow++)
            {
                IXLCell xlCell = null;
                for (int iColumn = 1; iColumn <= result._colCnt; iColumn++)
                {
                     try
                    {
                        xlCell = range.Cell(iRow, iColumn);
                        if (innerRanges.Any(x => x.Ranges.Cells().Contains(xlCell)))
                            xlCell = null;
                    }
                    catch(Exception ex)
                    {
                        var errorMessage = string.Format("Cell: {0} Error: {1} FormulaA1: {2}", xlCell.Address.ColumnLetter,
                            ex.Message, xlCell.FormulaA1);
                        errors.Add(new TemplateError(errorMessage, range));

                        xlCell.Value = "#ERROR#";
                    }
                    finally
                    {
                        result._cells.Add(iRow, iColumn, xlCell);
                    }
                }
                if (iRow != result._rowCnt)
                    result._cells.AddNewRow();
            }

Basically we are catching any such errors and simply adding, "#ERROR#" to the troubled cell and we continue to process/generate the report.

Thanks.