Explicit vs. implicit value type

[woutertinusf19]

First of all thanks for a great library!

In order to work around a specific bug in Excel, I would like to know if the cell has been explicitly saved as a string value (with t="s" set on the <c> element) or if EPPlus just interpreted the value as a string based on some heuristics. I couldn't find any way to do this in the documentation or public members.

Some context: specifically the bug I'm struggling with is related to Cell.Style.QuotePrefix, which works fine from the EPPlus side, but is 'sticky' on Microsoft side. When a user (only) removes the single quote, Excel does not update the cell style and thus the bit remains true. The only difference in the XML is that Microsoft removes the t="s" attribute (and puts the value directly in the cell, instead of referring to the shared strings).

This is apparently a big enough clue for Excel itself to change the default horizontal alignment when rendering the sheet, but I need to do the same programmatically and I don't have access to the t attribute as far as I could tell.

Any advice appreciated!

[JanKallman]

In EPPlus, any cell value with t="s" will be of data type string.

worksheet.Cells["A1"].Value="1";  // Will appear as text in Excel. '1
if(worksheet.Cells["A1"].Value is string) //Check if the value is text in EPPlus.
{
  //Cell value is text.
}

The Cell.Style.QuotePrefix will only "style" the value as text. If it is set as a numeric value, the value will still be stored a number.

worksheet.Cells["A1"].Value=1D;  // Will appear as text in Excel, but is stored as a number. 
worksheet.Cells["A1"].Cell.Style.QuotePrefix=true; // The value will be formatted as text
if(worksheet.Cells["A1"].Value is string) //The value will not be a string, as it was written as a double.
{
  //Cell value is text.
}

[woutertinusf19]

Thanks for your fast reply! The issue though is that I'm not writing with EPPlus but parsing Excel documents from random sources and want to know whether the cells are aligned left or right. Testing for Cell.Style.QuotePrefix == true is unreliable because Excel for some reason doesn't remove that bit when the user first adds and later removes the leading ' from their cell.

EPPlus indeed treats any cell with t="s" as string. But it also treats cells without any t attribute as string. If I could tell the difference between those two, I would know whether the QuotePrefix should be applied or not.

[JanKallman]

If you load a workbook using EPPlus, you should normally only have string values in cells with type = "s" or "str".
This is the code that converts the values on load:

EPPlus/src/EPPlus/Utils/TypeConversion/ConvertUtil.cs

Line 797 in 6183911

internal static object GetValueFromType(XmlReader xr, string type, int styleId, ExcelWorkbook workbook)

Where do you see strings without the t attribute set? Can you provide a sample?

[woutertinusf19]

While figuring out a minimal case to reproduce this with Excel, I noticed that the whole QuotePrefix was a red herring. The reproduction is extremely simple:

• 1. Create a new workbook
• 2. Type any number into cell A1
• 3. Save it.

What you get is a cell like

            <c r="A1">
                <v>205</v>
            </c>

Reading the function you linked, a double value is supposed to result from this, but I'm getting "205" (string) as Cell.Value.

repro.xlsx

[JanKallman]

When I open this workbook in EPPlus I get a double in the Value of A1...

            using (var p = OpenTemplatePackage("repro.xlsx"))
            {
                var ws = p.Workbook.Worksheets[0];
                var v = ws.Cells["A1"].Value;
                Assert.IsInstanceOfType(v, typeof(double));
            }

Do you do anything differently?