This project has moved and is read-only. For the latest updates, please go here.

Looking for format code parsing code

Nov 27, 2012 at 3:31 AM

Hello, I'm looking for some code to handle Excel format codes commonly found in the formatCode attributes in OpenXML. I need to convert the format codes to another formatting language for a charting package I'm using. I cannot directly use the library because I am writing my code in java. I downloaded the source and did some grepping to find the code I'm looking for, but unfortunately, I failed to find it.

Does such code exist in ClosedXML, and if so, can you point me to it?

Thanks!

Nov 27, 2012 at 4:14 AM

If I understand your question correctly...

The number formats are usually stored as integers except when the user specifies a custom one. I use a simple dictionary to translate number format ids into a format string I can pass to the .ToString method and get the formatted string.

                var fCodes = new Dictionary<intstring>
                    {
                        {0, string.Empty},
                        {1, "0"},
                        {2, "0.00"},
                        {3, "#,##0"},
                        {4, "#,##0.00"},
                        {7, "$#,##0.00_);($#,##0.00)"},
                        {9, "0%"},
                        {10, "0.00%"},
                        {11, "0.00E+00"},
                        {12, "# ?/?"},
                        {13, "# ??/??"},
                        {14, "M/d/yyyy"},
                        {15, "d-MMM-yy"},
                        {16, "d-MMM"},
                        {17, "MMM-yy"},
                        {18, "h:mm tt"},
                        {19, "h:mm:ss tt"},
                        {20, "H:mm"},
                        {21, "H:mm:ss"},
                        {22, "M/d/yyyy H:mm"},
                        {37, "#,##0 ;(#,##0)"},
                        {38, "#,##0 ;[Red](#,##0)"},
                        {39, "#,##0.00;(#,##0.00)"},
                        {40, "#,##0.00;[Red](#,##0.00)"},
                        {45, "mm:ss"},
                        {46, "[h]:mm:ss"},
                        {47, "mmss.0"},
                        {48, "##0.0E+0"},
                        {49, "@"}
                    };

I hope it helps.
Nov 27, 2012 at 6:28 PM

Thanks, but actually I'm looking for something that knows how to interpret any given format code. I haven't found anything (looking at Apache POI at the moment), so I'll probably have to just look up the documentation on format codes and make something myself. I just figured this ought to have been done before, Excel being so old, and I also expect there's a lot to it and don't want to mess it up.