Python OpenPyXL Tutorial 🔥: modifying Excel files with Python | Python Excel automation

This Python Openpyxl Tutorial is about How to modify Excel (XLSX) files with Python and openpyxl library, and how to save modified data to a separate Excel XLSX file with Python.
This Python Excel automation and Python Openpyxl Tutorial is a quite detailed explanation how to automate Excel with Python, and fit for absolute beginners.
The Python for Excel and Openpyxl Tutorial includes the following topics:
- how to open Excel files with Python Openpyxl library
- how to get an active spreadsheet
- how to get a value of cells in Excel with Python
- how to save data to an Excel XLSX file with Python
- how to keep Excel formulas in cells when saving to a new file.
Follow me @:
Telegram: t.me/red_eyed_coder_club
Twitter: / codereyed
Facebook: redeyedcoderclub
Timecodes:
00:00 - Beginning. The description of the project.
00:40 - Installing Python openpyxl library
01:08 - Reading an excel file with openpyxl library.
01:39 - Getting a working spreadsheet to get data from.
02:46 - The structure of Sheet objects. Iterating through Sheet, and row objects
04:07 - Specifying the range of cells to get.
04:49 - Writing new data to the excel file
06:48 - Saving modified excel file with openpyxl library.
** Web Scraping course **
is available via Patreon here:
/ red_eyed_coder_club
or its landing:
red-eyed-coder-club.github.io...
See Also:
How to read XLSX files and convert them to a list of dictionaries:
• Python OpenPyXL Tutori...
Python CSV tutorial:
• Python CSV tutorial: H...
➥➥➥ SUBSCRIBE FOR MORE VIDEOS ➥➥➥
Red Eyed Coder Club is the best place to learn Python programming and Django:
Subscribe ⇢ / @redeyedcoderclub
Python Openpyxl Tutorial: modifying Excel files | Python Excel automation
• Python OpenPyXL Tutori...
#pythonexcel #openpyxl #openpyxltutorial #pythonopenpyxl #pythonexcelautomation

[@RedEyedCoderClub]

What video should I make next? Any suggestions? *Write me in comments!*
Follow me @:
Telegram: t.me/red_eyed_coder_club
Twitter: twitter.com/CoderEyed
Facebook: fb.me/redeyedcoderclub
Help the channel grow! Please Like the video, Comment, SHARE & Subscribe!

[@RubenRodriguez-jx4sc]

Just what I was looking for!! THANK YOU.

[@RedEyedCoderClub]

Thanks for watching!

[@user-fs3yf5gq9i]

Thank you for the detailed video lesson, now I understand what's what, thank you again for your efforts.... helping people understand !

[@RedEyedCoderClub]

Thank you very much!

[@darkgll8356]

Thank you! That's a brilliant tutorial!

[@RedEyedCoderClub]

Thanks for watching and your comment!

[@olyfomina9927]

You are a wonderful person, you do such useful and very necessary video lessons, Thank you very much!!!

[@RedEyedCoderClub]

Thank you, and have a nice day!

[@BernhardWeber-l5b]

Perfect! Exactly what I need! 👍👍

[@RedEyedCoderClub]

Great! Glad that it helped :D

[@lisw22]

An excellent guide. there is a lot to learn! accessible and understandable is class.

[@RedEyedCoderClub]

Thank you!

[@docmalc47]

I would like to give a BIG THANK YOU for this video.
Certain parts were tough to understand due to accent but thats what captions are for =)
You really did a great job with this video and how you explained things... again thank you, this video saved me!

[@docmalc47]

If I could kindly offer 1 piece of feedback (from someone who has watched your video 8+ times in 36 hours lol).... you seem to speak at a perfect speed while you're explaining things in general however, when you start to explain your code piece by piece you tend to talk faster. Coupled with the accent makes it a bit tougher. I typically have captions on all the time and I realized as I was trying to read the captions it would go faster at those times.
You do a great job in how you use tech/coding terminology (ie enumerate, element of row list, etc) because it makes sense how you lay it out. But thats where the faster talking, accent, and trying to process what you're saying posed a challenge for me.
BUT that could just me, I'm a noob =)
Nothing to cast any negativity on the video, it was great... just a bit of feedback from someone who is grateful for the help!
Thanks!

[@RedEyedCoderClub]

Thank you for your review! I will try to speak slower and more clear

[@docmalc47]

@@RedEyedCoderClub I think you did great buddy

[@user-ho6ul2vp2d]

thank you for your efforts to help people with very interesting and useful information!!!

[@RedEyedCoderClub]

Thank you very much!

[@user-fw4od8cg5s]

Thanks for the video... This week I will definitely need your video hint!

[@RedEyedCoderClub]

Thank you very much!

[@mohithkailash]

Exact video I want, thank you man

[@RedEyedCoderClub]

Thanks for watching and your comment!

[@user-jp1dp5me2h]

The video is great! It was interesting.👍👍👍

[@RedEyedCoderClub]

Thank you!

[@kostya9169]

Cool video tutorial on Python Openpyxl.

[@RedEyedCoderClub]

Thank you!

[@kostya9169]

Thanks a lot for the video tutorial, it helped a lot.

[@RedEyedCoderClub]

Thank you for the comment!

[@fritzsierratintaya4911]

Excelent video!!!

[@RedEyedCoderClub]

Thank you!

[@sergioscabarneto6690]

Thank you very much!

[@RedEyedCoderClub]

Thank you!

[@kostya9169]

Thank you very much for the interesting and informative video.

[@RedEyedCoderClub]

Thank you too!

[@namansharma3020]

Thanks a lot for the Video :)

[@RedEyedCoderClub]

Thanks for watching!

[@bilalmsd07]

how to add the data in the existing name table 'source', I tried to add the data to the named table 'source' but later when I open the file data is there but named table "source" isn't there anymore but data is. I need named table 'source' as well. how can I do that ?

[@kostya9169]

Great video, very interesting.

[@RedEyedCoderClub]

Thanks for your comment!

[@BesteFakoglu]

Thank you so much for this informative video! I tried to update also the columns via "enumerate" but it didn't work since the index always becomes zero. Do you know how to change also the columns?

[@RedEyedCoderClub]

Thanks for the comment. And you always can to subtract 1 to get an appropriate index.

[@zaynmunshi5229]

Is it possible to update the cells dynamically use iter_row ? I have a for loop that iterated between a min_row and max_row. The problem is that when I print (row[0]), it correctly prints the value of the first cell in each row between the selected min and max. But i want to now use the same for loop with iter_row to dynamically update the value of row[0] in each row between the selected min and max. Thats where my code is failing. Any help will be much appreciated.

[@RedEyedCoderClub]

Sorry, can't say anything without source code

[@acerswift4386]

Does openpyxl library working without installed MS Office or OpenOffice?

[@RedEyedCoderClub]

Of course, no need to have installed them

[@kushbaba123]

How can we write in columns first then rows?

[@RedEyedCoderClub]

Don't see a problem to get rows, and the get each cell... Looks something like this:
for row in rows:
.... first_column = row[0]
or a list comprehension can be something like this:
first_column = [row[0] for row in rows]

[@abdulaimohamed8281]

Great stuff!
How do I dynamically select a range and pasteSpecial - make its values equal to itself
import openpyxl
wb=openpyxl.load_workbook(path1)
sheet = wb['Sheet1']
names=sheet['A']
for cellObj in names:
val = str(cellObj.value)
if val[0] == "=":
#print(val)
excel.Range(dynamic range).Select() #PART I WANT TO SELECT IF TRUE
excel.Selection.PasteSpecial(Paste=-4163)
THANKS!!

[@RedEyedCoderClub]

Thank you for the comment. I didn't get what is "dynamically select a range..." What do you want to achieve?

[@srivatsansriram7620]

Not working on mac
It says no such directory exit

[@RedEyedCoderClub]

It should work. Try to check your code

[@shivshankervijikumar9883]

Is there a way password protect an excel file using openpyxl

[@RedEyedCoderClub]

TBH I don't know

[@iiAbdullah635]

didnt work
it tell me
print(cell.vaule)
AttributeError: 'Cell' object has no attribute 'vaule'

[@RedEyedCoderClub]

Maybe you have a typo?
print(cell.value)
You wrote 'vaule' instead of 'value'

[@parassharma3567]

use sublime instead of atom it'll work
it happened with me too

[@vladimir7759]

how to split values from a cell into multiple cells?

[@RedEyedCoderClub]

1. Read a cell
2. Use .split() method to split the string. For example, some_string.split(',')
3. Write the list that you'll get from the splitting to a new row

[@vladimir7759]

@@RedEyedCoderClub You are god, thank you.

[@RedEyedCoderClub]

Thank you