Search code examples
pythonxlsxwriter

Using XLSXWRITER in Python: How insert 27 digit number in cell and display it as text not as scientific number

I'm writing an Excel using XLSXWRITER package in Python. Formats are applied to cells and this all works, except for one cell when the value assigned is a 27 digit text string (extracted from some source).

I've read How to apply format as 'Text' and 'Accounting' using xlsxwriter. It suggests to set the number format to '@', but when I try:

WSFMT_TX_REFINFO          = wb.add_format({'num_format': '@'
                                         , 'align': 'right'
                                         , 'border_color': WS_TX_BORDERCOLOR
                                         , 'right': WS_TX_BORDERSYTLE
                                         })

and write a cell with:

refdata = '001022002024080400002400105'
ws.write(wsRow, WS_COL_REF_INFO, refdata, WSFMT_TX_REFINFO)

The cell is shown as

1.041E+24

and in the editor field as

1.041002024073E+24

If I change the format specification from '@' to 0, i.e. change

WSFMT_TX_REFINFO          = wb.add_format({'num_format': '@'

to

WSFMT_TX_REFINFO          = wb.add_format({'num_format': 0

the cell is shown as

1022002024080400000000000

Note that the digits after the 14th are replaced by zeros. In the editor field it shows as

1.0220020240804E+24

What I need: The number shall be show as 27 digit string, exactly as found in refdata Note: There are cases, where refdata may contain alphanumeric strings in some cases, besides pure 27 digit strings.

Any hint?

Solution

  • This may be a case where it is better to use write_string() instead of the more generic write().