EasyPoi多sheet导出功能实现
EXCEL
依赖:
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.1.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.1.0</version>
</dependency>
controller
package io.btm.modules.importexcel;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import io.btm.common.utils.R;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@RestController
@RequestMapping("importExcel")
public class ExcelController {
/**
* 导入
*/
@PostMapping("import")
@ApiOperation(value = "导入")
public R ordergeneratexml(MultipartFile file) throws Exception {
//根据file得到Workbook,主要是要根据这个对象获取,传过来的excel有几个sheet页
Workbook hssfWorkbook = getWorkBook(file);
//获取sheet数量
int sheetNum = hssfWorkbook.getNumberOfSheets();
ImportParams params = new ImportParams();
//表头在第几行
params.setTitleRows(3);
//是否需要通过key-value导入方法,获取特定字段
params.setReadSingleCell(true);
//判断一个cell是key的规则,可以自定义,默认就是 “:”
params.setKeyMark(":");
List<ExcelVo> listAll = new ArrayList<>();
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
//第几个sheet页
params.setStartSheetIndex(sheetIndex);
//获取表头下的数据
ExcelImportResult<ExcelVo> result = ExcelImportUtil.importExcelMore(file.getInputStream(), ExcelVo.class, params);
List<ExcelVo> list = result.getList();
//获取特定字段的map
Map<String, Object> map = result.getMap();
list.forEach(entity -> {
entity.setRukuriqi(map.get("日期:").toString());
entity.setDianpumingchen(map.get("店铺名:").toString());
});
listAll.addAll(list);
}
return R.ok().put("listAll", listAll);
}
public static Workbook getWorkBook(MultipartFile file) throws IOException {
//这样写excel能兼容03和07
InputStream is = file.getInputStream();
Workbook hssfWorkbook = null;
try {
hssfWorkbook = new HSSFWorkbook(is);
} catch (Exception ex) {
is =file.getInputStream();
hssfWorkbook = new XSSFWorkbook(is);
}
return hssfWorkbook;
}
}
实体类
package io.btm.modules.importexcel;
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExcelVo {
@Excel(name = "序号")
private String indexNum;
@Excel(name = "品名")
private String name;
@Excel(name = "订货数(斤)")
private String dinghuoNum;
@Excel(name = "去皮斤数")
private String qupiNum;
@Excel(name = "去皮斤数")
private String sunhaoNum;
@Excel(name = "供货单价")
private String gonghuoNum;
@Excel(name = "订单金额")
private String dingnanNum;
@Excel(name = "订单金额")
private String qupijine;
@Excel(name = "订单金额")
private String sunhaojine;
@Excel(name = "产品差价")
private String chanpindanjia;
@Excel(name = "实际结账")
private String shijijiezhang;
@Excel(name = "是否结款")
private String shifoujiekuan;
@Excel(name = "是否结款")
private String beizhu;
private String rukuriqi;
private String dianpumingchen;
}
EasyPoi 多sheet导出功能实现
本需求:
- 多sheet页导出
- 合并单元格(跨行、跨列)
- 多表头合并
一、引入maven jar包
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>3.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>3.2.0</version>
</dependency>
二、编写导出的实体类(使用注解形式)
1、DeptUtil 类
@ExcelTarget("deptUtil")
public class DeptUtil {
@Excel(name = "部门编号", width = 30 , needMerge = true)
private Integer id;
@Excel(name = "部门名称", width = 30 , needMerge = true)
private String deptName;
@ExcelCollection(name = "员工信息")
private List<EmpUtil> emps;
....省略getter、setter方法
2、EmpUtil类
@ExcelTarget("empUtil")
public class EmpUtil{
@Excel(name = "序号", width = 30, isColumnHidden = true)
private Integer id;
@Excel(name = "员工姓名", width = 30, groupName = "基本信息")
private String empName;
@Excel(name = "年龄", width = 30, type = 10, groupName = "基本信息")
private Integer age;
@Excel(name = "入职时间", width = 30, groupName = "工作信息", format = "yyyy/MM/dd HH:mm")
private Date hiredate;
@Excel(name = "薪酬", width = 30, type = 10, groupName = "工作信息")
private BigDecimal salary;
....省略getter、setter方法
3、核心代码
public String export(){
Workbook workBook = null;
try {
List<DeptUtil> exportList = exportService.exportList();
System.err.println(JSONArray.toJSONString(exportList));
// 创建参数对象(用来设定excel得sheet得内容等信息)
ExportParams deptExportParams = new ExportParams();
// 设置sheet得名称
deptExportParams.setSheetName("员工报表1");
// 创建sheet1使用得map
Map<String, Object> deptExportMap = new HashMap<>();
// title的参数为ExportParams类型,目前仅仅在ExportParams中设置了sheetName
deptExportMap.put("title", deptExportParams);
// 模版导出对应得实体类型
deptExportMap.put("entity", DeptUtil.class);
// sheet中要填充得数据
deptExportMap.put("data", exportList);
ExportParams empExportParams = new ExportParams();
empExportParams.setSheetName("员工报表2");
// 创建sheet2使用得map
Map<String, Object> empExportMap = new HashMap<>();
empExportMap.put("title", empExportParams);
empExportMap.put("entity", DeptUtil.class);
empExportMap.put("data", exportList);
// 将sheet1、sheet2、sheet3使用得map进行包装
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(deptExportMap);
sheetsList.add(empExportMap);
// 执行方法
workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
fileName = URLEncoder.encode("员工报表导出", "UTF-8");
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
workBook.write(outputStream);
outputStream.flush();
byte[] byteArray = outputStream.toByteArray();
excelStream = new ByteArrayInputStream(byteArray,0,byteArray.length);
outputStream.close();
}catch (Exception e){
e.printStackTrace();
}finally {
if(workBook != null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
return "success";
}
三、效果展示
有一个问题就是如果sheet填充的数据源是一样的,那么第二个sheet的内容就会为空
不过一般上要实现多sheet页展示,内容应该是不一样的,这里只是作为实现效果展示,就采用了同一个数据获取源。
以上就是所有内容了,如果想要学习更多可以查阅easyPoi的api(http://easypoi.mydoc.io/)
另外附上项目源码:https://github.com/wzqonly/easyPoi_export
多sheet导入导出 工具类
package com.hisign.utils;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.alibaba.fastjson.JSON;
import com.hisign.base.repository.BaseMapper;
import com.hisign.rest.exception.HisignException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.*;
@Transactional
public class ExcelUtil {
//日志
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/**
* excel导入工具类 单个excel
*
* @param mapper 需要进行数据插入的mapper
* @param file 导入的excel文件
* @param classType 新增数据的class类型
* @return 返回导入参数条数
*/
public static Integer importExcelUtil(BaseMapper mapper, Class classType, MultipartFile file) {
ImportParams importParams = new ImportParams();
importParams.setHeadRows(1);
//importParams.setTitleRows(1);
List<T> importList = null;
List<T> dateList = null;
//List<T> list = null;
try {
importList = ExcelImportUtil.importExcel(file.getInputStream(), classType, importParams);
logger.info("导入的数据 : " + JSON.toJSONString(importList));
String replaceJsonList = "";
if (importList.size() > 0) {
//反射 去空 未启用
// list = removeEmptyObject(dateList);
String jsonList = JSON.toJSONString(importList);
//去除空对象数据
replaceJsonList = jsonList.replaceAll(",\\{\\}", "");
logger.info("去除空对象之后数据 : " + replaceJsonList);
}
dateList = JSON.parseArray(replaceJsonList, classType);
//logger.info("移除空数据后 : " + JSON.toJSONString(dateList));
for (int i = 0; i < dateList.size(); i++) {
mapper.insert(dateList.get(i));
}
} catch (Exception e) {
throw new HisignException("文件导入失败!");
}
int dateNum = dateList.size();
logger.info("导入数据条数 : " + dateNum);
return dateNum;
}
/**
* excel导入工具类 多个excel
* <p>
* 要保证 sheet列表的顺序,mapperList的mapper顺序,classList的class顺序保持一致
*
* @param mapperList 需要操作新增的数据的mapper集合
* @param classTypeList 上传数据的数据类型
* @param file 导入文件
* @return sheetNum 导入sheet个数 dateNum导入数据条数
*/
public static Map<String, Object> importMoreExcelUtil(List<BaseMapper> mapperList, List<Class> classTypeList, MultipartFile file) {
logger.info("导入多excel");
HashMap<String, Object> resultMap = new HashMap<>();
Integer sheetNum = null;
Integer importUnm = 0;
try {
InputStream is = file.getInputStream();
HSSFWorkbook workbook = new HSSFWorkbook(is);
//得到导入的sheet数量
sheetNum = workbook.getNumberOfSheets();
logger.info("导入sheet数量为 : " + sheetNum);
ImportParams importParams = new ImportParams();
importParams.setHeadRows(1);
for (int i = 0; i < sheetNum; i++) {
logger.info("开始导入第 : " + i + 1 + " 个sheet");
importParams.setStartSheetIndex(i);
List<T> importList = ExcelImportUtil.importExcel(file.getInputStream(), classTypeList.get(i), importParams);
String jsonData = JSON.toJSONString(importList);
logger.info("导入第 " + i + 1 + " 个sheet , 数据为 : " + jsonData);
String replaceData = jsonData.replaceAll(",\\{\\}", "");
logger.info("去除空对象之后的数据 : " + replaceData);
List<T> dateList = JSON.parseArray(replaceData, classTypeList.get(i));
if (dateList.size() > 0) {
for (int j = 0; j < dateList.size(); j++) {
mapperList.get(i).insert(dateList.get(j));
}
}
importUnm += dateList.size();
}
} catch (Exception e) {
throw new HisignException(e.getMessage());
}
resultMap.put("sheetNum", sheetNum);
resultMap.put("dateNum", importUnm);
return resultMap;
}
/**
* 导出excel工具类 单个excel
*
* @param dateList 导出的数据集合
* @param classType 导出的数据类型
* @param fileName 导出的excel文件名称
* @param sheetName 导出的sheet名字 (要求为 中文名 + 数据库表名)
* @param response 响应
* @return 导出数据数量
*/
public static Integer exportExcelUtil(List<?> dateList, Class classType, String fileName, String sheetName, HttpServletResponse response) {
logger.info("导出文件名称为: " + fileName);
ExportParams exportParams = new ExportParams();
exportParams.setSheetName(sheetName);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, classType, dateList);
OutputStream out = null;
try {
response.setCharacterEncoding("UTF-8");
//response.setContentType("application/msexcel");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.addHeader("Access-Control-Allow-Origin", "*"); //跨域
//写出
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
throw new HisignException("导入文件失败!");
} finally {
try {
out.close();
} catch (IOException e) {
logger.error("关闭流失败!");
}
}
return dateList.size();
}
/**
* 导出excel工具类 多个excel
* <p>
* * 要保证 sheetName列表的顺序,dateList的date顺序,sheetName的name顺序保持一致
*
* @param classTypeList 导出数据实体集合
* @param dateList 导出数据集合列表
* @param sheetNameList 导出多个sheet的name集合,按顺序
* @param fileName 导出文件名称
* @param response 响应
* @return sheetNum 导出sheet个数 dateNum 导出数据条数
*/
public static Map<String, Object> exportMoreExcelUtil(List<Class> classTypeList, List<List<?>> dateList, List<String> sheetNameList, String fileName, HttpServletResponse response) {
logger.info("多sheet导出");
HashMap<String, Object> resultMap = new HashMap<>();
ArrayList<Map<String, Object>> sheetList = new ArrayList<>();
if (sheetNameList.size() > 0) {
for (int i = 0; i < sheetNameList.size(); i++) {
ExportParams exportParams = new ExportParams();
exportParams.setSheetName(sheetNameList.get(i));
HashMap<String, Object> exportMap = new HashMap<>();
exportMap.put("title", exportParams);
exportMap.put("entity", classTypeList.get(i));
exportMap.put("data", dateList.get(i));
sheetList.add(exportMap);
}
}
Workbook workbook = ExcelExportUtil.exportExcel(sheetList, ExcelType.HSSF);
OutputStream out = null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
response.addHeader("Access-Control-Allow-Origin", "*"); //跨域
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
throw new HisignException(e.getMessage());
} finally {
try {
out.close();
} catch (IOException e) {
logger.error("关闭流失败!");
}
}
Integer num = 0;
for (List<?> list : dateList) {
num += list.size();
}
resultMap.put("sheetNum", sheetNameList.size());
resultMap.put("dateNum", num);
return resultMap;
}
/**
* 移除集合中空对象 现在是泛型,无法获取 未启用
*
* @param list
* @return
*/
private static List<T> removeEmptyObject(List<T> list) {
if (list.size() > 0) {
Iterator<T> iterator = list.iterator();
while (iterator.hasNext()) {
//单个对象所有字段
Field[] fields = iterator.next().getClass().getFields();
//Field[] fields = iterator.next().getClass().getDeclaredFields();
Boolean flag = true;
for (Field field : fields) {
//访问私有属性
//field.setAccessible(true);
try {
//字典值
Object val = field.get(iterator.next());
if (val != null) {
//只要有一个字段不为空,就表示此对象不为空,结束校验
flag = false;
break;
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
if (flag) {
iterator.remove();
}
}
}
return list;
}
}