Python: How to add value to a specific excel sheet using openpyxl?

Question

So I wanted to add a value to a specific sheet in this case we have 2 sheets, Student Profile and Student Grades

from openpyxl import load_workbook

file = load_workbook("Registry.xlsx")
sheet = file.active
sheet.create_sheet("Student Grades")
#Student Profile
sheet["A1"] = "ID No.:"
sheet["B1"] = "Last Name:"
sheet["C1"] = "First Name:"
sheet["D1"] = "Middle Name:"
sheet["E1"] = "Sex:"
sheet["F1"] = "Date of Birth:"
file.save("Registry.xlsx")

but if we do it again with for Student Grades it just overrides the previous values of Student Profile since it stays in the sheet1

file = load_workbook("Registry.xlsx")
sheet = file.active
sheet["A1"] = "ID No:"
sheet["B1"] = "Math"
sheet["C1"] = "Science:"
sheet["D1"] = "English"
file.save("Registry.xlsx")

I tried to do:

sheet = file["Student Grades"].active
#or
sheet["Student Grades", "A1"] = "ID No."

but it returns as an error. But I noticed, if you accessed sheet 2, Student Grades, using Excel and saved before exiting. It places the value on sheet 2 instead 1. The problem is I need it to be fully automated so I can't access excel beforehand.

Edit: This is small part of a project with CRUD functions where we're restricted from using databases like sql. So we have to resort to excel instead. Since it have CRUD functions, excel file needs to be constantly updating when you have a added value to it. So i have to get that specific sheet and edit the cell values within it

Does this answer your question? [Get sheet by name using openpyxl](https://stackoverflow.com/questions/36814050/get-sheet-by-name-using-openpyxl) — Dennis Williamson, Nov 26 '22 at 13:10
ValueError: Inventory is not a valid coordinate or range and AttributeError: 'Worksheet' object has no attribute 'active' respectively — dioscuri, Nov 26 '22 at 13:14
No, I want to add values in a specific cell in a specific sheet — dioscuri, Nov 26 '22 at 13:15

score 0 · Answer 1 · answered Nov 26 '22 at 13:14

0

You need to create a sheet for the profiles with Workbook.create_sheet as you did for the grades.

Try this :

from openpyxl import load_workbook

wb = load_workbook("Registry.xlsx")

#Student Profile
ws_sp = wb.create_sheet("Student Profiles")
ws_sp["A1"] = "ID No.:"
ws_sp["B1"] = "Last Name:"
ws_sp["C1"] = "First Name:"
ws_sp["D1"] = "Middle Name:"
ws_sp["E1"] = "Sex:"
ws_sp["F1"] = "Date of Birth:"

#Student Grades
ws_sg = wb.create_sheet("Student Grades")
ws_sg["A1"] = "ID No:"
ws_sg["B1"] = "Math"
ws_sg["C1"] = "Science:"
ws_sg["D1"] = "English"

wb.save("Registry.xlsx")

answered Nov 26 '22 at 13:14

Timeless

22,580
4
12
30

What if I had to add values to that specific sheet later down the line without creating a new sheet later down the line? – dioscuri Nov 26 '22 at 13:23
Just load the workbook with `load_workbook("Registry.xlsx")` and add whatever you want. – Timeless Nov 26 '22 at 13:30
but how can I add a value in a cell to an already existing worksheet? – dioscuri Nov 26 '22 at 13:33
For example `ws_sg["B2"] = 20` will set a grade for the column `Maths` in the sheet `Students Grace`. Anyway, I think you're complicating things for yourself. How about you put in your post a sample of your input and a clear expected output ? – Timeless Nov 26 '22 at 13:35
This is small part of a project with CRUD functions where we're restricted from using databases like sql. So we have to resort to excel instead. The problem is the excel file needs to be constantly updating when you have a added value to it. If I just `ws_sp=create_sheet("Student Profile")` over and over it will generate `Student Profile1 and Student Profile2`. So i have to get that specific sheet and edit the values within it – dioscuri Nov 26 '22 at 13:54
There are a few methods to open an existing worksheet, however if you wish to open a specific sheet for updating use **ws_sp = wb[]**. e.g. **ws_sp = wb["Student Profile"]** – moken Nov 27 '22 at 11:34

Python: How to add value to a specific excel sheet using openpyxl?

1 Answers1