Read excel information in matrix form with python

Question

I’m using Python 2.7 to read values from an Excel document. When I have only one row of values with respective header I can read, doing

from openpyxl import Workbook
from openpyxl import load_workbook
wb = load_workbook("Resultados.xlsx")
Nod=49 #numero de celulas com dados
sheet1=wb.get_sheet_by_name('Primeira')
ph_value=[]
for a in range(1,Nod+1): 
    ph_value.append(sheet1.cell(row=3, column=a).value)

However I have Excel sheets that instead of having a row of data with multiple columns as the previous example, I have several rows with multiple columns, ie an array. In addition to the values I need to associate the respective row and column headers and I can only do for one row, as follows:

sheet6=wb.get_sheet_by_name('PTran_A')
p_t_a_value=[]
for a in range(2,Nod+1): 
    p_t_a_value.append(sheet6.cell(row=3, column=a).value)

p=[]
ind_p=[]

Cycle that takes all values from the line and only counts those that have value, filled cells and their indexes, eliminating those that are blank. These indexes correspond to what happens in each header/header:

for b in range(0,len(p_t_a_value)):
    if (p_t_a_value[b]!=None):
        p.append(p_t_a_value[b])
        ind_p.append(b)

How can I take the values for all rows and columns and can have the values and indexes as I did for a single row?

Answer 1

According to the documentation, you can also navigate the rows/columns through numbers (indexes).

I suggest a loop inside the other:

from openpyxl import load_workbook
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb['big_data'] # ws agora é uma IterableWorksheet

for row in ws.rows:
    for cell in row:
        print(cell.value)

http://openpyxl.readthedocs.io/en/default/optimized.html