Python Forum
Read and write active Excel file - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: Read and write active Excel file (/thread-34114.html)



Read and write active Excel file - euras - Jun-27-2021

Hi, is it possible to read and write an active Excel workbook? All I have found so far, is either create a new Excel and write inn, or provide a path to an Excel file, and then read / write (but the file needs to be closed before the script runs, otherwise it will fail). I hope there are some ways to work with an active Excel file?

from openpyxl import Workbook
wb = Workbook() # creates a new Excel
wb_obj = openpyxl.load_workbook(path) # or load an Excel with a path



RE: Read and write active Excel file - Larz60+ - Jun-28-2021

You can, and save in place, but that's not the proper way to do it because if you make a mistake, you will have overwritten you original spreadsheet.
Instead save to a new spreadsheet and when satisfied, you can rename it.

Example:
from openpyxl import load_workbook

excelfile = 'runways.xlsx'                # modify to your spreadsheet name
excelout = 'ModifiedRunways.xlsx'   # ditto

wb = load_workbook(excelfile)
sheet_ranges = wb['runways']        # modify to your sheet name
print(sheet_ranges['F2'].value)

sheet_ranges['F2'] = 'New Guy'

wb.save(excelout)



RE: Read and write active Excel file - Pedroski55 - Jun-28-2021

Not sure I understand the question clearly.

When you open your excel, nothing happens to it.

When you save your excel, it will overwrite, without warning, an existing file with the same name.

That's why it is good to save by adding the time, or 'version2' or something to the original file name:

name = path.split('.')
output = name[0] + 'version2.' + name[1]
If by "active" you mean, more than 1 person may be accessing the excel file at the same time (i.e. "active"), you could get the last access time or modification time from os.stat(file), and compare that with the stat_result from when you opened it.

(st_atime: represents the time of most recent access. It is expressed in seconds.
st_mtime: represents the time of most recent content modification. It is expressed in seconds.)

Looks like that could lead to confusion though!

# Get the status of
# the specified path
status = os.stat(path)

# Print the status
# of the specified path
print(status)

os.stat_result(st_mode=33188, st_ino=795581, st_dev=2056, st_nlink=1, st_uid=1000,
st_gid=1000, st_size=243, st_atime=1531567080, st_mtime=1530346690, st_ctime=1530346690)


RE: Read and write active Excel file - Larz60+ - Jun-29-2021

Pedroski55 -- I think you responded to the wrong post.


RE: Read and write active Excel file - Pedroski55 - Jun-29-2021

No, I meant to write here.

Not sure what he means by "active".

I presume he means, the file may already be open in Excel or that others may open it. That won't stop openpyxl from opening it.

I often do that. If the file is open in Excel, as long as you don't make and or save any changes, when you close it nothing happens.

If openpyxl has already saved changes, you will see them when you reopen the file.

If an Excel file is accessed by multiple users, all of whom may change it, well, chaos could result.

I believe Excel can lock cell groups or whole sheets such that a password is needed to alter those cells. I never worked with that.

I don't know if openpyxl can handle that.