CODEWITHSUNDEEP
pythonexceldictionaryopenpyxl
eisp4nzer
eisp4nzer

Reputation: 9

OpenPyxl need help editing sheet

I'm trying to extract data from an Excel sheet: So far so good, but I'm trying to find a better way to implement a value to a dictionary, it's kind of boring doing it line by line and I'm struggling to find a way to do it in a single line

Here's what I got so far:

import openpyxl

Stocks = openpyxl.load_workbook("D:\Coding\Projects\Stocks\Stocks.xlsx")

BR_NOW = Stocks["BR_NOW"]
PT_NOW = Stocks["PT_NOW"]

BR_Last = {}
PT_Last = {}

BR_Last[BR_NOW["A1"].value]=[BR_NOW["B1"].value]
BR_Last[BR_NOW["A2"].value]=[BR_NOW["B2"].value]
BR_Last[BR_NOW["A3"].value]=[BR_NOW["B3"].value]
BR_Last[BR_NOW["A4"].value]=[BR_NOW["B4"].value]
BR_Last[BR_NOW["A5"].value]=[BR_NOW["B5"].value]
BR_Last[BR_NOW["A6"].value]=[BR_NOW["B6"].value]
BR_Last[BR_NOW["A7"].value]=[BR_NOW["B7"].value]
BR_Last[BR_NOW["A8"].value]=[BR_NOW["B8"].value]

But it goes until "A101"

What would be the best way to do this?

Many thanks in advance

EDIT:

Thank you all,

Now I'm trying to insert in Excel values from the dictionary, but only if these values are smaller than the ones already inserted in those cells (So I have Last, Min and Max values of the stock market)

I came up with this:

for i in range(1,83):
    if Stocks["BR_MIN"["B"+str(i)].value] <= BR_Last.value:
        Stocks["BR_MIN"["B"+str(i)].value] = BR_Last.value

What am I doing wrong? It says: TypeError: string indices must be integers

Many thanks in advance

Upvotes: -2

Views: 62

Answers (2)

m.i.cosacak
m.i.cosacak

Reputation: 730

you can use this;

for i in range(1,102):
   BR_Last[BR_NOW["A"+str(i)].value]=[BR_NOW["B"+str(i)].value]

Upvotes: 1

Josh
Josh

Reputation: 1601

How about making a loop from 1 to 101 and then dynamically referring to the cell address?

Like this?

import openpyxl

Stocks = openpyxl.load_workbook("D:\Coding\Projects\Stocks\Stocks.xlsx")

BR_NOW = Stocks["BR_NOW"]
PT_NOW = Stocks["PT_NOW"]

BR_Last = {}
PT_Last = {}

for i in range(1,102):
    BR_Last[BR_NOW["A" + str(i)].value]=[BR_NOW["B" + str(i)].value]

Upvotes: 1

Related Questions