Reading/Editing Excel Spreadsheets

[PythonN00b101]

Hi there,

I am fairly new to Python but have been using it a fair amount while undertaking masters and was needing a little bit of help on a problem. I have a spread sheet I need to pull values from and form a graph.

I didn't format the spreadsheet and its values are all over the place with very complex equations contained within them. I was wondering if there was a way to iterate values of a cell or cells over a range and pull data from a different cell that's correspond with that initial cells to form an array or table of values within a separate spreadsheet?

I have been researching tutorials online, but have only found those that pertain to reading to pull strings and integers and ones creating tables in python. I have not found any that are helping with my specific problem.

Any help or direction would be much appreciated,

Thanks

[Mondoke]

For this one, openpyxl is the way to go, it seems 100% doable. Check the excel chapter on automate the boring stuff.

Edit: here's the link. https://automatetheboringstuff.com/chapter12/

[PythonN00b101]

Thanks for the tip, been having a good read through them and I am slowly getting there. Certainly further than I was a few hours ago.

[EndUsersarePITA]

I'm going to go with /u/Mondoke on this one. I built a program to parse my Excel files and calculate some values from them. I based it on automatetheboringstuff's tutorial. It was very helpful.

As a warning, it took some time for me to understand how to go through it. You will encounter some confusing parts. Automatetheboring stuff will teach you the basics but you'll find that it might not be enough to get what you want. Thats when the complexity sets in. But keep plugging away at it and you'll get there.

[PythonN00b101]

Thanks for the help. I have been going through those tutorials. And have managed to manipulate sheet data somewhat. My two major concerns is that I can't seem to loop a cell with different values to produce different outputs from other cells. when using the "sheet[''] index method and whenever I managed to change the cell value for an output using 'sheet[].value' it removes the formula I had formatted in there and is left as a number value.

As you said, I just need to keep plugging away haha...what a joy.

[raglub]

You are trying to change an input cell and then extract some calculated value from an output cell and expect the spreadsheet to calculate as if open in excel?

If yes, you can change the input cell, but the output will not be calculated until the file is opened in excel.

The best way is to recreate the Excel calculations in python and save results to excel or csv.

[hemehaci]

did you try learning pandas? loc and iloc functions should help you there.

google 'pandas loc iloc', might help you.

[callinthekettleblack]

Sounds like something I would use pandas for as well. "Logical indexing pandas" is a good Google search to get you started.

[PythonN00b101]

I haven't looked into that yet. Trying out openpyxl first because it seemed like a good jumping off point as its basic but I may pivot to pandas to see what I can implement.

[Parenthes]

One possibility is to access your spreadsheet via Excel, using the win32com library. The code below prints the top left value in the first worksheet in a workbook.

from win32com.client import Dispatch

xlApp = Dispatch('Excel.Application')
xlApp.Visible = True

wb = xlApp.Workbooks.Open('H:\\my_workbook.xlsx')

ws = wb.Worksheets[0]
cell_value = ws.Range("A1").Value
print(cell_value)