Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

xlrd original value of the cell

I'm reading xls file using xlrd. The problem is, when xlrd reading value like this "12/09/2012", i get result like this "xldate:41252.0". When I use xlrd.xldate_as_tuple, i get this result:

(2016, 12, 10, 0, 0, 0)

My code:

curr_row = -1
while curr_row < num_rows:
    curr_row += 1
    row = worksheet.row(curr_row)     
        for x in xrange(num_cols):
            field_type = worksheet.cell_type(curr_row, x)
            if field_type == 3: # this is date
                field_value = worksheet.cell_value(curr_row, x)
                print worksheet.cell(curr_row, x).value
                print xlrd.xldate_as_tuple(field_value, 1)

Result:

41252.0
(2016, 12, 10, 0, 0, 0)

Both results are wrong for me. How can i get original cell value "12/09/2012" using xlrd ?

like image 660
artyomboyko Avatar asked Apr 26 '13 05:04

artyomboyko


People also ask

How do I check if an xlrd cell is empty?

To check if it is empty: simply write if cell_vel == ''

Which is better xlrd or Openpyxl?

openpyxl has a broader approval, being mentioned in *7 company stacks & 7 developers stacks; compared to xlrd, which is listed in 5 company stacks and 6 developer stacks.

What is the use of xlrd?

xlrd is a library for reading data and formatting information from Excel files in the historical . xls format. This library will no longer read anything other than . xls files.


1 Answers

According to the docstring, you should pass your workbook's datemode to xldate_as_tuple as a second parameter:

from datetime import datetime
import xlrd


book = xlrd.open_workbook("test.xls")
sheet = book.sheet_by_index(0)
a1 = sheet.cell_value(rowx=0, colx=0)

print a1  # prints 41252.0
print xlrd.xldate_as_tuple(a1, 1)  # prints (2016, 12, 10, 0, 0, 0)

a1_tuple = xlrd.xldate_as_tuple(a1, book.datemode)  
print a1_tuple  # prints (2012, 12, 9, 0, 0, 0)

a1_datetime = datetime(*a1_tuple)
print a1_datetime.strftime("%m/%d/%Y")  # prints 12/09/2012
like image 123
alecxe Avatar answered Sep 20 '22 14:09

alecxe