Read And Write Excel Files In Python Using Openpyxl In PyCharm- Excel Styling and formatting Python

In this video, we will discuss different ways to read excel in python and we will also discuss how to perform write operations as well.
We have many libraries which allow us to read excel but I personally prefer openpyxl. You can also go ahead with panda library.
Python Excel Lib
www.python-exc...
Python Series
• Python Tutorials For B...
Blog
learn-automatio...
All Playlist
/ mukeshotwani
Connect with us:
Linkedin- / mukesh-otwani-93631b99
Instagram- / mukeshotwani
Facebook Group- / 256655817858291
Facebook Page- / seleniumwebdrivermukesh
Twitter- / mukeshotwani
Blog- learn-automatio...

[@Frostbyte-Game-Studio]

how many times have i been saved by indians

[@Mukeshotwani]

Thanks Arvin 🙏🙏

[@mohanapriya4372]

it doesn't matter where we come from.. its all about the effort we put so far.. don't make any controversial thoughts brother..

[@vaibhavparsadi5986]

This is the best lesson I have come across in KZbin!

[@Mukeshotwani]

Thank you Vaibhav

[@ibatuhanozdemirr]

For some reason your video appears as 4th video when i search openpyxl. But this is miles better than the first 3. Great job.

[@Mukeshotwani]

Thank you 😊Batuhan

[@yomismo5711]

Exactly!

[@Professor_Giggles]

Still true 💯

[@BernhardWeber-l5b]

Most elegant Excel reading solution I have found on KZbin. Thank you!

[@Mukeshotwani]

Wow, thank you! Sir

[@Professor_Giggles]

You just taught me something that I didn’t learn in the basics of Python course, thank you!

[@Mukeshotwani]

Thank you so much, I am glad it helped.

[@feliciadzagbletey2191]

I am very glad to add to the comments where people find absolute understanding and even into deeper details of related tutorials like classes for example.... You have explained well with this video I hope to see more of you. Well done sir

[@Mukeshotwani]

Thank you so much Felicia 😍😍😍

[@KC-pi6po]

Perfect explanation for something i should have remembered from my courses a few years ago. I hope many more people find this video, because changing excel-files this way is very useful to me. Please accept my humble upvote and support

[@Mukeshotwani]

Thank you so much Mate .

[@Shortzzz_123]

How can someone be soo perfect in explaining each and every step 😍

[@Mukeshotwani]

Thanks Shabana for such a nice comment. This made my day. Happy Weekend ☺

[@anirudhramaswamy786]

you make stuffs easy to learn !! thanks a lot !

[@Mukeshotwani]

Thank you Anirudh.

[@johncheung4905]

The best tutorial on automating Excel with python. Thank you !!!!!

[@Mukeshotwani]

Thank you John.

[@zakeycart1933]

Thank you so much for your video! This has helped me immensely!! I have been so frustrated not understanding websites explaining the same things that you did.

[@Mukeshotwani]

Thanks Zakey I am really glad to see this comment. Thanks ,I am glad it helped you to understand this concept.

[@samsahoo2006]

Very Nice and detailed. All other videos doesn't tell about the basic. Need to refer to their all other videos to understand what they are doing. I love this tutorial. Solves my specific need.

[@Mukeshotwani]

Thank you Sam 😊

[@SurenderKumar-ov7pd]

Thank you very much. Because of your video I could solve the problem persisting for 2-3 days. Thanks again

[@Mukeshotwani]

Awesome Cheers Surender

[@yomismo5711]

Very Good explanation. Clean, step by step and clear. I recommend this video a lot. Thank you very much! 👍

[@Mukeshotwani]

Glad it helped! Thank you so much.

[@blameItleaveit]

Clean and concise 👌

[@Mukeshotwani]

Thanks Rabin.

[@budisudra]

very clear step by step exlpanation. Good Job

[@Mukeshotwani]

Glad it was helpful! Thanks Budi

[@suppergrant8122]

Great and very useful video !! Thank you, Mukesh!

[@Mukeshotwani]

Glad you liked it

[@harpritsinhyadav7271]

Clearly explained.Thank you for this great tutorial.

[@brijrajkacha9839]

Thank you sir, i attended full session and make a notes of it. Sir, please make a vedio on readig and writing txt file using python.

[@Mukeshotwani]

Hi Briraj, here is the video kzbin.info/www/bejne/hpuVineHrJZjoZo

[@sandeepnallala48]

I came across this channel for 1st time and its really cool to learn these things from such a teacher, thank you sir : ) it helped me. thanks sir : )

[@Mukeshotwani]

Thank you so much Sandeep for such a beautiful comment.

[@mostafaismail43]

Wonderful. Concise and very clear.

[@Mukeshotwani]

Thank you

[@prashanthsriramsetty8998]

explained very well in detail. I highly recommend this video who are trying to learn from scratch.

[@MaiPhoenix]

So clearly explained. I also enjoy it a lot. Thank you!

[@sapankushwaha4069]

simple and shandarr!!!!

[@Mukeshotwani]

Thanks Sapan

[@pradeepraghavan6407]

great tutorial very helpful thanks you very much.

[@Mukeshotwani]

Hi Pradeep Glad it was helpful!

[@AllOut842]

Great video…. Like you presentation a lot

[@Mukeshotwani]

Glad you liked it! Thanks mate

[@ganeshdevarshetty4405]

Bhai Maja aa gaya...

[@Mukeshotwani]

Thanks Ganesh

[@samratkamble5820]

Detailed explanation.. helped me to go from scratch.. thank you..

[@Mukeshotwani]

welcome Samrat

[@alaingoulet4468]

Great presentation. I really like how you make sure we understand what is happening on more difficult coding.

[@Mukeshotwani]

Thanks Alain, this means a lot to me.

[@senthilmanickam1671]

Good lesson I have come across in KZbin! very Nice

[@Mukeshotwani]

Awesome, thank you so much Senthil.

[@SkyiHighh]

I laughed so hard when you were waiting for the excel sheet and didn't show up because of error 'solid'. XD. Great content. This video helped me in my project. Thank you

[@Mukeshotwani]

Glad it helped! Thanks Mate

[@teotia_nitin_81]

Such a good informative video. Please make whole tutorial videos working in excel using python.

[@Scotti.Q]

Hello Mukesh, thanks for this useful and illustrative interaction video between Py.Charm and Excel! I'm not sure I'm convinced by the built-in Python functionality in Excel in actual versions but using Python as a modern "engine" to VBA in order to even more directly interact with the Excel spreadsheets is promising...

[@Imran20091990]

Excellent..very useful 👏👏👏

[@sivaramreddivari1287]

I'm so grateful to you sir..

[@Mukeshotwani]

Thanks Shivaram Keep learning

[@vb9950]

Thank you for the video. Very useful.

[@Mukeshotwani]

Glad it was helpful!

[@josemariavicentetascon8428]

Good Lesson and thank you very much for sharing your knowledge

[@Mukeshotwani]

So nice of you Thanks Jose

[@muporepain]

Excellent videos, but without subtitles I don't understand a thing from this accent... Thank you for the video.

[@Mukeshotwani]

Sorry about that Afrodite , I will try to add subtitles from next videos.

[@muporepain]

@@Mukeshotwani Thank you for your reply and for this great effort you make and you explain everything so nice… I hope I didn’t sound mean… :-)

[@mvvishy]

Very nice tutorial Sir. Thank you.

[@Mukeshotwani]

Thank you Vishy

[@nikitakeche3905]

Thanks for the video.. it cleared my concept

[@niruandhp8772]

Awsome Class

[@Mukeshotwani]

Thanks Mate

[@jiten4486]

Great video! Best I’ve come across after multiple searches. Thank you for this

[@Mukeshotwani]

You're very welcome! Jitendra and I am really glad you like this videos.

[@vikassinghal2103]

Hi Mukesh, I was watching your openpyxl video which is really good. Do you have any video on using openpyxl on POM pattern with Pytest framework. I mean reading and writing data from/into excel using POM and pytest. Could you please share that ?

[@Mukeshotwani]

Complete framework series coming soon Vikas.

[@mustaphamust1100]

VERY NICE VIDEO thank you

[@Mukeshotwani]

Thank you too Mustapha.

[@rzo21]

Great session ..... thank you!!!!

[@sivaramreddivari1287]

Gold 🏆

[@Mukeshotwani]

Thnks

[@bangtap2613]

Mukesh, you have been a huge help, Many thanks, mate. I look forward to viewing your other videos. Thanks !!

[@TomofHelatrobus]

I kept getting errors in pycharm due to the deprecated syntax I was using for openpyxl. I couldn't upgrade to the most recent version of openpyxl because I was using the wrong syntax, but couldn't find the correct syntax until I saw it in this video. Thanks for the help!

[@Mukeshotwani]

Thank you mate I am glad it helped you 👍

[@pavansrinivasvedantham9006]

Very nice explanation... thank you

[@khalidjj2073]

Very nice video! Questions I have a folder with a hundreds of csv files. 1. How can I print out the same row and column and save it to a new file 2. How can I write or update the same row/column with new value Thanks Please more videos...

[@Mukeshotwani]

Hi Khalid, what is the doubt here?

[@khalidjj2073]

No doubt. Just need help with my questions I have a folder with hundreds of csv files ***Looking for puthoncode exampme to achieve Wanted to ask one other question How to move files with a keyword file names to a specific folder. Move similar keyword files from a folder into its own unique folder Thank you Mukesh!

[@basavrajningadali4919]

great explanation brother

[@Mukeshotwani]

Glad you liked it Thanks Basavraj

[@Mukeshotwani]

Glad you liked it Thanks Basavraj

[@jallahkennedy8298]

This is a really good lesson. Thank you for helping me so much.

[@Bipulku2005]

Nice Video.

[@Mukeshotwani]

Thanks for the visit Bipul

[@suryakantthakur1879]

The best one....Thank you very much.

[@Mukeshotwani]

Thank you Suryakant.

[@graceangeline9990]

your are really saved me.... thnx bro

[@Chuquemeka]

Wow! A very big thank you for this.

[@The_YashWanti]

its really good... thnx for this

[@sravansurapaneni5328]

Thank you brother.

[@Mukeshotwani]

Most welcpme Sravan

[@asit05]

Great explation...

[@raviray4660]

Great video 👍.. 1 ques ,how we use a excel data as a input in our webpage..

[@Mukeshotwani]

Hi Ravi, once you get data in variable then you can use in sendKeys() method

[@raviray4660]

@@Mukeshotwani thanks Mukesh. 😇😇

[@soliver111]

Excellent how to demo Thanks

[@Mukeshotwani]

Thank you

[@franklegarreta]

Great tutorial Mukesh! One question though - You showed how to use Openpyxl Patternfill arguments to Write an Excel cell with solid foreground color but what if you wanted to Read the style of an Excel cell? For example - have python test if a cell is greyed out?

[@Mukeshotwani]

Thanks Frank and I am glad you liked it. You can check this article for reading cell style newbedev.com/python-openpyxl-how-to-read-a-cell-font-color

[@franklegarreta]

@@Mukeshotwani Thanks for the info on font color Mukesh, but is it possibly to reliably get the value (preferably in hex) for empty grey filled cells? I have tried using the following but with unpredictable results; cell_under_test.fill.start_color.index

[@mayanktailor2319]

If I give large sized value for any excel cell using python (larger than excel column width), and when open excel sheet, I have to adjust excel column width manually. Is there any command in python which auto adjust the excel column width?

[@Mukeshotwani]

Hey Mayank openpyxl will help read and write but adjust excel column width have not tried.

[@umeshmore8251]

Nice tutorial and very well explained. Thanks 🙏

[@revanthgowd5744]

Thank you the session was usefull 👍

[@Mukeshotwani]

Glad it was helpful! Revanth

[@divya_yerva8974]

Thanks sir for your class

[@Mukeshotwani]

Most welcome Divya

[@RoManov99x]

Thank you sooo much!

[@Mukeshotwani]

Glad it helped! Thanks Bahaa

[@burysandrine6201]

Thank you so much. It was extremely helpful

[@Mukeshotwani]

Thank you Bury 🙏

[@ravibtps]

Thanks for such a nice video. I have a query How we can close the sheet and end the process?

[@nimanshjain6661]

nice video helped me complete my project way faster as using excel helped me a lot I just have a doubt that if I use excel 365 will it work?

[@ontreprenor]

Op bhai

[@Mukeshotwani]

Thanks Bhai

[@nabilnabil1824]

Thank you , Mukesh

[@mohammedaskar4268]

Thanks!

[@Mukeshotwani]

Welcome!

[@arjunkatyal1989]

Very nice explanation. In detail. If possible could you please explain on how to prepare complex graphs in excel using python openpyexcel

[@Mukeshotwani]

Sure Will try Arjun, in the meanwhile please refer below link for charts and graphs. openpyxl.readthedocs.io/en/stable/charts/introduction.html

[@arjunkatyal1989]

Ok thank you

[@АлексКалашников-у6н]

lists=wb.sheetnames list_1 = wb[lists[0]] It's not easier that way? Or you need to know the name of the sheets! Your lessons are great! I like that it's not just the documentation, but to consider the code adapted for convenient use!

[@shreyshrivastava240]

Thank you very much

[@Mukeshotwani]

welcome Shrey 🙂😇🙏

[@shreyshrivastava240]

Sir can u make one on data cleaning and graph plotting

[@Mukeshotwani]

Sure Shrey will try.

[@titututus]

Very nice,plz make more video

[@saurabhrawal1433]

Thank you so much :-)

[@Mukeshotwani]

You're welcome! Saurabh. Keep learning.

[@gautamgovinda5140]

Thanks

[@Mukeshotwani]

Welcome mate

[@KishoreKumar-mh4td]

Great! thanks a lot... how to create multiple sheets with different names fed into the cells of each sheet like mail merge from a list.

[@mb26100]

Very well explained. Thank you ! I included the package Openpyxl. tried exactly the same thing but could not get the .value function when I did it. Any reason why would that happen ?

[@Mukeshotwani]

Hey Munmun, can you tell me what error did you get when you executed the code?

[@mb26100]

@@Mukeshotwani It worked fine. It was a typo. Thank you for your reply !

[@garimad2320]

Hi Mukesh - This is amazing source - Thanks for such a great work. Can you please recommend any library to write to xlsb file ?

[@himanshugupta241]

Thank you so much sir.

[@sanjaygauri8096]

Sir, Your video is really excellent. I am facing one problem and need your help, that is how to write data to excel using python when the file is open and the name of the file should not change. Please Help

[@nihalrahman8233]

thnxxxx brother it helps

[@nisar_pasha]

Can you please upload reading the excel having formula to a particular cell

[@Mukeshotwani]

HI Nisar, please use below data_only=True Example wb = openpyxl.load_workbook(filename, data_only=True)

[@royalwinautomatedsoftware2985]

@@Mukeshotwani hai sir, im using data_only=True . That time automatic remove formula my excel sheet. How solve I keep formula every cell. Please help me

[@swarnay.v6446]

Great explanation sir. Can we import this excel into mysql using openpyxl

[@poornimahanchinal5120]

It was really a perfectly understandable vedio, but I need a deeper knowledge about checking the colour of a cell of existing excel sheet using python please help me with that sir

[@Bindu_Ivaturi]

Thank you for the beautiful video!! Would also like to understand whether is it possible to get some data on condition, like if I want the name of the person where address is India using openpyxl. Thank you!

[@Mukeshotwani]

Yes you can! we need to map this condition in code.

[@satydevnegi5454]

If we have a value in first column and we want to take a value in same row but 5th of 6 th column what coding will be for that

[@Mukeshotwani]

Hi Satydev coding will be same but you need to change cell number only.

[@sanjaybhasker7234]

Can we extract data from opened excel file and save it to another opened excel file in python..?

[@Mukeshotwani]

Yes u can

[@ahmedalkassab9751]

thanks :)

[@tukaramkore9506]

Yes Enjoying

[@naveenkumaranupoju8771]

Hi sir can u please explain how to open excel file automatically from any path?

[@Mukeshotwani]

Hi Naveen, what is your use case? openpyxl will open in memory in will read.

[@ishwariit]

Thanks bro it helped a lot

[@shubharoy007]

How to save multiple entities at the same time using loop?

[@anilthakkar2372]

brother how do we search for a specific string in a list of xlsx files within a folder and print its common list? for. e.g. if I want to search string "Accomplished" in all the xlsx files present In a folder called Task. would be grateful if u could solve this for me.

[@nishavenkatesh7987]

very good explanation

[@Mukeshotwani]

Thanks for liking Nisha

[@pavanbondada1361]

nice video .... if we have more sheet for example 5 sheets and we need to change sheetname dynamically how can we do

[@RashidKhan-tz5ct]

Hi Mukesh, Thanks for the value addition. Please help me in getting the data from cell range like from B2:B17 in an excel sheet. Thanks in advance