Commits

Christian Wyglendowski  committed c359908

Added custom number formats.

  • Participants
  • Parent commits bfdaf64

Comments (0)

Files changed (4)

 # add a border
 bigguy.border(top="medium", bottom="medium")
 
-# set a specific number format. only a few are supported - see styles.py in
-# the Format class.
+# set a builtin number format
 bigguy.number_format('0.00')
 
 # another shared format
 boldfont = wb.stylesheet.new_format()
 boldfont.font(bold=True)
 
+# and another
+highprec = wb.stylesheet.new_format()
+
+# set a custom number format on the shared format
+highprec.number_format("0.000")
+
 # the API supports adding rows
 row1 = sheet.row(1)
 
 row3.cell("C3", shared_formula, format=bigguy)
 
 # you can work with cells directly on the sheet
-sheet.cell('D1', 12)
-sheet.cell('D2', 12)
-sheet.cell('D3', shared_formula)
+sheet.cell('D1', 12.0005, format=highprec)
+sheet.cell('D2', 11.9995, format=highprec)
+sheet.cell('D3', shared_formula, format=highprec)
 
 # and directly via row and column indicies
 sheet.cell(coords=(0, 4), value=40)

File tests/unit/test_numbering.py

+from xlsxcessive.style import Stylesheet
+
+
+class TestStylesheetNumbering(object):
+    def setup(self):
+        self.styles = Stylesheet(None)
+
+    def test_can_add_custom_number_format(self):
+        self.styles.add_custom_number_format("#.0000")
+        assert "#.0000" in self.styles.custom_numbers
+
+    def test_adding_custom_styles_returns_an_int(self):
+        actual = type(self.styles.add_custom_number_format("#.0000"))
+        expected = int
+        assert actual == expected
+
+    def test_adding_custom_styles_increments_the_int(self):
+        first = self.styles.add_custom_number_format("#.0000")
+        second = self.styles.add_custom_number_format("#.)0000")
+        assert second > first
+
+    def test_adding_the_same_style_twice_does_not_increment(self):
+        first = self.styles.add_custom_number_format("#.0000")
+        second = self.styles.add_custom_number_format("#.0000")
+        assert first == second, (first, second)
+
+class TestFormatNumbering(object):
+    def setup(self):
+        self.styles = Stylesheet(None)
+        self.format = self.styles.new_format()
+
+    def test_using_a_builtin_format_does_not_add_a_custom_format(self):
+        self.format.number_format('0.00')
+        assert not self.styles.custom_numbers
+
+    def test_using_a_custom_format_does_add_a_custom_format(self):
+        self.format.number_format('0.00000')
+        assert self.styles.custom_numbers
+
+    def test_format_codes_are_escaped(self):
+        self.format.number_format('"x"0.00')
+        assert '"x"0.00' in self.styles.custom_numbers
+

File xlsxcessive/markup.py

 stylesheet = """\
 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
+%(numfmts)s
 %(fonts)s
 <fills count="1"><fill /></fills>
 %(borders)s

File xlsxcessive/style.py

+from xml.sax.saxutils import escape
 from xlsxcessive import markup
 from xlsxcessive import errors
 
 
 class Stylesheet(object):
+    CUSTOM_NUM_OFFSET = 100
+
     def __init__(self, workbook):
         self.workbook = workbook
         self.fonts = []
         self.formats = []
         self.borders = []
+        self.custom_numbers = {}
         self._init_defaults()
 
     def _init_defaults(self):
         f.index = self.formats.index(f)
         return f
             
+    def add_custom_number_format(self, formatstring):
+        """formatstring should be an XML escaped string."""
+        if formatstring in self.custom_numbers:
+            return self.custom_numbers[formatstring]
+        numid = self.CUSTOM_NUM_OFFSET + len(self.custom_numbers)
+        self.custom_numbers[formatstring] = numid
+        return numid
+
     def __str__(self):
+        numfmts = ''
         fonts = ''
         formats = ''
         borders = ''
+        if self.custom_numbers:
+            fcount = len(self.custom_numbers)
+            fxml = ""
+            for fcode, fid in self.custom_numbers.iteritems():
+                nf = '<numFmt numFmtId="%d" formatCode="%s"/>\n' % (fid, fcode)
+                fxml += nf
+            numfmts = '<numFmts count="%d">%s</numFmts>' % (fcount, fxml)
         if self.fonts:
             fxml = "\n".join(str(f) for f in self.fonts)
             fcount = len(self.fonts)
             bcount = len(self.borders)
             borders = '<borders count="%d">%s</borders>' % (bcount, bxml)
         return markup.stylesheet % {
+            'numfmts':numfmts,
             'fonts':fonts, 
             'formats':formats,
             'borders':borders,
         self._alignment = value
 
     def number_format(self, fmt):
-        if fmt not in self.COMMON_NUM_FORMATS:
-            msg = "%r is not a valid number format." % fmt
-            raise errors.XlsxFormatError(msg)
-        self._number_format = self.COMMON_NUM_FORMATS[fmt]
+        fmt = escape(fmt, {'"':"&quot;"})
+        all_formats = {}
+        all_formats.update(self.COMMON_NUM_FORMATS)
+        all_formats.update(self.stylesheet.custom_numbers)
+        if fmt not in all_formats:
+            fmtid = self.stylesheet.add_custom_number_format(fmt)
+        else:
+            fmtid = all_formats[fmt]
+        self._number_format = fmtid
 
     def __str__(self):
         attrs = []