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

Рет қаралды 169,606

3 жыл бұрын

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-excel.org/
Python Series
• Python Tutorials For B...
Blog
learn-automation.com/
All Playlist
/ mukeshotwani
Connect with us:
Linkedin- / mukesh-otwani-93631b99
Instagram- / mukeshotwani
Facebook Group- / 256655817858291
Facebook Page- / seleniumwebdrivermukesh
Twitter- / mukeshotwani
Blog- learn-automation.com

Пікірлер: 298

@Frostbyte-Game-Studio 2 жыл бұрын

how many times have i been saved by indians

@Mukeshotwani 2 жыл бұрын

Thanks Arvin 🙏🙏

@mohanapriya4372 2 жыл бұрын

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

@ibatuhanozdemirr 3 жыл бұрын

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 3 жыл бұрын

Thank you 😊Batuhan

@yomismo5711 2 жыл бұрын

Exactly!

@Professor_Giggles 4 ай бұрын

Still true 💯

@dhaval1489 3 жыл бұрын

It's great resource most other tutorial cover pandas or buildin excel library, this first video discussing openpyxl, keep the great job

@harpritsinhyadav7271 3 жыл бұрын

Clearly explained.Thank you for this great tutorial.

@KC-pi6po 3 жыл бұрын

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 3 жыл бұрын

Thank you so much Mate .

@vaibhavparsadi5986 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thank you Vaibhav

@BernhardWeber-l5b 2 жыл бұрын

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

@Mukeshotwani 2 жыл бұрын

Wow, thank you! Sir

@jiten4486 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

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

@alaingoulet4468 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thanks Alain, this means a lot to me.

@Professor_Giggles 4 ай бұрын

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

@Mukeshotwani 4 ай бұрын

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 😍😍😍

@yomismo5711 2 жыл бұрын

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

@Mukeshotwani 2 жыл бұрын

Glad it helped! Thank you so much.

@MaiPhoenix Жыл бұрын

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

@Chuquemeka 3 жыл бұрын

Wow! A very big thank you for this.

@prashanthsriramsetty8998 2 жыл бұрын

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

@umeshmore8251 3 жыл бұрын

Nice tutorial and very well explained. Thanks 🙏

@samratkamble5820 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

welcome Samrat

@johncheung4905 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thank you John.

@SurenderKumar-ov7pd 2 жыл бұрын

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

@Mukeshotwani 2 жыл бұрын

Awesome Cheers Surender

@zakeycart1933 2 жыл бұрын

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 2 жыл бұрын

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

@Shortzzz_123 2 жыл бұрын

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

@Mukeshotwani 2 жыл бұрын

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

@sandeepnallala48 2 жыл бұрын

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 2 жыл бұрын

Thank you so much Sandeep for such a beautiful comment.

@bangtap2613 2 жыл бұрын

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

@samsahoo2006 3 жыл бұрын

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 3 жыл бұрын

Thank you Sam 😊

@KishoreKumar-mh4td 3 жыл бұрын

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.

@anirudhramaswamy786 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thank you Anirudh.

@burysandrine6201 3 жыл бұрын

Thank you so much. It was extremely helpful

@Mukeshotwani 3 жыл бұрын

Thank you Bury 🙏

@pavanbondada1361 3 жыл бұрын

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

@vamseekrishna2389 2 жыл бұрын

Thanks for the very informative video..., I have one question when I am counting on the number of rows it gives me the exact number, but sometimes I have merged rows and how to count in such scenario?

@garimad2320 3 жыл бұрын

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

@josemariavicentetascon8428 3 жыл бұрын

Good Lesson and thank you very much for sharing your knowledge

@Mukeshotwani 3 жыл бұрын

So nice of you Thanks Jose

@Scotti.Q 6 ай бұрын

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...

@jallahkennedy8298 Жыл бұрын

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

@vikassinghal2103 3 жыл бұрын

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 3 жыл бұрын

Complete framework series coming soon Vikas.

@blameItleaveit 3 жыл бұрын

Clean and concise 👌

@Mukeshotwani 3 жыл бұрын

Thanks Rabin.

@ishwariit 3 жыл бұрын

Thanks bro it helped a lot

@nihalrahman8233 3 жыл бұрын

thnxxxx brother it helps

@deekshakatiyar8380 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thank you Deeksha, sure will try to make more video on python with excel.

@nikitakeche3905 2 жыл бұрын

Thanks for the video.. it cleared my concept

@suppergrant8122 2 жыл бұрын

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

@Mukeshotwani 2 жыл бұрын

Glad you liked it

@senthilmanickam1671 3 жыл бұрын

Good lesson I have come across in KZfaq! very Nice

@Mukeshotwani 3 жыл бұрын

Awesome, thank you so much Senthil.

@rzo21 Жыл бұрын

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

@mostafaismail43 Жыл бұрын

Wonderful. Concise and very clear.

@Mukeshotwani Жыл бұрын

Thank you

@brijrajkacha9839 2 жыл бұрын

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 2 жыл бұрын

Hi Briraj, here is the video kzfaq.info/get/bejne/htBijKeC3JeUm2w.html

@RashidKhan-tz5ct 2 жыл бұрын

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

@mohamedshahidomar3682 2 жыл бұрын

Which python package should be used for reading '.xls' format ?

@budisudra 2 жыл бұрын

very clear step by step exlpanation. Good Job

@Mukeshotwani 2 жыл бұрын

Glad it was helpful! Thanks Budi

@suryakantthakur1879 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

Thank you Suryakant.

@anilthakkar2372 2 жыл бұрын

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.

@pavansrinivasvedantham9006 2 жыл бұрын

Very nice explanation... thank you

@dhaval1489 3 жыл бұрын

24:58 - python uses 0 indexing, so instead of increasing variable by adding one, you should have changed 1 to 0, it is a standard practice

@lukeyxo 2 жыл бұрын

changing 1 to 0 (which i assume youre talking about the start of the range) would not work because then it would call cell 0,0 which does not work as the first cell is 1,1

@graceangeline9990 2 жыл бұрын

your are really saved me.... thnx bro

@dineshbabu-xp1sw 3 жыл бұрын

Hi Sir, I would like to search/find a text in a column and get its cell address/co-ordinates using Openpyxl. Could you please help me on this?

@Imran20091990 2 жыл бұрын

Excellent..very useful 👏👏👏

@mb26100 3 жыл бұрын

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 3 жыл бұрын

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

@mb26100 3 жыл бұрын

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

@vb9950 10 ай бұрын

Thank you for the video. Very useful.

@Mukeshotwani 10 ай бұрын

Glad it was helpful!

@TomofHelatrobus 3 жыл бұрын

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 3 жыл бұрын

Thank you mate I am glad it helped you 👍

@AllOut842 2 жыл бұрын

Great video…. Like you presentation a lot

@Mukeshotwani 2 жыл бұрын

Glad you liked it! Thanks mate

@The_YashWanti 2 жыл бұрын

its really good... thnx for this

@Drone_Achayan 2 жыл бұрын

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 2 жыл бұрын

Glad it helped! Thanks Mate

@pradeepraghavan6407 2 жыл бұрын

great tutorial very helpful thanks you very much.

@Mukeshotwani 2 жыл бұрын

Hi Pradeep Glad it was helpful!

@msscreations7151 2 жыл бұрын

Hi ..is it possible to create worksheets also ?

@filzahhashmi5591 3 жыл бұрын

How do I store a range of cells in a variable from excel in pycharm???

@titututus 3 жыл бұрын

Very nice,plz make more video

@sivaramreddivari1287 2 жыл бұрын

I'm so grateful to you sir..

@Mukeshotwani 2 жыл бұрын

Thanks Shivaram Keep learning

@mayanktailor2319 3 жыл бұрын

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 3 жыл бұрын

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

@mvvishy 2 жыл бұрын

Very nice tutorial Sir. Thank you.

@Mukeshotwani 2 жыл бұрын

Thank you Vishy

@AbhishekMishrawillshine 3 жыл бұрын

How do we format entire column.. say a column with date and time in excel and if i want to change it to specific date format then how do i read and write in python?

@narendramishra3503 Жыл бұрын

Hey Mukesh. I have an xlsm file which I am conditionally formatting on certain conditions. When I am saving the result to another excel sheet the background color, fonts and other things are not retained. I want them to be as original format. Can anyone help me with this?

@Bindu_Ivaturi 3 жыл бұрын

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 3 жыл бұрын

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

@nabilnabil1824 3 жыл бұрын

Thank you , Mukesh

@NagaRaju-se5ov 2 жыл бұрын

Hi Mukesh I am following ur videos, ur videos are excellent and thanks for ur time.. I need one help on Excel in python.. We may have around 3000 to 4000 scripts in the framework and sometimes more also based on the application that we are automating . Now question is if we want to retrieve 2590 record from excel sheet , it may be take sometime for retrieval right ? Can we consider Excel sheet as database so that data retrieval will be faster and execution wont take much time also. How can we do this scenario in python . Could you please help and Thanks in Advance..

@divya_yerva8974 3 жыл бұрын

Thanks sir for your class

@Mukeshotwani 3 жыл бұрын

Most welcome Divya

@asit05 2 жыл бұрын

Great explation...

@msinzamam413 2 жыл бұрын

Sir, when i tried.. It showing typeerror string indices must be integers

@shubharoy007 3 жыл бұрын

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

@himanshugupta241 2 жыл бұрын

Thank you so much sir.

@ravibtps Жыл бұрын

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

@poornimahanchinal5120 3 жыл бұрын

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

@arkabose89 3 жыл бұрын

I want to write/update value to a range in sn existing excel worksheet by inputting a dataframe. Is that possible?

@Mukeshotwani 3 жыл бұрын

Hi Arka never tried this but I guess it can be done check Openpyxl doc openpyxl.readthedocs.io/en/stable/

@arkabose89 3 жыл бұрын

@@Mukeshotwani yes, by combining pandas and using openpyxl as engine it can be done

@satydevnegi5454 3 жыл бұрын

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 3 жыл бұрын

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

@nimanshjain6661 2 жыл бұрын

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?

@franklegarreta 2 жыл бұрын

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 2 жыл бұрын

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 2 жыл бұрын

@@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

@sapankushwaha4069 2 жыл бұрын

simple and shandarr!!!!

@Mukeshotwani 2 жыл бұрын

Thanks Sapan

@MOHDATIF-ix4gr 10 ай бұрын

Can I copy data from one excel file to paste on another new excel file??

@arjunkatyal1989 3 жыл бұрын

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

@Mukeshotwani 3 жыл бұрын

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

@arjunkatyal1989 3 жыл бұрын

Ok thank you

@user-fi8zh3hp4t Жыл бұрын

can we save .xlsm file also using comment wb.save("Same file path want to override") i tried but file got corrupt after run this command..is there any other way to override to save .xlsm file?

@durgeshgautam1376 2 жыл бұрын

Please tell me how are you adding that excel file in ur pycharm

@revanthgowd5744 2 жыл бұрын

Thank you the session was usefull 👍

@Mukeshotwani 2 жыл бұрын

Glad it was helpful! Revanth

@saurabhrawal1433 3 жыл бұрын

Thank you so much :-)

@Mukeshotwani 3 жыл бұрын

You're welcome! Saurabh. Keep learning.

@mustaphamust1100 3 жыл бұрын

VERY NICE VIDEO thank you

@Mukeshotwani 3 жыл бұрын

Thank you too Mustapha.

@RoManov99x 2 жыл бұрын

Thank you sooo much!

@Mukeshotwani 2 жыл бұрын

Glad it helped! Thanks Bahaa

@edgbaston149 2 жыл бұрын

Thank you so much sir

@thewanderersolo8069 2 жыл бұрын

i want text boxes description boxes , titles , radio buttons ,check boxes , please help

@swarnay.v6446 Жыл бұрын

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

@prakashs4301 Жыл бұрын

Hi,after operation with openpyxl .iam not able to open the excel file . The file opens, but the formatting isn't correct. After hitting recover, excel reports that there was an issue with the table xml. Is there a workaround/fix for this? Can you please assist on this .