0

I am basically trying to read data from an excel file and I want to add it in an array. The excel file has multiple rows and multiple columns and I want them to be shown the exact same way in the array too. This data needs to be shown in an array

from importlib.resources import open_binary
import openpyxl
import numpy as np
import array
import sys

wb = openpyxl.load_workbook("IEEE.xlsx")
bus1 = wb['33-Bus']
bus2 = wb['69-Bus']

rowMax = bus1.max_row
columnMax = bus1.max_column
print(rowMax,columnMax)

for i in range(1,rowMax+1):
  for j in range(1,columnMax+1):

    result = [bus1.cell(i,j).value]

    print(result)

I want the loop to run and add the first cell to the array, then run again and add the second element and keep doing that until it hits the end of that row. Then I want it to create another array for the second row. Then add all of these arrays of the rows data together in a new array. So a 2D array. Can anyone help me with that? I have javascript knowledge and python is kinda different so I can't seem to figure this out

Barmar
  • 741,623
  • 53
  • 500
  • 612
DemonSlayer987
  • 1
  • You create `matrix = []` outside the loop. You create `row = []` inside the first loop. Then you `row.append( bus1.cell(i,j).value )` in the inner loop, and `matrix.append(row)` after it. However, you could do this all in one operation with pandas. – Tim Roberts Oct 24 '22 at 22:00
  • We call them *lists*, not arrays. To fix the problem with the looping code, use `.append` to add elements to the list, and ensure it is created before the loop. Since you want to make a list of lists, you need to use this technique separately for each loop. Try writing the code for a single loop first, for practice. See the linked duplicate for reference. – Karl Knechtel Oct 24 '22 at 22:57

1 Answers1

0

Implementing what I said in my comment,

matrix = []
for i in range(1,rowMax+1):
  row = []
  for j in range(1,columnMax+1):
    row.append( bus1.cell(i,j).value )
  matrix.append(row)

Or

matrix = []
for i in range(1,rowMax+1):
  matrix.append( [bus1.cell(i,j+1).value for j in range(columnMax)] )

Or even

matrix = [
    [bus1.cell(i+1,j+1).value for j in range(columnMax)]
    for i in range(rowMax)
]

Again, however pandas has a read_excel function that can do this in one step.

Tim Roberts
  • 48,973
  • 4
  • 21
  • 30
  • I got it, thank you so much! Also I had a quick question. If I wanted to print this data, how would I do it? Like what if I want to print the exact element? Cause printing matrix[] prints the whole array. And I know printing the row[] will print the element but is there a command that implements "matrix" in it too: matrix[row[]] – DemonSlayer987 Oct 25 '22 at 00:31
  • Well, what do you want to print? If you want to print a row at a time, you can do `for row in matrix:` / `print(row)`. If you need more specific formatting, you'll have to grab the elements of the row. – Tim Roberts Oct 25 '22 at 05:29
  • let's say I want to call "row" 2 in matrix 3. I can manage to print the whole row within a matrix, Like I can print matrix 3 but if I try to get the element 2 within that, I can't see to do it: **for row in matrix[3]:** / **print(row[2])** This gives me a *'int' object is not subscriptable* – DemonSlayer987 Oct 25 '22 at 07:16
  • That suggests you may not have copied the code correctly. That shouldn't happen with the code I posted. If `print(matrix[3])` prints a list, then `matrix[3][2]` should work. – Tim Roberts Oct 25 '22 at 17:47