Python 如何在 OpenPyXL 中使用 column=numbers 而不是字母读取单元格范围？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36984422/
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
How to read range of cells using column=numbers instead of letters in OpenPyXL?
提问by Charlie Clark
I know I can read range of cells using that code:
我知道我可以使用该代码读取单元格范围：
worksheet.cell(range="A1:J1").value
but I prefer to use numbers instead of letters to select columns, something like that:
但我更喜欢使用数字而不是字母来选择列，例如：
worksheet.cell(row=1,column=1:10).value
Is this possible?
这可能吗？
回答by trans1st0r
Are you sure worksheet.cell(range="A1:J1").valueis possible?
你确定worksheet.cell(range="A1:J1").value有可能吗？
The above may be possible using rangefunction as given in the official documentation:
使用range官方文档中给出的函数可以实现上述功能：
http://openpyxl.readthedocs.io/en/default/api/openpyxl.worksheet.worksheet.html
http://openpyxl.readthedocs.io/en/default/api/openpyxl.worksheet.worksheet.html
There are 2 ways to use worksheet.cell:
Usage: cell(coodinate='A15') or cell(row=15, column=1)
If coordinates are not given, then row and column must be given.
有两种使用 worksheet.cell 的方法：
用法：单元格(coodinate='A15') 或单元格(row=15, column=1)
如果没有给出坐标，则必须给出行和列。
So, you can use a list comprehension:
因此，您可以使用列表理解：
data = [worksheet.cell(row=1,column=i).value for i in range(1,11)]
回答by Charlie Clark
ws.cell()can only return individual cells so ranges for it make no sense. To access a range of cells you can use ws.iter_rows()or ws.rowsor ws.columns. Upto and including version 2.3 ws.iter_rows()only accepts Excel-style range notation but you can use row and column offsets to create a range. Starting with version 2.4 you will be able to provide fully numerical (1-based indexing) values for min_row, min_col, max_rowand max_col.
ws.cell()只能返回单个单元格，因此范围没有意义。要访问一系列单元格，您可以使用ws.iter_rows()或ws.rows或ws.columns。2.3 及以上版本ws.iter_rows()仅接受 Excel 样式的范围表示法，但您可以使用行和列偏移量来创建范围。与2.4版本开始，你将能够为提供全面的数字（从1开始的索引）值min_row，min_col，max_row和max_col。
回答by Ibo
It seems you want to break a range into rows and store the cell values of each row in a tuple or list. There are 2 methods that are clean and easy for this:
似乎您想将一个范围分成几行并将每一行的单元格值存储在一个元组或列表中。有两种方法既干净又简单：
Assumptions:
假设：
ws: worksheet e.g. ws=wb['Sheet1']
ws：工作表例如 ws=wb['Sheet1']
rng: any range in ws e.g. rng=ws['A1':'D10']or rng=ws['A1':'D' + str(ws.max_column)]
rng：ws 中的任何范围，例如rng=ws['A1':'D10']或rng=ws['A1':'D' + str(ws.max_column)]
Method 1:
方法一：
t=tuple(rng)
t=tuple(rng)
this will return a tuple that contains tuples of each row. So in this example, t will have 10 tuples and each inner tuple will have 4 elements/values.
这将返回一个包含每行元组的元组。所以在这个例子中，t 将有 10 个元组，每个内部元组将有 4 个元素/值。
Method 2:
方法二：
final_list=[]
for row in rng:
mylist=list(row)
final_list.append(mylist)
this will be pretty much the same thing except instead of tuples you would have lists.
这将是几乎相同的事情，除了您将拥有列表而不是元组。
NOTE:
笔记：
list of lists or tuple of tuples will store cellsand not their values, so to access their value do this:
列表列表或元组元组将存储cells而不是它们的值，因此要访问它们的值，请执行以下操作：
first_cell_value=t[0][0].value
回答by AmirHosein Sadeghimanesh
Depending on how you want to read this data! For example
取决于你想如何读取这些数据！例如
type(sheet['A7':'B8'])
will give you <class 'tuple'>, by typing
会给你<class 'tuple'>，通过输入
type(sheet['A7':'B8'][i])
where i=1,2, you will get the same type. Therefore it is a tuple of tuples. It has as many tuple as the number of rows in the range.
其中 i=1,2，您将获得相同的类型。因此它是元组的元组。它具有与范围内行数一样多的元组。
I define a function with the start-end indeices of the row-column range as its arguments and returning a similar tuple of tuples but having values of the cells inside. You can similarly define your own function with a different type of output and any information that you want to extract.
我定义了一个函数，以行列范围的开始结束索引作为其参数，并返回一个类似的元组元组，但其中包含单元格的值。您可以类似地使用不同类型的输出和您想要提取的任何信息来定义您自己的函数。
def RangeValue(a, b, c, d):
return(tuple(tuple(sheet.cell(row=a+i, column=c+j).value for j in range(d-c+1)) for i in range(b-a+1)))
Now for the range A7:B8, you just need to ask RangeValue(7,8,1,2).
现在对于 A7:B8 范围，您只需要询问RangeValue(7,8,1,2).
