CODEWITHSUNDEEP
pythonexcelopenpyxl
Rob B.
Rob B.

Reputation: 128

How write values to individual cells with OpenPyXL?

I'm trying to figure out how to add data to an Excel spreadsheet with openpyxl but I haven't figured out to write values to individual Excel cells.

What I'm wanting to do is to save the print results from the loop into the 1st four rows of Excel and save it as fundamental_data. Here is the code. Please let me know if you know of any good tutorials for openpyxl. 

from openpyxl import Workbook
import urllib
import re

symbolslist = ["aapl","spy","goog","nflx"] 

from openpyxl import load_workbook
wb2 = load_workbook('fundamental_data.xlsx')

i=0
while i<len(symbolslist):
    url = "http://finance.yahoo.com/q?s="+symbolslist[i]+"&q1=1"
    htmlfile = urllib.urlopen(url)
    htmltext = htmlfile.read()
    regex = '<span id="yfs_l84_'+symbolslist[i]+'">(.+?)</span>'
    pattern = re.compile(regex)
    price = re.findall(pattern,htmltext)
    print "The price of", symbolslist[i], " is ", price 
    i+=1

wb = Workbook()
wb.save('fundamental_data.xlsx')

Upvotes: 0

Views: 1718

Answers (2)

Jellema
Jellema

Reputation: 2002

Looking for the prices on the specified URL works well, only you forget to specify in which sheets/cells you want to write the data. Do this:

wb = Workbook()
# select sheet
ws = wb.active

i = 0
while i<len(symbolslist):
    url = "http://finance.yahoo.com/q?s="+symbolslist[i]+"&q1=1"
    htmlfile = urllib.urlopen(url)
    htmltext = htmlfile.read()
    regex = '<span id="yfs_l84_'+symbolslist[i]+'">(.+?)</span>'
    pattern = re.compile(regex)
    price = re.findall(pattern,htmltext)
    print "The price of", symbolslist[i], " is ", price 
    # specify in which cell you want to write the price
    # in this case: A1 to A4, where the row is specified by the index i
    # rownumber must start at index 1
    ws.cell(row=i+1, column=1).value = price[0]
    i += 1

And in the end (watch out it will just overwrite existing data):

wb.save('fundamental_data.xlsx')

Also you can compare it with the following openpyxl documentation

And if you want more information on the openpyxl module, you can open the python console:

>>> import openpyxl
>>> help(openpyxl)

Or for a specific package content:

>>> help(openpyxl.workbook)

Upvotes: 3

Scott
Scott

Reputation: 37

import openpyxl
CreateFile = openpyxl.load_workbook(filename= 'excelworkbook.xlsx')
Sheet = CreateFile.active
Sheet['A1']=('What Ever You Want to Type')
CreateFile.save('excelworkbook.xlsx')

This is my most simple example.

Upvotes: 1

Related Questions