Automated Invoice Generation: Creating Professional Excel Invoices with Apache POI in Java

Article

In business applications, generating professional invoices is a common requirement. While PDFs are often used for final distribution, Excel invoices provide flexibility for editing, calculations, and further analysis. Apache POI is the definitive Java library for working with Microsoft Office documents, making it ideal for programmatically creating detailed, formatted Excel invoices directly from Java applications.

What is Apache POI?

Apache POI is the Java API for Microsoft Documents that provides:

Excel Support: Read/write .xls and .xlsx formats
Word Processing: Create and modify Word documents
PowerPoint: Generate presentations programmatically
Rich Formatting: Cells, fonts, colors, borders, and formulas
Charts and Images: Embed visual elements in spreadsheets

Why Excel Invoices with Apache POI?

Automation: Generate invoices automatically from order data
Customization: Fully control layout, formatting, and branding
Calculations: Built-in Excel formulas for totals and taxes
Professional Appearance: Create branded, professional invoices
Client Flexibility: Excel files are editable and widely compatible

Invoice Generation Architecture

Order Data → Java Service → Apache POI → Excel Invoice → Email/Download ↓ Template Processing → Branded Output ↓ Calculations → Totals, Taxes, Discounts

Setting Up Apache POI

1. Add Dependencies:

<!-- pom.xml --> <dependencies> <!-- Apache POI for Excel --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.4</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>5.2.4</version> </dependency> <!-- Spring Boot for REST API --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!-- Jackson for JSON --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> </dependency> <!-- Validation --> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-validation</artifactId> </dependency> </dependencies>

Core Invoice Service

1. Excel Invoice Service:

@Service @Slf4j public class ExcelInvoiceService { private final CompanyConfig companyConfig; // Styles cache private Map<String, CellStyle> styles = new HashMap<>(); public ExcelInvoiceService(CompanyConfig companyConfig) { this.companyConfig = companyConfig; } public Workbook generateInvoice(InvoiceData invoiceData) { Workbook workbook = new XSSFWorkbook(); // .xlsx format try { initializeStyles(workbook); createInvoiceSheet(workbook, invoiceData); return workbook; } catch (Exception e) { log.error("Failed to generate invoice", e); throw new InvoiceGenerationException("Failed to generate Excel invoice", e); } } public byte[] generateInvoiceBytes(InvoiceData invoiceData) { try (Workbook workbook = generateInvoice(invoiceData); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { workbook.write(outputStream); return outputStream.toByteArray(); } catch (Exception e) { log.error("Failed to generate invoice bytes", e); throw new InvoiceGenerationException("Failed to generate invoice file", e); } } private void createInvoiceSheet(Workbook workbook, InvoiceData invoiceData) { Sheet sheet = workbook.createSheet("Invoice"); // Set column widths sheet.setColumnWidth(0, 12 * 256); // Column A: 12 characters sheet.setColumnWidth(1, 40 * 256); // Column B: 40 characters sheet.setColumnWidth(2, 12 * 256); // Column C: 12 characters sheet.setColumnWidth(3, 15 * 256); // Column D: 15 characters sheet.setColumnWidth(4, 15 * 256); // Column E: 15 characters sheet.setColumnWidth(5, 15 * 256); // Column F: 15 characters int currentRow = 0; // Header section currentRow = createHeaderSection(sheet, currentRow, invoiceData); currentRow++; // Empty row // Billing and shipping addresses currentRow = createAddressSection(sheet, currentRow, invoiceData); currentRow++; // Empty row // Invoice details currentRow = createInvoiceDetailsSection(sheet, currentRow, invoiceData); currentRow++; // Empty row // Line items table currentRow = createLineItemsSection(sheet, currentRow, invoiceData); currentRow++; // Empty row // Totals section currentRow = createTotalsSection(sheet, currentRow, invoiceData); currentRow++; // Empty row // Terms and conditions currentRow = createTermsSection(sheet, currentRow, invoiceData); // Auto-size some columns for better fit sheet.autoSizeColumn(1); // Description column } private int createHeaderSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; // Company logo and name (Row 1) Row companyRow = sheet.createRow(rowNum++); createMergedCell(sheet, companyRow, 0, 2, 0, 0, companyConfig.getCompanyName(), styles.get("headerTitle")); // Invoice title (Row 2) Row titleRow = sheet.createRow(rowNum++); createMergedCell(sheet, titleRow, 0, 2, 0, 0, "INVOICE", styles.get("invoiceTitle")); // Company details (Row 3-6) Row addressRow = sheet.createRow(rowNum++); createCell(addressRow, 0, companyConfig.getAddress(), styles.get("normal")); Row cityRow = sheet.createRow(rowNum++); createCell(cityRow, 0, companyConfig.getCity() + ", " + companyConfig.getState() + " " + companyConfig.getZip(), styles.get("normal")); Row phoneRow = sheet.createRow(rowNum++); createCell(phoneRow, 0, "Phone: " + companyConfig.getPhone(), styles.get("normal")); Row emailRow = sheet.createRow(rowNum++); createCell(emailRow, 0, "Email: " + companyConfig.getEmail(), styles.get("normal")); return rowNum; } private int createAddressSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; // Billing Address Row billToTitleRow = sheet.createRow(rowNum++); createCell(billToTitleRow, 0, "BILL TO:", styles.get("sectionHeader")); Row billToNameRow = sheet.createRow(rowNum++); createCell(billToNameRow, 0, invoiceData.getCustomer().getName(), styles.get("bold")); Row billToAddressRow = sheet.createRow(rowNum++); createCell(billToAddressRow, 0, invoiceData.getCustomer().getAddress(), styles.get("normal")); Row billToCityRow = sheet.createRow(rowNum++); createCell(billToCityRow, 0, invoiceData.getCustomer().getCity() + ", " + invoiceData.getCustomer().getState() + " " + invoiceData.getCustomer().getZipCode(), styles.get("normal")); // Shipping Address (if different) if (invoiceData.getShippingAddress() != null && !invoiceData.getShippingAddress().equals(invoiceData.getCustomer().getAddress())) { rowNum++; // Empty row Row shipToTitleRow = sheet.createRow(rowNum++); createCell(shipToTitleRow, 0, "SHIP TO:", styles.get("sectionHeader")); Row shipToNameRow = sheet.createRow(rowNum++); createCell(shipToNameRow, 0, invoiceData.getShippingAddress().getName(), styles.get("bold")); Row shipToAddressRow = sheet.createRow(rowNum++); createCell(shipToAddressRow, 0, invoiceData.getShippingAddress().getAddress(), styles.get("normal")); Row shipToCityRow = sheet.createRow(rowNum++); createCell(shipToCityRow, 0, invoiceData.getShippingAddress().getCity() + ", " + invoiceData.getShippingAddress().getState() + " " + invoiceData.getShippingAddress().getZipCode(), styles.get("normal")); } return rowNum; } private int createInvoiceDetailsSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; // Create a 2-column layout for invoice details Row invoiceNoRow = sheet.createRow(rowNum++); createCell(invoiceNoRow, 3, "Invoice Number:", styles.get("boldRight")); createCell(invoiceNoRow, 4, invoiceData.getInvoiceNumber(), styles.get("normal")); Row invoiceDateRow = sheet.createRow(rowNum++); createCell(invoiceDateRow, 3, "Invoice Date:", styles.get("boldRight")); createCell(invoiceDateRow, 4, formatDate(invoiceData.getInvoiceDate()), styles.get("normal")); Row dueDateRow = sheet.createRow(rowNum++); createCell(dueDateRow, 3, "Due Date:", styles.get("boldRight")); createCell(dueDateRow, 4, formatDate(invoiceData.getDueDate()), styles.get("normal")); Row poNumberRow = sheet.createRow(rowNum++); createCell(poNumberRow, 3, "PO Number:", styles.get("boldRight")); createCell(poNumberRow, 4, invoiceData.getPurchaseOrderNumber(), styles.get("normal")); Row termsRow = sheet.createRow(rowNum++); createCell(termsRow, 3, "Terms:", styles.get("boldRight")); createCell(termsRow, 4, invoiceData.getPaymentTerms(), styles.get("normal")); return rowNum; } private int createLineItemsSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; // Table header Row headerRow = sheet.createRow(rowNum++); String[] headers = {"Item #", "Description", "Quantity", "Unit Price", "Amount"}; for (int i = 0; i < headers.length; i++) { createCell(headerRow, i, headers[i], styles.get("tableHeader")); } // Line items for (InvoiceItem item : invoiceData.getItems()) { Row itemRow = sheet.createRow(rowNum++); createCell(itemRow, 0, item.getItemNumber(), styles.get("normalCenter")); createCell(itemRow, 1, item.getDescription(), styles.get("normal")); createCell(itemRow, 2, item.getQuantity(), styles.get("normalCenter")); createCell(itemRow, 3, formatCurrency(item.getUnitPrice()), styles.get("currency")); // Calculate line total double lineTotal = item.getQuantity() * item.getUnitPrice(); createCell(itemRow, 4, formatCurrency(lineTotal), styles.get("currency")); } return rowNum; } private int createTotalsSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; // Start from column 3 to align with amount column int startCol = 3; // Subtotal Row subtotalRow = sheet.createRow(rowNum++); createCell(subtotalRow, startCol, "Subtotal:", styles.get("boldRight")); createCell(subtotalRow, startCol + 1, formatCurrency(calculateSubtotal(invoiceData)), styles.get("currency")); // Discount if (invoiceData.getDiscountAmount() > 0 || invoiceData.getDiscountPercent() > 0) { double discount = calculateDiscount(invoiceData); Row discountRow = sheet.createRow(rowNum++); String discountLabel = "Discount"; if (invoiceData.getDiscountPercent() > 0) { discountLabel += " (" + invoiceData.getDiscountPercent() + "%)"; } createCell(discountRow, startCol, discountLabel, styles.get("boldRight")); createCell(discountRow, startCol + 1, formatCurrency(discount), styles.get("currency")); } // Tax if (invoiceData.getTaxRate() > 0) { double taxAmount = calculateTax(invoiceData); Row taxRow = sheet.createRow(rowNum++); createCell(taxRow, startCol, "Tax (" + invoiceData.getTaxRate() + "%):", styles.get("boldRight")); createCell(taxRow, startCol + 1, formatCurrency(taxAmount), styles.get("currency")); } // Shipping if (invoiceData.getShippingAmount() > 0) { Row shippingRow = sheet.createRow(rowNum++); createCell(shippingRow, startCol, "Shipping:", styles.get("boldRight")); createCell(shippingRow, startCol + 1, formatCurrency(invoiceData.getShippingAmount()), styles.get("currency")); } // Total Row totalRow = sheet.createRow(rowNum++); createCell(totalRow, startCol, "TOTAL:", styles.get("totalLabel")); createCell(totalRow, startCol + 1, formatCurrency(calculateTotal(invoiceData)), styles.get("totalAmount")); // Amount paid and balance due if (invoiceData.getAmountPaid() > 0) { Row paidRow = sheet.createRow(rowNum++); createCell(paidRow, startCol, "Amount Paid:", styles.get("boldRight")); createCell(paidRow, startCol + 1, formatCurrency(invoiceData.getAmountPaid()), styles.get("currency")); Row balanceRow = sheet.createRow(rowNum++); createCell(balanceRow, startCol, "Balance Due:", styles.get("boldRight")); createCell(balanceRow, startCol + 1, formatCurrency(calculateTotal(invoiceData) - invoiceData.getAmountPaid()), styles.get("currencyBold")); } return rowNum; } private int createTermsSection(Sheet sheet, int startRow, InvoiceData invoiceData) { int rowNum = startRow; Row termsTitleRow = sheet.createRow(rowNum++); createCell(termsTitleRow, 0, "Terms and Conditions:", styles.get("sectionHeader")); if (invoiceData.getTermsAndConditions() != null) { for (String term : invoiceData.getTermsAndConditions()) { Row termRow = sheet.createRow(rowNum++); createCell(termRow, 0, "• " + term, styles.get("normal")); } } // Payment instructions if (invoiceData.getPaymentInstructions() != null) { rowNum++; // Empty row Row paymentTitleRow = sheet.createRow(rowNum++); createCell(paymentTitleRow, 0, "Payment Instructions:", styles.get("sectionHeader")); Row paymentRow = sheet.createRow(rowNum++); createCell(paymentRow, 0, invoiceData.getPaymentInstructions(), styles.get("normal")); } return rowNum; } private void initializeStyles(Workbook workbook) { styles.clear(); // Header Title Style CellStyle headerTitle = workbook.createCellStyle(); Font headerFont = workbook.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 16); headerFont.setBold(true); headerFont.setColor(IndexedColors.DARK_BLUE.getIndex()); headerTitle.setFont(headerFont); styles.put("headerTitle", headerTitle); // Invoice Title Style CellStyle invoiceTitle = workbook.createCellStyle(); Font titleFont = workbook.createFont(); titleFont.setFontName("Arial"); titleFont.setFontHeightInPoints((short) 14); titleFont.setBold(true); titleFont.setColor(IndexedColors.BLACK.getIndex()); invoiceTitle.setFont(titleFont); styles.put("invoiceTitle", invoiceTitle); // Normal Text Style CellStyle normal = workbook.createCellStyle(); Font normalFont = workbook.createFont(); normalFont.setFontName("Arial"); normalFont.setFontHeightInPoints((short) 10); normal.setFont(normalFont); normal.setWrapText(true); styles.put("normal", normal); // Bold Text Style CellStyle bold = workbook.createCellStyle(); Font boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setFontHeightInPoints((short) 10); boldFont.setBold(true); bold.setFont(boldFont); styles.put("bold", bold); // Bold Right Aligned CellStyle boldRight = workbook.createCellStyle(); boldRight.setFont(boldFont); boldRight.setAlignment(HorizontalAlignment.RIGHT); styles.put("boldRight", boldRight); // Normal Center Aligned CellStyle normalCenter = workbook.createCellStyle(); normalCenter.setFont(normalFont); normalCenter.setAlignment(HorizontalAlignment.CENTER); styles.put("normalCenter", normalCenter); // Section Header Style CellStyle sectionHeader = workbook.createCellStyle(); Font sectionFont = workbook.createFont(); sectionFont.setFontName("Arial"); sectionFont.setFontHeightInPoints((short) 11); sectionFont.setBold(true); sectionFont.setColor(IndexedColors.DARK_BLUE.getIndex()); sectionHeader.setFont(sectionFont); sectionHeader.setBorderTop(BorderStyle.THIN); sectionHeader.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex()); styles.put("sectionHeader", sectionHeader); // Table Header Style CellStyle tableHeader = workbook.createCellStyle(); Font tableFont = workbook.createFont(); tableFont.setFontName("Arial"); tableFont.setFontHeightInPoints((short) 10); tableFont.setBold(true); tableFont.setColor(IndexedColors.WHITE.getIndex()); tableHeader.setFont(tableFont); tableHeader.setFillForegroundColor(IndexedColors.DARK_BLUE.getIndex()); tableHeader.setFillPattern(FillPatternType.SOLID_FOREGROUND); tableHeader.setAlignment(HorizontalAlignment.CENTER); tableHeader.setBorderTop(BorderStyle.THIN); tableHeader.setBorderBottom(BorderStyle.THIN); tableHeader.setBorderLeft(BorderStyle.THIN); tableHeader.setBorderRight(BorderStyle.THIN); tableHeader.setTopBorderColor(IndexedColors.BLACK.getIndex()); tableHeader.setBottomBorderColor(IndexedColors.BLACK.getIndex()); tableHeader.setLeftBorderColor(IndexedColors.BLACK.getIndex()); tableHeader.setRightBorderColor(IndexedColors.BLACK.getIndex()); styles.put("tableHeader", tableHeader); // Currency Style CellStyle currency = workbook.createCellStyle(); currency.setFont(normalFont); currency.setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00")); currency.setAlignment(HorizontalAlignment.RIGHT); styles.put("currency", currency); // Bold Currency Style CellStyle currencyBold = workbook.createCellStyle(); currencyBold.setFont(boldFont); currencyBold.setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00")); currencyBold.setAlignment(HorizontalAlignment.RIGHT); styles.put("currencyBold", currencyBold); // Total Label Style CellStyle totalLabel = workbook.createCellStyle(); Font totalFont = workbook.createFont(); totalFont.setFontName("Arial"); totalFont.setFontHeightInPoints((short) 12); totalFont.setBold(true); totalLabel.setFont(totalFont); totalLabel.setAlignment(HorizontalAlignment.RIGHT); totalLabel.setBorderTop(BorderStyle.DOUBLE); totalLabel.setTopBorderColor(IndexedColors.BLACK.getIndex()); styles.put("totalLabel", totalLabel); // Total Amount Style CellStyle totalAmount = workbook.createCellStyle(); totalAmount.setFont(totalFont); totalAmount.setDataFormat(workbook.createDataFormat().getFormat("$#,##0.00")); totalAmount.setAlignment(HorizontalAlignment.RIGHT); totalAmount.setBorderTop(BorderStyle.DOUBLE); totalAmount.setTopBorderColor(IndexedColors.BLACK.getIndex()); styles.put("totalAmount", totalAmount); } // Utility methods private void createCell(Row row, int column, String value, CellStyle style) { Cell cell = row.createCell(column); cell.setCellValue(value); if (style != null) { cell.setCellStyle(style); } } private void createCell(Row row, int column, double value, CellStyle style) { Cell cell = row.createCell(column); cell.setCellValue(value); if (style != null) { cell.setCellStyle(style); } } private void createCell(Row row, int column, int value, CellStyle style) { Cell cell = row.createCell(column); cell.setCellValue(value); if (style != null) { cell.setCellStyle(style); } } private void createMergedCell(Sheet sheet, Row row, int firstRow, int lastRow, int firstCol, int lastCol, String value, CellStyle style) { CellRangeAddress mergedRegion = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol); sheet.addMergedRegion(mergedRegion); Cell cell = row.createCell(firstCol); cell.setCellValue(value); if (style != null) { cell.setCellStyle(style); } } // Calculation methods private double calculateSubtotal(InvoiceData invoiceData) { return invoiceData.getItems().stream() .mapToDouble(item -> item.getQuantity() * item.getUnitPrice()) .sum(); } private double calculateDiscount(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = invoiceData.getDiscountAmount(); if (invoiceData.getDiscountPercent() > 0) { discount += subtotal * (invoiceData.getDiscountPercent() / 100); } return discount; } private double calculateTax(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = calculateDiscount(invoiceData); double taxableAmount = subtotal - discount; return taxableAmount * (invoiceData.getTaxRate() / 100); } private double calculateTotal(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = calculateDiscount(invoiceData); double tax = calculateTax(invoiceData); double shipping = invoiceData.getShippingAmount(); return subtotal - discount + tax + shipping; } private String formatCurrency(double amount) { return String.format("$%.2f", amount); } private String formatDate(LocalDate date) { return date.format(DateTimeFormatter.ofPattern("MMM dd, yyyy")); } }

Data Models

1. Invoice Data Models:

@Data @Builder @NoArgsConstructor @AllArgsConstructor public class InvoiceData { @NotBlank private String invoiceNumber; @NotNull private LocalDate invoiceDate; @NotNull private LocalDate dueDate; @NotNull private Customer customer; private Address shippingAddress; private String purchaseOrderNumber; private String paymentTerms; @NotEmpty private List<InvoiceItem> items; private Double discountPercent; private Double discountAmount; private Double taxRate; private Double shippingAmount; private Double amountPaid; private List<String> termsAndConditions; private String paymentInstructions; } @Data @Builder @NoArgsConstructor @AllArgsConstructor public class Customer { @NotBlank private String name; @NotBlank private String address; private String city; private String state; private String zipCode; private String email; private String phone; } @Data @Builder @NoArgsConstructor @AllArgsConstructor public class Address { @NotBlank private String name; @NotBlank private String address; private String city; private String state; private String zipCode; } @Data @Builder @NoArgsConstructor @AllArgsConstructor public class InvoiceItem { @NotBlank private String itemNumber; @NotBlank private String description; @NotNull private Integer quantity; @NotNull private Double unitPrice; // Optional fields private String unitOfMeasure; private String taxCode; } @Data @Configuration @ConfigurationProperties(prefix = "company") public class CompanyConfig { private String companyName; private String address; private String city; private String state; private String zip; private String phone; private String email; private String website; private String logoPath; }

REST Controller

1. Invoice Controller:

@RestController @RequestMapping("/api/invoices") @Slf4j @Validated public class InvoiceController { private final ExcelInvoiceService invoiceService; public InvoiceController(ExcelInvoiceService invoiceService) { this.invoiceService = invoiceService; } @PostMapping("/generate") public ResponseEntity<byte[]> generateInvoice(@Valid @RequestBody InvoiceData invoiceData) { try { byte[] excelBytes = invoiceService.generateInvoiceBytes(invoiceData); String filename = "invoice-" + invoiceData.getInvoiceNumber() + ".xlsx"; return ResponseEntity.ok() .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=\"" + filename + "\"") .header(HttpHeaders.CONTENT_TYPE, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") .body(excelBytes); } catch (InvoiceGenerationException e) { log.error("Invoice generation failed", e); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build(); } } @PostMapping("/preview") public ResponseEntity<InvoicePreview> previewInvoice(@Valid @RequestBody InvoiceData invoiceData) { try { // Calculate totals for preview double subtotal = calculateSubtotal(invoiceData); double discount = calculateDiscount(invoiceData); double tax = calculateTax(invoiceData); double total = calculateTotal(invoiceData); InvoicePreview preview = InvoicePreview.builder() .invoiceNumber(invoiceData.getInvoiceNumber()) .customerName(invoiceData.getCustomer().getName()) .invoiceDate(invoiceData.getInvoiceDate()) .dueDate(invoiceData.getDueDate()) .subtotal(subtotal) .discount(discount) .tax(tax) .shipping(invoiceData.getShippingAmount()) .total(total) .itemCount(invoiceData.getItems().size()) .build(); return ResponseEntity.ok(preview); } catch (Exception e) { log.error("Invoice preview failed", e); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).build(); } } // Calculation methods (similar to service, but for preview) private double calculateSubtotal(InvoiceData invoiceData) { return invoiceData.getItems().stream() .mapToDouble(item -> item.getQuantity() * item.getUnitPrice()) .sum(); } private double calculateDiscount(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = invoiceData.getDiscountAmount() != null ? invoiceData.getDiscountAmount() : 0; if (invoiceData.getDiscountPercent() != null && invoiceData.getDiscountPercent() > 0) { discount += subtotal * (invoiceData.getDiscountPercent() / 100); } return discount; } private double calculateTax(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = calculateDiscount(invoiceData); double taxableAmount = subtotal - discount; if (invoiceData.getTaxRate() != null && invoiceData.getTaxRate() > 0) { return taxableAmount * (invoiceData.getTaxRate() / 100); } return 0; } private double calculateTotal(InvoiceData invoiceData) { double subtotal = calculateSubtotal(invoiceData); double discount = calculateDiscount(invoiceData); double tax = calculateTax(invoiceData); double shipping = invoiceData.getShippingAmount() != null ? invoiceData.getShippingAmount() : 0; return subtotal - discount + tax + shipping; } } @Data @Builder class InvoicePreview { private String invoiceNumber; private String customerName; private LocalDate invoiceDate; private LocalDate dueDate; private double subtotal; private double discount; private double tax; private double shipping; private double total; private int itemCount; }

Advanced Features

1. Template-Based Invoice Service:

@Service @Slf4j public class TemplateInvoiceService { private final ExcelInvoiceService excelInvoiceService; private final CompanyConfig companyConfig; public TemplateInvoiceService(ExcelInvoiceService excelInvoiceService, CompanyConfig companyConfig) { this.excelInvoiceService = excelInvoiceService; this.companyConfig = companyConfig; } public InvoiceData createInvoiceFromOrder(Order order) { return InvoiceData.builder() .invoiceNumber(generateInvoiceNumber()) .invoiceDate(LocalDate.now()) .dueDate(calculateDueDate(LocalDate.now(), order.getPaymentTerms())) .customer(createCustomerFromOrder(order)) .shippingAddress(createShippingAddressFromOrder(order)) .purchaseOrderNumber(order.getPurchaseOrderNumber()) .paymentTerms(order.getPaymentTerms()) .items(createInvoiceItemsFromOrder(order)) .discountPercent(order.getDiscountPercent()) .discountAmount(order.getDiscountAmount()) .taxRate(order.getTaxRate()) .shippingAmount(order.getShippingCost()) .amountPaid(order.getAmountPaid()) .termsAndConditions(getDefaultTermsAndConditions()) .paymentInstructions(getPaymentInstructions()) .build(); } private String generateInvoiceNumber() { // Generate sequential invoice number String prefix = "INV"; String year = String.valueOf(LocalDate.now().getYear()); String sequence = String.format("%05d", getNextInvoiceSequence()); return prefix + year + sequence; } private LocalDate calculateDueDate(LocalDate invoiceDate, String paymentTerms) { if ("Net 15".equals(paymentTerms)) { return invoiceDate.plusDays(15); } else if ("Net 30".equals(paymentTerms)) { return invoiceDate.plusDays(30); } else if ("Net 45".equals(paymentTerms)) { return invoiceDate.plusDays(45); } else { return invoiceDate.plusDays(30); // Default to Net 30 } } private Customer createCustomerFromOrder(Order order) { return Customer.builder() .name(order.getCustomerName()) .address(order.getBillingAddress()) .city(order.getBillingCity()) .state(order.getBillingState()) .zipCode(order.getBillingZip()) .email(order.getCustomerEmail()) .phone(order.getCustomerPhone()) .build(); } private Address createShippingAddressFromOrder(Order order) { if (order.getShippingAddress() == null) { return null; } return Address.builder() .name(order.getShippingName()) .address(order.getShippingAddress()) .city(order.getShippingCity()) .state(order.getShippingState()) .zipCode(order.getShippingZip()) .build(); } private List<InvoiceItem> createInvoiceItemsFromOrder(Order order) { return order.getItems().stream() .map(orderItem -> InvoiceItem.builder() .itemNumber(orderItem.getSku()) .description(orderItem.getDescription()) .quantity(orderItem.getQuantity()) .unitPrice(orderItem.getUnitPrice()) .unitOfMeasure(orderItem.getUnit()) .build()) .collect(Collectors.toList()); } private List<String> getDefaultTermsAndConditions() { return Arrays.asList( "Payment is due within the terms specified", "Late payments may be subject to a 1.5% monthly service charge", "All sales are final unless otherwise specified", "Returns require authorization and must be in original condition" ); } private String getPaymentInstructions() { return String.format( "Please make checks payable to %s and mail to %s, %s, %s %s. " + "For wire transfers, please contact accounting at %s.", companyConfig.getCompanyName(), companyConfig.getAddress(), companyConfig.getCity(), companyConfig.getState(), companyConfig.getZip(), companyConfig.getEmail() ); } private synchronized Long getNextInvoiceSequence() { // In production, this would come from a database sequence return System.currentTimeMillis() % 100000; } }

Error Handling

1. Custom Exceptions:

public class InvoiceGenerationException extends RuntimeException { public InvoiceGenerationException(String message) { super(message); } public InvoiceGenerationException(String message, Throwable cause) { super(message, cause); } } @RestControllerAdvice @Slf4j public class InvoiceExceptionHandler { @ExceptionHandler(InvoiceGenerationException.class) public ResponseEntity<ErrorResponse> handleInvoiceGenerationException(InvoiceGenerationException e) { log.error("Invoice generation error", e); ErrorResponse error = ErrorResponse.builder() .code("INVOICE_GENERATION_ERROR") .message("Failed to generate invoice") .timestamp(Instant.now()) .build(); return ResponseEntity.status(HttpStatus.INTERNAL_SERVER_ERROR).body(error); } @ExceptionHandler(MethodArgumentNotValidException.class) public ResponseEntity<ErrorResponse> handleValidationException(MethodArgumentNotValidException e) { List<String> errors = e.getBindingResult() .getFieldErrors() .stream() .map(error -> error.getField() + ": " + error.getDefaultMessage()) .collect(Collectors.toList()); ErrorResponse error = ErrorResponse.builder() .code("VALIDATION_ERROR") .message("Invalid invoice data") .details(errors) .timestamp(Instant.now()) .build(); return ResponseEntity.status(HttpStatus.BAD_REQUEST).body(error); } } @Data @Builder class ErrorResponse { private String code; private String message; private Object details; private Instant timestamp; }

Configuration

1. Application Properties:

# application.yml company: company-name: "Your Company Name" address: "123 Business Street" city: "Your City" state: "Your State" zip: "12345" phone: "(555) 123-4567" email: "[email protected]" website: "www.yourcompany.com" server: port: 8080 spring: jackson: date-format: "yyyy-MM-dd" time-zone: "UTC"

Best Practices

1. Memory Management:

@Service public class InvoiceMemoryService { private final ExcelInvoiceService invoiceService; public InvoiceMemoryService(ExcelInvoiceService invoiceService) { this.invoiceService = invoiceService; } @Async public CompletableFuture<byte[]> generateInvoiceAsync(InvoiceData invoiceData) { return CompletableFuture.supplyAsync(() -> { try (Workbook workbook = invoiceService.generateInvoice(invoiceData); ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) { workbook.write(outputStream); return outputStream.toByteArray(); } catch (Exception e) { throw new InvoiceGenerationException("Async invoice generation failed", e); } }); } public void cleanupTempFiles() { // Clean up any temporary files created during invoice generation // Apache POI typically doesn't create temp files, but good practice } }

Conclusion

Apache POI provides a powerful foundation for generating professional Excel invoices directly from Java applications. The combination of precise formatting control, automatic calculations, and flexible data integration makes it ideal for automating invoice generation in business applications.

By creating reusable invoice templates and services, Java developers can build robust invoicing systems that produce branded, professional documents while maintaining full control over the layout and calculations. The Excel format provides clients with editable invoices that can be easily imported into accounting systems, making it a practical choice for business-to-business applications.