Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Custom double format #,##0.000_);[Red]\(#,##0.000\) will convert to datetime #267

Closed
shps951023 opened this issue Jun 29, 2021 · 8 comments
Labels
bug Something isn't working
Projects

Comments

@shps951023
Copy link
Member

shps951023 commented Jun 29, 2021

Version : v0.16.0-0.17.1

image

@shps951023 shps951023 created this issue from a note in MiniExcel (In progress) Jun 29, 2021
@shps951023 shps951023 added the bug Something isn't working label Jun 29, 2021
@shps951023 shps951023 changed the title v0.16.1-0.17.1 auto datetime convert bug v0.16.1-0.17.1 auto custom datetime format convert will cause double value to datetime type bug Jun 29, 2021
@shps951023 shps951023 changed the title v0.16.1-0.17.1 auto custom datetime format convert will cause double value to datetime type bug v0.16.0-0.17.1 auto custom datetime format convert will cause double value to datetime type bug Jun 29, 2021
@shps951023
Copy link
Member Author

image

@shps951023
Copy link
Member Author

shps951023 commented Jun 29, 2021

styles.xml

    <numFmts count="2">
        <numFmt numFmtId="43" formatCode="_(* #,##0.00_);_(* \(#,##0.00\);_(* &quot;-&quot;??_);_(@_)"/>
        <numFmt numFmtId="166" formatCode="#,##0.000_);[Red]\(#,##0.000\)"/>
    </numFmts>
    <cellStyleXfs count="3">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0"/>
        <xf numFmtId="43" fontId="1" fillId="0" borderId="0" applyFont="0" applyFill="0" applyBorder="0" applyAlignment="0" applyProtection="0"/>
        <xf numFmtId="0" fontId="2" fillId="0" borderId="0"/>
    </cellStyleXfs>
    <cellXfs count="5">
        <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
        <xf numFmtId="0" fontId="0" fillId="2" borderId="1" xfId="0" applyFill="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="center" vertical="center"/>
        </xf>
        <xf numFmtId="14" fontId="3" fillId="3" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="center" vertical="center" wrapText="1"/>
        </xf>
        <xf numFmtId="166" fontId="4" fillId="2" borderId="1" xfId="0" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="center" vertical="center"/>
        </xf>
        <xf numFmtId="166" fontId="4" fillId="2" borderId="1" xfId="1" applyNumberFormat="1" applyFont="1" applyFill="1" applyBorder="1" applyAlignment="1">
            <alignment horizontal="center" vertical="center"/>
        </xf>
    </cellXfs>

sheet.xml

    <sheetData>
        <row r="1" spans="1:4" ht="28.8">
            <c r="A1" s="1">
                <v>10618</v>
            </c>
            <c r="B1" s="2" t="s">
                <v>0</v>
            </c>
            <c r="C1" s="3">
                <v>43.199999999999996</v>
            </c>
            <c r="D1" s="4">
                <v>1.2</v>
            </c>
        </row>
    </sheetData>

@shps951023
Copy link
Member Author

shps951023 commented Jun 29, 2021

void Main()
{
	var basicDate = new DateTime(2021, 01, 01);
	var formatCode = @"#,##0.000_);[Red]\(#,##0.000\)";
	var basicDateFormatString = basicDate.ToString(formatCode);
	var formatCodeDate = DateTime.TryParseExact(basicDateFormatString, formatCode, CultureInfo.InvariantCulture,
			DateTimeStyles.AllowLeadingWhite | DateTimeStyles.AllowTrailingWhite, out var outDate);
	Console.WriteLine(outDate);
}

outDate @"#,##0.000_);[Red]\(#,##0.000\)" format is datetime format, damn...

image

@shps951023
Copy link
Member Author

It have to follow ECMA-376 number format strings standard

@shps951023
Copy link
Member Author

image

@shps951023
Copy link
Member Author

shps951023 commented Jun 29, 2021

ExcelDataReader
image
It looks like string type 20/05/2021 20/05/2021 20/05/2021 is better?

@shps951023
Copy link
Member Author

image

@shps951023 shps951023 changed the title v0.16.0-0.17.1 auto custom datetime format convert will cause double value to datetime type bug Auto custom datetime format #,##0.000_);[Red]\(#,##0.000\) convert will cause double value to datetime type bug Jun 30, 2021
@shps951023 shps951023 changed the title Auto custom datetime format #,##0.000_);[Red]\(#,##0.000\) convert will cause double value to datetime type bug Custom double format #,##0.000_);[Red]\(#,##0.000\) will convert to datetime Jun 30, 2021
shps951023 added a commit that referenced this issue Jun 30, 2021
-[Bug] Fix v0.16.0-0.17.1 custom format contains specific format (eg:`#,##0.000_);[Red]\(#,##0.000\)`), automatic converter will convert double to datetime #267
@shps951023 shps951023 mentioned this issue Jun 30, 2021
@shps951023
Copy link
Member Author

v 0.17.3 fix this bug

@shps951023 shps951023 moved this from In progress to Done in MiniExcel Jun 30, 2021
@shps951023 shps951023 moved this from Done to Not Important in MiniExcel Jul 1, 2021
@shps951023 shps951023 moved this from Not Important to Done in MiniExcel Jul 1, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working
Projects
MiniExcel
  
Done
Development

No branches or pull requests

1 participant