1

I'm trying to load data to a panda dataframe from an existing Excel-file, "file1", do some modifications to it, and then export the dataframe to a new sheet in the same workbook ("file1"). My code works fine when "file1" is closed, but if I have the file open in Excel at the same time as I run my code, I get the error: PermissionError: [Errno 13] Permission denied. What is the issue here? is Python not able to save a workbook which is simultaneously open in Excel, or are there any workarounds here?

This is my code:

import pandas as pd
from openpyxl import load_workbook
 
#Read the input data to a panda dataframe       
df = pd.read_excel(workbook_path, sheet_name = 'input_data')

---Do stuff to the dataframe--

#Access the existing excel-file
workbook = load_workbook(workbook_path) 

#Create a writer-object   
writer = pd.ExcelWriter(workbook_path, engine = 'openpyxl')
writer.book = workbook

#Create a new sheet called "Results"
df.to_excel(writer,sheet_name = 'Results')

#Save the workbook
writer.save()
writer.close()

This is the full output of the error: enter image description here

Åsmund Sælen
  • 171
  • 1
  • 2
  • 6
  • 1
    No. Excel has an exclusive lock on the file and will not allow it to be modified. This is standard functionality, as anyone who has ever had to work with files on a shared network drive will attest to. *"OK - who has 'foo.xls' open?"* – SiHa Nov 09 '20 at 08:18

1 Answers1

3

While a file is open in Excel, write access from other applications is blocked to maintain data integrity within the file that is open. This means you should only be able to get read-only access. This is not specific to Python.

There may be a workaround for certain use cases, that lets you add data through a linked CSV (see Is it possible to write to an existing Excel file when it is **open** on the desktop? for details).

buddemat
  • 4,552
  • 14
  • 29
  • 49