Extract the data and modify an existing excel with openpyxl

Question

I would like to modify an existing excel via openpyxl. The purpose is to take finance data and insert it in the specific columns so that it can perform the calculations.

I would like column 1 to show the opening prices

I write this code.

import datetime as dt
import pandas as pd
import pandas_datareader.data as web

from openpyxl import load_workbook

start = dt.datetime(2018, 1, 1)
end = dt.datetime(2019, 1, 1)

ticker = "AAPL"

yahoo = web.DataReader(ticker, 'yahoo', start, end)

#print(yahoo['Open'])


wb = load_workbook('aa.xlsx')
ws = wb.active
ws.title = "Change Sheet__"

ws["A"].value(aaa)

wb.save("aa.xlsx")

The error is:

Traceback (most recent call last): File "C:\Users\Davide\Desktop\aa.py", line 23, in ws["A"].value(aaa) AttributeError: 'tuple' object has no attribute 'value'

Traceback (most recent call last): File "C:\Users\Davide\Desktop\aa.py", line 23, in ws["A"].value(aaa) AttributeError: 'tuple' object has no attribute 'value' — user3465752, Nov 10 '19 at 14:23
Does this answer your question? [Python How to use ExcelWriter to write into an existing worksheet](https://stackoverflow.com/questions/34744863/python-how-to-use-excelwriter-to-write-into-an-existing-worksheet) — Umar.H, Nov 10 '19 at 14:55
Can you edit my code with the example ? I try but it isn't work — user3465752, Nov 10 '19 at 16:16

Umar.H · Answer 1 · 2019-11-10T21:55:59.990

0

IIUC, this should work :

from openpyxl import load_workbook
wb = load_workbook('aa.xlsx')
ws = wb['Sheet1'] # choose your sheet.

then we decide your column to replace with your df.

col_to_replace = 'A'
for index, row in df.iterrows():
    cell = f'{col_to_replace}{index+2}'
    ws[cell] = row[0]

wb.save('aa.xlsx')

edited Nov 10 '19 at 21:55

answered Nov 10 '19 at 21:46

Umar.H

22,559
7
39
74

Or just use the methods provided by the library. – Charlie Clark Nov 11 '19 at 10:01
@Charlie Clark can you post an example? From my experience working with pandas it usually overwrites excel sheets destroying any dependent formulas, although it's been a while since I've done anything excel based. – Umar.H Nov 11 '19 at 10:07
1

I never mentioned Pandas. openpyxl worksheets have `iter_rows()` and `iter_cols()` methods which the OP would know if they'd read the documentation. – Charlie Clark Nov 11 '19 at 10:09

Extract the data and modify an existing excel with openpyxl

1 Answers1

then we decide your column to replace with your df.