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.