Skip to main content

Jinja based Excel Template supporting 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_to_excel-1.0.1.tar.gz (10.0 kB view details)

Uploaded Source

Built Distribution

jinja_to_excel-1.0.1-py3-none-any.whl (15.6 kB view details)

Uploaded Python 3

File details

Details for the file jinja_to_excel-1.0.1.tar.gz.

File metadata

  • Download URL: jinja_to_excel-1.0.1.tar.gz
  • Upload date:
  • Size: 10.0 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/1.8.3 CPython/3.10.13 Darwin/23.5.0

File hashes

Hashes for jinja_to_excel-1.0.1.tar.gz
Algorithm Hash digest
SHA256 e94d6c73558b7e903672877ed63081ee7dd3a1c4727e3808d52f5424c875191d
MD5 27a85f59780e40df61e50f348220ccb0
BLAKE2b-256 7a717b49ab0a138b115606fa2eb8ae94e9f330d51fccbeca4e7b30fb55e8aab1

See more details on using hashes here.

File details

Details for the file jinja_to_excel-1.0.1-py3-none-any.whl.

File metadata

  • Download URL: jinja_to_excel-1.0.1-py3-none-any.whl
  • Upload date:
  • Size: 15.6 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: poetry/1.8.3 CPython/3.10.13 Darwin/23.5.0

File hashes

Hashes for jinja_to_excel-1.0.1-py3-none-any.whl
Algorithm Hash digest
SHA256 0e89d1a3f869b62ee8f73014c83a8013caa9506814df86dcc0a2193a1e617d65
MD5 f3d7cd75206a7b95c7dfa44e068d0ed4
BLAKE2b-256 0d6209abc6165c07cc1c6c44c8cbca498df6fa5adcb94616ea6d6344e433363c

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