def format_number_with_openpyxl(f, cell, wb):
s_fmt = cell.number_format
a_fmt = numfmt.extract_number_format(s_fmt)
if a_fmt:
return numfmt.format_number(f, a_fmt, ',', '.')
else:
return "%g" % f
def format_number_with_xlrd(f, cell, wb):
xf = wb.xf_list[cell.xf_index]
fmt_key = xf.format_key
fmt = wb.format_map[fmt_key]
s_fmt = fmt.format_str
a_fmt = numfmt.extract_number_format(s_fmt)
if a_fmt:
s_f = numfmt.format_number(f, a_fmt, ',', '.')
else:
s_f = "%g" % f
return s_f
def format_number(f, cell, wb):
xf = wb.xf_list[cell.xf_index]
fmt_key = xf.format_key
fmt = wb.format_map[fmt_key]
s_fmt = fmt.format_str
a_fmt = numfmt.extract_number_format(s_fmt)
if a_fmt:
s_f = numfmt.format_number(f, a_fmt, div1000, div1)
else:
s_f = str(f)
return s_f
def test_parse(self):
self.assertEqual(None, numfmt.extract_number_format('No number inside'))
self.assertEqual(None, numfmt.extract_number_format('x#,###0.00x'))
self.assertEqual(None, numfmt.extract_number_format('#,#0.00'))
self.assertEqual(None, numfmt.extract_number_format('#.##0,00'))
self.assertEqual(None, numfmt.extract_number_format('x#.##0.00x'))
self.assertEqual(None, numfmt.extract_number_format('#,##0,00'))
self.assertEqual(None, numfmt.extract_number_format('#,####'))
self.assertEqual(('#', '###', None), numfmt.extract_number_format('#,###'))
# Currency
self.assertEqual(('#', '##0', '00'), numfmt.extract_number_format('#,##0.00\ [$€-407];[RED]\-#,##0.00\ [$€-407]'))
# Some number
self.assertEqual((None, '#', '#'), numfmt.extract_number_format('GENERAL'))
# Integer
self.assertEqual((None, '0', None), numfmt.extract_number_format('0'))
# Two digits after comma
self.assertEqual((None, '0', '00'), numfmt.extract_number_format('0.00'))
# From "Number format codes"
# http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx
self.assertEqual((None, '####', '#'), numfmt.extract_number_format('####.#'))
self.assertEqual((None, '#', '000'), numfmt.extract_number_format('#.000'))
self.assertEqual((None, '0', '#'), numfmt.extract_number_format('0.#'))
self.assertEqual((None, '#', '0#'), numfmt.extract_number_format('#.0#'))
# not supported: '???.???'
# not supported: '# ???/???'
self.assertEqual(('#', '###', None), numfmt.extract_number_format('#,###'))
def one_test(f, s_fmt, s_expected): a = numfmt.extract_number_format(s_fmt) s_f = numfmt.format_number(f, a, div1000=',', div1='.') self.assertEqual(s_f, s_expected)