<properties>
<poi.version>3.17</poi.version>
</properties>
<!-- POI -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi.version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi.version}</version>
</dependency>
导出数据需要一个模板表格,可以使用占位符动态的添加值{0}这就是占位符![](https://i-blog.csdnimg.cn/blog_migrate/7bd54d7c744ccb88f5e3656410879b2e.png)
工具类,需要时可以自己修改里面的代码.
package com.buba.utils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.MessageFormat;
import java.text.SimpleDateFormat;
import java.util.*;
public class TemplateExcelUtil<T> {
/**
* 导出excel
* 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出
* temp 模板路径
* target 存储目标地址
* params 表头里的参数数组
* headersId 表格属性列名对应的javaBean字段---你需要导出的字段名(为了更灵活控制你想要导出的字段)
* dtoList 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象
* 返回响应体
*/
public void exportExcel(String temp,String target, String[] params,List<String> headersId,
List<T> dtoList) {
/*(二)字段*/
Map<Integer, String> titleFieldMap = new HashMap<>();
int value = 0;
for (int i = 0; i < headersId.size(); i++) {
if (!headersId.get(i).equals(null)) {
titleFieldMap.put(value, headersId.get(i));
value++;
}
}
/* (三)读取模板*/
XSSFWorkbook wb = null;
File file = new File(temp);
try {
wb = (XSSFWorkbook) WorkbookFactory.create(new FileInputStream(file));
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
/** 得到第一个sheet */
XSSFSheet sheet = wb.getSheetAt(0);
sheet.setDefaultColumnWidth((short)20); //设置默认表格宽度
//补充标题头的参数 下面的代码就是获取占位符所在位置,这个得自己根据自己模板占位符的位置修改下面的参数值
XSSFRow row0 = sheet.getRow(0);
XSSFCell cell00 = row0.getCell(0);
String string00 = MessageFormat.format(cell00.getStringCellValue(),params);
cell00.setCellValue(string00);
//补充标题头的参数
XSSFRow row1 = sheet.getRow(1);
XSSFCell cell11 = row1.getCell(1);
String string11 = MessageFormat.format(cell11.getStringCellValue(), params);
cell11.setCellValue(string11);
XSSFRow row2 = sheet.getRow(2);
XSSFCell cell20 = row2.getCell(0);
String string20 = MessageFormat.format(cell20.getStringCellValue(), params);
cell20.setCellValue(string20);
//得到表格的样式
XSSFCellStyle cellStyle = sheet.getRow(4).getCell(0).getCellStyle();
XSSFRow row=null;
XSSFCell cell=null;
//表格标题一行的字段的集合
Collection zdC = titleFieldMap.values();
Iterator<T> labIt = dtoList.iterator();//总记录的迭代器
int zdRow =3;//列序号
while (labIt.hasNext()) {//记录的迭代器,遍历总记录
//int zdCell = 0;
zdRow++;
row = sheet.createRow(zdRow);
T l = (T) labIt.next();
// 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值
Field[] fields = l.getClass().getDeclaredFields();//获得JavaBean全部属性
for (short i = 0; i < fields.length; i++) {//遍历属性,比对
int zdCell = 0;
Field field = fields[i];
String fieldName = field.getName();//属性名
Iterator<String> zdIt = zdC.iterator();//一条字段的集合的迭代器
while (zdIt.hasNext()) {//遍历要导出的字段集合
if (zdIt.next().equals(fieldName)) {//比对JavaBean的属性名,一致就写入,不一致就丢弃
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//拿到属性的get方法
Class tCls = l.getClass();//拿到JavaBean对象
try {
Method getMethod = tCls.getMethod(getMethodName,
new Class[] {});//通过JavaBean对象拿到该属性的get方法,从而进行操控
Object val = getMethod.invoke(l, new Object[] {});//操控该对象属性的get方法,从而拿到属性值
Class t = getMethod.getReturnType();
cell = row.createCell((short) zdCell);
cell.setCellStyle(cellStyle);//单元格样式
//根据值的类型设置单元格的值
switch (t.getName()) {
case "java.lang.Integer":
if(null!=val){
cell.setCellValue((Integer)val);
}
break;
case "java.util.Date":
if(null!=val){
Date date=(Date)val;
SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
cell.setCellValue(sdf.format(date));
}
break;
default:
cell.setCellValue(String.valueOf(val));
break;
}
//zdCell++;
} catch (SecurityException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
zdCell++;
}
}
}
//4.存储文件
try {
FileOutputStream out=new FileOutputStream(target);
wb.write(out);
out.flush();
out.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* Excel读取 操作
*/
public static List<List<String>> readExcel(InputStream is)
throws IOException {
Workbook wb = null;
try {
wb = WorkbookFactory.create(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
/** 得到第一个sheet */
Sheet sheet = wb.getSheetAt(0);
/** 得到Excel的行数 */
int totalRows = sheet.getPhysicalNumberOfRows();
/** 得到Excel的列数 */
int totalCells = 0;
if (totalRows >= 1 && sheet.getRow(0) != null) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
}
List<List<String>> dataLst = new ArrayList<List<String>>();
/** 循环Excel的行 */
for (int r = 0; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null)
continue;
List<String> rowLst = new ArrayList<String>();
/** 循环Excel的列 */
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = "";
if (null != cell) {
/*HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
cellValue= hSSFDataFormatter.formatCellValue(cell);*/
// 以下是判断数据的类型
CellType type = cell.getCellTypeEnum();
switch (type) {
case NUMERIC: // 数字
cellValue = cell.getNumericCellValue() + "";
break;
case STRING: // 字符串
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break;
case FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break;
case BLANK: // 空值
cellValue = "";
break;
case _NONE: // 故障
cellValue = "非法字符";
break;
default:
cellValue = "未知类型";
break;
}
}
rowLst.add(cellValue);
}
/** 保存第r行的第c列 */
dataLst.add(rowLst);
}
return dataLst;
}
}
实体类
package com.buba.pojo;
import com.buba.utils.ExcellFiled;
import org.springframework.format.annotation.DateTimeFormat;
import java.util.Date;
public class ItemData {
private String organizeName;//机构名称
@DateTimeFormat(pattern="yyyy-MM-dd")
private Date createtime;//机构id号码
private Integer organizeId;//机构id号码
private Integer numActivities;//计划召开桌数
private Integer numActual;//实际召开桌数
private Float implRate;//执行率
private Integer implRanking;//执行排名
private Integer newClient;//新客户量
private Integer oldClient;//老客户量
private Integer yxClient;//意向客户
private Float money;//预估保费
public String getOrganizeName() {
return organizeName;
}
public void setOrganizeName(String organizeName) {
this.organizeName = organizeName;
}
public Integer getOrganizeId() {
return organizeId;
}
public void setOrganizeId(Integer organizeId) {
this.organizeId = organizeId;
}
public Integer getNumActivities() {
return numActivities;
}
public void setNumActivities(Integer numActivities) {
this.numActivities = numActivities;
}
public Integer getNumActual() {
return numActual;
}
public void setNumActual(Integer numActual) {
this.numActual = numActual;
}
public Float getImplRate() {
return implRate;
}
public void setImplRate(Float implRate) {
this.implRate = implRate;
}
public Integer getImplRanking() {
return implRanking;
}
public void setImplRanking(Integer implRanking) {
this.implRanking = implRanking;
}
public Integer getNewClient() {
return newClient;
}
public void setNewClient(Integer newClient) {
this.newClient = newClient;
}
public Integer getOldClient() {
return oldClient;
}
public void setOldClient(Integer oldClient) {
this.oldClient = oldClient;
}
public Integer getYxClient() {
return yxClient;
}
public void setYxClient(Integer yxClient) {
this.yxClient = yxClient;
}
public Float getMoney() {
return money;
}
public void setMoney(Float money) {
this.money = money;
}
public Date getCreatetime() {
return createtime;
}
public void setCreatetime(Date createtime) {
this.createtime = createtime;
}
public ItemData() {
}
public ItemData(String organizeName, Date createtime, Integer organizeId, Integer numActivities, Integer numActual, Float implRate, Integer implRanking, Integer newClient, Integer oldClient, Integer yxClient, Float money) {
this.organizeName = organizeName;
this.createtime = createtime;
this.organizeId = organizeId;
this.numActivities = numActivities;
this.numActual = numActual;
this.implRate = implRate;
this.implRanking = implRanking;
this.newClient = newClient;
this.oldClient = oldClient;
this.yxClient = yxClient;
this.money = money;
}
}
导出的controller ,自己打断点看看程序怎么走的从而修改代码里的值,很简单的.
@RequestMapping("/exportTempExcel")
@ResponseBody
public String exportTempExcel(){
//文件输出的位置
String target = "F:/temp/exportTempExcel.xlsx";
//模板名字
String temp = "F:/temp/hebeiTemp.xlsx";
//参数列表
String[] params = new String[5];
params[0] = "占位符0";
params[1] = "占位符1";
params[2] = "占位符2";
params[3] = "占位符3";
params[4] = "占位符4";
List<ItemData> list = new ArrayList<>();
ItemData itemData = new ItemData("机构名称",new Date(),1,1,1,1f,1,1,1,1,1f);
list.add(itemData);
list.add(itemData);
list.add(itemData);
list.add(itemData);
list.add(itemData);
list.add(itemData);
list.add(itemData);
//excel表中对应的javaBean属性的集合
List<String> propertyList = createPropertyList();
TemplateExcelUtil ex = new TemplateExcelUtil();
ex.exportExcel(temp,target,params,propertyList,list);
return "ok";
}
private List<String> createPropertyList() {
List<String> propertyList = new ArrayList<String>();
propertyList.add("organizeName");
propertyList.add("createtime");
propertyList.add("organizeId");
propertyList.add("numActivities");
propertyList.add("numActual");
propertyList.add("implRate");
propertyList.add("newClient");
propertyList.add("oldClient");
propertyList.add("yxClient");
propertyList.add("money");
return propertyList;
}
导出结果.
![](https://i-blog.csdnimg.cn/blog_migrate/68f9f197f4c5610036a7aa5a0f828921.png)