在Python中使用xlrd将数字Excel数据作为文本读取 [英] Reading numeric Excel data as text using xlrd in Python

问题描述

我想使用xlrd读取一个Excel文件，我想知道是否有一种方法可以忽略在Excel文件中使用的单元格格式，只是将所有数据作为文本？

这里是我用于far的代码：

  import xlrd 
 $ b b xls_file ='xltest.xls'
 xls_workbook = xlrd.open_workbook（xls_file）
 xls_sheet = xls_workbook.sheet_by_index（0）
 
 raw_data = [[''] * xls_sheet。 ncols for _ in range（xls_sheet.nrows）] 
 raw_str =''
 feild_delim ='，'
 text_delim =''
 
 for rnum in range xls_sheet.nrows）：
 for cnum in range（xls_sheet.ncols）：
 raw_data [rnum] [cnum] = str（xls_sheet.cell（rnum，cnum）.value）
 
 $ b for rnum in range（len（raw_data））：
 for cnum in range（len（raw_data [rnum]））：
 if（cnum == len（raw_data [rnum]） - ：
 feild_delim ='\\\
'
 else：
 feild_delim ='，'
 raw_str + = text_delim + raw_data [rnum] [cnum] + text_delim + feild_delim 
 
 final_csv = open（'FINAL.csv'，'w'）
 final_csv.write（raw_str）
 final_csv.close（）
  / pre> 
 
 
此代码有效，但有一些字段（如邮政编码）作为数字导入，因此它们具有十进制零后缀。例如，在Excel文件中是否有邮政编码79854，它将作为79854.0导入。
 
 
 
我已经尝试在这个 xlrd规格，但
 $ p 
 
解决方案
这是因为Excel中的整数值以浮点形式导入Python。因此， sheet.cell（r，c）.value 返回一个浮点数。尝试将值转换为整数，但首先确保这些值在Excel中是整数开头：
  cell = sheet.cell r，c）
 cell_value = cell.value 
如果cell.ctype在（2,3）和int（cell_value）== cell_value：
 cell_value = int（cell_value）
 全部  co.uk/svn/xlrd/trunk/xlrd/doc/xlrd.html?p=4966rel =nofollow noreferrer> xlrd spec 。
 
I am trying to read in an Excel file using xlrd, and I am wondering if there is a way to ignore the cell formatting used in Excel file, and just import all data as text?

Here is the code I am using for far:
import xlrd

xls_file = 'xltest.xls'
xls_workbook = xlrd.open_workbook(xls_file)
xls_sheet = xls_workbook.sheet_by_index(0)

raw_data = [['']*xls_sheet.ncols for _ in range(xls_sheet.nrows)]
raw_str = ''
feild_delim = ','
text_delim = '"'

for rnum in range(xls_sheet.nrows):
    for cnum in range(xls_sheet.ncols):
        raw_data[rnum][cnum] = str(xls_sheet.cell(rnum,cnum).value)

for rnum in range(len(raw_data)):
    for cnum in range(len(raw_data[rnum])):
        if (cnum == len(raw_data[rnum]) - 1):
            feild_delim = '\n'
        else:
            feild_delim = ','
        raw_str += text_delim + raw_data[rnum][cnum] + text_delim + feild_delim

final_csv = open('FINAL.csv', 'w')
final_csv.write(raw_str)
final_csv.close()
This code is functional, but there are certain fields, such as a zip code, that are imported as numbers, so they have the decimal zero suffix.  For example, is there is a zip code of '79854' in the Excel file, it will be imported as '79854.0'.

I have tried finding a solution in this xlrd spec, but was unsuccessful.
解决方案
That's because integer values in Excel are imported as floats in Python. Thus, sheet.cell(r,c).value returns a float. Try converting the values to integers but first make sure those values were integers in Excel to begin with:
cell = sheet.cell(r,c)
cell_value = cell.value
if cell.ctype in (2,3) and int(cell_value) == cell_value:
    cell_value = int(cell_value)
It is all in the xlrd spec.

                        
这篇关于在Python中使用xlrd将数字Excel数据作为文本读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！