python xlrd从excel文本单元格接收浮点数 [英] python xlrd receiving float from excel text cell

问题描述

我正在尝试使用xlrd从Excel文件中读取值.在日期，数字和到目前为止的文本方面，它一直都很棒.我有一列(类别)，其中的单元格包含文本(这些单元格的格式设置为文本).当我打印出单元格值时，将显示浮点数而不是文本.我还打印了要检查的Cell对象的ctype，它显示为Number.我已经阅读了xlrd的文档和教程，似乎无法找到发生这种情况的原因.可能是我的excel文件被弄乱了吗?有什么建议或正确方向的建议吗?

I am attempting to read values from an Excel file using xlrd. It has been working great on dates, numbers, and up until now text. I have a column (category) with cells containing text (the cells are formatted as text). When I print out the cell value a float is displayed instead of the text. I also printed out the ctype of the Cell object(s) to check and it is showing as Number. I've read through the documentation and tutorial of xlrd and can't seem to find why this is occurring. Could it be that my excel file is somehow messed up? Any suggestions or pointers in the right direction?

import xlrd
import datetime

workbook = xlrd.open_workbook('training.xls')
courseSheet = workbook.sheet_by_index(0)

for row in range(courseSheet.nrows):
    title = courseSheet.cell_value(row, 2)
    date = courseSheet.cell_value(row, 4)
    date = datetime.datetime(*xlrd.xldate_as_tuple(date, workbook.datemode))
    dateTuple = date.timetuple()
    category = courseSheet.cell_value(row, 7)
    print category

推荐答案

背景:对于每个单元格，xlrd报告存储在XLS文件中的内在值(如果有).最初仅根据文件中的记录类型分配值类型(例如NUMBER和RK记录包含浮点数).它按照这里，并使用该信息来覆盖值类型，在这种情况下，显然是要使用日期时间，日期或时间而不是数字. xlrd并不声称能够根据归因于该单元格的格式呈现单元格值.

Background: For each cell, xlrd reports the intrinsic value (if any) that is stored in the XLS file. The value types are assigned initially solely on the basis of the record type in the file (e.g. NUMBER and RK records contain floating-point numbers). It classifies formats as described here and uses that information to override the value type where it is apparent that a datetime, date, or time is intended rather that a number. xlrd does not purport to be able to render cell values according to the format ascribed to the cell.

所讨论的单元格显然已以数字形式输入.如果他们应用了文本格式，则不会使它们成为文本单元格".

The cells in question have evidently been entered as numbers. If they have had a text format applied to them, that does not make them "text cells".

您说""当我打印出单元格值时，将显示浮点数而不是文本""... ...请举一些示例(a)创建文件时在单元格中键入的内容(b )有什么证据表明单元格的格式为文本"?(c)什么是repr(cell.value)?(d)什么是您希望显示的文本"?

You say """When I print out the cell value a float is displayed instead of the text""" ... please give some examples of (a) what was typed into the cell when the file was created (b) what is the evidence that "the cells are formatted as text" (c) what is repr(cell.value) (d) what is "the text" that you expected to be displayed?

您可能会发现以下代码很有用:

You may find the following code useful:

import xlrd, sys

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

book = xlrd.open_workbook(sys.argv[1], formatting_info=1)
sheet = book.sheet_by_index(0)
for rowx in xrange(sheet.nrows):
    for colx in xrange(sheet.ncols):
        dump_cell(sheet, rowx, colx)

这篇关于python xlrd从excel文本单元格接收浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！