Skip to main content

Jinja based Excel Template support multiple sheets

Project description

Jinja Excel Template Engine

This package can handle some Excel reporting requirements.

It contains:

  1. Dynamic sheets based on the data
  2. Images are inside the Excel file,
  3. Multiple level of grouping in the data (this library does not handle it, but you can have your own data pipeline to prepare the data.),
  4. Printing header setting,
  5. Basic charts

This library will not be suitable for you if you are after a completely non-technical maintainable Excel template. In fact, there is none of them, if your Excel template is complicated enough, especially dynamic sheets.

This package will suitable for you for the following scenarios:

  1. if you have XML knowledge and know how to read openpyxl documents, and
  2. if you have Jinja2 knowledge

Concept

Excel templating is always complicated and normal Excel file does not support them. We have to use an intermediate file named ExcelXML.

Data + Jinja Template ---(Jinja Engine)---> ExcelXML ---(Excel Generator)---> Excel

With Data and Jinja Template, the Jinja Engine can generate the ExcelXML, then the Excel Generator can generate the Excel file. An ExcelXML is an XML representing a single Excel file.

The ExcelXML has a specific format, so the Excel Generator can read it and draw the Excel.

Here is an example of the ExcelXML.

<Excel>
    <sheet sheetName="myFirstSheet" print_area="A1:L10" print_header="1:3">
        <row> <!-- first row -->
            <cell value="A1" />
            <cell value="B1" />
            <cell value="C1" />
        </row>
        <row /> <!-- second row, empty row -->
        <row> <!-- third row -->
            <cell value="A3" />
            <cell value="B3" />
            <cell value="C3" />
        </row>
    </sheet>
    <sheet sheetName="mySecondSheet" print_area="A1:L10" print_header="1:3">
        <row> <!-- first row -->
            <cell value="A1" />
            <cell value="B1" />
            <cell value="C1" />
        </row>
        <row /> <!-- second row, empty row -->
        <row> <!-- third row -->
            <cell value="A3" />
            <cell value="B3" />
            <cell value="C3" />
        </row>
    </sheet>
</Excel>

The Jinja template is to construct the ExcelXML with your own data pipeline.

Why did I chose XML not JSON? JSON is too messy to representing meta data and attributes, so I chose XML.

Sample

Please check the tests/countries_test.py. It shows the raw data and group countires by region and display countries in multiple sheets. It also contains some basic diagrams.

If you are interested in more complicated example, you can refer to tests/test.xml that is the test XML I am using to test my output and it finally hit 99% code coverage.

TODO list

  1. Filtering and grouping
  2. Auto column width (the current implement is not good enough)
  3. Calculated cell
  4. Auto number, date and text detection
  5. Spend more time on openpyxl document (yes unfortunately, I haven't got enough time)
  6. Pivot table?? (not sure the use case about it, put it in low priority)

About me

I am from .NET, Java and NodeJS background. This is my first project in Python. When I started this project, I wanted to go to NodeJS, but ExcelJS is not powerful enough comparing to openpyxl. That is why I chose Python. If I am not good at the Python standard, please let me know by email hkalex@gmail.com. Thanks.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

jinja_excel_template-1.0.4.tar.gz (10.0 kB view details)

Uploaded Source

Built Distribution

jinja_excel_template-1.0.4-py3-none-any.whl (16.0 kB view details)

Uploaded Python 3

File details

Details for the file jinja_excel_template-1.0.4.tar.gz.

File metadata

  • Download URL: jinja_excel_template-1.0.4.tar.gz
  • Upload date:
  • Size: 10.0 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/1.6.1 CPython/3.11.3 Darwin/22.5.0

File hashes

Hashes for jinja_excel_template-1.0.4.tar.gz
Algorithm Hash digest
SHA256 469a9d24f7f626a575f28ae8efe4757e13cef18014d00ae03ec807694478cb8a
MD5 53e39d01d36df7e425af5e79325cf532
BLAKE2b-256 bbaac5d6e1353dfefbc08efb251a3edf732c2a6d70ffcf2f84e0981fad63637e

See more details on using hashes here.

File details

Details for the file jinja_excel_template-1.0.4-py3-none-any.whl.

File metadata

File hashes

Hashes for jinja_excel_template-1.0.4-py3-none-any.whl
Algorithm Hash digest
SHA256 15003c4f5a336a56ae81eb9a94a6fc8dfacfff1b73428889fd28f74e3fe8c4b2
MD5 3ec12ed6f52c93ed472363ed1fa2b5ba
BLAKE2b-256 a62b5cd73a7f6045044ce02e40b48ad17312e618118146a21c0be1ebe997fd27

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page