easypoi
一、常用注解介绍#
注解介绍：
easypoi起因就是Excel的导入导出,最初的模板是实体和Excel的对应,model–row,filed–col 这样利用注解我们可以和容易做到excel到导入导出，经过一段时间发展,现在注解有5个类分别是：
@Excel作用到filed上面,是对Excel一列的一个描述@ExcelCollection表示一个集合,主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示@ExcelEntity表示一个继续深入导出的实体,但他没有太多的实际意义,只是告诉系统这个对象里面同样有导出的字段@ExcelIgnore和名字一样表示这个字段被忽略跳过这个导导出@ExcelTarget这个是作用于最外层的对象,描述这个对象的id,以便支持一个对象可以针对不同导出做出不同处理
@Excel#
这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,涵盖了常用的Excel需求,需要大家熟悉这个功能,主要分为基础,图片处理,时间处理,合并处理几块,name_id是上面讲的id用法,这里就不累言了：
@ExcelCollection#
一对多的集合注解,用以标记集合是否被数据以及集合的整体排序：
@ExcelEntity#
标记是不是导出excel 标记为实体类,一遍是一个内部属性类,标记是否继续穿透,可以自定义内部id：
二、使用#
导入依赖：#
<!--easypoi 导入导出 -->
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-web</artifactId>
<version>4.2.0</version>
</dependency>
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-annotation</artifactId>
<version>4.2.0</version>
</dependency>
构建对应实体关系：#
1.DayShiftExcelVo：
package com.cdtye.itps.jjxt.model.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* @ClassName BureauDayShiftExcelVo
* @Description TODO 日交班导出Excel实体Vo
* @Author Zhongks
* @Date 2021/5/7 11:34
* @Version 1.0
**/
@Data
@Accessors(chain = true)
@AllArgsConstructor
@ExcelTarget(value = "DayShiftExcelVo")
public class DayShiftExcelVo {
@Excel(name = "日期", width = 20,height = 60)
private String inputDate;
@Excel(name = "基本情况", width = 20,height = 60)
private String basicInformation;
@Excel(name = "问题描述", width = 20,height = 60)
private String faultDescription;
@Excel(name = "上报情况", width = 20,height = 60)
private String reportType;
@Excel(name = "站段分析", width = 20,height = 60)
private String segmentAnalysis;
@Excel(name = "供电处审核", width = 20,height = 60)
private String review;
@Excel(name = "流程", width = 20,height = 60, replace = {"电调暂存草稿单_0","电调已提交状态_1","电调已分析状态_2","供电部审核通过_3","供电部审核不通过_4"})
private String process;
@Excel(name = "重点追踪", width = 20,height = 60, replace = {"非重点追踪_0","重点追踪_1"})
private String track;
}
12345678910111213141516171819202122232425262728293031323334353637383940414243444546
excel显示效果：
2.SkylightCashStatisticsExcelVo：
package com.cdtye.itps.jjxt.model.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* @ClassName SkylightCashStatisticsExcelVo
* @Description TODO 天窗兑现统计导出Excel实体Vo
* @Author Zhongks
* @Date 2021/5/8 10:05
* @Version 1.0
**/
@Data
@Accessors(chain = true)
@AllArgsConstructor
@ExcelTarget(value = "SkylightCashStatisticsExcelVo")
public class SkylightCashStatisticsExcelVo {
@Excel(name = "段别", width = 20,needMerge = true)
private String section;
@Excel(name = "线路等级", width = 20,needMerge = true)
private String lineLevel;
@Excel(name = "线别", width = 20,needMerge = true)
private String lineName;
@ExcelEntity(name = "申请",show = true)
private SkylightCashStatisticsCommonExcelVo applicationExcelVo;
@Excel(name = "取消", width = 20,needMerge = true)
private String cancel;
@Excel(name = "作业车数量", width = 20,needMerge = true)
private String numberOfOperationVehicles;
@Excel(name = "检修列数量", width = 20,needMerge = true)
private String numberOfMaintenanceTrains;
@ExcelEntity(name = "申请时间",show = true)
private SkylightCashStatisticsCommonExcelVo applicationTimeExcelVo;
@ExcelEntity(name = "给点时间",show = true)
private SkylightCashStatisticsCommonExcelVo giveTimeExcelVo;
@ExcelEntity(name = "作业时间",show = true)
private SkylightCashStatisticsCommonExcelVo workTimeExcelVo;
@Excel(name = "取消", width = 20,needMerge = true)
private String cancelReason;
}
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556
SkylightCashStatisticsCommonExcelVo：
package com.cdtye.itps.jjxt.model.vo;
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.experimental.Accessors;
/**
* @ClassName ApplicationExcelVo
* @Description TODO 天窗兑现统计 子集公共vo
* @Author Zhongks
* @Date 2021/5/8 10:11
* @Version 1.0
**/
@Data
@Accessors(chain = true)
@AllArgsConstructor
@ExcelTarget(value = "SkylightCashStatisticsExcelVo")
public class SkylightCashStatisticsCommonExcelVo {
@Excel(name = "供电类", width = 20)
private String powerSupply;
@Excel(name = "非供电类", width = 20)
private String nonPowerSupply;
}
12345678910111213141516171819202122232425262728
excel显示效果：
3.使用@ExcelCollection显示效果：（由于实现原理一致，本文章不做示例）
Service层
/**
* @Author Zhongks
* @Description //TODO excel导出
* @Date 12:25 2021/5/7
* @Param [list, response]
* @return void
**/
public void export(BureauDayShiftVo bureauDayShiftVo,HttpServletResponse response) {
try {
// 设置下载的Excel名称，以当前时间为文件后缀，
String dateTime = DateUtil.formatDateString(new Date(), DateUtil.DATE_FORMAT);
String fileName = "供电安全质量日交班表"+dateTime+".xlsx";
// 设置响应输出的头类型
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="+fileName);
// excel信息部分
//供电处重点信息追踪表信息
bureauDayShiftVo.setTrackFlag(1);
Map<String, Object> trackSafeQualityMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
//日安全质量信息表信息
bureauDayShiftVo.setTrackFlag(0);
Map<String, Object> safeQualityParamsMap =this.getTrackSafeQualityMap(bureauDayShiftVo);
//天窗兑现统计表
Map<String, Object> skylightCashStatisticsMap = this.getSkylightCashStatisticsMap();
//添加表
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(trackSafeQualityMap);//sheet1
sheetsList.add(safeQualityParamsMap);//sheet2
sheetsList.add(skylightCashStatisticsMap);//sheet3
//创建excel文件的方法
Workbook workbook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.HSSF);
//通过response输出流直接输入给客户端
ServletOutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @Author Zhongks
* @Description //TODO 返回重点追踪以及非重点追踪excel信息
* @Date 9:31 2021/5/8
* @Param [bureauDayShiftVo]
* @return java.util.List<java.util.Map<java.lang.String,java.lang.Object>>
**/
public Map<String, Object> getTrackSafeQualityMap(BureauDayShiftVo bureauDayShiftVo){
List<DayShiftExcelVo> exportList = new LinkedList<>();
List<Map<String, Object>> allTrackSafeQualityList = bureauDayShiftMapper.getAllTrackSafeQualityList(bureauDayShiftVo);
//封装数据
allTrackSafeQualityList.forEach(map -> {
String basicInformation="单位："+map.get("unitdeptname")+"\n"+
"线别："+map.get("lineName")+"\n"+
"所亭："+map.get("bdsSubstationName")+"\n"+
"开关号："+map.get("switchNo")+"\n"+
"故障地点："+DateUtil.formatDateString(map.get("stopDate"), DateUtil.DATE_FORMAT)+"\n"+
"发生时间："+map.get("unitdeptname")+"\n"+
"停时（分钟）："+map.get("unitdeptname")+"\n"+
"天气："+map.get("unitdeptname")+"\n"+
"专业分类："+map.get("unitdeptname")+"\n";
String segmentAnalysis="单位："+map.get("unitdeptname")+"\n"+
"单位：详见分析报告"+"\n";
String isTrack="";
if(bureauDayShiftVo.getTrackFlag()==0){
isTrack="否";
}else{
isTrack="是";
}
String review="科室："+map.get("trackunitdeptname")+"\n"+
"问题类别："+map.get("faultCategoryConfigName")+"\n"+
"定责考核："+map.get("dutyType")+"\n"+
"审核结果："+map.get("switchNo")+"\n"+
"重点追踪："+isTrack+"\n";
DayShiftExcelVo dayShiftExcelVo=new DayShiftExcelVo(
DateUtil.formatDateString(map.get("inputDate"), DateUtil.DATE_FORMAT),
basicInformation,
(String)map.get("faultDescription"),
(String)map.get("reporttype"),
segmentAnalysis,
review,
map.get("safeQualityState").toString(),
String.valueOf(bureauDayShiftVo.getTrackFlag()));
exportList.add(dayShiftExcelVo);
});
ExportParams exportParams = new ExportParams();
//设置边框样式
exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
// 设置sheet的名称
if(bureauDayShiftVo.getTrackFlag()==0){
exportParams.setSheetName("日安全质量信息");
}else{
exportParams.setSheetName("供电处重点追踪信息");
}
Map<String, Object> map = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
map.put("title", exportParams);
// 模版导出对应得实体类型，即包含了List的对象
map.put("entity", DayShiftExcelVo.class);
// sheet中要填充得数据
map.put("data", exportList);
return map;
}
/**
* @Author Zhongks
* @Description //TODO 返回天窗兑现统计excel信息
* @Date 10:59 2021/5/8
* @Param []
* @return java.util.Map<java.lang.String,java.lang.Object>
**/
public Map<String, Object> getSkylightCashStatisticsMap(){
List<SkylightCashStatisticsExcelVo> exportList = new LinkedList<>();
//ToDo 得到天窗兑现统计列表数据并进行封装
//示例数据
SkylightCashStatisticsCommonExcelVo applicationExcelVo=new SkylightCashStatisticsCommonExcelVo("申请供电类","申请非供电类");
SkylightCashStatisticsCommonExcelVo applicationTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("申请时间供电类","申请时间非供电类");
SkylightCashStatisticsCommonExcelVo getTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("给点时间供电类","给点时间非供电类");
SkylightCashStatisticsCommonExcelVo workTimeExcelVo=new SkylightCashStatisticsCommonExcelVo("作业时间供电类","作业时间非供电类");
SkylightCashStatisticsExcelVo skylightCashStatisticsExcelVo=new SkylightCashStatisticsExcelVo("怀化供电段","高铁","沪昆高速线",
applicationExcelVo,"取消","10","10",applicationTimeExcelVo,getTimeExcelVo,workTimeExcelVo,"天窗取消原因");
exportList.add(skylightCashStatisticsExcelVo);
exportList.add(skylightCashStatisticsExcelVo);
exportList.add(skylightCashStatisticsExcelVo);
//供电处重点追踪信息表
ExportParams exportParams = new ExportParams();
//设置边框样式
exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
// 设置sheet的名称
exportParams.setSheetName("天窗兑现统计");
Map<String, Object> map = new HashMap<>();
// title的参数为ExportParams类型，目前仅仅在ExportParams中设置了sheetName
map.put("title", exportParams);
// 模版导出对应得实体类型，即包含了List的对象
map.put("entity", SkylightCashStatisticsExcelVo.class);
// sheet中要填充得数据
map.put("data", exportList);
return map;
}
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151
bureauDayShiftMapper.getAllTrackSafeQualityList(bureauDayShiftVo) 数据返回示例：
{
"data": [
{
"dutyType": null,
"unitdeptname": "长沙供电段",
"faultPlace": "故障地点",
"reporttype": null,
"faultmajorname": "接触网",
"safeQualityState": 0,
"faultCategoryConfigName": null,
"lineName": "南广铁路",
"stopMinute": null,
"weatherInfo": null,
"inputDate": 1620460879000,
"archiveFiles": [],
"trackunitdeptname": "安全科",
"bdsSubstationName": null,
"faultDescription": "故障概况\n",
"id": "1390912864588001281",
"stopDate": 1619798400000,
"switchNo": null
}
],
"message": "操作成功",
"status": 200
}
1234567891011121314151617181920212223242526
excel实现换行说明：
控制器：#
@ApiOperation("导出供电安全质量日交班表")
@GetMapping("/export")
public AjaxJson export(BureauDayShiftVo query, HttpServletResponse response) {
bureauDayShiftService.export(query,response);
return AjaxJson.success();
}
123456
三、Excel导出效果#
四、多sheet页工具类#
1.pom依赖
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-base</artifactId>
<version>4.3.0</version>
</dependency>
2.工具类ExcelExport
public class ExcelExport {
private HSSFWorkbook wb;
private String fileName;
public ExcelExport(String fileName) {
if (wb == null)
this.wb = new HSSFWorkbook();
this.fileName = fileName;
}
//导出参数
public static ExportParams getExportParams(String name) {
//表格名称,sheet名称,导出版本
return new ExportParams(name, name, ExcelType.HSSF);
}
public void setWorkBook(Workbook wb) {
this.wb = (HSSFWorkbook) wb;
}
public void doExportTwo(String path) throws Exception {
final OutputStream os = new FileOutputStream(new File(path + "/" + fileName + ".xls"));
this.wb.write(os);
os.flush();
os.close();
}
}
1234567891011121314151617181920212223242526272829
多sheet导出#
public class Test {
public static void main(String[] args) throws Exception {
//多个map，对应了多个sheet
List<Map<String, Object>> listMap = new ArrayList<>();
for (int i = 0; i < 4; i++) {
Map<String, Object> map = new HashMap<>();
//表格title
map.put("title", ExcelExport.getExportParams("测试" + i));
//表格对应实体
map.put("entity", TestEntity.class);
List<Test.TestEntity> data = new ArrayList<Test.TestEntity>();
for (int j = 0; j < 100; j++) {
Test.TestEntity testEntity = new Test.TestEntity();
testEntity.setUsername("张三" + j);
testEntity.setAge(18 + j);
data.add(testEntity);
}
map.put("data", data);
listMap.add(map);
}
Workbook wb = ExcelExportUtil.exportExcel(listMap, ExcelType.HSSF);
final ExcelExport export = new ExcelExport("基础物料");
export.setWorkBook(wb);
export.doExportTwo("E:\\temp");
}
@Data
static class TestEntity {
@Excel(name = "姓名", width = 15)
private String username;
@Excel(name = "年龄", width = 15)
private int age;
}
}
多sheet导入#
1.功能方法
@Override
public void importFile(MultipartFile file) throws Exception {
final String filename = file.getOriginalFilename();
final Workbook workBook = ExcelUtil.getWorkBook(file);
// 此多线程方法仅供测试，如上正式环境 需要封装，会导致系统CPU炸裂
// 多sheet导入用线程池
// int nThreads = 4;
// ThreadPoolExecutor threadPoolExecutor = new ThreadPoolExecutor(nThreads, nThreads, 0L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(51), r -> {
// Thread t = new Thread(r);
// t.setUncaughtExceptionHandler((t1, e) -> log.error("线程异常：thread={},异常e={}", t1, e.getMessage()));
// return t;
// });
for (int sheetIndex = 0; sheetIndex < workBook.getNumberOfSheets(); sheetIndex++) {
byte[] bytes = file.getBytes();
final InputStream inputStream = new ByteInputStream(bytes, bytes.length);
final String sheetName = workBook.getSheetName(sheetIndex);
final List<BaseMaterialImportDTO> materialImportList = new ArrayList<>();
final List<BaseMaterialImportDTO> materialImportFailList = new ArrayList<>();
final ImportParams params = new ImportParams();
params.setHeadRows(1);
params.setTitleRows(0);
params.setStartSheetIndex(sheetIndex);
// threadPoolExecutor.submit(() -> {
if (filename.lastIndexOf(FormatExcel.FORMAT_CSV) > -1) {
final CsvImportParams csvImportParams = new CsvImportParams();
csvImportParams.setEncoding(CsvImportParams.GBK);
csvImportParams.setHeadRows(1);
CsvImportUtil.importCsv(inputStream, BaseMaterialImportDTO.class, csvImportParams, new IReadHandler<BaseMaterialImportDTO>() {
/**
* 行数据处理
*
* @param baseMaterialImportDTO
*/
@Override
public void handler(final BaseMaterialImportDTO baseMaterialImportDTO) {
BaseMaterialServiceImpl.this.verifyRowData(baseMaterialImportDTO, materialImportFailList, materialImportList);
}
@Override
public void doAfterAll() {
BaseMaterialServiceImpl.this.doAfter(materialImportList, materialImportFailList, sheetName);
}
});
} else if (filename.lastIndexOf(FormatExcel.FORMAT_XLSX) > -1) {
ExcelImportUtil.importExcelBySax(inputStream, BaseMaterialImportDTO.class, params, new IReadHandler<BaseMaterialImportDTO>() {
@Override
public void handler(final BaseMaterialImportDTO baseMaterialImportDTO) {
BaseMaterialServiceImpl.this.verifyRowData(baseMaterialImportDTO, materialImportFailList, materialImportList);
}
@Override
public void doAfterAll() {
BaseMaterialServiceImpl.this.doAfter(materialImportList, materialImportFailList, sheetName);
}
});
} else if (filename.lastIndexOf(FormatExcel.FORMAT_XLS) > -1) {
params.setNeedVerify(true);
try {
final ExcelImportResult<BaseMaterialImportDTO> excelImportResult = ExcelImportUtil.importExcelMore(inputStream, BaseMaterialImportDTO.class, params);
this.doAfter(excelImportResult.getList(), excelImportResult.getFailList(), sheetName);
} catch (Exception e) {
log.error("解析或插入数据异常：异常e={}", e.getMessage());
}
} else {
throw new ServiceException("仅支持上传excel、csv文件");
}
// });
}
}
2.实体类BaseMaterialImportDTO.java
import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;
import javax.validation.constraints.NotBlank;
/**
* 物料导入类
*/
@Data
public class BaseMaterialImportDTO {
@Excel(name = "成品编码")
@NotBlank(message = "[成品编码]不能为空")
private String parentMaterialNo;
@Excel(name = "子项物料代码")
@NotBlank(message = "[子项物料代码]不能为空")
private String materialNo;
@Excel(name = "物料名称")
@NotBlank(message = "[物料名称]不能为空")
private String materialName;
@Excel(name = "规格型号")
@NotBlank(message = "[规格型号]不能为空")
private String materialSpecification;
@Excel(name = "物料属性")
@NotBlank(message = "[物料属性]不能为空")
private String materialProperty;
@Excel(name = "工位")
@NotBlank(message = "[工位]不能为空")
private String materialWorkstation;
}
1234567891011121314151617181920212223242526272829303132333435363738
3.verifyRowData方法是校验方法，doAfter是入数据库方法
https://blog.csdn.net/weixin_43296313/article/details/128183513
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· TypeScript + Deepseek 打造卜卦网站：技术与玄学的结合
· Manus的开源复刻OpenManus初探
· AI 智能体引爆开源社区「GitHub 热点速览」
· 三行代码完成国际化适配，妙~啊~
· .NET Core 中如何实现缓存的预热？