一、需求分析
公司业务部门需要,根据一些数据,加上表头,导出需要的excel表格。效果如下:
二、代码实现
【依赖准备】
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
对于excel样式,笔者的做法是,读取设置好的xlsx模板样式,设置导出表格的样式。
poi导出的各类样式设置
笔者的样式设置:
public static void setPrintSetupInfoMargin(Sheet templeteSheet, Sheet outputSheet, String type) {
PrintSetup psTemplete = templeteSheet.getPrintSetup();
PrintSetup psOutput = outputSheet.getPrintSetup();
//页眉边距设置
psOutput.setHeaderMargin(psTemplete.getHeaderMargin());
psOutput.setFooterMargin(psTemplete.getFooterMargin());
//设置宽、高页数以适合纸张
psOutput.setFitWidth(psTemplete.getFitWidth());
psOutput.setFitHeight(psTemplete.getFitHeight());
//设置纸张尺寸
psOutput.setPaperSize(psTemplete.getPaperSize());
//设置页码开始
psOutput.setPageStart(psTemplete.getPageStart());
//设置是否横向打印
psOutput.setLandscape(psTemplete.getLandscape());
//设置是按顺序从左到右还是自上而下
psOutput.setLeftToRight(psTemplete.getLeftToRight());
//设置是否处于草稿模式
psOutput.setDraft(psTemplete.getDraft());
//设置比例
psOutput.setScale(psTemplete.getScale());
//页边距设置
psOutput.setHeaderMargin(psTemplete.getHeaderMargin());
psOutput.setFooterMargin(psTemplete.getFooterMargin());
if (".xls".equals(type)) {
psOutput.setScale((short) (psTemplete.getScale()+5));
outputSheet.setMargin(HSSFSheet.TopMargin, templeteSheet.getMargin(SXSSFSheet.TopMargin));
outputSheet.setMargin(HSSFSheet.BottomMargin, templeteSheet.getMargin(SXSSFSheet.BottomMargin));
outputSheet.setMargin(HSSFSheet.RightMargin, templeteSheet.getMargin(SXSSFSheet.RightMargin));
outputSheet.setMargin(HSSFSheet.LeftMargin, templeteSheet.getMargin(SXSSFSheet.LeftMargin));
}else {
outputSheet.setMargin(SXSSFSheet.TopMargin, templeteSheet.getMargin(SXSSFSheet.TopMargin));
outputSheet.setMargin(SXSSFSheet.BottomMargin, templeteSheet.getMargin(SXSSFSheet.BottomMargin));
outputSheet.setMargin(SXSSFSheet.RightMargin, templeteSheet.getMargin(SXSSFSheet.RightMargin));
outputSheet.setMargin(SXSSFSheet.LeftMargin, templeteSheet.getMargin(SXSSFSheet.LeftMargin));
}
//页面打印选项设置(根据模板的选择来设置)
outputSheet.setFitToPage(templeteSheet.getFitToPage());
//页脚设置
Footer psTempletefooter = templeteSheet.getFooter();
Footer psOutputfooter = outputSheet.getFooter();
psOutputfooter.setCenter(psTempletefooter.getCenter());
//页眉设置省略。。。
//设置标题(实现打印时每一页都有同个头部标题)
outputSheet.setRepeatingRows(templeteSheet.getRepeatingRows());
outputSheet.setRepeatingColumns(templeteSheet.getRepeatingColumns());
}
根据模板设置长宽:
//设置行宽度、行高度
for (int i = 0; i < items.get(0).length + 3; i++) {
sheet.setColumnWidth(i, tempSheet.getColumnWidth(i));
}
设置表头样式:
//设置合并区域
sheet.addMergedRegion(new CellRangeAddress(startLine + 2, startLine + 3, itemLists.length - 1, itemLists.length - 1));
效果图片:
手动控制分页的情况:
//设置分页符
sheet.setRowBreak(currNowCow - 1);
效果如下:
项目涉密,不能贴完整代码,谢谢理解哈!