How can I have xlsxwriter write backslashes in number format (for LibreOffice)?

Question

Snippet to reproduce:

import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()
data_fmt = workbook.add_format({'num_format':  r"0.00##\%;[RED](0.00##\%)"})
worksheet.write('A1', 5.5, data_fmt)

workbook.close()

My expecatation would be that when I open up that file and inspect the format for A1 it would be 0.00##\%;[RED](0.00##\%).

Instead I get 0.00##%;[RED](0.00##%)

NOTE: The format, taken from this stackoverflow question is what would give you the percent sign at the end without multiplying the value by 100.

EDIT2:

>>> import xlsxwriter
>>> xlsxwriter.__version__
'1.0.2'

Also, I am opening the file in LibreOffice.

EDIT3:

Here's a snippet that tries 1, 2, 3, and 4 backslashes. All of them produce the same format when I open the result in libreoffice.

import xlsxwriter

fmts = [r"0.00##\%;[RED](0.00##\%)", r"0.00##\\%;[RED](0.00##\\%)",
        r"0.00##\\\%;[RED](0.00##\\\%)", r"0.00##\\\\%;[RED](0.00##\\\\%)"]
fp = "test{}.xlsx"
for i, fmt in enumerate(fmts):
    workbook = xlsxwriter.Workbook(fp.format(i))
    worksheet = workbook.add_worksheet()
    data_fmt = workbook.add_format({'num_format': fmt})
    worksheet.write('A1', 5.5, data_fmt)
    workbook.close()

EDIT 4:

I have confirmed that this is issue does not exist in excel. So currently it is strictly a LibreOffice issue.

So is this a question? And what is your question? What output is produced by that, and what do you want? — hyper-neutrino, Oct 24 '17 at 20:56
sorry, I accidentally hit submit before I was done. Full question now up — michael_j_ward, Oct 24 '17 at 20:57

score 2 · Answer 1 · answered Oct 24 '17 at 21:05

2

Format is as you have desired. Are you checking under custom by right clicking the A1 cell?

answered Oct 24 '17 at 21:05

Anil_M

10,893
6
47
74

Interesting. I am using LibreOffice, and when I had a coworker open it up in excel, he also saw `5.5%`. So it's a libre-office specfic thing. NOTE: When i directly paste in that format-string while already inside LibreOffice, I get the desired effect – michael_j_ward Oct 24 '17 at 21:20
It may be LibreOffice thing. Try double back slashes \\ instead of single infront of % and see if helps. – Anil_M Oct 24 '17 at 21:41
@micheal_j_ward Try the suggestion to use double (escaped) backslashes. That's how Excel stores them internally. Excel doesn't strictly require them to be escaped but other applications, such as LibreOffice, might. – jmcnamara Oct 25 '17 at 06:45
@jmcnamara and Anil_M, I updated the question with a new snippet that tests 1, 2, 3, and 4 backslashes. All of them produce the same format string when inspected in excel (that produces the `550%` instead of `5.5%`) – michael_j_ward Oct 25 '17 at 20:43
Actually, I was wrong about the backslashes. I'll post an answer. – jmcnamara Oct 26 '17 at 07:57

score 0 · Answer 2 · answered Oct 26 '17 at 07:57

Your program works as expected with Excel. Here is the output when I ran it:

Also I created a new file in Excel, entered 5.5 and your format (0.00##\%;[Red]\(0.00##\%\)), saved it, and verified the format as it is written by Excel:

$ unzip percent.xlsx -d percent

$ xmllint --format percent/xl/styles.xml | grep -i red
<numFmt numFmtId="164" formatCode="0.00##\%;[Red]\(0.00##\%\)"/>

As you can see there is a slight difference with backslashes for the brackets but nothing that would change how Excel would display the number:

0.00##\%;[RED](0.00##\%)    # Input
0.00##\%;[Red]\(0.00##\%\)  # Saved by Excel

So I would guess that this is a LibreOffice issue. You could verify that by creating a file with the format you want in Excel, saving it, and then opening it in LibreOffice.

How can I have xlsxwriter write backslashes in number format (for LibreOffice)?

2 Answers2