DEV Community

Masui Masanori
Masui Masanori

Posted on

【.NET Core】【ClosedXML】Getting cell values 1

Intro

When I use ClosedXML to get values from Excel, the values somethimes aren't as same as the actual values.
I want to know when they will be happened.

Environments

  • .NET Core ver.3.1.402
  • ClosedXML ver.0.95.3

Base project

ISpreadsheetValueGetter.cs

using System.Collections.Generic;
namespace FileAccesses
{
    public interface ISpreadsheetValueGetter
    {
        Dictionary<string, string> LoadSample();
    }
}
Enter fullscreen mode Exit fullscreen mode

SpreadsheetValueGetter.cs

using System.Collections.Generic;
using ClosedXML.Excel;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

namespace FileAccesses
{
    public class SpreadsheetValueGetter: ISpreadsheetValueGetter
    {
        private readonly ILogger<SpreadsheetValueGetter> _logger;
        private readonly string _filePath;
        public SpreadsheetValueGetter(ILogger<SpreadsheetValueGetter> logger,
            IConfiguration config)
        {
            _logger = logger;
            _filePath = config["SampleFilePath"];
        }
        public Dictionary<string, string> LoadSample()
        {
            using(var book = new XLWorkbook(_filePath))
            {
                var sheet = GetTargetSheet(book);
                if (sheet == null)
                {
                    return new Dictionary<string, string>();
                } 
                return GetCellValues(sheet);
            }
        }
        private IXLWorksheet? GetTargetSheet(XLWorkbook book)
        {
            // Get Worksheet from Workbook
        }
        private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
        {
            // Get cell values
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

Get Worksheet by named cell

When I want to get a Worksheet by a cell name what is named on the Workbook, I can do this.

SpreadsheetValueGetter.cs

...
        private IXLWorksheet? GetTargetSheet(XLWorkbook book)
        {
            var result = book.Cell("NameOnBook");
            return result?.Worksheet;
        }
...
Enter fullscreen mode Exit fullscreen mode

If the name is named on a Worksheet, I can get in this way.
(I'm afraid it isn't the best way)

SpreadsheetValueGetter.cs

...
        private IXLWorksheet? GetTargetSheet(XLWorkbook book)
        {
            return (book.Worksheets.FirstOrDefault(s =>
                    s.Cell("NameOnSheet") != null));
        }
...
Enter fullscreen mode Exit fullscreen mode

Get cell values

Most of values and Excel funcitions(ex. "VLOOKUP", "SUM" etc.) can be gotten by "GetFormattedString".

string cellValue = sheet.Cell(2, 2).GetFormattedString();
Enter fullscreen mode Exit fullscreen mode

It also get the cell format like "1.00" and so on.

But some values, formulas or functions aren't match with the values what are gotten by "GetFormattedString".

So I try some of the way to get the value and think how to avoid the problem.

SpreadsheetValueGetter.cs

...
        private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
        {
            var results = new Dictionary<string, string>();
            AddCellValue(results, sheet.Cell(2, 2), sheet, "error");

            return results;
        }
        private void AddCellValue(Dictionary<string, string> dictionary,
            IXLCell cell,
            IXLWorksheet sheet, 
            string name)
        {
            dictionary.Add($"{name}_FormattedString", cell.GetFormattedString());
            dictionary.Add($"{name}_CachedValue", cell.CachedValue?.ToString() ?? "");
            dictionary.Add($"{name}_ValueCached", cell.ValueCached ?? "");
            dictionary.Add($"{name}_DataType", cell.DataType.ToString());
            dictionary.Add($"{name}_FormulaAi", cell.FormulaA1);
        }
...
Enter fullscreen mode Exit fullscreen mode

Grammatical mistakes of formulas

Actual value

=s
Enter fullscreen mode Exit fullscreen mode

Display on Excel

#NAME?
Enter fullscreen mode Exit fullscreen mode

Code

...
        private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
        {
            var results = new Dictionary<string, string>();
            AddCellValue(results, sheet.Cell(2, 2), sheet, "error");        
            return results;
        }
...
Enter fullscreen mode Exit fullscreen mode

Result

{
    "error_FormattedString": "s",
    "error_CachedValue": "s",
    "error_ValueCached": "#NAME?",
    "error_DataType": "Text",
    "error_FormulaAi": "{s}"
}
Enter fullscreen mode Exit fullscreen mode

ValueCached

In this sample, I only can get displayed value on Excel by "ValueCached".
The most important problem is it has been deprecated.

But though the compiler suggests using "CachedValue" instead of "ValueCached", the result isn't same in this case.

At least I want to know the mistake and distinct the case of setting "s" as cell value.

Get Excel function's errors

I can get the error by using evaluating formula.

...
            try
            {
                var calcResult = sheet.Evaluate(cell.FormulaA1);
                dictionary.Add($"{name}_Evaluate", calcResult?.ToString()?? "null");   
            }
            catch(Exception ex)
            {
                dictionary.Add($"{name}_Evaluate_Error", ex.Message);
            }
...
Enter fullscreen mode Exit fullscreen mode

Result

{
...
    "error_Evaluate_Error": "Identifier expected."
}
Enter fullscreen mode Exit fullscreen mode

IFERROR

I get more complecated problem when I use "IFERROR" function and set the cell what is written incorrect formula as the first argument.

Actual value

=IFERROR(B2,-1)
Enter fullscreen mode Exit fullscreen mode

Display on Excel

-1
Enter fullscreen mode Exit fullscreen mode

Code

...
        private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
        {
            var results = new Dictionary<string, string>();
...
            AddCellValue(results, sheet.Cell(2, 3), sheet, "ifError");        
            return results;
        }
...
Enter fullscreen mode Exit fullscreen mode

Result

{
...
    "ifError_FormattedString": "s",
    "ifError_CachedValue": "s",
    "ifError_ValueCached": "-1",
    "ifError_DataType": "Text",
    "ifError_FormulaAi": "IFERROR(B2,-1)"
}
Enter fullscreen mode Exit fullscreen mode

This problem can't be resolve by evaluating formula.
Because the result is "0".

Shall I evaluate all refference cell formulas when the cell use "IFERROR" function?

Latest comments (0)