Commits

Sven Hendriks  committed b6c809a

Added support for dates; Dates are automatically transformed to an integer representing the number of days since either 1/1/1900 or 1/1/1904; The date format can be specified explicitely, otherwise a default date format will be applied; Also cleaned up some tabs in the code that sneaked in previously

  • Participants
  • Parent commits 0075ae3

Comments (0)

Files changed (4)

File xlsxcessive/markup.py

 workbook = """\
 <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
 <workbook xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
+<workbookPr date1904="%(date1904)s"/>
   <sheets>
     %(sheets)s
   </sheets>

File xlsxcessive/style.py

         self.new_format()
         self.font()
         self.border(top='none', right='none', bottom='none', left='none')
+        # Init a default number format for dates
+        self.default_date_format = self.new_format()
+        self.default_date_format.number_format('mm-dd-yy')
 
     def border(self, **params):
         border = Border(**params)
         '#,##0.00':4,
         '0%':9,
         '0.00%':10,
+        'mm-dd-yy':14,
+        'd-mmm-yy':15,
+        'd-mmm':16,
+        'mmm-yy':17,
     }
 
     def __init__(self, stylesheet):
         self.name = params.get('name')
         self.family = params.get('family')
         self.bold = params.get('bold')
-	self.italic = params.get('italic')
-	self.underline = params.get('underline')
+        self.italic = params.get('italic')
+        self.underline = params.get('underline')
         self.index = None
-	self.color = params.get('color')
+        self.color = params.get('color')
 
     def __str__(self):
         elems = [
             '<sz val="%d"/>' % self.size if self.size else '',
             '<name val="%s"/>' % self.name if self.name else '',
             '<family val="%d"/>' % self.family if self.family else '',
-	    '<color rgb="%s"/>' % self.color if self.color else '',
+            '<color rgb="%s"/>' % self.color if self.color else '',
             '<b/>' if self.bold else '',
             '<i/>' if self.italic else '',
             '<u/>' if self.underline else '',

File xlsxcessive/workbook.py

     def __init__(self):
         self.sheets = []
         self.stylesheet = Stylesheet(self)
+        self.date1904 = False 
 
     def new_sheet(self, name):
         sid = len(self.sheets) + 1
 
     def __str__(self):
         sheet_references = "".join(s.ref for s in self.sheets)
-        return markup.workbook % {'sheets':sheet_references}
+        return markup.workbook % {'date1904':'true' if self.date1904 else 'false', 
+                                  'sheets':sheet_references}
 

File xlsxcessive/worksheet.py

 import decimal
 import operator
 import string
+import datetime
 
 from xml.sax.saxutils import escape
 from xlsxcessive import markup
 
+class UnsupportedDateBase(Exception): pass
 
 class Worksheet(object):
     """An OOXML Worksheet."""
 
         Passes *args and **params to the Cell class constructor.
         """
-        cell = Cell(*args, **params)
+        cell = Cell(*args, worksheet=self, **params)
         rowidx = int(cell.coords[0])
         row = self.row(rowidx + 1)
         row.add_cell(cell)
         return ''
 
 class Cell(object):
-    def __init__(self, reference=None, value=None, coords=None, format=None):
+    def __init__(self, reference=None, value=None, coords=None, format=None, worksheet=None):
         self._reference = reference.upper() if reference else reference
         self._coords = coords
         self.cell_type = None
             self._set_value(value)
         self.format = format
         self.merge_range = None
+        self.worksheet = worksheet
 
     @classmethod
     def from_reference(cls, ref):
         self.merge_range = "%s:%s" % (self.reference, other.reference)
 
     def _set_value(self, value):
-        if isinstance(value, (int, float, long, decimal.Decimal)):
+        if isinstance(value, (int, float, long, decimal.Decimal, datetime.date)):
             self.cell_type = "n"
         elif isinstance(value, basestring):
             self.cell_type = "inlineStr"
     
     value = property(fget=_get_value, fset=_set_value)
 
+    # Implementation of DATEVALUE to meet the requirements
+    # described in 3.17.4.1 of the OOXML spec part 4
+    #
+    # For 1900 based sytems:
+    #
+    # DATEVALUE("01-Jan-1900") results in the serial value 1.0000000...
+    # DATEVALUE("03-Feb-1910") results in the serial value 3687.0000000...
+    # DATEVALUE("01-Feb-2006") results in the serial value 38749.0000000...
+    # DATEVALUE("31-Dec-9999") results in the serial value 2958465.0000000...
+    #
+    # Furthermore:
+    # 
+    # DATEVALUE("28-Feb-1900") results in 59
+    # DATEVALUE("01-Mar-1900") results in 61
+    #
+    # For 1904 based systems:
+    #
+    # DATEVALUE("01-Jan-1904") results in the serial value 0.0000000...
+    # DATEVALUE("03-Feb-1910") results in the serial value 2225.0000000...
+    # DATEVALUE("01-Feb-2006") results in the serial value 37287.0000000...
+    # DATEVALUE("31-Dec-9999") results in the serial value 2957003.0000000...
+    #
+    def _serialize_date(self, dateobj, base=1900):
+        if base == 1900:
+            if dateobj < datetime.date(1900, 3, 1):
+                delta = datetime.date(base, 1, 1) - datetime.timedelta(days=1)
+            else:
+                delta = datetime.date(base, 1, 1) - datetime.timedelta(days=2)
+        elif base == 1904:
+            delta = datetime.date(base, 1, 1)
+        else:
+            raise UnsupportedDateBase, 'Date base must be either 1900 or 1904'
+        return (dateobj - delta).days
+
     def _format_value(self):
         if self.cell_type == 'inlineStr':
             return "<is><t>%s</t></is>" % self.value
         elif self.cell_type == 'n':
-            return "<v>%s</v>" % self.value
+            if isinstance(self.value, datetime.date):
+                if self.worksheet and self.worksheet.workbook.date1904:
+                    base = 1904
+                else: 
+                    base = 1900
+                return "<v>%s</v>" % self._serialize_date(self.value, base)
+            else:
+                return "<v>%s</v>" % self.value
         elif self.cell_type == 'str':
             return str(self.value)
 
         ]
         if self.format:
             attrs.append('s="%d"' % self.format.index)
+        # if we don't have an explicit format and the value is a date,
+        # then try to apply a default date format to the cell
+        elif isinstance(self.value, datetime.date):
+            # do we have a reference to the parent worksheet?
+            if self.worksheet:
+                idx = self.worksheet.workbook.stylesheet.default_date_format.index
+                attrs.append('s="%d"' % idx)
         return '<c %s>%s</c>' % (" ".join(attrs), self._format_value())
 
     @property