Web-WF Python Tornado Part 3 (Introduction to Openpyexcel)

Introduction

As a webServer APIServer, I would like to introduce Tornado, which is easy and excellent (I think), in several parts.

[Web-WF Python Tornado Part 1] (https://qiita.com/sin_hayasi/private/c5af3b21e7abda196cfd) [Web-WF Python Tornado Part 2] (https://qiita.com/sin_hayasi/private/bdc4f45b092ed5433aec) Web-WF Python Tornado Part 3 (This article

Target

Full stack engineer, front engineer, person who likes Python, person who read part 1 and part 2, person who was forced to output some material with Excel

Python3.6 or above, tornado installed

goal

This time, I will introduce a module that is convenient to have in the application with a slightly different taste. （Openpyxl）

What is Openpyxl

Module that can read, write and edit Excel files from python It is convenient to output the aggregated result as a report in Excel, or conversely to import the Excel data. This time I will create a sample to output the invoice [Official here] (https://openpyxl.readthedocs.io/en/stable/)

Installation

Openpyxl can only be installed with pip

$ pip install openpyxl

preparation of excel template

Prepare the following template (The part in red is a macro sample)

Source code

Add the following sources to the sources introduced in Part 1 and Part 2.

main.py

import os
import datetime
import openpyxl as px
from openpyxl.writer.excel import save_virtual_workbook

class prtInvoice(tornado.web.RequestHandler):
    def get(self):
        #Load invoice template
        wb = px.load_workbook(BASE_DIR+u'/template.xlsx')
        ws = wb.active
        #Specify print range
        ws.page_setup.fitToWidth = 1
        ws.page_setup.fitToHeight = 0
        ws.sheet_properties.pageSetUpPr.fitToPage = True
        #Prepare such a model (actually from DB etc.
        model = {
            'companyName': 'Test company',
            'items': [
                {'itemName': 'Product A', 'price': 1000, 'quantity': 1},
                {'itemName': 'Product B', 'price': 2000, 'quantity': 2},
                {'itemName': 'Product C', 'price': 3000, 'quantity': 3}
            ]
        }

        #Set today's date
        #F2 is a cell in Excel
        dt_now = datetime.datetime.now()
        ws['F2'] = dt_now.strftime("%Y year%m month 〆day")
        #Set company name
        ws['B7'] = '{0}You'.format(model['companyName'])
        for i, item in enumerate(model['items']):
            #Set the product name
            ws['B'+str(i+25)] = '　　{0}'.format(item['itemName'])
            #Set quantity
            ws['D'+str(i+25)] = item['quantity']
            #Set unit price
            ws['E'+str(i+25)] = item['price']

        #Download in Excel format
        self.set_header('Cache-Control',
                        'no-store, no-cache, must-revalidate, max-age=0')
        self.set_header(
            'Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        fileName = "Invoice.xlsx"
        self.set_header('Content-Disposition',
                        "attachment; filename=\"{}\"".format(fileName))
        self.write(save_virtual_workbook(wb))

def make_app():
    return tornado.web.Application(
        [
        (r"/html", htmlHandler),
        (r"/json", jsonHandler),
        (r"/excel", prtInvoice),
        ],
        debug=True,
    )

ws['B7'] = 'Hello'

The feature of this module is that you can set and read data in Excel cells like this.

Run & browse

You can download the Excel file by directly specifying the URL as shown below. (Saved directly in the client download folder)

When you open the downloaded Excel file Yes, I managed to display that kind of data