java相关:基于apache poi根据模板导出excel的实现方法

发布于 2020-7-21|标签java
复制链接
下面小妖就为大家带来一篇基于apache poi根据模板导出excel的实现方法。小妖觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小妖过来看看吧
需要预先新建编辑好一个excel文件,设置好样式。编辑好输出的数据,根据excel坐标一一对应。支持列表数据输出,列表中列合并。代码如下: ```java package com.icourt.util; import org.apache.commons.collections4.CollectionUtils; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.io.*; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.regex.Matcher; import java.util.regex.Pattern; /** * 描述:poi根据模板导出excel,根据excel坐标赋值,如(B1) */ public class ExcelExportUtil { //模板map private Map tempWorkbook = new HashMap(); //模板输入流map private Map tempStream = new HashMap(); /** * 功能:按模板向Excel中相应地方填充数据 */ public void writeData(String templateFilePath, Map dataMap, int sheetNo) throws IOException, InvalidFormatException { if (dataMap == null || dataMap.isEmpty()) { return; } //读取模板 Workbook wbModule = getTempWorkbook(templateFilePath); //数据填充的sheet Sheet wsheet = wbModule.getSheetAt(sheetNo); for (Entry entry : dataMap.entrySet()) { String point = entry.getKey(); Object data = entry.getValue(); TempCell cell = getCell(point, data, wsheet); //指定坐标赋值 setCell(cell, wsheet); } //设置生成excel中公式自动计算 wsheet.setForceFormulaRecalculation(true); } /** * 功能:按模板向Excel中列表填充数据.只支持列合并 */ public void writeDateList(String templateFilePath, String[] heads, List> datalist, int sheetNo) throws IOException, InvalidFormatException { if (heads == null || heads.length tempCells = new ArrayList(heads.length); for (String point : heads) { TempCell tempCell = getCell(point, null, wsheet); //取得合并单元格位置 -1:表示不是合并单元格 int pos = isMergedRegion(wsheet, tempCell.getRow(), tempCell.getColumn()); if (pos > -1) { CellRangeAddress range = wsheet.getMergedRegion(pos); tempCell.setColumnSize(range.getLastColumn() - range.getFirstColumn()); } tempCells.add(tempCell); } //赋值 for (int i = 0; i dataMap = datalist.get(i); for (int j = 0; j -1) { CellRangeAddress rangeAddress = mergeRegion(sheet, tempCell.getRow(), tempCell.getRow(), tempCell.getColumn(), tempCell.getColumn() + tempCell.getColumnSize()); setRegionStyle(tempCell.getCellStyle(), rangeAddress, sheet); } Row rowIn = sheet.getRow(tempCell.getRow()); if (rowIn == null) { copyRows(tempCell.getRow() - 1, tempCell.getRow() - 1, tempCell.getRow(), sheet);//复制上一行 rowIn = sheet.getRow(tempCell.getRow()); } Cell cellIn = rowIn.getCell(tempCell.getColumn()); if (cellIn == null) { cellIn = rowIn.createCell(tempCell.getColumn()); } //根据data类型给cell赋值 if (tempCell.getData() instanceof String) { cellIn.setCellValue((String) tempCell.getData()); } else if (tempCell.getData() instanceof Integer) { cellIn.setCellValue((int) tempCell.getData()); } else if (tempCell.getData() instanceof Double) { cellIn.setCellValue((double) tempCell.getData()); } else { cellIn.setCellValue((String) tempCell.getData()); } //样式 if (tempCell.getCellStyle() != null && tempCell.getColumnSize() == -1) { cellIn.setCellStyle(tempCell.getCellStyle()); } } /** * 功能:写到输出流并移除资源 */ public void writeAndClose(String templateFilePath, OutputStream os) throws IOException, InvalidFormatException { if (getTempWorkbook(templateFilePath) != null) { getTempWorkbook(templateFilePath).write(os); tempWorkbook.remove(templateFilePath); } if (getInputStream(templateFilePath) != null) { getInputStream(templateFilePath).close(); tempStream.remove(templateFilePath); } } /** * 功能:判断指定的单元格是否是合并单元格 */ private Integer isMergedRegion(Sheet sheet, int row, int column) { for (int i = 0; i = firstRow && row = firstColumn && column = pStartRow) && (region.getLastRow() dataMap = new HashMap(); dataMap.put("B1", "03_Alpha_项目工作时间统计表"); dataMap.put("B2", "统计时间:2017/01/01 - 2017/03/31"); excel.writeData(templateFilePath, dataMap, 0); List> datalist = new ArrayList>(); Map data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, "3/10/17"); data.put(2, "18:50"); data.put(3, "19:00"); data.put(4, "李子鹏"); data.put(5, "新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用新增项目键值对接口,供任务计时调用"); data.put(6, "代码开发"); data.put(7, "3.17"); datalist.add(data); data = new HashMap(); data.put(1, ""); data.put(2, ""); data.put(3, ""); data.put(4, ""); data.put(5, ""); data.put(6, ""); data.put(7, ""); datalist.add(data); String[] heads = new String[]{"B4", "C4", "D4", "E4", "F4", "G4", "H4"}; excel.writeDateList(templateFilePath, heads, datalist, 0); //写到输出流并移除资源 excel.writeAndClose(templateFilePath, os); os.flush(); os.close(); } } ``` 大体思路:最主要是制作好模版代码根据模版,读取设置好的列的格式,在循环数据行,读取模版中的对应的行,存在该行就取得,不存在看是否需要copy某一行,不需要就手动创建无制定格式的行,后面在为该行的每一列对应的给个单元格制定格式和数据。
冀ICP备17029012号-4 | 版权所有©鲍亚龙 |免责声明  | GIF图库  | NUXT版