Repository: jazzband/tablib (python)

Issue:
Question / feature request: Excel - format as date 
I can feed a tablib cell a datetime, and formatting that cell as date in Excel works - the number shown initially gets converted to a valid date.

Can i tell tablib to format the cell as datetime right away, so the Excel file shows date formatting right after being opened?

(Looks like XlsxWriter and xlwt can do that, but i'm reluctant to switch.)


Pull request description:
feat: xls: add styling for date/time types
fixes #210
Following suggestion by https://github.com/jazzband/tablib/issues/210#issuecomment-487998186

Patch:
diff --git a/src/tablib/formats/_xls.py b/src/tablib/formats/_xls.py
index 2c6c527..947625d 100644
--- a/src/tablib/formats/_xls.py
+++ b/src/tablib/formats/_xls.py
@@ -1,6 +1,6 @@
 """ Tablib - XLS Support.
 """
-
+import datetime
 from io import BytesIO
 
 import xlrd
@@ -12,6 +12,9 @@ import tablib
 # special styles
 wrap = xlwt.easyxf("alignment: wrap on")
 bold = xlwt.easyxf("font: bold on")
+datetime_style = xlwt.easyxf(num_format_str='M/D/YY h:mm')
+date_style = xlwt.easyxf(num_format_str='M/D/YY')
+time_style = xlwt.easyxf(num_format_str='h:mm:ss')
 
 
 class XLSFormat:
@@ -138,6 +141,13 @@ class XLSFormat:
                 elif len(row) < dataset.width:
                     ws.write(i, j, col, bold)
 
+                # format date types
+                elif isinstance(col, datetime.datetime):
+                    ws.write(i, j, col, datetime_style)
+                elif isinstance(col, datetime.date):
+                    ws.write(i, j, col, date_style)
+                elif isinstance(col, datetime.time):
+                    ws.write(i, j, col, time_style)
                 # wrap the rest
                 else:
                     try:


Test patch:
diff --git a/tests/test_tablib.py b/tests/test_tablib.py
index fbb26f9..d557d25 100755
--- a/tests/test_tablib.py
+++ b/tests/test_tablib.py
@@ -12,6 +12,7 @@ from io import BytesIO, StringIO
 from pathlib import Path
 from uuid import uuid4
 
+import xlrd
 from openpyxl.reader.excel import load_workbook
 
 import tablib
@@ -1254,6 +1255,23 @@ class XLSTests(BaseTestCase):
         book = tablib.Databook().load(in_stream, 'xls')
         self.assertEqual(book.sheets()[0].title, 'Founders')
 
+    def test_xls_export_with_dates(self):
+        date = dt.date(2019, 10, 4)
+        time = dt.time(14, 30)
+        date_time = dt.datetime(2019, 10, 4, 12, 30, 8)
+        data.append((date, time, date_time))
+        data.headers = ('date', 'time', 'date/time')
+        _xls = data.xls
+        xls_book = xlrd.open_workbook(file_contents=_xls, formatting_info=True)
+        row = xls_book.sheet_by_index(0).row(1)
+
+        def get_format_str(cell):
+            return xls_book.format_map[xls_book.xf_list[cell.xf_index].format_key].format_str
+
+        self.assertEqual('m/d/yy', get_format_str(row[0]))
+        self.assertEqual('h:mm:ss', get_format_str(row[1]))
+        self.assertEqual('m/d/yy h:mm', get_format_str(row[2]))
+
 
 class XLSXTests(BaseTestCase):
     def test_xlsx_format_detect(self):