java相关：基于apache poi根据模板导出excel的实现方法
发布于 2020-7-21|
下面小妖就为大家带来一篇基于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某一行，不需要就手动创建无制定格式的行，后面在为该行的每一列对应的给个单元格制定格式和数据。
