Export "Purchase Order Overlay Print" to Excel with Java (Headers & Footers)
Headers & FootersScene 0010
Purchase order rows computing Amount = Quantity × Unit Price, then Tax-Inclusive Amount by tax rate, with a total row at the end; a FOOTER generates one line at the bottom of the document for the preparer / reviewer signature note. Implemented declaratively in pure XML — no Java business code required.
About this demo
This demo exports the "Purchase Order Overlay Print" workbook: 1 data sheet(s); MAPPING over 7 columns (物料编码, 物料名称, 数量, 单价, 金额, 税率, 含税金额), 10 formula(s), 1 style rule(s). On the Java side it builds 5 data rows and calls exportPurchaseOrder() once — everything else is driven by the config.
It belongs to the "Headers & Footers" group: FOOTER printing: fixed signature and approval lines generated at the bottom of a document.
What you can copy from this page
- The complete Java test class (79 lines, runnable as-is)
- The complete XML config (32 lines: mapping, formulas, styles, charts and every other rule)
- The exported xlsx file (real output, comparable with the preview above)
Related searches: Java export Excel footer, Excel footer for pre-printed forms Java, Apache POI header footer export, export Purchase Order Overlay Print to Excel with Java
Output specs
- Scene no.
- 0010
- Group
- Headers & Footers
- Config lines
- 32
- Source lines
- 79
Java source · Footer0010PurchaseOrderDemo.java
This is the complete test class that produced the Excel file above — copy it and run as-is. Java only builds the data rows; all statistics and styling live in the XML config.
package com.github.paohaijiao.biz.footer;
import com.github.paohaijiao.statement.JQuickRow;
import com.github.paohaijiao.xml.ex.JQuickExcelExportXmlParseFactory;
import com.github.paohaijiao.xml.factory.JQuickFactory;
import com.github.paohaijiao.xml.factory.JQuickXmlFactory;
import com.github.paohaijiao.xml.handler.JQuickParseHandler;
import org.junit.Test;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
public class Footer0010PurchaseOrderDemo {
private static final String XML = "jquick/biz/footer/0010_footer_purchase-order.xml";
/** 导出：3 条采购明细 + 合计 + 尾部占位行，回读校验公式、样式与页脚。 */
@Test
public void exportPurchaseOrder() throws Exception {
List<JQuickRow> rows = new ArrayList<>();
rows.add(detail("M001", "无线鼠标", 100, 25.50, 0.13));
rows.add(detail("M002", "机械键盘", 80, 30.00, 0.13));
rows.add(detail("M003", "显示器", 50, 12.50, 0.09));
rows.add(totalRow());
rows.add(footerPlaceholder());
File out = new File("D:/test/excel/biz/footer/0010_footer_purchase-order.xlsx");
try (OutputStream os = new FileOutputStream(out)) {
JQuickParseHandler parser = new JQuickExcelExportXmlParseFactory("sage", rows, os);
JQuickFactory factory = new JQuickXmlFactory(parser, XML);
Footer0010PurchaseOrderService service = factory.createApi(Footer0010PurchaseOrderService.class);
service.exportPurchaseOrder("field", "value");
}
}
/** 构造一条采购明细（e=金额、g=含税金额由 FORMULAS 计算，占位保列）。 */
private static JQuickRow detail(String code, String name, int qty, double price, double taxRate) {
JQuickRow row = new JQuickRow();
row.put("a", code);
row.put("b", name);
row.put("c", qty);
row.put("d", price);
row.put("e", null);
row.put("f", taxRate);
row.put("g", null);
return row;
}
/** 合计行：只写「合计」文字，其余列以空值占位（必须补齐全部映射列）。 */
private static JQuickRow totalRow() {
JQuickRow row = new JQuickRow();
row.put("a", "合计");
row.put("b", null);
row.put("c", null);
row.put("d", null);
row.put("e", null);
row.put("f", null);
row.put("g", null);
return row;
}
/** 尾部占位行：承接 FOOTER 页脚，避免页脚覆盖合计行。 */
private static JQuickRow footerPlaceholder() {
JQuickRow row = new JQuickRow();
row.put("a", null);
row.put("b", null);
row.put("c", null);
row.put("d", null);
row.put("e", null);
row.put("f", null);
row.put("g", null);
return row;
}
}Minimal invocation (4 lines)
The core is these four lines: hand the rows to the parser, the parser to the factory, get a typed Service and call it. Copy them straight into your project.
// rows 由业务代码构造（List<JQuickRow>），os 为输出流，见下方完整源码
JQuickParseHandler parser = new JQuickExcelExportXmlParseFactory("sage", rows, os);
JQuickFactory factory = new JQuickXmlFactory(parser, XML);
Footer0010PurchaseOrderService service = factory.createApi(Footer0010PurchaseOrderService.class);XML config · 0010_footer_purchase-order.xml
Formats (FORMAT), formulas (FORMULAS) and styles (STYLE) are all declared in this XML, fully decoupled from the Java code. The full config is below — copy it as-is.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE excels PUBLIC "-//PAOHAIJIAO//DTD API EXCEL 1.0//EN"
"classpath:paohaijiao/dtd/Jquick-excel.dtd">
<excels namespace="com.github.paohaijiao.biz.footer.Footer0010PurchaseOrderService">
<!-- 导出：采购订单套打 -->
<excel name="exportPurchaseOrder" returnClass="void">
<![CDATA[
EXPORT WITH SHEET="采购订单", HEADER=true,
MAPPING={"a":"物料编码","b":"物料名称","c":"数量","d":"单价","e":"金额","f":"税率","g":"含税金额"},
FORMAT={"c":"0","d":"#,##0.00","e":"#,##0.00","f":"0.00%","g":"#,##0.00"},
FORMULAS={
E2:'C2*D2',G2:'E2*(1+F2)',
E3:'C3*D3',G3:'E3*(1+F3)',
E4:'C4*D4',G4:'E4*(1+F4)',
C5:'SUM(C2:C4)',E5:'SUM(E2:E4)',G5:'SUM(G2:G4)'
},
STYLE={ROW 5:{bold:true,fillPattern:solid_foreground,fillForegroundColor:lightYellow,alignment:right,verticalAlignment:center}},
FOOTER="制单人：__________ 审核人：__________ 日期：__________"
]]>
</excel>
<!-- 导入解析：读取导出的采购订单（最后一行是 FOOTER 签字说明） -->
<excel name="importPurchaseOrder" returnClass="java.util.List">
<![CDATA[
IMPORT WITH SHEET="采购订单", HEADER=true,
MAPPING={"物料编码":"code","物料名称":"name","数量":"qty","单价":"price","金额":"amount","税率":"taxRate","含税金额":"amountWithTax"}
]]>
</excel>
</excels>The Excel file, preview image, XML config and source code on this page were all produced by a real JQuick-Excel run and are free to copy into your own project.