Skip to content

Working on an XLS report?

Within opentrack we have multiple tools involved in working on xls reports, below we document some of them.

The bread and butter of all of this is "from xlwt import Workbook"

Existing xls files

competition reports:

- project/comp/excel_output.py          - Main big comp download report
- project/comp/excel_powerof10.py       - Power of 10 report
- project/summary/performance_excel.py  - Performances report
- project/entry/views.py                - Paid Entries & Financials report

club reports:

- project/federation/club_members_xls_download.py       - Club Members report
- project/federation/xls_download.py                    - listing page reports (e.g. competitions, athletes, venue, etc.)

Federation reports:

- project/federation/fed_dash_xls_download.py           - Federation dashboard report
- project/surreyleague/views.py                         - Start list report

Euroath reports:

- project/euroath/exports.py            - Calendar used by export_european_calendars command report
- project/compliance/views.py           - Compliance (single or multiple) reports

Payment reports:

- project/payments/views.py             - Reports on the treasurer menu and also through club payments tab


Helpful utility files

Here we will list some of the useful utility files for developers when developing and their details.

- project/reference/xls_utils.py
- project/comp/excel_output.py
- project/comp/excel_upload.py

xls_utils.py

This file was made recently as of August 2022. It was made because we had a lot of duplication across some of the xls files listed in Existing xls files

Some useful tools include (make sure you don't duplicate their functionality):

- W (class)                     - Wrapper that helps manage worksheets including setting widths and writing column names in the worksheet.
- write_to_sheet (method)       - Uniform tool used to vastly reduce the repetitive workbook write methods, pass in relevant parameters, (can even use an sql query) and it'll write in all the data to the worksheet.
- convert_to_colnames (method)  - Pass in the list of fields either "." separated or "_" separated and it will replace those with " " and put it in title case.
- format_xls_amount (method)    - Return a string containing the currency and amount together.
- is_none (method)              - Return empty string if none or string of object.
- access (method)               - Highly useful, fetches related objects too if "." separated (e.g. person.extra.postal_town as the fieldname on a role object)
- format objects (variables)    - date_format, decimal_format, etc. Used for manually defined excels

excel_output.py

...

excel_upload.py

...

Setting up a new xls report

If you're setting up a new report and you don't quite know what to do there are some things to keep in mind.

Automatic setup

Tools:

  • Workbook class - (from xlwt import Workbook)
  • W class - (from project.reference.xls_utils import W)
  • write_to_sheet function - (from project.reference.xls_utils import write_to_sheet)
  • convert_to_colnames function - (from project.reference.xls_utils import convert_to_colnames)

The "Workbook" class allows you to declare the workbook instance. from it you can run the add_sheet method which will return the worksheet object which you can wrap with the W class.

Using the "W" wrapper class on the sheet will provide you useful tools for setting column widths with setWidths and writing the column headers with writeFields.

The "write_to_sheet" function file takes in the parameters including the W wrapper object and will output your data at the given row in a correct format (e.g. date or money).

The "convert_to_colnames" function takes in a list of fields and displays them in a better format for use as column headings

  • You can provide "multi_row_fields" for when you want multiple rows in one cell. "money_fields" and "currency" for if you have money-based fields, "date_fields" for any date-based fields. "number_fields_fields" for any numerical-based fields where you don't want a currency symbol, either ints or floats.

  • You can also provide an SQL query, if you pass in the query data it's set up to account for those in case you're not using a django type filter

  • It also works using the access class, meaning you can use related fields, e.g. if I have a queryset of roles I pass in, I can have a field be "person.extra.interest_c" and it will fetch the related fields. This also works for @property methods, but not classes.

EXAMPLE CODE

qry = Queryset objects (e.g. IndivEntry.objects.filter())
from xlwt import Workbook
from project.reference.xls_utils import W, write_to_sheet, convert_to_colnames
wb = Workbook()
sheet = wb.add_sheet("My Sheet")
w = W(sheet)
sheet.write(0, 0, "Random data I can put at a specific cell.")
fields = ['field_1', 'field_2', 'field_3', 'field_4', 'field_5']
w.writeFields(4, convert_to_colnames(fields))
# Optional parameters for "write_to_sheet", field-type fields : multi_row_fields, date_fields, number_fields, money_fields
write_to_sheet(w, 5, qry, fields, multi_row_fields=['field_3'], date_fields=['field_4', 'field_5'])

Manual setup

Tools:

  • Workbook class - (from xlwt import Workbook)
  • W class - (from project.reference.xls_utils import W)

The "Workbook" class allows you to declare the workbook instance. from it you can run the add_sheet method passing in a sheet name string, which will return the worksheet object which you can wrap with the W class. You can call the writeFields() methos if you want to write a whole row at once given just the row and the fields, very good for getting the column headers

You can use the write() method of sheet passing in a row and a column and the text you want to enter into the cell at that position.

You can input the formats: date_format, number_format, decimal_format, money_format, totals_format, wrap_text. If you call W(row, col, data, format) this calls the wrksheet write method, and you can pass in that format for the data passed in, that will then be written to the cell at position (row, col)

EXAMPLE CODE

ies = IndivEntry.objects.filter().order_by('entry_num')
if ies:
    sheet = wb.add_sheet("Entries")

    sheet.write(1, 0, "All entries")
    colnames = ["Order No.", "Bib", "First Name", "Last Name", "Events"]

    row = 4
    for (i, colname) in enumerate(colnames):
        sheet.write(row, i, colname)
    row = 5
    for ie in ies:
        p = ie.person
        col = 0
        sheet.write(row, col, ie.entry_num)
        sheet.write(row, col+1, ie.get_bib(prefetched_competitors=prefetched_competitors))
        sheet.write(row, col+2, p.first_name)
        sheet.write(row, col+3, p.last_name)
        ecs = []
        for iee in ie.events_entered.select_related('event').all():
            ecs.append(iee.event.event_code)
        ecs = ", ".join(ecs)
        sheet.write(row, col+4, ecs)

        row += 1

Notes

...