OpenXml - Preserve apostrophe when reading Excel file

Question

I'm trying to read an Excel file which looks like this:

...reading the file using this example https://stackoverflow.com/a/26063965/281451

But when I try to print the content of the cells, it's missing the leading apostrophes and it prints something like this:

cell val: 10000001

instead of what I'm expecting:

cell val: '10000001

Is there anyway of achieving this?

Answer 1

The leading apostrophe is not considered to be a part of a cell's value. This is more the convention that Excel uses to have some text instead of a number.

To give you an example, let's say you want to write 0001 in some cell, if you write it like that MS Excel will parse it as a number and set the cell to number 1.
However, if you write that text with leading apostrophe then MS Excel will leave it as it is.

Anyway, here is how you can detect if you should add that leading apostrophe:

var document = SpreadsheetDocument.Open(filePath, false);
var sharedStringTable = document.WorkbookPart.SharedStringTablePart.SharedStringTable;

var cellFormats = document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats;

// ...

if (cell.DataType == CellValues.SharedString)
{
    bool isQuoted = false;
    if (cell.StyleIndex != null && cell.StyleIndex.HasValue)
    {
        var cellFormat = cellFormats.ChildElements[(int)cell.StyleIndex.Value] as CellFormat;
        isQuoted = cellFormat.QuotePrefix;
    }

    Console.WriteLine("cell val: " + (isQuoted ? "'" : "") + sharedStringTable.ElementAt(Int32.Parse(cellValue)).InnerText);
}

// ...