CODEWITHSUNDEEP
openpyxl
Tristan Lee
Tristan Lee

Reputation: 33

Reading an .xltx file and Writing to .xlsx file with openpyxl

I've been struggling with this problem for a while and couldn't find a solution else where. I have several excel templates in xltx format that I want to read, then write a new xlsx file after filling in some cells.

Every time I run my code it creates a corrupted excel file. Using a preview extension in VS code I'm able to see that the values were correctly changed. When I read an xlsx file instead of an xltx it works fine. Does openpyxl just not allow what I am trying to do?

import openpyxl
import win32com.client

report = openpyxl.load_workbook("0100048-A5_R_11.xltx")
sheet = report["A5 form"]

search_arr = ["Test_Date"]


for r in range(2, sheet.max_row+1):
    for c in range(3,sheet.max_column+1):
        val = sheet.cell(r,c).value
        
        if val != None and "$!" in str(val):
            sheet.cell(r,c).value = 1

report.active = 1
report.save("output.xlsx")

Upvotes: 3

Views: 925

Answers (2)

johnson
johnson

Reputation: 4405

Copied from the docs:

You can specify the attribute template=True, to save a workbook as a template:

wb = load_workbook('document.xlsx')
wb.template = True
wb.save('document_template.xltx')

or set this attribute to False (default), to save as a document:

wb = load_workbook('document_template.xltx')
wb.template = False
wb.save('document.xlsx')

Upvotes: 6

Red
Red

Reputation: 199

I am piggy backing off the answer from @johnson. I tried using his solution. When I included the parameter in save "as_template = False", I received the following error:

TypeError: Workbook.save() got an unexpected keyword argument 'as_template'

I looked the docs and couldn't find the "as_template" parameter for save. They may have changed the documentation in the past year. Below is the solution that I found to work.

file_path = 'TEMPLATE_FILE.xltx"

wb = openpyxl.load_workbook(file_path)
wb.template = False

wb.save(wb.replace(".xltx",".xlsx"))

The original file is a excel template file, ending in .xltx. Simply open the file using .load_workbook(). The important step is adding the line "wb.template = False". If you exclude this line you will get a file format error when opening the file later. Finally, save the file using .save but change the file format to ".xlsx". enter image description here

Upvotes: -1

Related Questions