Python Forum
Read and write active Excel file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Read and write active Excel file
#1
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
Reply
#2
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)
Reply
#3
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)
Reply
#4
Pedroski55 -- I think you responded to the wrong post.
Reply
#5
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.
Larz60+ likes this post
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Python openyxl not updating Excel file MrBean12 1 250 Mar-03-2024, 12:16 AM
Last Post: MrBean12
  Copy Paste excel files based on the first letters of the file name Viento 2 348 Feb-07-2024, 12:24 PM
Last Post: Viento
  Last record in file doesn't write to newline gonksoup 3 364 Jan-22-2024, 12:56 PM
Last Post: deanhystad
  Recommended way to read/create PDF file? Winfried 3 2,784 Nov-26-2023, 07:51 AM
Last Post: Pedroski55
  write to csv file problem jacksfrustration 11 1,373 Nov-09-2023, 01:56 PM
Last Post: deanhystad
  python Read each xlsx file and write it into csv with pipe delimiter mg24 4 1,310 Nov-09-2023, 10:56 AM
Last Post: mg24
  Search Excel File with a list of values huzzug 4 1,147 Nov-03-2023, 05:35 PM
Last Post: huzzug
  Updating sharepoint excel file odd results cubangt 1 754 Nov-03-2023, 05:13 PM
Last Post: noisefloor
  Active Directory integration dady 2 465 Oct-13-2023, 04:02 AM
Last Post: deanhystad
Question Special Characters read-write Prisonfeed 1 582 Sep-17-2023, 08:26 PM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020