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
Photo how I write the output into xml file in python? 3lnyn0 1 245 Oct-31-2021, 05:40 PM
Last Post: Gribouillis
  |SOLVED] Glob JPGs, read EXIF, update file timestamp? Winfried 5 435 Oct-21-2021, 03:29 AM
Last Post: buran
  [SOLVED] Read text file from some point till EOF? Winfried 1 260 Oct-10-2021, 10:29 PM
Last Post: Winfried
  rows from sql query need to write to a file as columns sjcsvatt 6 395 Oct-09-2021, 12:45 AM
Last Post: snippsat
  Can't write to file Mark17 2 343 Sep-24-2021, 03:45 PM
Last Post: Mark17
  Problem in saving .xlsm (excel) file using pandas dataframe in python shantanu97 2 666 Aug-29-2021, 12:39 PM
Last Post: snippsat
  How to write in text file - indented block Joni_Engr 3 590 Aug-09-2021, 06:30 PM
Last Post: deanhystad
  Read csv file through PyCharm kimx0961 3 878 Aug-01-2021, 07:05 PM
Last Post: perfringo
  dataframe write to tab delimited differs from Excel koh 0 381 Aug-01-2021, 02:46 AM
Last Post: koh
  UART Serial Read & Write to MP3 Player Doesn't Work bill_z 15 1,418 Jul-17-2021, 04:19 PM
Last Post: bill_z

Forum Jump:

User Panel Messages

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