I know I can read range of cells using that code:
worksheet.cell(range="A1:J1").valuebut I prefer to use numbers instead of letters to select columns, something like that:
worksheet.cell(row=1,column=1:10).valueIs this possible?
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.rows or 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_row and max_col.
answered May 2 '16 at 15:42
-
Single cell access example for A1:
ws.cell(row=1, column=1).value = 'test'– Aralox Aug 30 '17 at 3:28 -
Aside from iter_rows, it seems like there should be a single method (ws.cells) that can provides range comprehension by truth tests args to determine if someone wants a row, col, cell, or range. ie
if row=None, col=7: return ws.columns[7]. Perhaps once I become competent at unit testing I could help with this... – virtualxtc Aug 25 '18 at 23:12
Are you sure worksheet.cell(range="A1:J1").value is possible?
The above may be possible using range function as given in the official documentation:
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.
So, you can use a list comprehension:
data = [worksheet.cell(row=1,column=i).value for i in range(1,11)]
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']
rng: any range in ws e.g. rng=ws['A1':'D10'] or rng=ws['A1':'D' + str(ws.max_column)]
Method 1:
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.
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 cells and not their values, so to access their value do this:
first_cell_value=t[0][0].value
Depending on how you want to read this data! For example
type(sheet['A7':'B8'])will give you <class 'tuple'>, by typing
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 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).
