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

Ambiguous

Answer 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:

Cyder

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. – Ambiguous

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. – Ambiguous

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. – Cyder

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

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 – Ambiguous

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! – Turki

Put it as a parameter in writer object may help, e.g. writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter', datetime_format='mm-dd-yy') #67995885 – Loudmouthed

Answer 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

Englacial

Answer 3

date = workbook.add_format({'num_format': 'dd-mm-yyyy'})

worksheet.write(1, 1 , 02-12-199, date)

Myra