Skip the culture information mark in judgment of datatype

[Matsuyanagi]

Do you want to request a feature or report a bug?

• Bug

Version of ClosedXML

0.93
and
develop branch commit 19f5468

What is the current behavior?

The output of cell.GetFormattedString() is not correct in the cell with date format with culture specification.
It becomes a serial number (43252 in this case).

cell.GetFormattedString()	A1 copy text	GetFormat(A1,TRUE)	GetFormat(A1,FALSE)
2018/6/1	2018-6-1	yyyy-m-d;@	yyyy/m/d;@
43252	2018-6-1 12:00 AM	[$-409]yyyy-m-d h:mm AM/PM;@	[$-409]yyyy/m/d h:mm AM/PM;@
43252	1-Jun	[$-409]d-mmm;@	[$-409]d/mmm;@
43252	1-Jun-18	[$-409]d-mmm-yy;@	[$-409]d/mmm/yy;@
43252	01-Jun-18	[$-409]dd-mmm-yy;@	[$-409]dd/mmm/yy;@

GetFormat() refers to this page.

What is the expected behavior or new feature?

The date formatted string.

cell.GetFormattedString()	A1 copy text	GetFormat(A1,TRUE)	GetFormat(A1,FALSE)
2018/6/1	2018-6-1	yyyy-m-d;@	yyyy/m/d;@
2018/6/1 0:00 AM	2018-6-1 12:00 AM	[$-409]yyyy-m-d h:mm AM/PM;@	[$-409]yyyy/m/d h:mm AM/PM;@
1/Jun	1-Jun	[$-409]d-mmm;@	[$-409]d/mmm;@
1/Jun/18	1-Jun-18	[$-409]d-mmm-yy;@	[$-409]d/mmm/yy;@
01/Jun/18	01-Jun-18	[$-409]dd-mmm-yy;@	[$-409]dd/mmm/yy;@

Did this work in previous versions of our tool? Which versions?

I recently started using ClosedXML.
I don't know.

Reproducibility

Code to reproduce problem:

static void Main()
{
    var filename = "date.xlsm";

    // workbook
    using ( var wb = new ClosedXML.Excel.XLWorkbook( filename ) ) {
        // First sheet
        var sheet = wb.Worksheets.First();

        foreach ( var row in sheet.RowsUsed() ) {
            foreach ( var cell in row.CellsUsed( true ) ) {
                Console.Write( cell.GetFormattedString() );
                Console.Write( "\t" );
            }
            Console.WriteLine("");
        }
    }
}

XLCell.GetFormattedString() internally formats the string according to datatype with ParseCellValueFromString().
The determination of dataType is made with XLWorkbook.GetDataTypeFromFormat(), but if '0' is included in the culture information "[$-409]", it will be wrongly judged as XLDataType.Number.

It can solve by skipping "[$-...]" with XLWorkbook.GetDataTypeFromFormat() as follows.

/ClosedXML/Excel/XLWorkbook_Load.cs:1923

private static XLDataType? GetDataTypeFromFormat(String format)
{
    int length = format.Length;
    String f = format.ToLower();
    for (Int32 i = 0; i < length; i++)
    {
        Char c = f[i];
        if (c == '"')
            i = f.IndexOf('"', i + 1);
        else if ( c == '[' && i + 1 < length && f[ i + 1 ] == '$' ) // skip '[$...]' culture information mark
            i = f.IndexOf( ']', i + 1 );                            //
        else if (c == '0' || c == '#' || c == '?')
            return XLDataType.Number;
        else if (c == 'y' || c == 'm' || c == 'd' || c == 'h' || c == 's')
            return XLDataType.DateTime;
    }
    return null;
}

• I attached a sample spreadsheet.
  
  date.xlsx