Working with sheet dates

Asked

Viewed 223 times

2

Guys, I’m looking for data in a spreadsheet using python, and in one of the worksheets it contains only dates. The problem is that when I look for these dates they come in the form of tuples that are immutable. I would like some suggestions on how to treat these dates, as they will need to be compared with other dates.

def fetch_datas(wb):
    temp = []
    dados = list()
    for sheet in wb.sheetnames:
        for row in enumerate(wb[sheet].values):
            if row[0] == 0:
                continue
            temp.append(row)

follows the file link: https://drive.google.com/file/d/13YCxd4w_EaNAQp4DzUWPLfvn7OhYfy0-/view?usp=sharing

python openpyxl
  • 1

    The ideal for questions that contain access to files is to provide either the file so we can work on it and help you or just a part of the file/ example, if you just give an example of tuples generated when reading the Worksheet would help enough!

  • I made available the file in Google Drive and the part of the code that returns me to Tupla with the data.

1 answer

1

Take a look at that answer: https://stackoverflow.com/questions/13962837/reading-date-as-a-string-not-float-from-excel-using-python-xlrd

In it, Voce finds this line of code that can help you:

a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(cell_value, book.datemode))

I made a basic code that reads the dates:

import xlrd, datetime


def open_file(path):

    book = xlrd.open_workbook(path)
    first_sheet = book.sheet_by_index(0)

    num_rows = first_sheet.nrows - 1
    num_cells = first_sheet.ncols - 1
    curr_row = -1

    while curr_row < num_rows:
        curr_row += 1
        row = first_sheet.row(curr_row)
        print ('Row:', curr_row)
        curr_cell = -1
        while curr_cell < num_cells:
            curr_cell += 1
            cell_type = first_sheet.cell_type(curr_row, curr_cell)
            cell_value = first_sheet.cell_value(curr_row, curr_cell)
            #print (' ', cell_type, ':', cell_value)
            if cell_type == 3:
                a1_as_datetime = datetime.datetime(*xlrd.xldate_as_tuple(cell_value, book.datemode))
                print ('datetime: %s' % a1_as_datetime)


if __name__ == "__main__":
    path = "excel.xlsx"
    open_file(path)

In 'first_sheet.cell_type(curr_row, curr_cell)', Voce can see the data type of each excel cell. For dates, the type is "3". So when the code finds a cell with type 3, it applies the line I quoted above to give the cast to datetime.

For this to work this way, Voce must change the type of excel cells to date.

You can see the complete list of types in: https://xlrd.readthedocs.io/en/latest/api.html#xlrd.sheet.Cell

Browser other questions tagged python openpyxl

You are not signed in. Login or sign up in order to post.