CODEWITHSUNDEEP
pythondatetimexlsxwriter
user12432841
user12432841

Reputation: 3

How to force Python XlsxWriter to write to cell in custom format

I am writing a script to generate an xlsx file to be uploaded into our system. One of the columns contains the current date which needs to be formatted in a very specific way (custom: m/d/yyyy h:mm' to be specific). I have a command in the script that successfully formats the cell correctly, which I know because if I check the cell formatting in the generated xlsx it shows the correct format. However, xlsxwriter doesn't actually write to that format since its rejected by the upload. If I open the spreadsheet and 'touch' the value (hit enter on the cell) then it correctly formats to the value I expect.

Is there any way to force xlsxwriter to write the value in the format expected so that I don't need to go into this sheet and touch the value? Is there anyway to automate this 'touch' that I perform? I doubt it given that xlsxwriter does not manipulate spreadsheets, just generates them.

The image provided shows two date values. The first is the 'touched' value that I expect, the second is the value written by the script. Note that the values are actually the same, one has just been formatted by excel.

Example of 'touched' value versus written value:

Example of 'touched' value versus written value

I have tried formatting the datetime string in separate manners to try to fix this, but nothing has yielded results. I have not been able to find any other documention relating to a similar issue.

import xlsxwriter
import datetime

x = datetime.datetime.now()
Date = str(x.strftime("%m")) + "/" + str(x.strftime("%d")) + "/" + str(x.strftime("%Y")) + " " + str(x.strftime("%I")) + ":" + str(x.strftime("%M")) + ":00" + " " + str(x.strftime("%p"))
workbook = xlsxwriter.Workbook("C:/" + File_Name)
worksheet = workbook.add_worksheet('Fills')
Dealer_out = ['External', Dealer_Name, Dealer_Exchange, Symbol, 'Manual_JV', '', '', '', Date, Account_Number, Account_Number, Opposite_Side[Dealer_Side.upper()], Dealer_Amount, Dealer_Price, 'TRUE']
Header = ['Source', 'Context', 'Exchange', 'Internal Symbol', 'Origin', 'Order Id', 'Exchange Order Id', 'Fill Id', 'Fill Time', 'Intern Account Number', 'External Account Number', 'Side', 'Quantity', 'Price', 'IsActive']
row = 0
col = 0
#write header to spreadsheet
cell_bold = workbook.add_format({'bold': True})
cell_format1 = workbook.add_format({'num_format': 'm/d/yyyy h:mm'})
cell_format1.set_num_format('m/d/yyyy h:mm')
#write dealer information to spreadsheet
for cell in Dealer_out:
if col == 8:
    worksheet.write(row, col, cell, cell_format1)
else:
    worksheet.write(row, col, cell)
col += 1
db.close()
workbook.close()

The values that are written out in 'Dealer_out' are read and parsed from a csv, that part of the script has not been included. Thank you ahead of time for the assistance.

Upvotes: 0

Views: 1165

Answers (1)

jmcnamara
jmcnamara

Reputation: 41644

It looks like you are writing strings (that look like dates) with a date format.

However, dates in Excel are actually numbers with a date format. This is explained in the Working with Dates and Time section of the XlsxWriter docs.

You will need to extend you program to convert the date strings that you read from the CSV file to Datetime objects. If you then write the datetime objects (with write() and a format) you will get the dates in the format you require.

Upvotes: 0

Related Questions