Skip to content

Generate Excel

SØAD includes built-in support for Excel file generation using Apache POI. This recipe demonstrates how to create Excel spreadsheets with data from your database, format cells, and serve them as downloadable files—perfect for reports, data exports, or financial statements.

Generate Excel from Database Data

sales_report.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
from utils import render
from models import Sale
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.ss.usermodel import CellStyle, Font, FillPatternType, IndexedColors
from java.io import ByteArrayOutputStream
from java.util import Date
from java.text import SimpleDateFormat

class Sales_report(object):
    def excel(self, ctx):
        # Create workbook and worksheet
        workbook = XSSFWorkbook()
        sheet = workbook.createSheet("Sales Report")

        # Create styles for header and data
        header_style = workbook.createCellStyle()
        header_font = workbook.createFont()
        header_font.setBold(True)
        header_font.setColor(IndexedColors.WHITE.getIndex())
        header_style.setFont(header_font)
        header_style.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex())
        header_style.setFillPattern(FillPatternType.SOLID_FOREGROUND)

        date_style = workbook.createCellStyle()
        date_format = workbook.getCreationHelper().createDataFormat()
        date_style.setDataFormat(date_format.getFormat("yyyy-mm-dd"))

        currency_style = workbook.createCellStyle()
        currency_style.setDataFormat(date_format.getFormat("#,##0.00"))

        # Create header row
        header_row = sheet.createRow(0)
        headers = ["ID", "Product Name", "Customer", "Sale Date", "Quantity", "Unit Price", "Total Amount"]

        for col_idx, header in enumerate(headers):
            cell = header_row.createCell(col_idx)
            cell.setCellValue(header)
            cell.setCellStyle(header_style)

        # Fetch data from database
        sales = Sale.findAll().orderBy("sale_date DESC")

        row_idx = 1
        for sale in sales:
            row = sheet.createRow(row_idx)

            # ID
            row.createCell(0).setCellValue(sale.get("id"))

            # Product Name
            row.createCell(1).setCellValue(sale.get("product_name") or "")

            # Customer
            row.createCell(2).setCellValue(sale.get("customer_name") or "")

            # Sale Date
            date_cell = row.createCell(3)
            if sale.get("sale_date"):
                date_cell.setCellValue(sale.getDate("sale_date"))
                date_cell.setCellStyle(date_style)

            # Quantity
            row.createCell(4).setCellValue(sale.getInteger("quantity") or 0)

            # Unit Price
            price_cell = row.createCell(5)
            price_cell.setCellValue(sale.getDouble("unit_price") or 0.0)
            price_cell.setCellStyle(currency_style)

            # Total Amount
            total_cell = row.createCell(6)
            total_amount = (sale.getInteger("quantity") or 0) * (sale.getDouble("unit_price") or 0.0)
            total_cell.setCellValue(total_amount)
            total_cell.setCellStyle(currency_style)

            row_idx += 1

        # Auto-size columns
        for col_idx in range(len(headers)):
            sheet.autoSizeColumn(col_idx)

        # Write to byte array
        output_stream = ByteArrayOutputStream()
        workbook.write(output_stream)
        workbook.close()

        # Generate filename with current date
        date_format = SimpleDateFormat("yyyy-MM-dd")
        filename = "sales_report_%s.xlsx" % date_format.format(Date())

        ctx.go_to = render.as_blob(
            ctx,
            output_stream.toByteArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename,
            attachment=True
        )

CREATE TABLE sale (
    id int NOT NULL AUTO_INCREMENT,
    product_name varchar(255),
    customer_name varchar(255),
    sale_date date,
    quantity int,
    unit_price decimal(10,2),
    PRIMARY KEY (id)
);

INSERT INTO sale (product_name, customer_name, sale_date, quantity, unit_price) VALUES
('Laptop Pro 15"', 'Acme Corporation', '2024-01-15', 5, 2499.99),
('Wireless Mouse', 'Tech Solutions Ltd', '2024-01-16', 25, 29.99),
('USB-C Hub', 'Digital Dynamics', '2024-01-17', 10, 89.99),
('Monitor 4K 27"', 'Creative Agency', '2024-01-18', 3, 599.99),
('Mechanical Keyboard', 'StartupXYZ', '2024-01-19', 15, 149.99),
('Webcam HD', 'Remote Workers Inc', '2024-01-20', 8, 79.99),
('Tablet Pro', 'Design Studio', '2024-01-21', 2, 899.99),
('Smartphone', 'Mobile Solutions', '2024-01-22', 12, 799.99),
('Headphones Pro', 'Audio Experts', '2024-01-23', 6, 299.99),
('Power Bank', 'Travel Co', '2024-01-24', 20, 49.99);

Download the Excel File

The user can download the Excel report via:

/t/example/sales_report/excel

The browser will download the formatted Excel file with proper styling and data formatting.

Generate Excel with Charts

For more advanced Excel generation with charts and pivot tables, you can use Apache POI's chart capabilities.

sales_chart.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
from utils import render
from models import Sale
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.ss.usermodel import CellStyle, Row
from org.apache.poi.xddf.usermodel.chart import ChartTypes, XDDFDataSource, XDDFNumericalDataSource, AxisPosition
from org.apache.poi.xddf.usermodel.chart import XDDFDataSourcesFactory, XDDFChartData
from org.apache.poi.ss.util import CellRangeAddress
from java.io import ByteArrayOutputStream
from java.util import Date
from java.text import SimpleDateFormat

class Sales_chart(object):
    def excel(self, ctx):
        workbook = XSSFWorkbook()
        sheet = workbook.createSheet("Sales by Product")

        # Aggregate sales data by product
        product_sales = {}
        sales = Sale.findAll()

        for sale in sales:
            product = sale.get("product_name") or "Unknown"
            total = (sale.getInteger("quantity") or 0) * (sale.getDouble("unit_price") or 0.0)

            if product in product_sales:
                product_sales[product] += total
            else:
                product_sales[product] = total

        # Create data rows
        row_idx = 0

        # Headers
        header_row = sheet.createRow(row_idx)
        header_row.createCell(0).setCellValue("Product")
        header_row.createCell(1).setCellValue("Total Sales")
        row_idx += 1

        # Data rows
        for product, total_sales in product_sales.items():
            row = sheet.createRow(row_idx)
            row.createCell(0).setCellValue(product)
            row.createCell(1).setCellValue(total_sales)
            row_idx += 1

        # Create a chart
        drawing = sheet.createDrawingPatriarch()
        anchor = drawing.createAnchor(0, 0, 0, 0, 4, 1, 15, 20)  # Position of chart

        chart = drawing.createChart(anchor)
        chart.setTitleText("Sales by Product")

        category_axis = chart.createCategoryAxis(AxisPosition.BOTTOM)
        value_axis = chart.createValueAxis(AxisPosition.LEFT)

        # Define data sources for the chart
        data_range = CellRangeAddress(1, row_idx - 1, 1, 1)  # Sales data
        category_range = CellRangeAddress(1, row_idx - 1, 0, 0)  # Product names

        categories = XDDFDataSourcesFactory.fromStringCellRange(sheet, category_range)
        values = XDDFDataSourcesFactory.fromNumericCellRange(sheet, data_range)

        # Create bar chart
        chart_data = chart.createData(ChartTypes.BAR, category_axis, value_axis)
        series = chart_data.addSeries(categories, values)
        series.setTitle("Sales Amount", None)

        chart.plot(chart_data)

        # Auto-size columns
        sheet.autoSizeColumn(0)
        sheet.autoSizeColumn(1)

        # Write to byte array
        output_stream = ByteArrayOutputStream()
        workbook.write(output_stream)
        workbook.close()

        # Generate filename
        date_format = SimpleDateFormat("yyyy-MM-dd")
        filename = "sales_chart_%s.xlsx" % date_format.format(Date())

        ctx.go_to = render.as_blob(
            ctx,
            output_stream.toByteArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename,
            attachment=True
        )

Download the Chart Excel File

The user can download the Excel file with embedded chart via:

/t/example/sales_chart/excel

Generate Excel Template

Sometimes you need to generate Excel templates with predefined formatting that users can fill out.

invoice_template.py
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
from utils import render
from org.apache.poi.xssf.usermodel import XSSFWorkbook
from org.apache.poi.ss.usermodel import CellStyle, Font, BorderStyle, FillPatternType, IndexedColors, HorizontalAlignment
from org.apache.poi.ss.util import CellRangeAddress
from java.io import ByteArrayOutputStream

class Invoice_template(object):
    def excel(self, ctx):
        workbook = XSSFWorkbook()
        sheet = workbook.createSheet("Invoice Template")

        # Create styles
        title_style = workbook.createCellStyle()
        title_font = workbook.createFont()
        title_font.setBold(True)
        title_font.setFontHeightInPoints(16)
        title_style.setFont(title_font)
        # Center alignment - remove if causing issues
        # title_style.setAlignment(HorizontalAlignment.CENTER)

        header_style = workbook.createCellStyle()
        header_font = workbook.createFont()
        header_font.setBold(True)
        header_style.setFont(header_font)
        header_style.setBorderBottom(BorderStyle.THIN)
        header_style.setBorderTop(BorderStyle.THIN)
        header_style.setBorderLeft(BorderStyle.THIN)
        header_style.setBorderRight(BorderStyle.THIN)
        header_style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex())
        header_style.setFillPattern(FillPatternType.SOLID_FOREGROUND)

        border_style = workbook.createCellStyle()
        border_style.setBorderBottom(BorderStyle.THIN)
        border_style.setBorderTop(BorderStyle.THIN)
        border_style.setBorderLeft(BorderStyle.THIN)
        border_style.setBorderRight(BorderStyle.THIN)

        # Title
        title_row = sheet.createRow(0)
        title_cell = title_row.createCell(0)
        title_cell.setCellValue("INVOICE TEMPLATE")
        title_cell.setCellStyle(title_style)
        sheet.addMergedRegion(CellRangeAddress(0, 0, 0, 6))

        # Company info section
        sheet.createRow(2).createCell(0).setCellValue("Company Name:")
        sheet.createRow(3).createCell(0).setCellValue("Address:")
        sheet.createRow(4).createCell(0).setCellValue("Phone:")
        sheet.createRow(5).createCell(0).setCellValue("Email:")

        # Invoice details
        sheet.createRow(2).createCell(4).setCellValue("Invoice No:")
        sheet.createRow(3).createCell(4).setCellValue("Date:")
        sheet.createRow(4).createCell(4).setCellValue("Due Date:")

        # Customer section
        sheet.createRow(7).createCell(0).setCellValue("Bill To:")
        sheet.createRow(8).createCell(0).setCellValue("Customer Name:")
        sheet.createRow(9).createCell(0).setCellValue("Customer Address:")

        # Items table header
        header_row = sheet.createRow(12)
        headers = ["Description", "Quantity", "Unit Price", "Total"]
        for col_idx, header in enumerate(headers):
            cell = header_row.createCell(col_idx)
            cell.setCellValue(header)
            cell.setCellStyle(header_style)

        # Create empty rows for items with borders
        for row_idx in range(13, 23):  # 10 empty rows
            row = sheet.createRow(row_idx)
            for col_idx in range(4):
                cell = row.createCell(col_idx)
                cell.setCellStyle(border_style)

        # Total section
        total_row = sheet.createRow(24)
        total_row.createCell(2).setCellValue("TOTAL:")
        total_cell = total_row.createCell(3)
        total_cell.setCellStyle(border_style)

        # Set column widths
        sheet.setColumnWidth(0, 8000)  # Description
        sheet.setColumnWidth(1, 3000)  # Quantity
        sheet.setColumnWidth(2, 4000)  # Unit Price
        sheet.setColumnWidth(3, 4000)  # Total

        # Write to byte array
        output_stream = ByteArrayOutputStream()
        workbook.write(output_stream)
        workbook.close()

        ctx.go_to = render.as_blob(
            ctx,
            output_stream.toByteArray(),
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "invoice_template.xlsx",
            attachment=True
        )

Download the Template

The user can download the Excel template via:

/t/example/invoice_template/excel

Do you know?

  • Use attachment=True to force download instead of displaying inline
  • Apache POI supports both .xls (older format) and .xlsx (newer format) files
  • For large datasets, consider using streaming APIs like SXSSF for better memory efficiency
  • You can password-protect Excel files using workbook.writeProtectWorkbook(password, username)