how to set a column to DATE format in xlsxwriter

Question

I am working on a project where I am writing out onto an xlsx spreadsheet and need to format the one column for 'Date'. I get the program to run and all but the column format is still set to 'General'.

Try this in a different way with different code to see if anyone answers.:

for row in cur.execute('''SELECT `Mapline`,`Plant`,`Date`,`Action` from AEReport'''):
    lengthOfHeadings = len(row)
    output = '%s-%s.xlsx' % ("AEReport",now.strftime("%m%d%Y-%H%M"))
    workbook = xlsxwriter.Workbook(output, {'strings_to_numbers':True})
    worksheet = workbook.add_worksheet()

    format=workbook.add_format({'font_size':'8','border':True})
    format2=workbook.add_format({'font_size':'8','border':True,'num_format':'mm/dd/yy hh:mm'})
    count = 0
    for name in range(0,lengthOfHeadings):
        if name==row[2]:
            name=int(name)
            worksheet.write(counter, count, row[name],format2)
    else:
        worksheet.write(counter, count, row[name],format)
    count += 1
counter += 1

Slihthinden

Solution 1

To get the date time format working, you would have to get the date value converted to a excel serial date value.

Here is an example showing how does it work:

import pandas as pd
data = pd.DataFrame({'test_date':pd.date_range('1/1/2011', periods=12, freq='M') })
writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')

data.test_date = data.test_date - pd.datetime(1899, 12, 31)

pd.core.format.header_style = None    
data.to_excel(writer, sheet_name='test', index=False)

workbook  = writer.book
worksheet = writer.sheets['test']

formatdict = {'num_format':'mm/dd/yyyy'}
fmt = workbook.add_format(formatdict)

worksheet.set_column('A:A', None, fmt)

writer.save()

This is how the output will look like:

Solution 2

from datetime import datetime

date_format = workbook.add_format({'num_format':'yyyy-mm-dd hh:mm:ss'})

worksheet.write(0, 0, datetime.today(),date_format)

result: image from Excel Generated

Comments

[Slihthinden]

I would like to use this, but I keep getting an error when I try to run pandas atm. ImportError: Missing required dependencies ['numpy'] I have done all the installing of pandas and the dependencies including numpy, so not sure what all is wrong. Thanks for the suggestion tho.

[Slihthinden]

ImportError: Missing required dependencies ['numpy'] I have done all the installing of pandas and the dependencies including numpy, so not sure what all is wrong. Thanks for the suggestion tho.

[Abbas]

If that is the error you are getting, say import numpy as np it shall rectify the error, even better show the piece of code that is failing, you may get a solution.

[Slihthinden]

When I put in the import numpy statement I get another error - 'ImportError: DLL load failed: %1 is not a valid Win32 application'

[Slihthinden]

Apparently the error above was due to my having updated my Python version yesterday and while I run on a 64-bit machine, I had been using a 32-bit Python and updated to the 64-bit version so it invalidated all of my modules that were 32-bit. Trying this again

[Diego]

Converting the dates to integers is a great workaround since pandas overwrites all formatting when working with dates. I just had to make a small change to the column: data['test_date'] = data['test_date'].dt.days. Thanks!