一.参考官网文档：easypoi文档
二.导入easypoi的maven依赖
<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>
三.生成Excel准备工作
3.1 对象实体添加注解
public class TBreakRule implements Serializable {/*** id*/@Excel(name = "编号",orderNum = "1")private Integer id;@Excel(name = "违停编号",width = 25,orderNum = "2")private String breakCode;/*** 车牌号*/@Excel(name = "车牌号",orderNum = "3")private String plateNumber;/*** 地址*/@Excel(name = "地址",width = 60,orderNum = "4")private String address;/*** 图片*/private String picture;@Excel(name = "车辆正面照片",type = 2,width = 50,height = 60,orderNum = "5")private String picture1;@Excel(name = "车辆左侧照片",type = 2,width = 50,height = 60,orderNum = "5")private String picture2;@Excel(name = "车辆右侧照片",type = 2,width = 50,height = 60,orderNum = "5")private String picture3;/*** 违章信息*/@Excel(name = "违章信息",orderNum = "6")private String breakInfo;/*** 车牌颜色*/@Excel(name = "车牌颜色",orderNum = "7")private String plateColor;/*** 01：大型车。02：小型车 07：2，3轮摩托车，08：轻便摩托车。13：农用运输车*/@Excel(name = "车辆类型",replace = {"大型车_01","小型车_02","摩托车_07","轻便摩托车_08","农用运输车_13"},orderNum = "8")private String carType;/*** 车颜色*/@Excel(name = "车身颜色",suffix = "色",orderNum = "9" )private String carColor;/*** 创建时间*/@Excel(name = "创建时间", format = "yyyy-MM-dd HH:mm:ss" ,width = 20,orderNum = "9")private Date createTime;/*** 创建人*/@Excel(name = "创建人",orderNum = "20")private String createBy;private Date updateTime;private String updateBy;private String delFlag;/*** 10：待审核，20：警告，30：待处理，40：待缴费，50：已处理，60：已推送，70：已作废*/@Excel(name = "状态",replace = {"待审核_10","警告_20","待处理_30","待缴费_40","已处理_50","已推送_60","已作废_70","已推送（旧数据）_4"},orderNum = "11")private String status;/*** 0.未推送 1.已推送*/@Excel(name = "推送状态",replace = {"未推送_0","已推送_1"},orderNum = "12")private String pushStatus;/*** 用户id*/private Integer userId;@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JsonFormat(timezone="GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")private Date startDate;@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")@JsonFormat(timezone="GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")private Date endDate;
3.2 批量下载阿里云OSS图片
/*** 传入要下载的图片的url列表，将url所对应的图片下载到服务器本地* @param fileName 服务器存储路径* @param urlList 阿里云图片地址集合* @return 返回服务器下载图片的路径集合*/private List<String> downloadPicture(String fileName,List<String> urlList) {File file=new File(fileName);// 不存在则创建文件夹if (!file.exists()){file.mkdir();}long imageNumber = System.currentTimeMillis();List<String>imageList=new ArrayList<>();URL url = null;for (String urlString : urlList) {try {url = new URL(urlString);DataInputStream dataInputStream = new DataInputStream(url.openStream());Random random=new Random();int rd=random.nextInt(9999);String imageName = imageNumber+""+rd + ".jpg";FileOutputStream fileOutputStream = new FileOutputStream(new File(fileName+"/"+imageName));byte[] buffer = new byte[1024];int length;while ((length = dataInputStream.read(buffer)) > 0) {fileOutputStream.write(buffer, 0, length);}dataInputStream.close();fileOutputStream.close();imageList.add(fileName+"/"+imageName);imageNumber++;} catch (MalformedURLException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}return imageList;}
四.导出Excel
/*** 导出带图片的Excel* @param fileName* @param list* @param response*/private void exportBreakRuleExcel(String fileName,List<TBreakRule>list, HttpServletResponse response){String now=DateUtils.format(new Date(),"yyyyMMddHHmmss");for (TBreakRule tBreakRule:list){// 将三张图片url分割，进行下载String[] pictures=tBreakRule.getPicture().split(",");List<String>imageList=new ArrayList<>();for (int i=0;i<pictures.length;i++){imageList.add(pictures[i]);}// 下载图片List<String> filePaths=downloadPicture(fileName,imageList);for (int j=0;j<filePaths.size();j++){switch (j){case 0:tBreakRule.setPicture1(filePaths.get(j));break;case 1:tBreakRule.setPicture2(filePaths.get(j));break;case 2:tBreakRule.setPicture3(filePaths.get(j));break;}}}EasyPoiUtil.exportExcel(list,"城市违章车辆管理平台","违章数据",TBreakRule.class,"违停数据"+now+".xls",response);}
EasyPoiUtil工具类代码:
public class EasyPoiUtil {/*** 功能描述：复杂导出Excel，包括文件名以及表名。创建表头** @param list 导出的实体类* @param title 表头名称* @param sheetName sheet表名* @param pojoClass 映射的实体类* @param isCreateHeader 是否创建表头* @param fileName* @param response* @return*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头** @param list 导出的实体类* @param title 表头名称* @param sheetName sheet表名* @param pojoClass 映射的实体类* @param fileName* @param response* @return*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) {defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));}/*** 功能描述：Map 集合导出** @param list 实体集合* @param fileName 导出的文件名称* @param response* @return*/public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {defaultExport(list, fileName, response);}/*** 功能描述：默认导出方法** @param list 导出的实体集合* @param fileName 导出的文件名* @param pojoClass pojo实体* @param exportParams ExportParams封装实体* @param response* @return*/private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);if (workbook != null) {downLoadExcel(fileName, response, workbook);}}/*** 功能描述：Excel导出** @param fileName 文件名称* @param response* @param workbook Excel对象* @return*/private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));workbook.write(response.getOutputStream());} catch (IOException e) {throw new RuntimeException(e);}}/*** 功能描述：默认导出方法** @param list 导出的实体集合* @param fileName 导出的文件名* @param response* @return*/private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);if (workbook != null) ;downLoadExcel(fileName, response, workbook);}/*** 功能描述：根据文件路径来导入Excel** @param filePath 文件路径* @param titleRows 表标题的行数* @param headerRows 表头行数* @param pojoClass Excel实体类* @return*/public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) {//判断文件是否存在if (StringUtils.isBlank(filePath)) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);} catch (NoSuchElementException e) {throw new RuntimeException("模板不能为空");} catch (Exception e) {e.printStackTrace();}return list;}/*** 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类** @param file 上传的文件* @param titleRows 表标题的行数* @param headerRows 表头行数* @param pojoClass Excel实体类* @return*/public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);} catch (NoSuchElementException e) {throw new RuntimeException("excel文件不能为空");} catch (Exception e) {throw new RuntimeException(e.getMessage());}return list;}}
导出后效果图：
如果觉得《easypoi 导出文本图片Excel》对你有帮助，请点赞、收藏，并留下你的观点哦！
