8000 ENH: ExcelWriter() accepts date and datetime formats · pandas-dev/pandas@19ae1dc · GitHub
[go: up one dir, main page]
Skip to content

Commit 19ae1dc

Browse files
Jaydyoujreback
Jaydyou
authored and
jreback
committed
ENH: ExcelWriter() accepts date and datetime formats
with ExcelWriter(date_format='YYYY-MM-DD', datetime_format='YYYY-MM-DD HH:MM:SS') you can set the formatstrings for Excel export BUG: xlsxwriter was not able to write date and datetime objects
1 parent 14bfddd commit 19ae1dc

File tree

4 files changed

+81
-18
lines changed
8000

4 files changed

+81
-18
lines changed

‎doc/source/release.rst

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,9 @@ pandas 0.13.1
5353
New features
5454
~~~~~~~~~~~~
5555

56+
- Added ``date_format`` and ``datetime_format`` attribute to ExcelWriter.
57+
(:issue:`4133`)
58+
5659
API Changes
5760
~~~~~~~~~~~
5861

‎doc/source/v0.13.1.txt

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -103,6 +103,9 @@ Enhancements
103103
improves parsing perf in many cases. Thanks to @lexual for suggesting and @danbirken
104104
for rapidly implementing. (:issue:`5490`, :issue:`6021`)
105105

106+
- ``date_format`` and ``datetime_format`` keywords can now be specified when writing to ``excel``
107+
files (:issue:`4133`)
108+
106109
- ``MultiIndex.from_product`` convenience function for creating a MultiIndex from
107110
the cartesian product of a set of iterables (:issue:`6055`):
108111

‎pandas/io/excel.py

Lines changed: 36 additions & 17 deletions
F438
Original file line numberDiff line numberDiff line change
@@ -355,6 +355,11 @@ class ExcelWriter(object):
355355
Engine to use for writing. If None, defaults to
356356
``io.excel.<extension>.writer``. NOTE: can only be passed as a keyword
357357
argument.
358+
date_format : string, default None
359+
Format string for dates written into Excel files (e.g. 'YYYY-MM-DD')
360+
datetime_format : string, default None
361+
Format string for datetime objects written into Excel files
362+
(e.g. 'YYYY-MM-DD HH:MM:SS')
358363
"""
359364
# Defining an ExcelWriter implementation (see abstract methods for more...)
360365

@@ -429,15 +434,25 @@ def save(self):
429434
"""
430435
pass
431436

432-
def __init__(self, path, engine=None, **engine_kwargs):
433-
# validate that this engine can handle the extnesion
437+
def __init__(self, path, engine=None,
438+
date_format=None, datetime_format=None, **engine_kwargs):
439+
# validate that this engine can handle the extension
434440
ext = os.path.splitext(path)[-1]
435441
self.check_extension(ext)
436442

437443
self.path = path
438444
self.sheets = {}
439445
self.cur_sheet = None
440446

447+
if date_format is None:
448< 8000 /td>+
self.date_format = 'YYYY-MM-DD'
449+
else:
450+
self.date_format = date_format
451+
if datetime_format is None:
452+
self.datetime_format = 'YYYY-MM-DD HH:MM:SS'
453+
else:
454+
self.datetime_format = datetime_format
455+
441456
def _get_sheet_name(self, sheet_name):
442457
if sheet_name is None:
443458
sheet_name = self.cur_sheet
@@ -518,9 +533,9 @@ def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
518533
style.__getattribute__(field))
519534

520535
if isinstance(cell.val, datetime.datetime):
521-
xcell.style.number_format.format_code = "YYYY-MM-DD HH:MM:SS"
536+
xcell.style.number_format.format_code = self.datetime_format
522537
elif isinstance(cell.val, datetime.date):
523-
xcell.style.number_format.format_code = "YYYY-MM-DD"
538+
xcell.style.number_format.format_code = self.date_format
524539

525540
if cell.mergestart is not None and cell.mergeend is not None:
526541
cletterstart = get_column_letter(startcol + cell.col + 1)
@@ -585,8 +600,8 @@ def __init__(self, path, engine=None, **engine_kwargs):
585600
super(_XlwtWriter, self).__init__(path, **engine_kwargs)
586601

587602
self.book = xlwt.Workbook()
588-
self.fm_datetime = xlwt.easyxf(num_format_str='YYYY-MM-DD HH:MM:SS')
589-
self.fm_date = xlwt.easyxf(num_format_str='YYYY-MM-DD')
603+
self.fm_datetime = xlwt.easyxf(num_format_str=self.datetime_format)
604+
self.fm_date = xlwt.easyxf(num_format_str=self.date_format)
590605

591606
def save(self):
592607
"""
@@ -612,9 +627,9 @@ def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
612627

613628
num_format_str = None
614629
if isinstance(cell.val, datetime.datetime):
615-
num_format_str = "YYYY-MM-DD HH:MM:SS"
630+
num_format_str = self.datetime_format
616631
if isinstance(cell.val, datetime.date):
617-
num_format_str = "YYYY-MM-DD"
632+
num_format_str = self.date_format
618633

619634
stylekey = json.dumps(cell.style)
620635
if num_format_str:
@@ -699,11 +714,14 @@ class _XlsxWriter(ExcelWriter):
699714
engine = 'xlsxwriter'
700715
supported_extensions = ('.xlsx',)
701716

702-
def __init__(self, path, engine=None, **engine_kwargs):
717+
def __init__(self, path, engine=None,
718+
date_format=None, datetime_format=None, **engine_kwargs):
703719
# Use the xlsxwriter module as the Excel writer.
704720
import xlsxwriter
705721

706-
super(_XlsxWriter, self).__init__(path, engine=engine, **engine_kwargs)
722+
super(_XlsxWriter, self).__init__(path, engine=engine,
723+
date_format=date_format, datetime_format=datetime_format,
724+
**engine_kwargs)
707725

708726
self.book = xlsxwriter.Workbook(path, **engine_kwargs)
709727

@@ -729,9 +747,9 @@ def write_cells(self, cells, sheet_name=None, startrow=0, startcol=0):
729747
for cell in cells:
730748
num_format_str = None
731749
if isinstance(cell.val, datetime.datetime):
732-
num_format_str = "YYYY-MM-DD HH:MM:SS"
750+
num_format_str = self.datetime_format
733751
if isinstance(cell.val, datetime.date):
734-
num_format_str = "YYYY-MM-DD"
752+
num_format_str = self.date_format
735753

736754
stylekey = json.dumps(cell.style)
737755
if num_format_str:
@@ -762,12 +780,16 @@ def _convert_to_style(self, style_dict, num_format_str=None):
762780
style_dict: style dictionary to convert
763781
num_format_str: optional number format string
764782
"""
765-
if style_dict is None:
766-
return None
767783

768784
# Create a XlsxWriter format object.
769785
xl_format = self.book.add_format()
786+
787+
if num_format_str is not None:
788+
xl_format.set_num_format(num_format_str)
770789

790+
if style_dict is None:
791+
return xl_format
792+
771793
# Map the cell font to XlsxWriter font properties.
772794
if style_dict.get('font'):
773795
font = style_dict['font']
@@ -788,9 +810,6 @@ def _convert_to_style(self, style_dict, num_format_str=None):
788810
if style_dict.get('borders'):
789811
xl_format.set_border()
790812

791-
if num_format_str is not None:
792-
xl_format.set_num_format(num_format_str)
793-
794813
return xl_format
795814

796815
register_writer(_XlsxWriter)

‎pandas/io/tests/test_excel.py

Lines changed: 39 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,7 @@
11
# pylint: disable=E1101
22

33
from pandas.compat import u, range, map
4-
from datetime import datetime
4+
from datetime import datetime, date
55
import os
66

77
import nose
@@ -661,6 +661,44 @@ def test_excel_roundtrip_datetime(self):
661661
recons = reader.parse('test1')
662662
tm.assert_frame_equal(self.tsframe, recons)
663663

664+
# GH4133 - excel output format strings
665+
def test_excel_date_datetime_format(self):
666+
df = DataFrame([[date(2014, 1, 31),
667+
date(1999, 9, 24)],
668+
[datetime(1998, 5, 26, 23, 33, 4),
669+
datetime(2014, 2, 28, 13, 5, 13)]],
670+
index=['DATE', 'DATETIME'], columns=['X', 'Y'])
671+
df_expected = DataFrame([[datetime(2014, 1, 31),
672+
datetime(1999, 9, 24)],
673+
[datetime(1998, 5, 26, 23, 33, 4),
674+
datetime(2014, 2, 28, 13, 5, 13)]],
675+
index=['DATE', 'DATETIME'], columns=['X', 'Y'])
676+
677+
with ensure_clean(self.ext) as filename1:
678+
with ensure_clean(self.ext) as filename2:
679+
writer1 = ExcelWriter(filename1)
680+
writer2 = ExcelWriter(filename2,
681+
date_format='DD.MM.YYYY',
682+
datetime_format='DD.MM.YYYY HH-MM-SS')
683+
684+
df.to_excel(writer1, 'test1')
685+
df.to_excel(writer2, 'test1')
686+
687+
writer1.close()
688+
writer2.close()
689+
690+
reader1 = ExcelFile(filename1)
691+
reader2 = ExcelFile(filename2)
692+
693+
rs1 = reader1.parse('test1', index_col=None)
694+
rs2 = reader2.parse('test1', index_col=None)
695+
696+
tm.assert_frame_equal(rs1, rs2)
697+
698+
# since the reader returns a datetime object for dates, we need
699+
# to use df_expected to check the result
700+
tm.assert_frame_equal(rs2, df_expected)
701+
664702
def test_to_excel_periodindex(self):
665703
_skip_if_no_xlrd()
666704

0 commit comments

Comments
 (0)
0