CODEWITHSUNDEEP
pythonexcelxlrdxlwt
jamuna
jamuna

Reputation: 113

I am having trouble in reading date and time from an excel sheet using python

import xlwt
import xlrd, xlutils

wb = xlwt.Workbook()
outsheet = wb.add_sheet('Page 1')

files = [r'c:\file1', r'C:c:\file2',]

outrow = 0


for f in files:

insheet = xlrd.open_workbook(f,formatting_info=True).sheets()[0]
for row in range(insheet.nrows):
    for col in range(insheet.ncols):
        outsheet.write(outrow, col,insheet.cell_value(row, col))
    outrow += 1
wb.save(r'C:\combined.xls')

i get output as
42753.61492
42753.61492
42753.61492
42753.61492
42753.61492

where i was looking something as this
2017-01-18 14:45:29 a
2017-01-18 14:45:29 a
2017-01-18 14:45:29 a
2017-01-18 14:45:29 a
2017-01-20 09:10:06 a

Upvotes: 1

Views: 195

Answers (3)

John Y
John Y

Reputation: 14559

You just need to set the number format when you're writing the output workbook, the same as you would in Excel. This is done by including another parameter on your outsheet.write() call.

Unfortunately the "official" documentation for xlwt is pretty difficult to use. Much better is the old tutorial PDF. There you should read up on styles (XFStyle and easyxf).

Or even better still is to ditch xlwt altogether and switch to XlsxWriter, which is easier to use, has more features, has vastly better documentation, is actively maintained, and generates current .xlsx files instead of outdated .xls files.

Upvotes: 2

mtkilic
mtkilic

Reputation: 1243

Can you give try to this:

from datetime import datetime
import xlwt
import xlrd, xlutils

wb = xlwt.Workbook()
outsheet = wb.add_sheet('Page 1')

files = [r'c:\file1', r'C:c:\file2',]

outrow = 0


for f in files:

insheet = xlrd.open_workbook(f,formatting_info=True).sheets()[0]
for row in range(insheet.nrows):
    for col in range(insheet.ncols):
        outsheet.write(outrow, col,datetime.fromtimestamp(float(insheet.cell_value(row, col))))
    outrow += 1
wb.save(r'C:\combined.xls')

Upvotes: 0

Mark Ransom
Mark Ransom

Reputation: 308548

>>> import datetime
>>> datetime.datetime(1899,12,30) + datetime.timedelta(days=42753.61492)
datetime.datetime(2017, 1, 18, 14, 45, 29, 88000)

Upvotes: 0

Related Questions