search

radiasoft / pykern

Apache License 2.0
5 stars 7 forks source link

xlsx formatting fails when round_digits=2 #200

Open robnagler opened 2 years ago

robnagler commented 2 years ago

Setup the test case this way:

--- a/tests/xlsx_data/2.in/case.py
+++ b/tests/xlsx_data/2.in/case.py
@@ -10,7 +10,7 @@ import pykern.xlsx
 PATH = 'case2.xlsx'
 w = pykern.xlsx.Workbook(path=PATH)
 s = w.sheet(title='s1')
-t = s.table(title='t1', defaults=PKDict(round_digits=0, num_fmt='currency'))
+t = s.table(title='t1', defaults=PKDict(round_digits=2, num_fmt='currency'))
 t.header(
     name='Name',
     count='Count',

And it fails:

$ diff '/home/vagrant/src/radiasoft/pykern/tests/xlsx_data/2.out/case2#1.csv' '/home/vagrant/src/radiasoft/pykern/tests/xlsx_work/2/case2#1.csv'
    1c1
< 24,24.1
---
> 24.0,24.0.1
robnagler commented 2 years ago

There are various bugs in the libraries here. xlsxwriter is outputting wrong count= in styles.xml:

  <numFmts count="2">
    <numFmt numFmtId="164" formatCode="@"/>
    <numFmt numFmtId="164" formatCode="@"/>
    <numFmt numFmtId="165" formatCode="$#,##0.00"/>
    <numFmt numFmtId="164" formatCode="@"/>
    <numFmt numFmtId="165" formatCode="$#,##0.00"/>
  </numFmts>

Then pandas is formatting values strangely: 24.0.1 should be 24. At least that's what Excel says. Even after save in Excel, pandas outputs: 24,24.1. It's kind of hard to know where the .1 is coming from.

pandas is used by pkunit to convert spreadsheets to csv, and has worked up until now (less complicated sheets).