2

I am currently using openpyxl library to modify data in excel. My excel has many sheets and I could do it for the first sheet. However, when I was trying to modify the another one, it doesn't work and will destroy the file(I cant open the excel again.)

Here is how I do for first sheet,

from openpyxl import load_workbook;
book = load_workbook('Template.xlsx')
sheet = book.active #active means get the first sheet
sheet['A1']= 100
book.save('Template.xlsx')

However, it doesn't work for another one

sheet2 = book.get_sheet_by_name('Table 2')
sheet2['F9'] = 100
book.save('Template.xlsx')

The AttributeError: 'NoneType' object has no attribute 'read'

Explanation: get_sheet_by_name is not identical to get_sheet_name in the another question.

Anyone knows how to fix this? Thanks a mil!

Improve this question
4
  • Thanks for your reply! But get_sheet_name or sheetnames is the function that returns the list of the names of worksheets in this workbook. Commented Jun 20, 2018 at 16:14
  • get_sheet_by_name is the function that Returns a worksheet by its name. I tried to use is to read and modify my second sheet. Commented Jun 20, 2018 at 16:16
  • Always provide the full traceback for errors as this makes identifying the problem a lot easier. Commented Jul 4, 2018 at 10:33
  • Got it, thanks Charlie! Commented Jul 4, 2018 at 16:03

1 Answer 1

Reset to default
6

You're very close to the solution actually. This is good so far:

from openpyxl import load_workbook
book = load_workbook('Template.xlsx')
sheet = book.active #active means last opened sheet
sheet['A1']= 100
book.save('Template.xlsx')

To change to another sheet, you need to call it by its name:

sheet2 = book['Sheet 2']
sheet2['F9'] = 100
book.save('Template.xlsx')
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Hi, Thanks so much for your reply. The book['Sheet 2'] does work and actually the book.get_sheet_by_name('Table 2') also work. But I think the problem is the book.save does not work in this case. I could save this for the first sheet but when I modify the second sheet and use this again, it would collapse.
Do you have write rights in the folder or on the file you're editing? I just executed the commands, and they work fine. (Win10/64 && Python 3.6.5 && openpyxl==2.5.4)
Yes, I write the absolute path of the file and it did works for the first sheet. Can you save your modification for both sheets? That would be weird, I might try another computer...
Yes, I can save. Only thing I can think about is you don't have "Sheet 2" present in your .xlsx file, therefore, openpyxl can't write nor save it. If that's the case, the sheet must be created first: book.create_sheet("Sheet 2") then execute the code you can see in my answer's 2nd block.
You are right, it dose work in another sheet, I create a new sheet and I could save both sheet. I don't know why it doesn't work in my template. Just need to figure it out, thanks!
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.