Clever Geek Handbook

Convert an integer (read from xlrd) back to date format (write using openpyxl)

I am using xlrd to read some dates in a file by writing them in a new book using openpyxl.

If the date is 25-Jun-14, she writes 41815

I just want to know how to get 41815 back on 25-Jun-14 again or 25/06/14, however all the stuff I read today seems extremely complicated.

+3


source to share


2 answers


With openpyxl, you can modify the file in place.

However, the conversion is pretty straightforward: Excel treats numbers (from 1900-01-01 as epochs) that are formatted as dates, so you just need to set the cell format.



originReport = xlrd.open_workbook('report1').sheet_by_index(0)
destReport = openpyxl.load_workbook('report2')[0]
c = destReport.cell(row=1,column=1)
c.value = originReport.cell_value(0,0)
c.number_format = "d-mmm-yy" # Excel formatting
# check the conversion
print(c.value)
2014-06-25 00:00:00
0


source


I believe the value originReport.cell_value(0,0)

is equal '25-jun-14'

.

from datetime import datetime
import re

a = '25-jun-14'
b = re.sub('-14', '-2014', a)

c = datetime.strptime(b, "%d-%b-%Y")

# write 25/06/2014
destReport.cell(row=1,column=1).value = c.strftime("%d/%m/%Y")


I think you should write 2014 instead of 14. Otherwise, how can you determine which positions are for the year.

0


source






More articles:


All Articles