rb = xlrd.open_workbook(file, formatting_info = True) cl = ['DI', 'DO', 'AI', 'AO', 'M', 'Event', 'VALVE', 'MODULE'] for i in cl: if i in rb.sheet_names(): sheet = rb.sheet_by_name(i) log("Проверка " + i) for rownum in range(1, sheet.nrows): row = sheet.row_values(rownum) eval(i)(row, rownum)

All cells in Excel have a text format.

If '9' is stored in the first cell, then row [0] will give 9.0, and I need '9'

It is fundamentally important for me to distinguish the values ​​of cells 9 and 9.0

Is it possible to use XLRD, how can I specify that I take everything only in text format from EXCEL?

  • Specify the data format on each line. cell (). value as row_values ​​() outputs data as is, except for formats. - Igor

3 answers 3

Are you sure that in Excel document cell text? The row_values() method row_values() data as it is.

If it gives out 9.0, then the "Numeric" format is in the source document.

If you need to get the text regardless of the cell format of the source document, you can use for example the sheet.cell_type(rowx, colx) to determine the type of data in the cell:

  • 1 - "text" display as it is.
  • 2 - "numerical" is rounded off and reduced to a string.
  • 3 - "Date" We convert the date to text.

Details here

  • Created a test document, put numbers in a column. Shows type 2, although the format is text. - Igor
  • So it’s not text that I’ve just tried for myself, if the “text” format displays the string '9', take a closer look at the cell format. It can also be “common”, it automatically determines the format in my opinion. - privod
  • one
    With assigning problem types to libreoffice. '9', then row [0] will give 9.0 - here the format is clearly not text, otherwise it would output '9' - Igor
  • the numeric type displays as a number without saving the format, the number two decimal places after 5.00 will be displayed as 5.0. Type 2. - Igor
  • I have Excel 2010. The format of all the cells, absolutely everyone, is text, neither general nor numeric, just text, but the row_values ​​() method still gets 9.0 - AND

As an alternative definition.

 #!/usr/bin/env python3 from xlrd import open_workbook wb_read = open_workbook('test.xls', formatting_info=True) ws = wb_read.sheets()[0] def dump_cell(sheet, rowx, colx): c = sheet.cell(rowx, colx) xf = sheet.book.xf_list[c.xf_index] fmt_obj = sheet.book.format_map[xf.format_key] print(rowx, colx, repr(c.value), c.ctype, fmt_obj.type, fmt_obj.format_key, fmt_obj.format_str) dump_cell(sheet=ws, rowx=1, colx=4) # лист(объект), ячейка(строка, столбец)

In one line, for text displays @

 print((ws.book.format_map[(ws.book.xf_list[ws.cell(1, 3).xf_index]).format_key]).format_str)

The function to find out the format of the cell, displays all, but you can leave only the necessary. For text, displays @, for a numeric format, 2 decimal places 0.00, etc.

    As it turned out in excel `e 2010 there is one oddity:

    If earlier the cell format was numeric and the cell stored a number, then after changing the format via PCM, the cell format ... in fact, it does not change, although it is shown that the text. To make it really text, you need to delete the value and then re-enter it, then the cell will really change the format and the python will see it through row_value ().