Skip to main content

Database report generation in .xls format according to the xml description

Project description

xls_report

Database report generation in xls-format according to the xml description

Example:

#!/usr/bin/python3

import sqlite3
from xls_report import XLSReport

connect = sqlite3.connect("chinook.sqlite")
cursor = connect.cursor()
report = XLSReport({
    'cursor': cursor,
    'xml': 'test_xls.xml',
    'callback_url': 'http://localhost',
    'callback_token': '12345',
    'callback_frequency': 20,
    'parameters': {
        'title0': 'Invoices',
        'customer': '',
        'title1': 'Albums',
        'title2': 'Money',
        'title3': 'Sales',
        'title4': 'Customers',
        'artist': ''}
})
report.to_file('test.xls')
cursor.close()
connect.close()

test.xls:

<?xml version='1.0' encoding='utf-8'?>
<book>
    <report>
        <name>{{title0}}</name>
        <styledef name="Subheaders">
            font: bold True; alignment: horiz centre;
            borders: left 1, top 1, bottom 1, right 1;
        </styledef>
        <styledef name="Totals">
            font: bold True;
        </styledef>
        <styledef name="Fields">borders: left 1, top 1, bottom 1, right 1;</styledef>
        <styledef name="Headers">font: bold True; alignment: horiz centre;</styledef>
        <literal col="0" row="0" stylename="Subheaders">Last name</literal>
        <literal col="1" row="0" stylename="Subheaders">First name</literal>
        <literal col="2" row="0" stylename="Subheaders">Amount</literal>
        <literal col="3" row="0" stylename="Subheaders">Discount</literal>
        <literal col="4" row="0" stylename="Subheaders">Total</literal>
        <sql>
            <request>
                SELECT b.LastName, b.FirstName, round(sum(a.Total), 2), round(sum(a.Total)/50, 2)
                    FROM Invoice AS a JOIN Customer AS b ON (b.CustomerId = a.CustomerId)
                    WHERE b.LastName LIKE '%{{customer}}%'
                    GROUP BY b.LastName, b.FirstName
                    ORDER BY b.LastName, b.FirstName;
            </request>
            <group step="1">
                <field col="0" name="Last name" header="no" row="1" stylename="Fields"/>
                <field col="1" name="First name" header="no" row="1" stylename="Fields"/>
                <field col="2" name="Amount" header="no" row="1" stylename="Fields"/>
                <field col="3" name="Discount" header="no" row="1" stylename="Fields"/>
            </group>
            <formula col="4" name="Total" row="1" header="no" stylename="Fields" format="0.00">
                    C{{cs}}-D{{cs}}
            </formula>
        </sql>
        <sql>
            <literal col="0" row="1" stylename="Totals">Total:</literal>
            <formula col="4" name="Total" row="1" header="no" stylename="Totals" format="0.00">
                    SUM(E2:E{{ds}})
            </formula>
        </sql>
    </report>
    <report>
        <name>{{title1}}</name>
        <literal col="0" row="0" stylename="Subheaders">Artist</literal>
        <literal col="1" row="0" stylename="Subheaders">Album</literal>
        <sql>
            <request>
                SELECT b.name as Artist, a.Title as Album
                    FROM Album a JOIN Artist b ON(b.ArtistId = a.ArtistId)
                    WHERE Artist LIKE '%{{artist}}%' ORDER BY Artist, Title;
            </request>
            <group step="1">
                <field col="0" name="Artist" header="no" row="1" width="20000" stylename="Fields"/>
                <field col="1" name="Album" header="no" row="1" width="20000" stylename="Fields"/>
            </group>
        </sql>
    </report>
    <report>
        <name>{{title2}}</name>
        <literal col="0" row="0" stylename="Headers">Report by some genres</literal>
        <literal col="0" row="2" stylename="Headers">Media</literal>
        <literal col="1" row="2" stylename="Headers">Genre</literal>
        <literal col="2" row="2" stylename="Headers">Amount</literal>
        <literal col="3" row="2" stylename="Headers">Discount</literal>
        <literal col="4" row="2" stylename="Headers">Charged</literal>
        <sql>
            <request>
                SELECT d.Name AS Media, round(sum(a.Quantity * a.UnitPrice), 2) AS Money,
                       round(sum(a.Quantity * a.UnitPrice/50), 2) AS Discount
                    FROM InvoiceLine AS a JOIN Track AS b ON(b.TrackId = a.TrackId) JOIN
                         Genre AS c ON (c.GenreId = b.GenreId) JOIN MediaType as d ON (d.MediaTypeId = b.MediaTypeId)
                    WHERE c.Name = 'Latin'
                    GROUP BY d.Name, c.Name
                    ORDER BY d.Name, c.Name
            </request>
            <group step="4">
                <field col="0" name="Media" header="no" row="3" width="7000"/>
                <field col="2" name="Money" header="no" row="3"/>
                <field col="3" name="Discount" header="no" row="3"/>
                <groupliteral col="1" name="Type" row="3" header="no" >Latin</groupliteral>
                <formula col="4" name="Total" row="3" header="no">C{{cs}}-D{{cs}}</formula>
            </group>
        </sql>
        <sql cycle="yes">
            <request>
                SELECT round(sum(a.Quantity * a.UnitPrice), 2) AS Money,
                       round(sum(a.Quantity * a.UnitPrice/50), 2) AS Discount
                    FROM InvoiceLine AS a JOIN Track AS b ON(b.TrackId = a.TrackId) JOIN
                         Genre AS c ON (c.GenreId = b.GenreId) JOIN MediaType as d ON (d.MediaTypeId = b.MediaTypeId)
                    WHERE c.Name = 'World'
                    GROUP BY d.Name, c.Name
                    ORDER BY d.Name, c.Name
            </request>
            <group step="4">
                <field col="2" name="Money" header="no" row="4"/>
                <field col="3" name="Discount" header="no" row="4"/>
                <groupliteral col="1" name="Type" row="4" header="no" >World</groupliteral>
                <formula col="4" name="Total" row="4" header="no">C{{cs}}-D{{cs}}</formula>
            </group>
        </sql>
        <sql cycle="yes">
            <group step="4">
                <groupliteral col="0" name="Type" row="5" header="no" stylename="Totals">Subtotal:</groupliteral>
                <formula col="4" name="Total" row="5" header="no" stylename="Totals" cycle="2">
                    INDIRECT("E" &amp; ({{ss}}+3)) + INDIRECT("E" &amp; ({{ss}}+4))
                </formula>
            </group>
        </sql>
        <sql>
            <literal col="0" row="0" stylename="Totals">Total:</literal>
            <formula col="4" name="Total" row="0" header="no" stylename="Totals" format="0.00">
                    INDIRECT("E" &amp; ({{cs}}-5)) + INDIRECT("E" &amp; ({{cs}}-1))
            </formula>
        </sql>
    </report>
    <report>
        <name>{{title3}}</name>
        <literal col="0" row="0" stylename="Subheaders">Media</literal>
        <literal col="1" row="0" stylename="Subheaders">Genre</literal>
        <literal col="2" row="0" stylename="Subheaders">Amount</literal>
        <literal col="3" row="0" stylename="Subheaders">Discount</literal>
        <literal col="4" row="0" stylename="Subheaders">Charged</literal>
        <sql>
            <request suppress="Media" skip="2" skip_totals="2" subtotal="Money, Discount, Charged" total="Money, Discount, Charged">
                SELECT d.Name AS Media, c.Name as Genre, round(sum(a.Quantity * a.UnitPrice), 2) AS Money,
                       round(sum(a.Quantity * a.UnitPrice/50), 2) AS Discount,
                       round(sum(a.Quantity * a.UnitPrice), 2) - round(sum(a.Quantity * a.UnitPrice/50), 2) AS Charged
                    FROM InvoiceLine AS a JOIN Track AS b ON(b.TrackId = a.TrackId) JOIN
                         Genre AS c ON (c.GenreId = b.GenreId) JOIN MediaType as d ON (d.MediaTypeId = b.MediaTypeId)
                    GROUP BY d.Name, c.Name
                    ORDER BY d.Name, c.Name
            </request>
            <group step="1">
                <field col="0" name="Media" header="no" row="1" width="7000" stylename="Fields"/>
                <field col="1" name="Genre" header="no" row="1" width="5000" stylename="Fields"/>
                <field col="2" name="Money" header="no" row="1" format="0.00" stylename="Fields"/>
                <field col="3" name="Discount" header="no" row="1" format="0.00" stylename="Fields"/>
                <field col="4" name="Charged" header="no" row="1" format="0.00" stylename="Fields"/>
            </group>
        </sql>
    </report>
    <report>
        <name>{{title4}}</name>
        <literal col="0" row="0" stylename="Subheaders">Customer</literal>
        <literal col="1" row="0" stylename="Subheaders">Media</literal>
        <literal col="2" row="0" stylename="Subheaders">Genre</literal>
        <literal col="3" row="0" stylename="Subheaders">Amount</literal>
        <literal col="4" row="0" stylename="Subheaders">Discount</literal>
        <literal col="5" row="0" stylename="Subheaders">Charged</literal>
        <sql>
            <request suppress="Customer, Media" skip="2" skip_totals="2" subtotal="Money, Discount, Charged" total="Money, Discount, Charged">
                SELECT f.LastName || ' ' || f.FirstName AS Customer, d.Name AS Media, c.Name as Genre,
                       round(sum(a.Quantity * a.UnitPrice), 2) AS Money,
                       round(sum(a.Quantity * a.UnitPrice/50), 2) AS Discount,
                       round(sum(a.Quantity * a.UnitPrice), 2) - round(sum(a.Quantity * a.UnitPrice/50), 2) AS Charged
                    FROM InvoiceLine AS a JOIN Track AS b ON(b.TrackId = a.TrackId) JOIN
                         Genre AS c ON (c.GenreId = b.GenreId) JOIN MediaType as d ON (d.MediaTypeId = b.MediaTypeId) JOIN
                         Invoice as e ON (e.InvoiceId = a.InvoiceId) JOIN Customer as f ON (f.CustomerId = e.CustomerId)
                    WHERE f.LastName LIKE '%%'
                    GROUP BY Customer, d.Name, c.Name
                    ORDER BY Customer, d.Name, c.Name
            </request>
            <group step="1">
                <field col="0" name="Customer" header="no" row="1" width="5000" stylename="Fields"/>
                <field col="1" name="Media" header="no" row="1" width="7000" stylename="Fields"/>
                <field col="2" name="Genre" header="no" row="1" width="5000" stylename="Fields"/>
                <field col="3" name="Money" header="no" row="1" format="0.00" stylename="Fields"/>
                <field col="4" name="Discount" header="no" row="1" format="0.00" stylename="Fields"/>
                <field col="5" name="Charged" header="no" row="1" format="0.00" stylename="Fields"/>
            </group>
        </sql>
    </report>
    <report>
        <name>Playlist</name>
        <literal col="0" row="0" stylename="Subheaders">Playlist</literal>
        <literal col="1" row="0" stylename="Subheaders">Album</literal>
        <literal col="2" row="0" stylename="Subheaders">Track</literal>
        <literal col="3" row="0" stylename="Subheaders">Milliseconds</literal>
        <literal col="4" row="0" stylename="Subheaders">Bytes</literal>
        <literal col="5" row="0" stylename="Subheaders">Price</literal>
        <sql>
            <request suppress="Playlist, Album" skip="2" skip_totals="2" subtotal="Milliseconds, Bytes, Price" total="Milliseconds, Bytes, Price">
                SELECT DISTINCT b.Name AS Playlist, d.Title AS Album, c.Name AS Track,
                       c.Milliseconds, c.Bytes, c.UnitPrice AS Price
                    FROM PlaylistTrack as a JOIN Playlist as b ON (b.PlaylistId=a.PlaylistId) JOIN
                         Track as c ON (c.TrackId=a.TrackId) JOIN Album as d ON (d.AlbumId=c.AlbumId)
                    ORDER BY b.Name, d.Title, c.Name
            </request>
            <group step="1">
                <field col="0" name="Playlist" header="no" row="1" width="5900" stylename="Fields"/>
                <field col="1" name="Album" header="no" row="1" width="20500" stylename="Fields"/>
                <field col="2" name="Track" header="no" row="1" width="20000" stylename="Fields"/>
                <field col="3" name="Milliseconds" header="no" row="1" width="5000" stylename="Fields"/>
                <field col="4" name="Bytes" header="no" row="1" width="5000" stylename="Fields"/>
                <field col="5" name="Price" header="no" row="1" progress="yes" width="5000" format="0.00" stylename="Fields"/>
            </group>
        </sql>
    </report>
</book>

See directory test. TODO: normal documentation.

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

xls_report-0.0.8.tar.gz (10.2 kB view details)

Uploaded Source

Built Distribution

xls_report-0.0.8-py3-none-any.whl (8.4 kB view details)

Uploaded Python 3

File details

Details for the file xls_report-0.0.8.tar.gz.

File metadata

  • Download URL: xls_report-0.0.8.tar.gz
  • Upload date:
  • Size: 10.2 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.6.0 importlib_metadata/4.6.1 pkginfo/1.7.1 requests/2.22.0 requests-toolbelt/0.9.1 tqdm/4.61.2 CPython/3.8.10

File hashes

Hashes for xls_report-0.0.8.tar.gz
Algorithm Hash digest
SHA256 59b6f8c08d886f528f7d814539bda1bed2df4904e9490a13970dd4e55f20c182
MD5 075c02e7267a8250c168d7baebac5a30
BLAKE2b-256 98bddf49bb5bf32f94996f8197b38489347a02b31a070900e29d4643c7b0cb60

See more details on using hashes here.

File details

Details for the file xls_report-0.0.8-py3-none-any.whl.

File metadata

  • Download URL: xls_report-0.0.8-py3-none-any.whl
  • Upload date:
  • Size: 8.4 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.6.0 importlib_metadata/4.6.1 pkginfo/1.7.1 requests/2.22.0 requests-toolbelt/0.9.1 tqdm/4.61.2 CPython/3.8.10

File hashes

Hashes for xls_report-0.0.8-py3-none-any.whl
Algorithm Hash digest
SHA256 1b4a85ee1d80cc685c549085e54c2d0df7c92e68511812c23f72a80cbf25a87e
MD5 246777a3bc0b9723535d3e58bce11f1b
BLAKE2b-256 f7370d21e641dae08d6cf78b65259cc37a5554251431d0c54a120f53ff49f134

See more details on using hashes here.

Supported by

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