CODEWITHSUNDEEP
pythonxlsxwriter
Menachem
Menachem

Reputation: 297

xlsxWriter issue regarding exporting dates as dates

I have a list that includes dates in the following format: 11/22/2019. When I export this list to Excel, The Excel sheet recognizes these dates as General and therefor I cannot sort on these dates. Is there a way to make these dates recognizable by Excel as dates without using VBA?

import xlsxwriter
workbook = xlsxwriter.Workbook('C:/test.xlsx')
worksheet = workbook.add_worksheet("Table 1")

dates_array = ["02/12/1999", "04/12/2009", "11/02/2019"]
# Export dates_array to Excel file
row = 0
column = 0
for item in dates_array:
    worksheet.write(row, column, item)
    row += 1
workbook.close()

Upvotes: 1

Views: 1246

Answers (2)

jmcnamara
jmcnamara

Reputation: 41554

Strictly speaking those aren’t dates, they are just strings that look like dates. You need to convert them into dates, which in Python is generally a datetime object and in Excel is a real number + a format.

XlsxWriter can convert Python datetime objects into Excel dates but it doesn’t automagically convert date-like strings to Excel dates. This is explained in more detail in the Working with Dates and Times section of the XlsxWriter docs.

Here is an example of how you could convert them:

from datetime import datetime
import xlsxwriter

workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet("Table 1")

dates_array = ["02/12/1999", "04/12/2009", "11/02/2019"]
date_format = workbook.add_format({'num_format': 'mm/dd/yyyy'})

# Set the column width for clarity.
worksheet.set_column(0, 0, 20)

row = 0
column = 0
for item in dates_array:

    date_time = datetime.strptime(item, '%m/%d/%Y')

    worksheet.write(row, column, date_time, date_format)
    row += 1

workbook.close()

Cell formatting, like the above, overrides column formatting so if you want to set a column format then you can omit the cell format, like this:

worksheet.set_column(0, 0, 20, date_format)

row = 0
column = 0
for item in dates_array:

    date_time = datetime.strptime(item, '%m/%d/%Y')

    worksheet.write(row, column, date_time)
    row += 1

workbook.close()

Finally, if you want to also add vertical alignment to the column format you can add that to the date format, like this:

date_format = workbook.add_format({'num_format': 'mm/dd/yyyy', 
                                   'align': 'vcenter'})

Upvotes: 2

roadrunner66
roadrunner66

Reputation: 7941

The code below works for me, but I can't explain why Excel does recognize certain date formats when entered manually, but leaves those formats as text when entered with xlsxwriter. 

import xlsxwriter
workbook = xlsxwriter.Workbook('C:/test.xlsx')
worksheet = workbook.add_worksheet("Table 1")

dates_array = [" 02/30/1999", "04/12/2009", "11/02/2019"]
# Export dates_array to Excel file
row = 0
column = 0
for item in dates_array:
    worksheet.write(row, column, item)
    format = workbook.add_format({'num_format': 'm/d/yyyy'})
    worksheet.write(row,column, item, format)      
    row += 1
workbook.close()

Upvotes: 1

Related Questions