Commits

Sven Hendriks committed 523409f

starting branch for date and time ISO 8601 representations

Comments (0)

Files changed (4)

tests/unit/test_cell.py

+import datetime
+
 from xlsxcessive.worksheet import Cell
-
+from xlsxcessive.workbook import Workbook # used for testing date conversions based to 1904
 
 # the ADDRESS function in Excel is useful for getting correct values to work from
 # for these tests.
         actual = c.value
         expected = "43\xc2\xb0"
         assert actual == expected
+
+    # the following tests expect the date base to be 1/1/1900
+    # (which is the default; 1/1/1904 has to be set in the
+    # workbook)
+    def test_date_conversion_1900_1_1(self):
+        c = Cell('A1', value=datetime.date(1900, 1, 1))
+        assert c.value == 1
+
+    def test_date_conversion_1900_2_28(self):
+        c = Cell('A1', value=datetime.date(1900, 2, 28))
+        assert c.value == 59
+
+    def test_date_conversion_1900_3_1(self):
+        c = Cell('A1', value=datetime.date(1900, 3, 1))
+        assert c.value == 61
+
+    def test_date_conversion_1910_2_3(self):
+        c = Cell('A1', value=datetime.date(1910, 2, 3))
+        assert c.value == 3687
+
+    def test_date_conversion_2006_2_1(self):
+        c = Cell('A1', value=datetime.date(2006, 2, 1))
+        assert c.value == 38749
+
+    def test_date_conversion_9999_12_31(self):
+        c = Cell('A1', value=datetime.date(9999, 12, 31))
+        assert c.value == 2958465
+
+    # the following tests expect the date base to be 1/1/1904
+    # the datebase is set in the workbook, thus we need
+    # to instantaite a Workbook object first
+    def test_date_conversion_1904_1_1_date1904(self):
+        wb = Workbook()
+        wb.date1904 = True
+        sheet = wb.new_sheet('Sheet 1')
+        c = sheet.cell('A1', value=datetime.date(1904, 1, 1))
+        assert c.value == 0
+
+    def test_date_conversion_1910_2_3_date1904(self):
+        wb = Workbook()
+        wb.date1904 = True
+        sheet = wb.new_sheet('Sheet 1')
+        c = sheet.cell('A1', value=datetime.date(1910, 2, 3))
+        assert c.value == 2225
+
+    def test_date_conversion_2006_2_1_date1904(self):
+        wb = Workbook()
+        wb.date1904 = True
+        sheet = wb.new_sheet('Sheet 1')
+        c = sheet.cell('A1', value=datetime.date(2006, 2, 1))
+        assert c.value == 37287
+
+    def test_date_conversion_9999_12_31_date1904(self):
+        wb = Workbook()
+        wb.date1904 = True
+        sheet = wb.new_sheet('Sheet 1')
+        c = sheet.cell('A1', value=datetime.date(9999, 12, 31))
+        assert c.value == 2957003
+
+    # time conversion tests
+    def test_time_conversion_00_00_00(self):
+        c = Cell('A1', datetime.time(0, 0, 0))
+        assert c.value == 0.0
+
+    def test_time_conversion_00_00_01(self):
+        c = Cell('A1', datetime.time(0, 0, 1))
+        assert c.value == 0.0000115, c.value
+
+    def test_time_conversion_10_05_54(self):
+        c = Cell('A1', datetime.time(10, 5, 54))
+        assert c.value == 0.4207639, c.value
+
+    def test_time_conversion_12_00_00(self):
+        c = Cell('A1', datetime.time(12, 0, 0))
+        assert c.value == 0.5
+
+    def test_time_conversion_23_59_59(self):
+        c = Cell('A1', datetime.time(23, 59, 59))
+        assert c.value == 0.9999884, c.value
+

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>

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 % {'date1904':'true' if self.date1904 else 'false', 
-                                  'sheets':sheet_references}
+        return markup.workbook % {'sheets':sheet_references}
 

xlsxcessive/worksheet.py

 
         Passes *args and **params to the Cell class constructor.
         """
-        params['worksheet'] = self
         cell = Cell(*args, **params)
         rowidx = int(cell.coords[0])
         row = self.row(rowidx + 1)
         self._is_date = False
         self._is_datetime = False
         self._is_time = False
+        self.worksheet = worksheet
         if value is not 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,
-                         datetime.date, datetime.time, datetime.datetime)):
+        if isinstance(value, (int, float, long, decimal.Decimal)):
             self.cell_type = "n"
-            if isinstance(value, datetime.datetime):
-                self._is_datetime = True
-            elif isinstance(value, datetime.date):
-                self._is_date = True
-            elif isinstance(value, datetime.time):
-                self._is_time = True
         elif isinstance(value, basestring):
             self.cell_type = "inlineStr"
             value = escape(value)
             self.cell_type = 'str'
             if value.shared:
                 value = value.share(self)
+        elif isinstance(value, (datetime.date, datetime.time, datetime.datetime)):
+            self.cell_type = 'inlineStr'
+            value = value.isoformat()
         else:
             raise ValueError("Unsupported cell value: %r" % value)
         self._value = value
     
     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
-
-    # Implementation of TIMEVALUE
-    #
-    # see OOXML spec part 4: 3.17.4.2 Time Representation
-    #
-    # TIMEVALUE("00:00:00") results in the serial value 0.0000000...
-    # TIMEVALUE("00:00:01") results in the serial value 0.0000115...
-    # TIMEVALUE("10:05:54") results in the serial value 0.4207639...
-    # TIMEVALUE("12:00:00") results in the serial value 0.5000000...
-    # TIMEVALUE("23:59:59") results in the serial value 0.9999884...
-    #
-    def _serialize_time(self, timeobj):
-        # calculate number of seconds since 00:00:00
-        seconds = (timeobj.second + timeobj.minute*60 + timeobj.hour*60*60)
-        return float(seconds) / 86400.0
-
-    # combination of DATEVALUE and TIMEVALUE
-    def _serialize_datetime(self, datetimeobj, base=1900):
-        date_float = float(self._serialize_date(datetimeobj.date(), base))
-        time_float = self._serialize_time(datetimeobj.time())
-        return date_float + time_float
-
     def _format_value(self):
         if self.cell_type == 'inlineStr':
             return "<is><t>%s</t></is>" % self.value
         elif self.cell_type == 'n':
-            if self._is_date or self._is_datetime:
-                if self.worksheet and self.worksheet.workbook.date1904:
-                    base = 1904
-                else: 
-                    base = 1900
-                if self._is_date:
-                    return "<v>%s</v>" % self._serialize_date(self.value, base)
-                else:
-                    return "<v>%s</v>" % self._serialize_datetime(self.value, base)
-            elif self._is_time:
-                return "<v>%s</v>" % self._serialize_time(self.value)
-            else:
-                return "<v>%s</v>" % self.value
+            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, datetime or time
-        # then try to apply a default format to the cell
-        elif self.worksheet:
-            if self._is_date:
-                idx = self.worksheet.workbook.stylesheet.default_date_format.index
-                attrs.append('s="%d"' % idx)
-            elif self._is_datetime:
-                idx = self.worksheet.workbook.stylesheet.default_datetime_format.index
-                attrs.append('s="%d"' % idx)
-            elif self._is_time:
-                idx = self.worksheet.workbook.stylesheet.default_time_format.index
-                attrs.append('s="%d"' % idx)
         return '<c %s>%s</c>' % (" ".join(attrs), self._format_value())
 
     @property