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();
}
}
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
}
}
}
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;
}
...
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));
}
...
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();
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);
}
...
Grammatical mistakes of formulas
Actual value
=s
Display on Excel
#NAME?
Code
...
private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
{
var results = new Dictionary<string, string>();
AddCellValue(results, sheet.Cell(2, 2), sheet, "error");
return results;
}
...
Result
{
"error_FormattedString": "s",
"error_CachedValue": "s",
"error_ValueCached": "#NAME?",
"error_DataType": "Text",
"error_FormulaAi": "{s}"
}
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);
}
...
Result
{
...
"error_Evaluate_Error": "Identifier expected."
}
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)
Display on Excel
-1
Code
...
private Dictionary<string, string> GetCellValues(IXLWorksheet sheet)
{
var results = new Dictionary<string, string>();
...
AddCellValue(results, sheet.Cell(2, 3), sheet, "ifError");
return results;
}
...
Result
{
...
"ifError_FormattedString": "s",
"ifError_CachedValue": "s",
"ifError_ValueCached": "-1",
"ifError_DataType": "Text",
"ifError_FormulaAi": "IFERROR(B2,-1)"
}
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?
Top comments (0)