xls-report 0.0.5

XLS U报告

根据XML描述生成XLS格式的数据库报告

示例：

#!/usr/bin/python3importsqlite3fromxls_reportimportXLSReportconnect=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><styledefname="Subheaders">
            font: bold True; alignment: horiz centre;
            borders: left 1, top 1, bottom 1, right 1;
        </styledef><styledefname="Totals">
            font: bold True;
        </styledef><styledefname="Fields">borders: left 1, top 1, bottom 1, right 1;</styledef><styledefname="Headers">font: bold True; alignment: horiz centre;</styledef><literalcol="0"row="0"stylename="Subheaders">Last name</literal><literalcol="1"row="0"stylename="Subheaders">First name</literal><literalcol="2"row="0"stylename="Subheaders">Amount</literal><literalcol="3"row="0"stylename="Subheaders">Discount</literal><literalcol="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><groupstep="1"><fieldcol="0"name="Last name"header="no"row="1"stylename="Fields"/><fieldcol="1"name="First name"header="no"row="1"stylename="Fields"/><fieldcol="2"name="Amount"header="no"row="1"stylename="Fields"/><fieldcol="3"name="Discount"header="no"row="1"stylename="Fields"/></group><formulacol="4"name="Total"row="1"header="no"stylename="Fields"format="0.00">
                    C{{cs}}-D{{cs}}
            </formula></sql><sql><literalcol="0"row="1"stylename="Totals">Total:</literal><formulacol="4"name="Total"row="1"header="no"stylename="Totals"format="0.00">
                    SUM(E2:E{{ds}})
            </formula></sql></report><report><name>{{title1}}</name><literalcol="0"row="0"stylename="Subheaders">Artist</literal><literalcol="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><groupstep="1"><fieldcol="0"name="Artist"header="no"row="1"width="20000"stylename="Fields"/><fieldcol="1"name="Album"header="no"row="1"width="20000"stylename="Fields"/></group></sql></report><report><name>{{title2}}</name><literalcol="0"row="0"stylename="Headers">Report by some genres</literal><literalcol="0"row="2"stylename="Headers">Media</literal><literalcol="1"row="2"stylename="Headers">Genre</literal><literalcol="2"row="2"stylename="Headers">Amount</literal><literalcol="3"row="2"stylename="Headers">Discount</literal><literalcol="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><groupstep="4"><fieldcol="0"name="Media"header="no"row="3"width="7000"/><fieldcol="2"name="Money"header="no"row="3"/><fieldcol="3"name="Discount"header="no"row="3"/><groupliteralcol="1"name="Type"row="3"header="no">Latin</groupliteral><formulacol="4"name="Total"row="3"header="no">C{{cs}}-D{{cs}}</formula></group></sql><sqlcycle="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><groupstep="4"><fieldcol="2"name="Money"header="no"row="4"/><fieldcol="3"name="Discount"header="no"row="4"/><groupliteralcol="1"name="Type"row="4"header="no">World</groupliteral><formulacol="4"name="Total"row="4"header="no">C{{cs}}-D{{cs}}</formula></group></sql><sqlcycle="yes"><groupstep="4"><groupliteralcol="0"name="Type"row="5"header="no"stylename="Totals">Subtotal:</groupliteral><formulacol="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><literalcol="0"row="0"stylename="Totals">Total:</literal><formulacol="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><literalcol="0"row="0"stylename="Subheaders">Media</literal><literalcol="1"row="0"stylename="Subheaders">Genre</literal><literalcol="2"row="0"stylename="Subheaders">Amount</literal><literalcol="3"row="0"stylename="Subheaders">Discount</literal><literalcol="4"row="0"stylename="Subheaders">Charged</literal><sql><requestsuppress="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><groupstep="1"><fieldcol="0"name="Media"header="no"row="1"width="7000"stylename="Fields"/><fieldcol="1"name="Genre"header="no"row="1"width="5000"stylename="Fields"/><fieldcol="2"name="Money"header="no"row="1"format="0.00"stylename="Fields"/><fieldcol="3"name="Discount"header="no"row="1"format="0.00"stylename="Fields"/><fieldcol="4"name="Charged"header="no"row="1"format="0.00"stylename="Fields"/></group></sql></report><report><name>{{title4}}</name><literalcol="0"row="0"stylename="Subheaders">Customer</literal><literalcol="1"row="0"stylename="Subheaders">Media</literal><literalcol="2"row="0"stylename="Subheaders">Genre</literal><literalcol="3"row="0"stylename="Subheaders">Amount</literal><literalcol="4"row="0"stylename="Subheaders">Discount</literal><literalcol="5"row="0"stylename="Subheaders">Charged</literal><sql><requestsuppress="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><groupstep="1"><fieldcol="0"name="Customer"header="no"row="1"width="5000"stylename="Fields"/><fieldcol="1"name="Media"header="no"row="1"width="7000"stylename="Fields"/><fieldcol="2"name="Genre"header="no"row="1"width="5000"stylename="Fields"/><fieldcol="3"name="Money"header="no"row="1"format="0.00"stylename="Fields"/><fieldcol="4"name="Discount"header="no"row="1"format="0.00"stylename="Fields"/><fieldcol="5"name="Charged"header="no"row="1"format="0.00"stylename="Fields"/></group></sql></report><report><name>Playlist</name><literalcol="0"row="0"stylename="Subheaders">Playlist</literal><literalcol="1"row="0"stylename="Subheaders">Album</literal><literalcol="2"row="0"stylename="Subheaders">Track</literal><literalcol="3"row="0"stylename="Subheaders">Milliseconds</literal><literalcol="4"row="0"stylename="Subheaders">Bytes</literal><literalcol="5"row="0"stylename="Subheaders">Price</literal><sql><requestsuppress="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><groupstep="1"><fieldcol="0"name="Playlist"header="no"row="1"width="5900"stylename="Fields"/><fieldcol="1"name="Album"header="no"row="1"width="20500"stylename="Fields"/><fieldcol="2"name="Track"header="no"row="1"width="20000"stylename="Fields"/><fieldcol="3"name="Milliseconds"header="no"row="1"width="5000"stylename="Fields"/><fieldcol="4"name="Bytes"header="no"row="1"width="5000"stylename="Fields"/><fieldcol="5"name="Price"header="no"row="1"progress="yes"width="5000"format="0.00"stylename="Fields"/></group></sql></report></book>

请参阅目录测试。待办事项：常规文档。

标签：

• report
• 数据库
• 报表
• 格式
• col
• xml
• xls
• row
• literal

欢迎加入QQ群-->： 979659372

推荐PyPI第三方库

• wdiffhtml

  使用“wdiff”从纯文本文件生成基于单词的*diff*。

• cwsp

  conworkshop的一个简单api包装器

• galaxy-updater

  检索所有Ansible Galaxy角色依赖项的最新版本。

• deconvoluted

  反褶积使执行积分变换变得简单和蟒蛇！

• LinkY

  创建到测试命令行工具的全局安装符号链接。

• murano-agent

  python murano代理

• excel2json-3

  将Excel工作表转换为JSON文件。

• zoidberg

  多个gerrit实例的事件响应工具

• syned

  syned（同步加速器元件字典）内核库

• setuptools-markdown

  [不推荐]对项目说明使用降价

• ome-ansible-molecule-dependencies

  测试某些负责角色的依赖项

• txcelery-py3

  Twisted的芹菜：使用延迟API管理Twisted中的芹菜任务

• sslscan

  扫描启用ssl的服务的框架和命令行工具

• django-closuretree

  django的高效树型数据结构

• linkat

  python对posix/linux函数linkat和symlinkat的支持。