使用 xlrd 在 python 中读取选定的列和所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31656054/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using xlrd to read selected columns and all rows in python
提问by Marion
I would like to loop through an excel table and get the values for selected columns in each row in a set of lists or dictionary. if in a dictionary, for each row, the value in the first selected column would be the key and the values in the other selected columns would be the values (array) for that key. I cannot figure out how to tell python to read values from only selected columns...for the excel table may have 10 columns but I am only interest in three for example, and the three of interest are not contiguous. Would appreciate your insights using XLRD.
我想遍历一个 excel 表并获取一组列表或字典中每一行中选定列的值。如果在字典中，对于每一行，第一个选定列中的值将是键，其他选定列中的值将是该键的值（数组）。我不知道如何告诉 python 只从选定的列中读取值……因为 excel 表可能有 10 列，但例如我只对三列感兴趣，并且感兴趣的三列不连续。感谢您使用 XLRD 的见解。
import xlrd
from xlrd import open_workbook
import arcpy
wb = open_workbook ("c:\Users\Admin\Documents\Marion\Courses\GEOG485\FinalProject\Data\ExcelFiles\Belize_Culvert_Nov15_V4.0.xlsx")
sheet = wb.sheet_by_index(1)
keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)]
dict_list = []
for rownum in range(sheet.nrows):
d = {keys[col_index]: sheet.cell(0, 5).value
for col_index in xrange(sheet.ncols)}:
dict_list.append(d)
The field that I want to use as key is column 5 and the values are columns #16 and #17 as an array value for each key...
我想用作键的字段是第 5 列，值是列 #16 和 #17 作为每个键的数组值...
回答by Anand S Kumar
Some issues in your code -
您的代码中的一些问题 -
keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)]- This always takes the keys as the value in the first row and 6th column (Rows and columns are 0 indexed.)d = {keys[col_index]: sheet.cell(0, 5).value- This is not even valid python syntax
keys = [sheet.cell(0, 5).value for col_index in xrange(sheet.ncols)]- 这总是将键作为第一行和第 6 列中的值（行和列的索引为 0。）d = {keys[col_index]: sheet.cell(0, 5).value- 这甚至不是有效的 python 语法
You can just loop over all the rows, take column index 4 (5th column) as key and the rest in a list and add that to a dictionary, Example -
您可以循环遍历所有行，将列索引 4（第 5 列）作为键，将其余的列在列表中并将其添加到字典中，例如 -
import xlrd
from xlrd import open_workbook
import arcpy
wb = open_workbook ("c:\Users\Admin\Documents\Marion\Courses\GEOG485\FinalProject\Data\ExcelFiles\Belize_Culvert_Nov15_V4.0.xlsx")
sheet = wb.sheet_by_index(1)
sheetdict = {}
for rownum in range(sheet.nrows):
sheetdict[sheet.cell(rownum,4)] = [sheet.cell(rownum,15),sheet.cell(rownum,16)]
In the end, sheetdicthas the required dictionary.
最后，sheetdict有所需的字典。
回答by Zong
Use this command
使用这个命令
row_slice(rowx, start_colx=0, end_colx=None)
Returns a slice of the Cell objects in the given row.
返回给定行中 Cell 对象的一部分。
