@@surajkumaragrawal4065 You don't need to install IDE's like PyCharm. Using VS Code is enough. Also, install the extensions that I recommend in this video. That's enough.
@@surajkumaragrawal4065 Try to do some projects in Python. If you can understand this video, try to automate some of your excel work. I built one beginner level Python project. ua-cam.com/video/-0bIqcayyW8/v-deo.html Once you learn core Python, try to clone that project on your own. That'll be a good practice for you.
Hey Balaji I’m working on a project which need to know if a value exists in excel sheet. I coded it but i want it to say true if one value is found if none is found it should say false pls help
I'm so glad that my video helped you. Check out my channel for more Python-related videos and if you are interested, subscribe to my channel. If you want to start learning Data Science, check out my recent video on NumPy ua-cam.com/video/3qXOTVhsUNY/v-deo.html Once again Thank you for your feedback :)
Hope you keep doing this python videos on libraries. I have just started learning python and i am very comfortable with your speed and the way you just spit all the functions in a simple manner.
This easy-to-understand tutorial is thorough in covering a range of basic operations using openpyxl. (The Coderunner and Excel Viewer extensions to VS Code were also very helpful to know about). Given Excel's popularity with the end users, it's well worth it for any Python developer to explore what can be done with openpyxl. This video is an excellent way to get started. THANK YOU for sharing your knowledge, Balaji.
AttributeError: partially initialized module 'openpyxl' has no attribute 'load_workbook' (most likely due to a circular import) i got this error while doing ......... please resolve this error ....................................
Welcome Bro. Subscribe to my channel for more such videos 🙂. If you want me to take session on any topic let me know. I'll try to create a video on topic also 🙂
Wow man. You improved a lot. I was one of your old subscribers. You asked some tips to improve. I told what I know. Now you speak better. Your voice is clear. You are like a professional youtuber now. Never settle. Go further.
Thanks Aji for your support. Sure, I'll never settle. Sorry for the reply. I somehow missed your notification. This week I'm going to release a video on Python for Data Science. Hope you love that too. Again, Thanks for your support.
@@ProgramWithBalaji Your course is just amazing, I had a school project for election to make using python...But for some reason by SQL and Access both were not working. I was scared as I didn't have a better choice than excel but didn't know how to write and read it through Python. Than I found your course and it helped me a lot....Thank You Balaji Bhaiyya!!!!!
@@nipuntiwari5517 Thank you so much Bhaiya for your positive comment. I'm so happy to hear my video helped you. Support me by liking this video and sharing the videos with your friends. If you want me to create a tutorial on any topic, let me know :)
@@ProgramWithBalaji Thank You!! But in my project this error is coming:- Cannot convert (2, 'Raghav') to Excel my code:- class Library(Tk): def __init__(self): super().__init__() self.geometry("850x650") self.resizable(0,0) self.title("Make Your own custom Library") self.booklist = [] self.dictrecord = {} self.builder_name = '' self.lib_name = '' self.user_info = [] def design(self): def store_and_verify(): # Making a Workbook using openpyxl. lib_records = Workbook() lib_records = load_workbook('Records.xlsx') sheet = lib_records['User Info'] # Converting these user inputs to tuple and appending it to a list called self.user_records. strname = (1,name_val.get()) self.user_info.append(strname) stremail = (2,email_val.get()) self.user_info.append(stremail) strpas = (3, pas_val.get()) self.user_info.append(strpas) strphone = (4, phone_val.get()) self.user_info.append(strphone) for row_indx, value in enumerate(self.user_info): sheet.cell(row=1, column=row_indx+1, value=value) # for row_indx, value in enumerate([name[0] for name in self.user_emails]): # sheet.cell(row=1, column=row_indx, value=value) # # for row_indx, value in enumerate([name[0] for name in self.user_passwords]): # sheet.cell(row=1, column=row_indx, value=value) # # for row_indx, value in enumerate([name[0] for name in self.user_phone_nos]): # sheet.cell(row=1, column=row_indx, value=value) lib_records.save('Records.xlsx') Label(self,text=''' Welcome to Nipun's Custom Library Make your own custom library''',font="Times 30 bold",fg="red").pack() Label(self,text=''' Here are a few things before we get started''',font="calibri 20 italic").pack() Label(self,text="Name",font="calibri 28 bold",fg="green").pack(anchor='w',padx=30,pady=20) Label(self, text="Email", font="calibri 28 bold", fg="green").pack(anchor='w', padx=30, pady=20) Label(self, text="Password", font="calibri 28 bold", fg="green").pack(anchor='w', padx=30, pady=20) Label(self, text="Phone Number", font="calibri 28 bold", fg="green",justify=LEFT).pack(anchor='w', padx=30, pady=20) name_val = StringVar() email_val = StringVar() pas_val = StringVar() phone_val = StringVar() name_val.set("") email_val.set("") pas_val.set("") phone_val.set("") Entry(self,textvar=name_val,borderwidth=5,relief=RIDGE,font="lucida 20 bold").place(x=350,y=265) Entry(self, textvar=email_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=350) Entry(self, textvar=pas_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=445) Entry(self, textvar=phone_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=545) f1 = Frame(self,borderwidth=10,relief=SUNKEN,bg="green") Button(f1,text="Submit",font="impact 20 italic",command=store_and_verify).pack() f1.place(x=710,y=375) A = Library() A.design() A.mainloop()
I created excel file XLSX. through visual studio code only and then trying to open in my folder drive however it is showing error your file extension is not valid and not compatible .i am using excel2010
I need some help, when i try to add an image, an error appears: fp.seek(0) ValueError: I/O operation on closed file, I made sure this was a file not a directory, I did the exact same thing as the tutorial
Hi, As the error says I think the write operation was performed on the closed file. We should close the file only after we perform all the crud operations
I have a usecase in same work book with multiple sheets in a particular column i need to update a formula for a range is it possbile to handle this use case ?? Please help to address
Well done!!! 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) What I really want is to remove the formula for that row only THANKS!!
How can I open an existing excel file with column a and b, calculate the sum of a and b and write it to the f column? In this video I couldn't find an example for that task.
Balaji S plz zoom in the code more. It will make it easier for us to read. Also try to speak a little more slowly while you teach. And lastly thank you for an amazing video.
Thank you for your comment. I'll definitely consider your suggestion for my next video. Keep supporting me by subscribing to my channel and by sharing my videos.
Hey..thank for the valuable information..can u explain me an easy way to add images from a folder to an excel and also how to resize the cell to fit the image
Hi Maggie, Sorry for the late reply. In this video I already told how to load an image file. Kindly check that out. To resize the image use the width and the height property of the image. You have to pass pixel values to them. from openpyxl.drawing.image import Image img = Image('img.jpg') img.width = 50 img.height = 50 Have a great day. Thank you for your feedback. Subscribe to my channel for more videos on Python.
Hi Balaji, Thanks for giving such a good video. I have query below: I m retrieving data from database then converting to pandas dataframe, then again im using same dataframe as input and storing in specific cell of excel. here im using module openpyxl. while storing the data into specific cell of excel ,im getting below error. ValueError : cannot convert 0 to colA value sample code : sheet['E5'] = df['colA'] can you help me to fix this issue
can someone explain this line please: sheet = wb['Sheet'] ??? I also saw it on tkinter when changing the value of text variable. I'm really confusing with that.... what is 'Sheet' ..why it's within [ ]. and what is the type of the sheet variable we created..........?
Hey Dude, By using wb['Sheet'], we can access the WorkSheet named 'Sheet' from your Excel Workbook. wb is an object of Workbook class. Using wb['Sheet'], we are accessing one of the properties of Workbook class to load the corresponding Worksheet. But, it is not possible to use [] to access the properties of an obj. But we need to use dot (.) operator. Suppose if you want to use [] on your custom object means, you need to implement __getitem__() magic method. The Workbook class have implementation like the below one (Note the usage of __getitem__ magic method): class Workbook: def __init__(self): # Load Worksheets to self.sheets self.sheets = {'Sheet': 'Sheet 1 Details'} def __getitem__(self, sheet): return self.sheets.get(sheet, "Sheet") wb = Workbook() print(wb.sheets.get('Sheet')) # Direct Way print(wb['Sheet']) # Shortcut way to access the sheets dictionary Use __getitem__() magic method to use this bracket notation feature directly on your object. But, you can use it to access only one property. Not more than one property. Hope my answer helps. If you have any queries, let me know.
@@ProgramWithBalaji Thanks a lot for your detailed answer with the magic method and all the explanation, I was not familiar with it before ..that it's actually a way to access a dictionary (I also tried to search about it and asked about it on some forum and no one give me detailed answer like yours). It seems to be little complicated.. I guess i should focus on your answer and read about it more.. I will let you know if it's clear. Again..Thanks a lot !!
HiI Balaji, I m an accountant, keen to learn python basically to use in excel work sheets. I dont have any coding background or knowledge. Can you please do a complete video for beginners. Thanks!
I try to do a complete Python tutorial series. Thanks for your suggestion. Python is a good choice and I'm sure you are going to love it a lot. If you have any queries regarding Python or any other programming languages, just let me know.
@@ProgramWithBalaji thanks for such a quick responses. I am trying to learn this language, not sure where do I start, found a lot of material ok UA-cam and other websites. Can you suggest a plan how should I start, as I don't have any coding knowledge or background. Thanks for your help!
@Suraj Kumar Agrawal Learning a Programming Language is just like learning any regional language. Python is a High level Programming Language that is so easy to learn and has very less rules. But, learning alone won't help you. Try to automate your work using Python. You can do a lot with Python.
Hi Balaji, I am new to the python and need to know how to sort one particular column from maximum to minimum in xlsx file using openpyxl. My file is already created and i am loading the file from desktop.
Hope this link helps you openpyxl.readthedocs.io/en/default/filters.html NOTE: OpenPyXl will add the relevant instructions to the file but will neither actually filter nor sort. Hope my answer helps. If something is unclear, let me know :)
Hello Balaji S I am working for a multi national and I want to learn openpyxl. I do not have any idas of programming. Do you have any particular online course which will help me to learn openpyxl in detail.
Can't you able to understand this video. In this video I explained all the core concepts regarding openpyxl. You just need to have python and vs code to follow along. Suppose if you don't have them means, checkout this video ua-cam.com/video/7wyl6fVhQLo/v-deo.html
Balaji S thank you for the quick reply. Actually I do not know the coding part. Which code I need to put all. That basic knowledge I do not have. Appreciate your advise
the problem is where you declared your 'import' make sure you used a capital letter. instead of : wb = openpyxl.workbook() It should be : wb = openpyxl.Workbook()
Simple only Bro. U already know how to read Excel data. Now u need to use selenium, to fetch the login fields and use the Excel data in the send keys method.
@@ProgramWithBalaji thanks bro you already get it now I only need one option which. when you read the first row and perform the actions close the browser window and open another window and copy the email And password from the second row I hope you understand bro
Contents covered in the Video
------------------------------------------------------------------
0. Install OpenPyXl - 01:00
1. Create WorkBook - 01:44
2. Install Extensions to speedup our Development - 02:47
3. Rename Worksheet Name - 05:44
4. Add Data - 06:43
5. Open Existing WorkSheet - 08:49
6. Insert Empty Rows at Specific Row Index - 11:00
7. Update Row - 13:57
8. Remove Row - 17:24
9. Removing Columns - 18:48
10. Insert Columns - 19:21
11. Read Data - 21:46
12. Read Data [One Another Way] - 21:46
13. Update Cell - 25:32
14. Important Properties of Cell Object- 27:33
15. Deleting a Cell - 28:12
16. Access a Range of Cells - 28:39
17.1. Fetching Entire Column - 28:43
17.2. Fetching Entire Row - 29:18
17.3. Fetching a1 to b3 - 29:52
17.4. Fetching a range of Rows - 30:19
17.5. Fetching a range of Columns - 30:34
18. Getting all the Existing Worksheets - 31:08
19. Creating a New WorkSheet - 31:26
20. Challenge - Add Data into a Sheet - 33:13
21. Solution - Add Data into a Sheet - 33:39
22. Add Chart - 33:54
23.1 Fetch Data to add it into the chart - 34:41
23.2 Challenge - Fetch Data - 35:57
23.3 Solution - Fetch Data - 36:09
23.4 Add Data into the Chart - 37:25
23.5 Add Chart to our Sheet - 37:58
23.6 Add Chart - Fixing Errors - 38:20
23.7 Chart Tooltip - 39:14
24. Add Image - 40:16
25. Resize Image - 41:32
26. Format Cells - 42:17
27. Remove WorkSheet - 45:03
28. Good Bye - 45:13
Hope this will helps you :)
I cannot perform these spets in idle, do i need to install pycharm Or any other application?
@@surajkumaragrawal4065 You don't need to install IDE's like PyCharm. Using VS Code is enough. Also, install the extensions that I recommend in this video. That's enough.
@@surajkumaragrawal4065 Try to do some projects in Python. If you can understand this video, try to automate some of your excel work.
I built one beginner level Python project. ua-cam.com/video/-0bIqcayyW8/v-deo.html
Once you learn core Python, try to clone that project on your own. That'll be a good practice for you.
Hey Balaji I’m working on a project which need to know if a value exists in excel sheet. I coded it but i want it to say true if one value is found if none is found it should say false pls help
appreciate your work.
This video on Openpyxl, is hands down the most complete video on UA-cam. I have checked.
Thank so much.
I'm so glad that my video helped you. Check out my channel for more Python-related videos and if you are interested, subscribe to my channel.
If you want to start learning Data Science, check out my recent video on NumPy
ua-cam.com/video/3qXOTVhsUNY/v-deo.html
Once again Thank you for your feedback :)
Hope you keep doing this python videos on libraries. I have just started learning python and i am very comfortable with your speed and the way you just spit all the functions in a simple manner.
Happy to hear that you enjoyed the video. Check out my channel and Please do subscribe for more videos on Python :)
This easy-to-understand tutorial is thorough in covering a range of basic operations using openpyxl. (The Coderunner and Excel Viewer extensions to VS Code were also very helpful to know about). Given Excel's popularity with the end users, it's well worth it for any Python developer to explore what can be done with openpyxl. This video is an excellent way to get started. THANK YOU for sharing your knowledge, Balaji.
Thank you so much for your positive comment. Please support me by liking this video, and sharing this video with others :)
Very nice ..
thanks for the video. I appreciate your work.
Спасибо, друг!
Мда. Русские повсюду))
Very useful bro. Thanks alot.
AttributeError: partially initialized module 'openpyxl' has no attribute 'load_workbook' (most likely due to a circular import)
i got this error while doing .........
please resolve this error ....................................
Bro very nice explanation...looks professional you Tuber....thanx for awesome videos
Welcome Bro. Subscribe to my channel for more such videos 🙂. If you want me to take session on any topic let me know. I'll try to create a video on topic also 🙂
Great Work Balaji. well explained.
Thank you 😊
Thank you from Russia! :)
Welcome 🙂. Subscribe to my channel for more such videos on Python and JavaScript
Thank you so much for the help
I want your valuable feedback as a comment. So, please share your feedback :)
Wow man. You improved a lot. I was one of your old subscribers. You asked some tips to improve. I told what I know. Now you speak better. Your voice is clear. You are like a professional youtuber now. Never settle. Go further.
Thanks Aji for your support. Sure, I'll never settle. Sorry for the reply. I somehow missed your notification.
This week I'm going to release a video on Python for Data Science. Hope you love that too.
Again, Thanks for your support.
@@ProgramWithBalaji Your course is just amazing, I had a school project for election to make using python...But for some reason by SQL and Access both were not working. I was scared as I didn't have a better choice than excel but didn't know how to write and read it through Python. Than I found your course and it helped me a lot....Thank You Balaji Bhaiyya!!!!!
@@nipuntiwari5517 Thank you so much Bhaiya for your positive comment. I'm so happy to hear my video helped you. Support me by liking this video and sharing the videos with your friends. If you want me to create a tutorial on any topic, let me know :)
@@ProgramWithBalaji Thank You!! But in my project this error is coming:-
Cannot convert (2, 'Raghav') to Excel
my code:-
class Library(Tk):
def __init__(self):
super().__init__()
self.geometry("850x650")
self.resizable(0,0)
self.title("Make Your own custom Library")
self.booklist = []
self.dictrecord = {}
self.builder_name = ''
self.lib_name = ''
self.user_info = []
def design(self):
def store_and_verify():
# Making a Workbook using openpyxl.
lib_records = Workbook()
lib_records = load_workbook('Records.xlsx')
sheet = lib_records['User Info']
# Converting these user inputs to tuple and appending it to a list called self.user_records.
strname = (1,name_val.get())
self.user_info.append(strname)
stremail = (2,email_val.get())
self.user_info.append(stremail)
strpas = (3, pas_val.get())
self.user_info.append(strpas)
strphone = (4, phone_val.get())
self.user_info.append(strphone)
for row_indx, value in enumerate(self.user_info):
sheet.cell(row=1, column=row_indx+1, value=value)
# for row_indx, value in enumerate([name[0] for name in self.user_emails]):
# sheet.cell(row=1, column=row_indx, value=value)
#
# for row_indx, value in enumerate([name[0] for name in self.user_passwords]):
# sheet.cell(row=1, column=row_indx, value=value)
#
# for row_indx, value in enumerate([name[0] for name in self.user_phone_nos]):
# sheet.cell(row=1, column=row_indx, value=value)
lib_records.save('Records.xlsx')
Label(self,text='''
Welcome to Nipun's Custom Library
Make your own custom library''',font="Times 30 bold",fg="red").pack()
Label(self,text='''
Here are a few things before
we get started''',font="calibri 20 italic").pack()
Label(self,text="Name",font="calibri 28 bold",fg="green").pack(anchor='w',padx=30,pady=20)
Label(self, text="Email", font="calibri 28 bold", fg="green").pack(anchor='w', padx=30, pady=20)
Label(self, text="Password", font="calibri 28 bold", fg="green").pack(anchor='w', padx=30, pady=20)
Label(self, text="Phone Number", font="calibri 28 bold", fg="green",justify=LEFT).pack(anchor='w', padx=30, pady=20)
name_val = StringVar()
email_val = StringVar()
pas_val = StringVar()
phone_val = StringVar()
name_val.set("")
email_val.set("")
pas_val.set("")
phone_val.set("")
Entry(self,textvar=name_val,borderwidth=5,relief=RIDGE,font="lucida 20 bold").place(x=350,y=265)
Entry(self, textvar=email_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=350)
Entry(self, textvar=pas_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=445)
Entry(self, textvar=phone_val, borderwidth=5, relief=RIDGE, font="lucida 20 bold").place(x=350, y=545)
f1 = Frame(self,borderwidth=10,relief=SUNKEN,bg="green")
Button(f1,text="Submit",font="impact 20 italic",command=store_and_verify).pack()
f1.place(x=710,y=375)
A = Library()
A.design()
A.mainloop()
Thank you, very much. It was very usefull.
Welcome. Thanks for your comment. Checkout my channel for more Python related videos.
Good
I want the script if I press 1 key button and whatever I script write it performed
Hi, I want to search a particular text in the sheet and then copy that entire row to a new sheet. Can you please help me on this using openpyxl
Here is my new tutorial on CSV parsing using Python - ua-cam.com/video/8z61LhMsyDM/v-deo.html
I created excel file XLSX. through visual studio code only and then trying to open in my folder drive however it is showing error your file extension is not valid and not compatible .i am using excel2010
Want to learn Python for Data Science. Here is my new video ua-cam.com/video/3qXOTVhsUNY/v-deo.html
thank you so much!
Thank you!
Welcome 🙂. If u want me to create video on any topic, let me know.
doing awesome job.. Can you help me !! how to create a new sheet with loop and store particular data in that created sheet ????
Thank you. I didn't get your question. You want to create many worksheets using loop?
I need some help, when i try to add an image, an error appears: fp.seek(0)
ValueError: I/O operation on closed file, I made sure this was a file not a directory, I did the exact same thing as the tutorial
Hi, As the error says I think the write operation was performed on the closed file. We should close the file only after we perform all the crud operations
how can we get values of the rows from the returned tuple after accessing entire row
Hi Priyanka, I didn't get what's your question. I already covered how to access row values in the video. I think it's in 29:21
can't use the max_row...
I have a usecase in same work book with multiple sheets in a particular column i need to update a formula for a range is it possbile to handle this use case ?? Please help to address
How to use vlookup in python. Pls make video
Well done!!!
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)
What I really want is to remove the formula for that row only
THANKS!!
How can I open an existing excel file with column a and b, calculate the sum of a and b and write it to the f column? In this video I couldn't find an example for that task.
How to append the data from a different rows and place it in another cell
Hi Balaji ,
Getting error : add_data() missing 1 required positional argument: 'data'
python3.8
from openpyxl import load_workbook
from openpyxl.chart import (LineChart, Reference, series)
wb = load_workbook("Hello.xlsx")
sheet = wb.active
data = [["no", "media", "no_of_followers"],[1, "twitter", 250],[2,"insta", 275]]
chart = LineChart
values = Reference(sheet, min_col=3, min_row=2, max_col=sheet.max_column, max_row=sheet.max_row)
categories = Reference(sheet, min_col=2, min_row=2, max_row=sheet.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)
sheet.add_chart(chart, "F2")
wb.save("Hello.xlsx")
got it , Thank you its chart= ineChart()
Yes. It should be LineChart()
If you want me to create a video on any topic, please let me know
Hi Balaji,
not sure why..but .save attribute is not working
Hi, could you please tell which you used in VScode in this video.
Hello, I didn't get your question? Are you asking about the theme?
@@ProgramWithBalaji yes the theme used only
@@karthikrajamony6044 Dracula Theme
@@ProgramWithBalaji thank you
Good one bro
Thanks Jay
Balaji S plz zoom in the code more. It will make it easier for us to read. Also try to speak a little more slowly while you teach. And lastly thank you for an amazing video.
Thank you for your comment. I'll definitely consider your suggestion for my next video. Keep supporting me by subscribing to my channel and by sharing my videos.
In an Excel Read Image data in each row and move to server is possible
Sorry dude. I didn't get you. U want to send the Excel data to some API??
Hey..thank for the valuable information..can u explain me an easy way to add images from a folder to an excel and also how to resize the cell to fit the image
Hi Maggie,
Sorry for the late reply. In this video I already told how to load an image file. Kindly check that out. To resize the image use the width and the height property of the image. You have to pass pixel values to them.
from openpyxl.drawing.image import Image
img = Image('img.jpg')
img.width = 50
img.height = 50
Have a great day. Thank you for your feedback. Subscribe to my channel for more videos on Python.
Thanks dude ❤
@@maggieb9269 Welcome. If you want my help in Python or in Angular/React, reach me out through mail
Hi Balaji,
I got below error while running the code.
Hi Balaji,
Thanks for giving such a good video.
I have query below:
I m retrieving data from database then converting to pandas dataframe, then again im using same dataframe as input and storing in specific cell of excel.
here im using module openpyxl. while storing the data into specific cell of excel ,im getting below error.
ValueError : cannot convert 0 to colA value
sample code :
sheet['E5'] = df['colA']
can you help me to fix this issue
use sheet['E5'].value = df['colA']
Note this actually paste the Dataframe called to the cell mentioned.
Hai Balaji
Helloooo Vijay :)
Hello XD
can someone explain this line please: sheet = wb['Sheet'] ???
I also saw it on tkinter when changing the value of text variable.
I'm really confusing with that.... what is 'Sheet' ..why it's within [ ]. and what is the type of the sheet variable we created..........?
Hey Dude,
By using wb['Sheet'], we can access the WorkSheet named 'Sheet' from your Excel Workbook.
wb is an object of Workbook class. Using wb['Sheet'], we are accessing one of the properties of Workbook class to load the corresponding Worksheet. But, it is not possible to use [] to access the properties of an obj. But we need to use dot (.) operator.
Suppose if you want to use [] on your custom object means, you need to implement __getitem__() magic method.
The Workbook class have implementation like the below one (Note the usage of __getitem__ magic method):
class Workbook:
def __init__(self):
# Load Worksheets to self.sheets
self.sheets = {'Sheet': 'Sheet 1 Details'}
def __getitem__(self, sheet):
return self.sheets.get(sheet, "Sheet")
wb = Workbook()
print(wb.sheets.get('Sheet')) # Direct Way
print(wb['Sheet']) # Shortcut way to access the sheets dictionary
Use __getitem__() magic method to use this bracket notation feature directly on your object. But, you can use it to access only one property. Not more than one property.
Hope my answer helps. If you have any queries, let me know.
@@ProgramWithBalaji Thanks a lot for your detailed answer with the magic method and all the explanation, I was not familiar with it before ..that it's actually a way to access a dictionary (I also tried to search about it and asked about it on some forum and no one give me detailed answer like yours). It seems to be little complicated.. I guess i should focus on your answer and read about it more.. I will let you know if it's clear. Again..Thanks a lot !!
Plz make complete course from biggin to last
Hey Dude, You want a course for what?
HiI Balaji,
I m an accountant, keen to learn python basically to use in excel work sheets. I dont have any coding background or knowledge. Can you please do a complete video for beginners. Thanks!
I try to do a complete Python tutorial series. Thanks for your suggestion. Python is a good choice and I'm sure you are going to love it a lot.
If you have any queries regarding Python or any other programming languages, just let me know.
@@ProgramWithBalaji thanks for such a quick responses. I am trying to learn this language, not sure where do I start, found a lot of material ok UA-cam and other websites. Can you suggest a plan how should I start, as I don't have any coding knowledge or background. Thanks for your help!
@Suraj Kumar Agrawal Learning a Programming Language is just like learning any regional language. Python is a High level Programming Language that is so easy to learn and has very less rules.
But, learning alone won't help you. Try to automate your work using Python. You can do a lot with Python.
Thank you from da hood!
Welcome 🙂
Hi Balaji,
I am new to the python and need to know how to sort one particular column from maximum to minimum in xlsx file using openpyxl. My file is already created and i am loading the file from desktop.
Hope this link helps you openpyxl.readthedocs.io/en/default/filters.html
NOTE: OpenPyXl will add the relevant instructions to the file but will neither actually filter nor sort.
Hope my answer helps. If something is unclear, let me know :)
@@ProgramWithBalaji thanks for sharing it. However ,script is completed successfully but not generating the output
@@sadhananarwade984 Which script? Got any error?
The script shared in link I used same sort of code which run successfully but no output
Hello Balaji S I am working for a multi national and I want to learn openpyxl. I do not have any idas of programming. Do you have any particular online course which will help me to learn openpyxl in detail.
Can't you able to understand this video. In this video I explained all the core concepts regarding openpyxl. You just need to have python and vs code to follow along. Suppose if you don't have them means, checkout this video ua-cam.com/video/7wyl6fVhQLo/v-deo.html
Did my answer helps you?
Balaji S thank you for the quick reply. Actually I do not know the coding part. Which code I need to put all. That basic knowledge I do not have. Appreciate your advise
Sorry Dude. I don't have any videos to teach Python from beginning. I'm sorry for that. But I can guide in your learning.
@@ProgramWithBalaji Thank you very much. sure i start to learn if i have any question please assist me
How to rename column name in openpyxl...
Any code is there ??
Hi Dude, It's just like updating other cells. I covered that topic in this video.
@@ProgramWithBalaji thanks for reply i got it dude
@@sakthiaravinth7633 Welcome 😀
Sir how can we transfer data from internet to excel through python openpyxl
Sorry Akash for the late reply. Did u get the answer for the question? Or u want my help.
i got error in starting when i start to save excel file " module object is not callable" give me solutuin of my problem
Can you share the code that you used for saving the workbook?
@@ProgramWithBalaji
import openpyxl
wb = openpyxl.workbook()
wb.save("testrun.xlsx")
the problem is where you declared your 'import'
make sure you used a capital letter.
instead of :
wb = openpyxl.workbook()
It should be :
wb = openpyxl.Workbook()
Can you teach us learn xlwings?
I'll try to do a video on that :)
Hi bro How can I log in to facebook with email and password in excel ? Python and selenium and openpyxl. Help pls bro
Simple only Bro. U already know how to read Excel data. Now u need to use selenium, to fetch the login fields and use the Excel data in the send keys method.
@@ProgramWithBalaji thanks bro you already get it now I only need one option which. when you read the first row and perform the actions close the browser window and open another window and copy the email And password from the second row I hope you understand bro
Resound coming bro
Oh, Sorry Bro. For my future videos, I'll try to remove echo, and background noise. But, other than the resound, u want me to improve anything else.
@@ProgramWithBalaji No need bro
Please next time zoom in your screen so we don't have difficulty in viewing
Sorry for the inconvenience. Sure, I'll increase the font size for my future videos 🙂
openpyxl is very slow !
its not good
Oh. I'm sorry. Can you please share the reasons? How can I make it good?