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!

Bi7n
  • 55
  • 1
  • 2
  • 9
  • 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. – Bi7n Jun 20 '18 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. – Bi7n Jun 20 '18 at 16:16
  • Always provide the full traceback for errors as this makes identifying the problem a lot easier. – Charlie Clark Jul 04 '18 at 10:33
  • Got it, thanks Charlie! – Bi7n Jul 04 '18 at 16:03

1 Answers1

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')
Smart Manoj
  • 5,230
  • 4
  • 34
  • 59
Riverman
  • 503
  • 6
  • 17
  • 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. – Bi7n Jun 21 '18 at 07:50
  • 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) – Riverman Jun 21 '18 at 15:40
  • 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... – Bi7n Jun 21 '18 at 15:51
  • 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. – Riverman Jun 21 '18 at 16:46
  • That would be weird.. I have Sheet 2 in my file but just cant call it. Thanks so much for your help and patience! I would try again. – Bi7n Jun 25 '18 at 07:56
  • 1
    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! – Bi7n Jun 25 '18 at 08:11
  • Glad it worked. Good luck for figuring out the initial issue. :) – Riverman Jun 25 '18 at 08:17