formatCode syntax rules....

[Jim Hargrave]

We now extract formatCode for chart number values (cached). These are the general syntax rules.

<img src="https://r2cdn.perplexity.ai/pplx-full-logo-primary-dark%402x.png" class="logo" width="120"/>

## Syntax Rules for Defining a FormatCode in OpenXML

The `FormatCode` in OpenXML specifies how numbers, dates, or text are displayed in cells, closely following Excel's number formatting conventions. Here are the core syntax rules and behaviors:

**1. Section Structure**

- A format code can have up to four sections, separated by semicolons (`;`):
    - 1st section: Format for positive numbers
    - 2nd section: Format for negative numbers
    - 3rd section: Format for zero values
    - 4th section: Format for text
- Example: `#,##0.00;[Red]-#,##0.00;0;"Text: "@`[^2].

**2. Displaying Text and Numbers**

- Enclose literal text in double quotes (`" "`), or precede a single character with a backslash (`\`).
- Single quotation marks must not be used to denote text.
- Characters inside double quotes or following a backslash are always treated as literals, not as part of the format syntax.
- Example: `$0.00" Surplus";$-0.00" Shortage"` displays `$125.74 Surplus` for positive and `$-125.74 Shortage` for negative numbers[^2].

**3. Special Characters**

- The following characters can be displayed without quotes: dollar sign, minus, plus, slash, parentheses, colon, exclamation, caret, ampersand, apostrophe, tilde, curly brackets, less-than, greater-than, equal sign, and space[^2].

**4. Text Section**

- If included, the text section must be last.
- The `@` symbol in the text section is replaced by the cell's text value.
- If `@` is omitted, cell text will not be displayed.
- You can combine text and the `@` symbol, e.g., `"Note: "@`[^2].

**5. Number Format Placeholders**

- `0` — Digit placeholder (shows insignificant zeros)
- `#` — Digit placeholder (does not show insignificant zeros)
- `.` — Decimal point
- `,` — Thousands separator
- `%` — Percentage (multiplies by 100 and appends `%`)
- `E+00` — Scientific notation

**6. Date and Time Formatting**

- Uses letters such as `m`, `d`, `y`, `h`, `s`, `AM/PM` for date and time representation.
- Example: `mm-dd-yy`, `h:mm AM/PM`[^6].

**7. Color and Conditional Formatting**

- Colors can be specified in square brackets, e.g., `[Red]`.
- Conditional formatting can be applied using `[condition]`, e.g., `[>100]0.00`.

**8. Predefined Formats**

- OpenXML recognizes standard format codes by ID, such as:
    - `0` = General
    - `1` = 0
    - `2` = 0.00
    - `9` = 0%
    - `10` = 0.00%
    - `14` = mm-dd-yy
- Custom formats can be defined as needed[^6].

**9. XML Representation**

- In OpenXML, the format code is typically set as the `formatCode` attribute of the `<numFmt>` element in SpreadsheetML, or as the content of the `<c:formatCode>` element in charts[^1][^3][^5].

**References:**

- [^1] FormatCode class and XML serialization
- [^2] NumberingFormats class, section rules, and text handling
- [^3] formatCode element definition
- [^5] NumberingFormat.FormatCode property
- [^6] Predefined number format codes in OpenXML

These rules ensure that FormatCode definitions in OpenXML are consistent with Excel's formatting expectations and render correctly in Office applications.

<div style="text-align: center">⁂</div>

[^1]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.drawing.charts.formatcode?view=openxml-3.0.1

[^2]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformats?view=openxml-3.0.1

[^3]: https://c-rex.net/samples/ooxml/e1/Part4/OOXML_P4_DOCX_formatCode_topic_ID0ECCEQB.html

[^4]: https://stackoverflow.com/questions/7872116/applying-number-format-to-a-cell-value-using-openxml

[^5]: https://learn.microsoft.com/en-us/dotnet/api/documentformat.openxml.spreadsheet.numberingformat.formatcode?view=openxml-3.0.1

[^6]: https://c-rex.net/samples/ooxml/e1/Part4/OOXML_P4_DOCX_numFmt_topic_ID0EHDH6.html

[^7]: https://learn.microsoft.com/ja-jp/dotnet/api/documentformat.openxml.spreadsheet.numberingformat.formatcode?view=openxml-3.0.1

[^8]: https://stackoverflow.com/questions/23501843/applying-number-formatting-in-openxml

[^9]: https://github.com/OfficeDev/open-xml-docs/blob/main/docs/spreadsheet/working-with-conditional-formatting.md

[^10]: https://www.brandwares.com/downloads/Open-XML-Explained.pdf

[^11]: http://web.mit.edu/~stevenj/www/ECMA-376-new-merged.pdf

[^12]: https://github.com/dotnet/Open-XML-SDK/issues/1413

[^13]: https://en.wikipedia.org/wiki/Office_Open_XML

[^14]: https://stackoverflow.com/questions/9929339/having-trouble-applying-a-number-format-with-openxml-in-c-sharp

[^15]: http://blog.livedoor.jp/openxml_memo/archives/13942619.html

[^16]: https://gist.github.com/think49/0057f0d540d81e1a24967b869b1ec72f

[Denis A. Konovalyenko]

Jim, I really appreciate your thorough analysis. Thank you very much!

--
You received this message because you are subscribed to the Google Groups "okapi-devel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to okapi-devel...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/okapi-devel/fd43759a-815a-4353-80d9-48714d7893af%40gmail.com.