Skip to content

Spreadsheet number formats always use en-US separators, and a caller cannot tell numeric cells from text #233

Open

Description

@IngLP

What happens

When a number format is applied to a spreadsheet cell, the group separator is always , and the decimal separator always . (numfmt.rs#L863, numfmt.rs#L884).

A workbook does not store separators: the spreadsheet application takes them from the viewer's locale. In locales that use the opposite convention (Italian, German, Spanish, Portuguese and others), the Markdown reads differently from what the workbook shows to its own users:

Value Format Output Read with , as decimal separator
5002 #,##0 5,002 five point zero zero two
1250 #,##0 "€" 1,250 € one euro and twenty-five cents

The module comment already explains why dates stay ISO-like (numfmt.rs#L4-L6): mm/dd versus dd/mm is ambiguous for a downstream reader. Group and decimal separators carry the same ambiguity, and the error is a factor of a thousand.

Why a caller cannot correct it

Cell exposes only blocks, the already formatted text (_anydoc.pyi#L200-L203). A caller cannot tell a numeric cell from a text cell that looks like a number, so swapping separators afterwards would also change text cells that already follow the reader's convention, such as 1.250 entered as text.

Possible fixes

Either would let a caller produce unambiguous output:

  1. Expose on the cell whether it holds a number, with its raw value and its number format code.
  2. An option that chooses the group and decimal separators used when number formats are applied.

Reproduction

anydoc 0.2.5, Python 3.12, openpyxl 3.1:

import openpyxl, anydoc

wb = openpyxl.Workbook()
ws = wb.active
ws.append(["Format", "Value"])
for value, fmt in [(5002, "#,##0"), (1250, '#,##0 "€"'), (1250000, "#,##0"), (1.09, "General")]:
    ws.append([fmt, value])
    ws.cell(ws.max_row, 2).number_format = fmt
wb.save("formats.xlsx")
print(anydoc.to_markdown("formats.xlsx"))

Output:

| Format | Value |
| --- | --- |
| #,##0 | 5,002 |
| #,##0 "€" | 1,250 € |
| #,##0 | 1,250,000 |
| General | 1.09 |

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions