Read excel information in array form with python

Question

Read excel information in array form with python

Navigation

#1 by (1 votes)

1

I'm using Python 2.7 to read values from an Excel document. When I have only one line of values with its 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 multi-column data row like the previous example, I have multiple rows with multiple columns, ie an array. In addition to the values I need to associate their row and column headers and I can only do it for a row, like this:

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=[]

A cycle that takes all the values of the row and only counts the values that have value, the filled cells and the respective indexes, eliminating the ones that are blank. These indices correspond to what occurs 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 get values for all rows and columns, can I have the values and indexes as I did for a single row?

python excel python-2.7

asked by anonymous 22.07.2016 / 17:25

1 answer

Multiple MySQL Banks? How to verify the domain that the user is

score 1 · 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)

link