Add support for Excel-native template files (`.xltx` files)

[gyansinha]

I've saved and read in a template file (xltx extension). I then overwrite cell values in a sheet using values from a dataframe and then write it out to an xlsx file. Excel can't seem to recognize this file though the code works fine with no errors - the extension needs to xltx. Am I just missing the plot here in terms of what needs to be done?

letters = collect('A':'Z')
xf = XLSX.open_xlsx_template(abspath("excel/xxx_template.xltx"))
sh = xf["DM"]
nr, nc = size(res_df)
for (i, c) in enumerate(names(res_df))
    for j in 1:nr
        cref = "$(letters[i])$(j+1)"
        sh[cref] = res_df[j, i] 
    end
end
XLSX.writexlsx("excel/test_format.xlsx", xf, overwrite=true)

XLSX v0.10.5-dev `https://github.com/TimG1964/XLSX.jl#Bug-fixing-post-#289`

I feel like this should be a standard use case - set up a template and then refresh the values from a script.

[TimG1964]

EDIT: This reply was based on poor research by me! I simply renamed an xlsx file as an xltx file, so it was till an xlsx file internally. The next reply is based on better understanding!

I created an empty spreadsheet with a sheet called DM using Excel and called it xxx_template.xltx.

Then I ran

using XLSX, DataFrames
res_df=DataFrame(abc = collect(1:10), def = collect(11:20))
letters = collect('A':'Z')
xf = XLSX.open_xlsx_template("xxx_template.xltx")
sh = xf["DM"]
nr, nc = size(res_df)
for (i, c) in enumerate(names(res_df))
    for j in 1:nr
        cref = "$(letters[i])$(j+1)"
        sh[cref] = res_df[j, i] 
    end
end
XLSX.writexlsx("test_format.xlsx", xf, overwrite=true)

and got this (which Excel read without any issue)

So, I can't replicate your problem.

1. Try downloading the package again. I've made a couple of recent changes (same branch). If the problem persists...
2. Can you share res_df and your template file? Maybe they contain something the code can't handle.
3. Does Excel produce any meaningful error message you can share?

Thanks.

[TimG1964]

Actually, I've done some actual research now which I should have done before:

Let's dive deeper into the differences between XLSX and XLTX files at the XML level:

1. Content Type

• XLSX: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml
• XLTX: application/vnd.openxmlformats-officedocument.spreadsheetml.template.main+xml
  This difference in content type indicates the file's purpose: a workbook (XLSX) or a template (XLTX).

2. File Structure
Both XLSX and XLTX files are ZIP archives containing XML files that define the workbook's structure and content. The main files and folders include:

• [Content_Types].xml: defines the content types of the files in the package
• _rels: contains relationship files that define connections between files
• xl: contains workbook-specific XML files

• workbook.xml: defines the workbook's properties, worksheets, and other elements
• worksheets: contains XML files for each worksheet
• styles.xml: defines styles and formatting

3. Workbook XML (xl/workbook.xml)
   The workbook.xml file contains key differences between XLSX and XLTX files:

• XLSX: typically contains workbook data, formulas, and formatting
• XLTX: may contain template-specific information, such as:

• Predefined formatting and layouts
• Placeholder data or text
• Specific worksheet structures or settings

4. Relationships (xl/_rels/workbook.xml.rels)
   The relationships file defines connections between the workbook and other files, such as worksheets and styles. In XLTX files, these relationships might be defined differently to accommodate template-specific requirements.

5. Template-specific XML elements
XLTX files may include additional XML elements that define template-specific properties, such as:

• templateSettings
• templateStyles
  These elements can influence how the template is used to create new workbooks.

6. XML namespace and schema
Both XLSX and XLTX files use the same XML namespace and schema, defined by the Office Open XML (OOXML) standards. However, XLTX files might use specific schema elements or attributes that are not present in XLSX files.

When you create a new workbook from an XLTX template, Excel uses the template's XML structure and content to generate a new workbook with the desired formatting and layout. In contrast, opening an XLSX file loads the existing workbook data and structure.

By understanding these differences, you can better appreciate how XLSX and XLTX files serve distinct purposes in Microsoft Excel.

XLSX.jl is specifically designed to read xlsx files, and does not understand the structure or content of xltx files (in as much as they differ).

I think this issue should be renamed to say "Add ability to process xltx files" and it should be marked as an enhancement (unfortunately, I cannot do this myself).

[gyansinha]

Thanks.

[gyansinha]

I will ask for an enhancement.

[TimG1964]

Suport for .xltx template files added in commit caff72a