Setting CellValueType does not behave like expected

[jbartula]

If I set the ValueType of a CellValue programatically like this:

sheet.Cells.SetValue(row, col, new CellValue("8007-2") { ValueType = CellValueType.Text });

the ValueType is ignored, and the Sheet treats the cell as a DateTime:

If I set it like this:

sheet.Cells.SetValue(row, col, CellValue.Text("8007-2"));

it works fine:

This is annoying and counter-intuitive, because I have a large switch() case that decides the valueType, which is then passed to SetValue. Doing CellValue.Text() every time is less convenient.

[jbartula]

It would be nice if you could just make the internal CellValue(object? data, CellValueType cellValueType) constructor public.

This would solve the issue. Thanks.

[anmcgrath]

Hi @jbartula It's a good question. I'm kind of wary about making this constructor public as it may increase the chance that the cell value type won't be correct.

Could you first have your switch statement set the variable cellValue and then use sheet.Cells.SetValue(row, col, cellValue)?

[jbartula]

Yes that's a possible alternative, but introduces more code (1 more line per each). I currently just use a second switch at the bottom that calls CellValue.Logical, CellValue.Text, etc. depending on the valueType.

Good point on the constructor, you could pass a string with a Number valueType which would break things...

But the question remains, why is the public property ValueType ignored in my first example? This is a (small) bug in my opinion (either it should trust the user override, or the property should be read-only)

[anmcgrath]

Hi @jbartula that's a fair call - the value shouldn't be able to be set privately and is an oversight

[anmcgrath]

@jbartula thanks for the feedback. I've made CellValue.Data and CellValue.ValueType have private setters. If you're happy with this making sense I can close this PR.

[jbartula]

Thanks.

[jbartula]

After the latest update, this bug reappared, with using CellValue.Text().

Hit enter

And it cuts off the zero, despite the cell type being Text

[jbartula]

This is a workaround:

sheet.Cells.BeforeCellValueConversion += (sender, args) =>
{
    if (sheet.Cells[args.Row, args.Column].ValueType == CellValueType.Text)
    {
        args.NewValue = CellValue.Text(args.OriginalValue?.ToString() ?? string.Empty);
    }
};

[anmcgrath]

After the latest update, this bug reappared, with using CellValue.Text().

Hit enter

And it cuts off the zero, despite the cell type being Text

Apologies, I'll look into this - is it with v0.7.1?

[anmcgrath]

Hi @jbartula I can't reproduce this... Are you please able to give some more information?