Make an Excel File Open Read-Only Recommended

A monthly report sits on a shared drive, and every month someone opens it, types a "quick what-if" into a cell, saves by reflex and changes the published numbers for everyone. Protecting the sheet with a password is heavy-handed for a report people are allowed to play with; what is wanted is a gentle nudge. Excel has two: read-only recommended, which makes Excel ask "open as read-only?" when the file is opened, and mark as final, which opens the file with editing turned off and a yellow bar explaining why. Both can be set from Python. Neither is security — anyone can click past them — and the page says exactly where each stops. It is part of Protecting and Sharing Excel Workbooks.

Prerequisites

pip install xlsxwriter openpyxl

The examples write small report workbooks and look inside the saved files to confirm the settings are there. Both settings are small XML elements in the .xlsx package, which makes them easy to check without Excel — a useful property, because the prompts themselves only appear in Excel.

Step 1 — Set read-only recommended with xlsxwriter

xlsxwriter has a method for it. Call it before closing the workbook:

import xlsxwriter

wb = xlsxwriter.Workbook("monthly_report.xlsx")
ws = wb.add_worksheet("Summary")
ws.write_row(0, 0, ["Region", "Revenue"])
for i, (region, revenue) in enumerate([("North", 482100), ("South", 391250), ("East", 297800)], start=1):
    ws.write_row(i, 0, [region, revenue])
wb.read_only_recommended()
wb.close()

import re
import zipfile

def file_sharing(path):
    xml = zipfile.ZipFile(path).read("xl/workbook.xml").decode()
    return re.findall(r"<fileSharing[^>]*/>", xml)

print(file_sharing("monthly_report.xlsx"))

The method writes a <fileSharing readOnlyRecommended="1"/> element into the workbook part. When Excel opens the file, it asks whether to open it read-only; answering yes opens it with saving to the same name disabled, so edits can only be kept under a new name. Answering no opens it normally, with full editing, so the flag guards against habit rather than intent. The prompt appears every time, for everyone, until someone saves the file with the option switched off in Save As → Tools → General Options.

Step 2 — Add it to a file written by openpyxl

openpyxl knows the fileSharing element when it reads a file, but its writer never outputs it, so there is no attribute to set. The element is small and its place in the XML is fixed — right after <fileVersion> if there is one, otherwise first inside <workbook> — so a short post-save patch adds it:

import shutil
from openpyxl import Workbook

def add_read_only_recommended(path):
    tmp = path + ".tmp"
    with zipfile.ZipFile(path) as src, zipfile.ZipFile(tmp, "w", zipfile.ZIP_DEFLATED) as dst:
        for item in src.infolist():
            data = src.read(item.filename)
            if item.filename == "xl/workbook.xml" and b"<fileSharing" not in data:
                xml = data.decode("utf-8")
                tag = '<fileSharing readOnlyRecommended="1"/>'
                if "<fileVersion" in xml:
                    xml = re.sub(r"(<fileVersion[^>]*/>)", r"\1" + tag, xml, count=1)
                else:
                    xml = re.sub(r"(<workbook[^>]*>)", r"\1" + tag, xml, count=1)
                data = xml.encode("utf-8")
            dst.writestr(item, data)
    shutil.move(tmp, path)

wb = Workbook()
wb.active["A1"] = "Published figures — do not edit"
wb.save("report_openpyxl.xlsx")
add_read_only_recommended("report_openpyxl.xlsx")
print(file_sharing("report_openpyxl.xlsx"))

The patch copies every part of the package unchanged except xl/workbook.xml, where it inserts the element in the position the file format requires — Excel is strict about element order, and an element in the wrong place makes it offer to repair the file. It only adds the element when none is present, so running it twice is harmless. Writing to a temporary file and moving it into place means a failure halfway never leaves a half-written report behind.

Step 3 — Check the patched file still opens

A patched package should be checked by something other than the code that patched it. Let LibreOffice open and convert it; a malformed workbook fails to convert:

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf report_openpyxl.xlsx > /dev/null 2>&1
test -s report_openpyxl.pdf && echo "converted OK" || echo "conversion failed"

from openpyxl import load_workbook

reloaded = load_workbook("report_openpyxl.xlsx")
print(reloaded.active["A1"].value)

Both LibreOffice and openpyxl open the patched file. That is also the key limitation of openpyxl here: it reads the file fine, and it will drop the flag the next time it saves it — the next step shows it.

Step 4 — Don't lose the flag on a re-save

Pipelines often build a workbook with one library and finish it with another. If openpyxl touches the file after the flag was set, the flag is gone:

wb = load_workbook("monthly_report.xlsx")
wb["Summary"]["C1"] = "Checked"
wb.save("monthly_report_resaved.xlsx")
print("before re-save:", file_sharing("monthly_report.xlsx"))
print("after re-save: ", file_sharing("monthly_report_resaved.xlsx"))
add_read_only_recommended("monthly_report_resaved.xlsx")
print("after patch:   ", file_sharing("monthly_report_resaved.xlsx"))

Make setting the flag the last step of the pipeline, after every library has finished with the file — the same rule as for watermarks and anything else openpyxl does not round-trip. What openpyxl Loses When Saving an Existing Workbook lists the rest.

Step 5 — Mark as final

Mark as final is a stronger nudge: Excel opens the file with the ribbon's editing commands disabled and a yellow message bar saying the author marked it final, with an Edit Anyway button. It is stored as a custom document property named _MarkAsFinal. Both libraries can write it:

from openpyxl.packaging.custom import BoolProperty

wb = xlsxwriter.Workbook("final_xlsxwriter.xlsx")
wb.add_worksheet().write("A1", "Final figures")
wb.set_custom_property("_MarkAsFinal", True)
wb.close()

wb = Workbook()
wb.active["A1"] = "Final figures"
wb.custom_doc_props.append(BoolProperty(name="_MarkAsFinal", value=True))
wb.save("final_openpyxl.xlsx")

for path in ("final_xlsxwriter.xlsx", "final_openpyxl.xlsx"):
    props = load_workbook(path).custom_doc_props
    print(path, [(p.name, p.value) for p in props.props])

load_workbook("final_xlsxwriter.xlsx").save("final_resaved.xlsx")
print("after openpyxl re-save:", [(p.name, p.value) for p in load_workbook("final_resaved.xlsx").custom_doc_props.props])

Unlike fileSharing, custom properties survive an openpyxl round trip, because openpyxl reads and writes them. Use mark-as-final for documents that genuinely should not change — a signed-off month-end pack, say — and read-only recommended for working reports that people may reasonably copy and adapt.

Removing the flag again

A report that was published read-only sometimes needs to become a working file again — for next month's version, say. Excel removes the flag through Save As → Tools → General Options; in a script, drop the element with the same kind of patch:

def remove_read_only_recommended(path):
    tmp = path + ".tmp"
    with zipfile.ZipFile(path) as src, zipfile.ZipFile(tmp, "w", zipfile.ZIP_DEFLATED) as dst:
        for item in src.infolist():
            data = src.read(item.filename)
            if item.filename == "xl/workbook.xml":
                data = re.sub(rb"<fileSharing[^>]*/>", b"", data)
            dst.writestr(item, data)
    shutil.move(tmp, path)

shutil.copy("monthly_report.xlsx", "next_month.xlsx")
remove_read_only_recommended("next_month.xlsx")
print(file_sharing("next_month.xlsx"))

The same fileSharing element also stores Excel's password to modify when one is set, so removing it removes that password too — check whether a file has one before stripping the element from files you did not create. Or simply load and save the file with openpyxl, which has the same effect as a side-effect — but the explicit function says what it does, and leaves the rest of the package untouched.

What these settings do not do

Both are conveniences. They do not encrypt anything, do not stop a determined edit, and are ignored by programs other than Excel — LibreOffice, Google Sheets and every Python library open the file normally. If a file must not be changed, protect the sheets with a password (Lock Cells and Protect a Sheet with openpyxl), publish it as a PDF, or control write access on the shared drive itself. If it must not be read, encrypt it: Password Protect an Excel File with Python.

Common pitfalls

• Expecting openpyxl to write the flag. It has no setting for it; use xlsxwriter or patch after saving.
• Setting the flag before a re-save. openpyxl drops it; add it last.
• Inserting the element in the wrong place. It must come right after fileVersion, or first in the workbook element; otherwise Excel repairs the file.
• Treating it as protection. It is a prompt, and only Excel shows it.
• Forgetting it is permanent until removed. Every user sees the prompt every time; remove it via Save As options when the file becomes a working copy.

Conclusion

For a report that should be opened read-only by default, call read_only_recommended() in xlsxwriter, or add <fileSharing readOnlyRecommended="1"/> to the workbook part of an openpyxl file with a small zip patch as the very last step. For documents that are finished, set the _MarkAsFinal custom property, which both libraries write and openpyxl preserves. Check the saved files, and keep real protection — passwords, PDFs, file permissions — for files that must not change.

Frequently asked questions

How do I make an Excel file open read-only recommended with Python? With xlsxwriter, call workbook.read_only_recommended() before closing the workbook. For files written by openpyxl, add the element fileSharing with readOnlyRecommended="1" to xl/workbook.xml after saving.

Does openpyxl support read-only recommended? It can read the setting but never writes it, so saving a workbook with openpyxl removes the flag. Add it after the last openpyxl save.

How do I mark an Excel workbook as final from Python? Add a boolean custom document property named _MarkAsFinal set to true — with xlsxwriter's set_custom_property or openpyxl's custom_doc_props. Excel then opens the file with editing disabled and an Edit Anyway button.

Is read-only recommended secure? No. Users can decline the prompt, and programs other than Excel ignore it. Use sheet protection, a PDF, file permissions or encryption when the file must not be changed or read.

Related

• Up one level: Protecting and Sharing Excel Workbooks — protection, passwords and clean sharing.
• Protect Workbook Structure with openpyxl — stopping sheets being added, deleted or renamed.
• Set Workbook Properties with Python — the document properties mark-as-final lives in.
• Remove Hidden Data Before Sharing an Excel File — the other pre-publication check.