Skip to content

50.reporting.md

Latest commit

 

History

History
166 lines (140 loc) · 5.47 KB

50.reporting.md

File metadata and controls

166 lines (140 loc) · 5.47 KB

Building Reports

Exporting Excel Reports

A report is dynamically showing data in formats such as tables and charts, so some people use the formula below to describe a report:

Report = Rich formatting + Dynamic data

Many third-party libraries support writing Excel files in Python programs, including:

  • xlwt
  • xlwings
  • openpyxl
  • xlsxwriter

Among them, xlwt only supports writing Excel files in xls format, but its performance is still fairly good. Below, we use xlwt as an example to show how to export Excel reports in a Django project.

Install xlwt.

pip install xlwt

View function for exporting an Excel table that contains all teacher information.

def export_teachers_excel(request):
    # Create a workbook
    wb = xlwt.Workbook()
    # Add a worksheet
    sheet = wb.add_sheet('Teacher Information')
    # Query all teacher information
    queryset = Teacher.objects.all()
    # Write the table header into the Excel sheet
    colnames = ('Name', 'Introduction', 'Positive Vote Count', 'Negative Vote Count', 'Subject')
    for index, name in enumerate(colnames):
        sheet.write(0, index, name)
    # Write teacher data into cells
    props = ('name', 'detail', 'good_count', 'bad_count', 'subject')
    for row, teacher in enumerate(queryset):
        for col, prop in enumerate(props):
            value = getattr(teacher, prop, '')
            if isinstance(value, Subject):
                value = value.name
            sheet.write(row + 1, col, value)
    # Save the Excel file
    buffer = BytesIO()
    wb.save(buffer)
    # Write binary data into the response body and set the MIME type
    resp = HttpResponse(buffer.getvalue(), content_type='application/vnd.ms-excel')
    # The Chinese filename needs to be turned into percent-encoding
    filename = quote('teachers.xls')
    # Tell the browser to download this file and use the matching filename
    resp['content-disposition'] = f'attachment; filename*=utf-8\'\'{filename}'
    return resp

Map the URL.

urlpatterns = [
    path('excel/', views.export_teachers_excel),
]

Exporting PDF Reports

If you need to export a PDF report in a Django project, you can use the third-party library reportlab to generate the content of the PDF file, then output the binary data of the file to the browser and set the MIME type to application/pdf. The code is shown below.

def export_pdf(request: HttpRequest) -> HttpResponse:
    buffer = io.BytesIO()
    pdf = canvas.Canvas(buffer)
    pdf.setFont("Helvetica", 80)
    pdf.setFillColorRGB(0.2, 0.5, 0.3)
    pdf.drawString(100, 550, 'hello, world!')
    pdf.showPage()
    pdf.save()
    resp = HttpResponse(buffer.getvalue(), content_type='application/pdf')
    resp['content-disposition'] = 'inline; filename="demo.pdf"'
    return resp

For how to customize the content of a PDF report with reportlab, you can refer to the official documentation of reportlab.

Frontend Charts

If the project needs frontend statistical charts, you can use Baidu's ECharts. The usual way is that the backend provides a data API and returns the data needed by the statistical chart, and the frontend uses ECharts to render bar charts, line charts, pie charts, scatter charts, and other charts. For example, if we want to make a report that counts every teacher's positive votes and negative votes, we can do it in the following way.

Data API:

def get_teachers_data(request):
    queryset = Teacher.objects.all()
    names = [teacher.name for teacher in queryset]
    good_counts = [teacher.good_count for teacher in queryset]
    bad_counts = [teacher.bad_count for teacher in queryset]
    return JsonResponse({'names': names, 'good': good_counts, 'bad': bad_counts})

Map the URL.

urlpatterns = [
    path('teachers_data/', views.get_teachers_data),
]

Use ECharts to generate a bar chart.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Teacher Rating Statistics</title>
</head>
<body>
    <div id="main" style="width: 600px; height: 400px"></div>
    <p>
        <a href="/">Return to Home</a>
    </p>
    <script src="https://cdn.bootcss.com/echarts/4.2.1-rc1/echarts.min.js"></script>
    <script>
        var myChart = echarts.init(document.querySelector('#main'))
        fetch('/teachers_data/')
            .then(resp => resp.json())
            .then(json => {
                var option = {
                    color: ['#f00', '#00f'],
                    title: {
                        text: 'Teacher Rating Statistics Chart'
                    },
                    tooltip: {},
                    legend: {
                        data: ['Positive', 'Negative']
                    },
                    xAxis: {
                        data: json.names
                    },
                    yAxis: {},
                    series: [
                        {
                            name: 'Positive',
                            type: 'bar',
                            data: json.good
                        },
                        {
                            name: 'Negative',
                            type: 'bar',
                            data: json.bad
                        }
                    ]
                }
                myChart.setOption(option)
            })
    </script>
</body>
</html>

The running result is shown below.