9

Using openpyxl, I am trying to read data from an Excel-Workbook and write data to this same Excel-Workbook. Getting data from the Excel-Workbook works fine, but writing data into the Excel-Workbook does not work. With the code below I get the value from Cell A1 in Sheet1 and print it. Then I try to put some values into the cells A2 and A3. This does not work. 

from openpyxl import Workbook
from openpyxl import load_workbook


wb = load_workbook("testexcel.xlsm")
ws1 = wb.get_sheet_by_name("Sheet1")

#This works:
print ws1.cell(row=1, column=1).value 

#This doesn't work:
ws1['A2'] = "SomeValue1"

#This doesn't work either:
ws1.cell(row=3, column=1).value = "SomeValue2"

I am sure the code is correct ... What is going wrong here?

Daniel
  • 5,095
  • 5
  • 35
  • 48
steady_progress
  • 3,311
  • 10
  • 31
  • 62

3 Answers3

20

I believe you are missing a save function. Try adding the additional line below. 

from openpyxl import Workbook
from openpyxl import load_workbook


wb = load_workbook("testexcel.xlsm")
ws1 = wb.get_sheet_by_name("Sheet1")

#This works:
print ws1.cell(row=1, column=1).value 

#This doesn't work:
ws1['A2'] = "SomeValue1"

#This doesn't work either:
ws1.cell(row=3, column=1).value = "SomeValue2"

#Add this line
wb.save("testexcel.xlsm")
Woodsy
  • 3,177
  • 2
  • 26
  • 50
  • 1
    Thank you for the answer ... I added the additional line. Now I get the following error message: " IOError: [Errno 13] Permission denied: 'testexcel.xlsm' " Seems like I do not have permission to save the file (Why the hell not?) – steady_progress Apr 27 '15 at 17:21
  • 1
    do you have the file open? – Woodsy Apr 27 '15 at 17:23
  • 1
    Try saving it under a different name – this is probably best practice on a file with macros anyway as you'll lose them without explicitly preserving them. – Charlie Clark Apr 27 '15 at 17:29
  • 1
    Instead of "wb.save("testexcel.xlsm")" I now used the line "wb.save("testexcel2.xlsm")". There wasn't an immediate error message (from the Shell) anymore. However, when I tried to open the newly created file "testexcel2.xlsm" by double-clicking on it, I received the following error message: "Excel cannot open the file 'testexcel2.xlsm' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file." – steady_progress Apr 27 '15 at 17:43
  • 2
    Thanks a lot for the anwer! ... I now tried "wb = load_workbook("testexcel.xlsm", keep_vba=True )" ... and now it works. – steady_progress Apr 27 '15 at 18:14
6

Use this to write a value:

ws1.cell(row=1, column=1,value='Hey')

On the other hand, the following will read the value:

ws1.cell(row=1, column=1).value
piet.t
  • 11,718
  • 21
  • 43
  • 52
Abul
  • 71
  • 1
  • 1
0

while saving the workbook, try giving the full path. for example: wb1.save(filename=r"C:\Users\7000027842\Downloads\test.xlsx")

Tousifur Rahman Khan
  • 1
  • 1