I am working as a Senior Financial Officer dealing with financial reporting. This video gives me a new powerful tool to quickly manipulate and process a large volume of data. I think I will surprize my boss with my new skills! Thank you, Tim!!!
to those who are thinking @6:52 that you can directly open the workbook or directly save the workbook using that load_workbook command.. no ppl.. you need to first make or upload a workbook at the location of your text editor or some random location then take that location from there .
For anyone like me who finds the lettering system tedious and annoying, rather than using the get column letter function you can just use sheet.cell(1,1) for example which would be cell A1, but I think this is nicer especially if youre using nested for loops.
New to python and starting my own project working with excel files. Tried to go through the documentation for openpyxl and was wasting alot of time learning unnecessary information based on what I was trying to accomplish. You've done a wonderful job with this and I was able to easily follow along and experiment along the way. Put together a py file for my notes on this module. Tim is Great!
Boomers at my work will now treat me as a god
As Tim noted, you cannot save the spreadsheet changes if it is opened in Excel (or in my case, Libre Office) for 'normal' use. I did figure a sort of workaround to this problem. If you open the spreadsheet as read only, your python code will run without a problem. Now to view the changes simply reload the spreadsheet. The reload command in Libre Office can be found under File >>> Reload (or press the keys Alt, f, l in that order(Note l is lowercase L)) Yes, you have to remember to reload the spreadsheet every time you make a change, but it is quicker than the close, reopen way, you could put a reminder in your python code. Note, if you want to edit the read only open file, there is a button on the top right that says Edit Document. but remember after editing it needs to be saved and reopened as read only. Use the python command try when doing a workbook save, and if it throws and exception, then ask the user to go through the close/reopen read only routine.
recently got into learning python. my mind is blown with the versatility of python. I now wish i had done software engineering back in school..
Actually did something similar a month ago, where I converted the excel file in to base64 in the end and sent it through an API to then generate it back into xlsc format through typescript. PyXl is amazing btw, anyone who watches this should try it. Very easy to use.
I've been learning Python by following a book and got confused and stuck for so long until I found this video. Thanks
This is quality stuff. This guy gets how to make a good tutorial video. No wasted time, points are explained enough to understand then moves on.
by far, the best video for starting excel with python
Hands done one of the best Python training videos out there I’ve. Method of instruction, clarity and examples are brilliant. Thank you so much.
For anyone reading there are random real-world use-cases for stuff like this on occasion, I recently did something similar with Word and Powershell. In my case, I would take data from a CSV and generate a document containing stuff like login information, Employee IDs, etc. It can save time when you are doing 25+ of these documents.
exactly what i needed, im a civil engineer and learning python. i was coding a building cost estimator desktop app in tkinter and needed python after calculating all materials for me, it should also create an excel workbook and type those results for me. so that i simply print out for the client in the shortest time possible. awesome video. thank you sir. im a complete beginner though, any one with a better way of doing it is welcome
you could use cell(row, column, value=None) method of openpyxl.worksheet.worksheet.Worksheet instance Returns a cell object based on the given coordinates. Usage: cell(row=15, column=1, value=5) Calling `cell` creates cells in memory when they are first accessed. :param row: row index of the cell (e.g. 4) :type row: int :param column: column index of the cell (e.g. 3) :type column: int :param value: value of the cell (e.g. 5) :type value: numeric or time or string or bool or none :rtype: openpyxl.cell.cell.Cell for itterating through a range of cells
Thanks!
I love Python, but Excel already has VBScript built in. In my work, I haven't had many cases where the python add in was more useful than writing my own macros in VBScript. It's not all that hard a language to learn anyway.
You should always refer to the sheet name you want because it's unknown which sheet is active. Of course you could check the active sheet name to see if you're wasting a function call, or you could just skip the worthless code. Columns above Z need base 26 math. Col 27 is "AA" so you need to compute the quotient and remainder. If quotient is > 26 repeat the process. Does get_column_letter() do that? Col 835 would be 163 base 26 or col "AFC", could be off by one.
the posibilities and list of errors is so good as it helps
@TechWithTim