在Python中使用xlrd讀取數字Excel資料作為文字
我正在使用xlrd讀取Excel檔案,我想知道是否有辦法忽略Excel檔案中使用的單元格格式,並將所有資料匯入文字?
以下是我正在使用的程式碼:
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()
這段程式碼是有效的,但是有一些欄位,如郵政編碼,作為數字匯入,所以它們具有十進位制零字尾.例如,Excel檔案中是否有郵政編碼’79854′,將匯入為’79854.0′.
我已經嘗試在這個ofollow,noindex" target="_blank">xlrd spec 找到一個解決方案,但沒有成功.
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)
這一切都在xlrd spec .
http://stackoverflow.com/questions/2739989/reading-numeric-excel-data-as-text-using-xlrd-in-python