Save a Workbook as an Excel Template with Python

An Excel template (.xltx) is a workbook that opens as an untitled copy: double-click it and Excel creates Book1 from it, so nobody overwrites the master by accident. Teams use templates for expense claims, order forms and monthly packs that people fill in by hand. Generating those templates from Python keeps them consistent — the same styles, dropdown lists and protected formulas in every version — and the reverse direction, filling a template into a finished report, is just as common. Both directions have one trap: a template is not a workbook with a different extension, and a mismatch between the extension and the file's internal content type gives you a file Excel will not open. This page belongs to Handling Excel File Formats and Conversions.

Prerequisites

pip install openpyxl pandas

openpyxl 3.1 was used for every example. Nothing here needs Excel; the files open in Excel, LibreOffice and other spreadsheet programs.

Step 1 — Build the workbook the template should contain

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Protection
from openpyxl.worksheet.datavalidation import DataValidation

wb = Workbook()
ws = wb.active
ws.title = "Expenses"
ws["A1"] = "Expense claim"
ws["A1"].font = Font(size=16, bold=True, color="4338CA")
ws.append([])
ws.append(["Date", "Category", "Description", "Amount"])
for c in ws[3]:
    c.font = Font(bold=True, color="FFFFFF")
    c.fill = PatternFill("solid", fgColor="4338CA")
    c.alignment = Alignment(horizontal="center")
for r in range(4, 24):
    for col in "ABCD":
        ws[f"{col}{r}"].protection = Protection(locked=False)
    ws[f"A{r}"].number_format = "dd mmm yyyy"
    ws[f"D{r}"].number_format = "#,##0.00"
ws["C24"], ws["D24"] = "Total", "=SUM(D4:D23)"
ws["C24"].font = ws["D24"].font = Font(bold=True)
ws["D24"].number_format = "#,##0.00"
dv = DataValidation(type="list", formula1='"Travel,Meals,Lodging,Supplies"', allow_blank=True)
ws.add_data_validation(dv)
dv.add("B4:B23")
for col, width in zip("ABCD", (14, 14, 36, 12)):
    ws.column_dimensions[col].width = width
ws.freeze_panes = "A4"
ws.protection.sheet = True
ws.print_area = "A1:D24"
print("input cells unlocked:", not ws["B4"].protection.locked, "| total locked:", ws["D24"].protection.locked)

The sheet has a title, a styled header row, twenty input rows whose cells are unlocked, a dropdown on the category column, a locked SUM total and sheet protection, so people can type into the input area but cannot break the formula. These are ordinary openpyxl features, covered in Lock Cells and Protect a Sheet with openpyxl and Add Dropdown Data Validation to Excel with openpyxl.

Step 2 — Save it as a real template

wb.template = True
wb.save("expense_claim.xltx")

wb.template = True is the line that matters. It changes the content type openpyxl writes for the workbook part from the normal workbook type to the template type — the declaration Excel reads to decide whether to open the file as an untitled copy. Saving to a name ending in .xltx without setting the flag produces a file whose extension says "template" and whose contents say "workbook".

Step 3 — Check the content type inside the file

An .xltx is a ZIP package like an .xlsx; the difference is one line of [Content_Types].xml. A tiny check makes the difference visible — and is worth keeping in a test:

import zipfile

def workbook_content_type(path):
    xml = zipfile.ZipFile(path).read("[Content_Types].xml").decode()
    start = xml.index('PartName="/xl/workbook.xml"')
    content_type = xml[start:].split('ContentType="', 1)[1].split('"', 1)[0]
    return content_type.split("spreadsheetml.", 1)[1]

Workbook().save("renamed_only.xltx")
for f in ("expense_claim.xltx", "renamed_only.xltx"):
    print(f"{f:20} {workbook_content_type(f)}")

The real template reports template.main+xml; the renamed file reports sheet.main+xml, the ordinary workbook type (the helper prints the part after application/vnd.openxmlformats-officedocument.spreadsheetml.). Nothing else in the two packages needs to differ — the sheets, styles and relationships are identical. Excel compares the extension with this type and refuses mismatches with "Excel cannot open the file because the file format or file extension is not valid" — the error explained in Fix "Excel Cannot Open the File Because the File Format or Extension Is Not Valid".

The same applies in the other direction and to other writers: xlsxwriter, for example, always writes the workbook content type, so xlsxwriter.Workbook("form.xltx") produces a mislabelled file. Build templates with openpyxl, or convert an xlsxwriter output by loading it in openpyxl, setting template = True and saving.

Step 4 — Fill the template into a normal workbook

Reports are often produced from a template: load the .xltx, write the data, save an .xlsx. openpyxl remembers that the source was a template, so the flag must be switched off before saving under a workbook name:

from openpyxl import load_workbook
import datetime as dt

claims = [(dt.date(2026, 9, 2), "Travel", "Train to Leeds", 64.4),
          (dt.date(2026, 9, 2), "Meals", "Client lunch", 38.9),
          (dt.date(2026, 9, 3), "Lodging", "Hotel, one night", 119.0)]
wb = load_workbook("expense_claim.xltx")
print("loaded as template:", wb.template)
ws = wb["Expenses"]
for i, row in enumerate(claims, start=4):
    for col, value in zip("ABCD", row):
        ws[f"{col}{i}"] = value
wb.template = False
wb.save("claim_jsmith_2026-09.xlsx")
print(workbook_content_type("claim_jsmith_2026-09.xlsx"), "| dropdowns kept:", len(ws.data_validations.dataValidation))

Without wb.template = False, the .xlsx would carry the template content type and hit the same "not valid" error. Styles, number formats, protection, the dropdown and the SUM formula all survive the round trip; openpyxl's general losses on load-and-save — charts and images among them — apply to templates too, as listed in What openpyxl Loses When Saving an Existing Workbook.

Step 5 — Templates with macros

A macro-enabled template has the extension .xltm. Load the source with keep_vba=True so the VBA project is preserved, and keep template = True when saving back to .xltm; to produce a filled, macro-enabled workbook, save as .xlsm with template = False. openpyxl cannot create or edit the macros themselves — it carries an existing VBA project through unchanged. Macro files are covered in Work with Macro-Enabled .xlsm Files in openpyxl.

Step 6 — Read a template's data with pandas

pandas reads .xltx files like any workbook, which is handy for checking what a template contains or for reading lookup lists stored in it:

import pandas as pd

print(pd.read_excel("claim_jsmith_2026-09.xlsx", header=2, nrows=3))
with pd.ExcelFile("expense_claim.xltx") as xf:
    print(xf.sheet_names)

Reading a filled claim uses header=2 to skip the title rows and nrows=3 to stop before the empty input rows and the total.

Step 7 — Put the template where people find it

A template on a shared drive is opened by double-click. To make it appear in Excel's File › New › Personal list instead, copy it into the folder Excel uses for personal templates (on Windows, by default Documents\Custom Office Templates; the location is set under File › Options › Save › Default personal templates location). A deployment script can copy the generated .xltx there with shutil.copy, overwriting last month's version so everyone starts from the current layout.

Step 8 — Regenerate the template from one script

Treat the template as build output. Keep the code from Steps 1 and 2 in a function that takes the things that change — the category list, the number of input rows, the company name in the title — and run it whenever one of them changes:

def build_template(path, categories, rows=20, title="Expense claim"):
    wb = Workbook()
    ws = wb.active
    ws.title = "Expenses"
    ws["A1"] = title
    ws.append([])
    ws.append(["Date", "Category", "Description", "Amount"])
    dv = DataValidation(type="list", formula1='"' + ",".join(categories) + '"', allow_blank=True)
    ws.add_data_validation(dv)
    dv.add(f"B4:B{3 + rows}")
    ws[f"D{4 + rows}"] = f"=SUM(D4:D{3 + rows})"
    wb.template = True
    wb.save(path)
    return workbook_content_type(path)

print(build_template("claim_v2.xltx", ["Travel", "Meals", "Lodging", "Supplies", "Training"]))

Generating the template means a new category is one list entry, not an afternoon of editing validation ranges by hand, and the content-type check at the end confirms the output is still a genuine template. A list-type validation written inline like this is limited to 255 characters; for longer lists, put the values on a hidden sheet and point the validation at that range.

Common pitfalls

• Renaming instead of flagging. An .xltx name without wb.template = True gives a mislabelled file.
• Loading a template and saving .xlsx. The flag sticks; set wb.template = False first.
• Expecting xlsxwriter to write templates. It writes the workbook type regardless of the name.
• Unprotected input areas. If the sheet is protected, unlock the input cells first or nobody can type into the template.
• Macro templates. Use .xltm and keep_vba=True; plain .xltx cannot hold macros.

Conclusion

A template is a workbook with a different content type, not a different extension. Build the layout in openpyxl, set wb.template = True and save as .xltx; when filling a template into a report, load it, write the data, set wb.template = False and save as .xlsx. Check the content type in a test, use .xltm with keep_vba=True for macros, and remember that xlsxwriter cannot produce a real template on its own.

Frequently asked questions

How do I save an Excel template (.xltx) with openpyxl? Set wb.template = True before calling wb.save("name.xltx"). The flag makes openpyxl write the template content type that Excel checks when opening the file.

Why does Excel say the file format or extension is not valid for my .xltx? The file's internal content type does not match its extension. Either it was saved with an .xltx name without wb.template = True, or a template was loaded and saved as .xlsx without setting wb.template = False.

Can xlsxwriter create .xltx templates? No. xlsxwriter always writes the normal workbook content type, whatever the file name. Load its output with openpyxl, set template = True and save it again to turn it into a real template.

Can pandas read .xltx files? Yes. pd.read_excel and pd.ExcelFile read .xltx templates with the openpyxl engine just like .xlsx workbooks.

Related

• Up one level: Handling Excel File Formats and Conversions — every workbook format and how to move between them.
• Fill an Excel Template with Python and openpyxl — filling templates at scale.
• Populate an Excel Template Without Losing Formatting — keeping the template's look intact.
• Apply a Reusable Style Theme Across an Excel Report — consistent styling for generated templates.