Use jinja2 template in excel file

background

When developing a system, I often have the opportunity to write a document written in Excel. In some cases, it is rarely necessary to create a large number of files with almost the same contents as shown below.

Greengrocer shopping procedure manual.xlsx yaoya.png

Fishmonger Shopping Procedures.xlsx sakanaya.png

The above two are exactly the same as the store you go to buy, except for what you buy.

In such a case, there is a method of "creating a greengrocer shopping procedure manual and then creating a fishmonger version by string replacement", but in some cases it can not be realized with the poor replacement function of Excel, so weeping manual replacement ( I think that there are many cases where the so-called hand sed) is carried out.

Therefore, openpyxl that reads and writes xlsx files from Python and Python's typical template engine jinja2 I've tried using / docs / dev /) to generate the text in an excel file with a template.

https://gist.github.com/kokumura/c44970102e1f33685152

python


pyxl.py [Xlsx filename containing the jinja template] [Output file name] [Template variable(YAML)]

By executing in this way, all cells of all sheets included in the original xlsx file will be scanned, the jinja2 template will be expanded if it is included, and the result will be output to another file.

How to use

First, prepare the following template file using Excel. The grammar is jinja2 itself.

template.xlsx template.png

Describe the variables used in the template in YAML.

yasai.yml


---
place:Greengrocer
targets:
  - name:Mandarin orange
    num:  3
  - name:Apple
    num:  2
  - name:Carrots
    num:  1

sakana.yml


---
place:Fish shop
targets:
  - name:pacific saury
    num:  2
  - name:The squid
    num:  1
  - name:Octopus
    num:  1

By executing as follows, "Greengrocer shopping procedure manual.xlsx" and "Fishmonger shopping procedure manual.xlsx" will be generated from template.xlsx and YAML file. I'm happy.

python xljj.py template.xlsx Greengrocer shopping procedure.xlsx yasai.yml
python xljj.py template.xlsx fishmonger shopping procedure.xlsx sakana.yml

bonus

openpyxl is unexpectedly versatile, and if you do your best, you can change the style and so on. However, the documentation isn't very well documented, so sometimes you'll need to be prepared to call features or private methods that aren't documented.

Below is a function that automatically does another common task: "Open an Excel file, select cell" A1 "on all sheets, switch to the first sheet and then save."

from openpyxl import load_workbook
import openpyxl.worksheet.views

def select_a1(workbook_path):
    wb = load_workbook(workbook_path)
    wb._active_sheet_index = 0
    for ws in wb.worksheets:
        ws.sheet_view.selection = (openpyxl.worksheet.views.Selection(),)
    wb.save(workbook_path)

Recommended Posts

Use jinja2 template in excel file
How to use template engine in pyramid 1 file application
Use Jinja2 for PasteScript template engine
Use of constraints file added in pip 7.1
(Note) Template file search order in Django
[Python] Implemented automation in excel file copying work
Standard .py file used in Python trials (template)-2020
Django template file organization
File operations in Python
Use config.ini in Python
File processing in Python
Use DataFrame in Java
Use dates in Python
Use Mean in DataFrame
Use Valgrind in Python
File operations in Python
Use ujson in requests
Jinja2 | Python template engine
Python programming in Excel
Use profiler in Python
[Python] How to change EXCEL file saved in xlsb to xlsx
Convert Excel file to text in Python for diff purposes
Let's use def in python
Use "$ in" operator with mongo-go-driver
Use let expression in Python
Use Anaconda in pyenv environment
Use Measurement Protocol in Python
Download the file in Python
Use callback function in Python
Use parameter store in Python
Use HTTP cache in Python
Use regular expressions in C
Use MongoDB ODM in Python
Use list-keyed dict in Python
Use Random Forest in Python
Use regular expressions in Python
Use Spyder in Python IDE
Template Method pattern in Java
Use Juman ++ in server mode
Display Japanese in JSON file
· Address already in use solution
Show Django ManyToManyField in Template
[Python] When you want to use all variables in another file
Get the formula in an excel file as a string in Python