[PYTHON] 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

Fishmonger Shopping Procedures.xlsx

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

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)