Generate Excel Report

A sales report workbook from the invoice and invoice_line tables with Apache POI. Open it in Excel and try File > Print: orientation, fit-to-width, repeating header row and "Page 1 of 3" are already set.

What is inside

Sheet	Shows
Invoices	Merged title, styles, date and number formats, `=F5-G5+H5` totals, `SUM` row, auto-filter, frozen header, OVERDUE rows in red (conditional formatting), print setup.
By customer	`COUNTIFS` and `SUMIFS` formulas that read the Invoices sheet.
Line items	Every invoice line, `=D2*E2` amounts, filter and repeating header row.

How it works

try (XSSFWorkbook wb = new XSSFWorkbook()) {
    XSSFSheet sh = wb.createSheet("Invoices");
    Row row = sh.createRow(4);
    row.createCell(0).setCellValue("INV-202610-001");
    row.createCell(8).setCellFormula("F5-G5+H5");          // a live Excel formula

    CellStyle money = wb.createCellStyle();
    money.setDataFormat(wb.createDataFormat().getFormat("#,##0.00"));

    sh.setAutoFilter(new CellRangeAddress(3, 20, 0, 8));     // filter buttons
    sh.createFreezePane(1, 4);                              // keep header visible

    PrintSetup ps = sh.getPrintSetup();                     // Excel's own print settings
    ps.setLandscape(true);
    ps.setPaperSize(PrintSetup.A4_PAPERSIZE);
    ps.setFitWidth((short) 1); ps.setFitHeight((short) 0);  // 1 page wide, as tall as needed
    sh.setFitToPage(true);
    sh.setRepeatingRows(CellRangeAddress.valueOf("4:4"));   // header row on every page
    sh.getFooter().setRight("Page &P of &N");               // 367 in Excel
    wb.write(out);
}

-- The same numbers in SQL (VAT 7% after discount)
SELECT i.invoice_no, c.name, i.status,
       SUM(l.quantity * l.unit_price) AS subtotal,
       ROUND((SUM(l.quantity * l.unit_price) - i.discount) * (1 + i.vat_rate / 100), 2) AS total
FROM invoice i JOIN customer c ON c.id = i.customer_id JOIN invoice_line l ON l.invoice_id = i.id
GROUP BY i.invoice_no, c.name, i.status, i.discount, i.vat_rate ORDER BY i.invoice_no;

Files: service/ExcelReportService.java.

Generate Excel Report

Sales report

What is inside