效果图网站模板,上海专业网站建设服,长沙模板建站平台,做影评的网站模版Excel是一个Java开发中必须会用到的东西#xff0c;之前博主也发过一篇关于使用Excel的文章#xff0c;但是最近工作中#xff0c;发现了一个更好的使用方法#xff0c;所以#xff0c;就对之前的博客进行总结#xff0c;然后就有了这篇新的#xff0c;万能通用的方法说…Excel是一个Java开发中必须会用到的东西之前博主也发过一篇关于使用Excel的文章但是最近工作中发现了一个更好的使用方法所以就对之前的博客进行总结然后就有了这篇新的万能通用的方法说明书了 好了闲话不多说直接上干货了 控制器代码如下
Operation(summary Excel导入, description Excel导入)GetMapping(/commPointManagement/importExcel)public ResultBody importExcel(RequestParam MultipartFile excel,RequestParam(value creatorId) Long creatorId,HttpServletResponse response){try {return commPointManagementService.importExcel(excel,creatorId,response);} catch (Exception e) {return ResultBody.failed(导入失败e.getMessage());}}/*** excel导出* param dto*/Operation(summary Excel导出, description Excel导出)PostMapping(/commPointManagement/downloadInfoExcel)public void downloadInfoExcel(RequestBody CommPointManagementDTO dto, HttpServletResponse response){commPointManagementService.downloadInfoExcel(dto,response);}/*** 下载模板*/GetMapping(/commPointManagement/downloadExcelTemplate)Operation(summary 下载模板excel模板, description 下载模板excel模板)public void exportExcelFormat(HttpServletResponse response) {commPointManagementService.exportExcelFormat(response);}上面总结了三个方法都是不使用模板的代码也有注释大家可根据注释获取自己需要的方法
service接口就不粘贴了直接上实现类了
public void exportExcelFormat(HttpServletResponse response) {ListListString customTitle new ArrayList();ListString titles Lists.newArrayList();titles.add(字段1);titles.add(字段2);titles.add(字段3);customTitle.add(titles);try {EasyPoiUtil.exportExcel(模板名称.xlsx, null, null, Lists.newArrayList(), CommPointManagementVO.class, customTitle, Lists.newArrayList(), response);} catch (Exception e) {throw new RuntimeException(e);}}Overridepublic ResultBody importExcel(MultipartFile excel,Long creatorId, HttpServletResponse response) throws Exception {//获取Excel中的数据集合ExcelImportResultCommPointManagementBean importExcel EasyPoiUtil.importExcel(excel, 1, 0, 0, false, CommPointManagementBean.class);ListCommPointManagementBean importList importExcel.getList();int failCount 0;int insertCount 0;if (CollUtil.isEmpty(importList)) {return ResultBody.ok().msg(导入excel成功);}Timestamp time new Timestamp(new Date().getTime());for (int i 0; i importList.size(); i) {CommPointManagementBean date importList.get(i);}return ResultBody.ok().data().msg(导入excel成功);}Overridepublic void downloadInfoExcel(CommPointManagementDTO dto, HttpServletResponse response) {//获取数据库数据IPageCommPointManagementVO page commPointManagementMapper.findPage(new PageParams(dto.getPage(), dto.getLimit()), dto);ListCommPointManagementVO records page.getRecords();ListListString customTitle new ArrayList();ListString titleList new ArrayList();titleList.add(字段1);titleList.add(字段2);titleList.add(字段3);customTitle.add(titleList);try {EasyPoiUtil.exportExcel(表名.xlsx, 表头, Excel中的页名, records,CommPointManagementVO.class, customTitle, new ArrayList(), response);} catch (Exception e) {throw new RuntimeException(e);}}上面方法中的CommPointManagementVO.class是接口返回的实体类虽然没有注解但是细心一点看还是能看出来的里面还有一个工具类EasyPoiUtil这个就是咱们今天的主角了主角介绍如下
package com.secondcloud.system.server.util;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** easyPoi excel通用导入导出*/
public class EasyPoiUtil {private static final String SUFFIX .xlsx;/*** exportExcel 通用导出方法(bean加注解)** param fileName 文件名* param title 标题* param sheetName 表单名* param list 数据* param pojoClass 数据类* param customTitle 自定义表头* param mergeList 自定义合并* param response 响应* throws Exception*/public static void exportExcel(String fileName, String title, String sheetName, List? list, Class? pojoClass,ListListString customTitle, ListCellRangeAddress mergeList, HttpServletResponse response)throws Exception {defaultExport(list, pojoClass, fileName, response, simpleParams(title, sheetName, fileName), customTitle,mergeList, null);}/*** exportExcelEntity 通用导出方法(自定义ExcelExportEntity)** param fileName 文件名* param title 标题* param sheetName 表单名* param list 数据* param entityList 数据配置* param customTitle 自定义表头* param mergeList 自定义合并* param response 响应* throws Exception*/public static void exportExcelEntity(String fileName, String title, String sheetName, List? list, ListExcelExportEntity entityList,ListListString customTitle, ListCellRangeAddress mergeList, HttpServletResponse response)throws Exception {defaultExport(list, null, fileName, response, simpleParams(title, sheetName, fileName), customTitle,mergeList, entityList);}/*** exportExcel 多表单导出方法(bean加注解)** param fileName 文件名* param configList title:标题 data:数据 entity:数据类* param response 响应* throws Exception*/public static void exportExcelMultiSheet(String fileName, ListMapString, Object configList,HttpServletResponse response) throws Exception {// 根据fileName后缀确定excel文件是xls还是xlsxExcelType type ExcelType.HSSF;if (StrUtil.endWith(fileName, SUFFIX)) {type ExcelType.XSSF;}Workbook workbook ExcelExportUtil.exportExcel(configList, type);if (workbook ! null) {try {if (CollectionUtil.isNotEmpty(configList)) {int sheetIndex 0;for (MapString, Object map : configList) {ListListString customTitle (ListListString) map.get(customTitle);ListCellRangeAddress mergeList (ListCellRangeAddress) map.get(mergeList);String sheetTitle Convert.toStr(map.get(sheetTitle));Sheet sheet workbook.getSheetAt(sheetIndex);if (sheet ! null) {// 自定义合并boolean mergeFlag CollectionUtil.isNotEmpty(mergeList);if (mergeFlag) {for (CellRangeAddress merge : mergeList) {sheet.addMergedRegion(merge);}}// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)int begin 0;int end 0;String tmpTitle null;// 已合并区域标识 ijListString mergedList new ArrayList();int i 0;if (sheetTitle ! null) {i;}int line i;CellStyle cellStyle null;for (ListString strings : customTitle) {Row row sheet.getRow(i);if (row null) {row sheet.createRow(i);}int j 0;for (String s : strings) {Cell cell row.getCell(j);if (cell null) {cell row.createCell(j);cell.setCellStyle(cellStyle);} else if (cellStyle null) {cellStyle cell.getCellStyle();}cell.setCellValue(s);if (!mergeFlag) {// 第一行合并规则if (i line) {if (StrUtil.isEmpty(s)) {end j;}if (tmpTitle ! null StrUtil.isNotEmpty(s) !tmpTitle.equals(s)) {if (end begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f begin; f end; f) {mergedList.add(i f);}}begin j;tmpTitle s;}if (tmpTitle null) {begin j;tmpTitle s;}} else if (i line 1) {// 第二行向上合并if (StrUtil.isEmpty(s) !mergedList.contains((i - 1) j)) {sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));mergedList.add((i - 1) j);mergedList.add(i j);}}}j;}if (!mergeFlag) {// 第一行合并规则(换行前)if (i line) {if (strings.size() 0 end begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f begin; f end; f) {mergedList.add(i f);}}}}i;}}sheetIndex;}}} catch (Exception e) {}downLoadExcel(fileName, response, workbook);}}/*** importExcel 通用导入方法(bean加注解)** param file 导入文件* param titleRows 标题行数* param headerRows 头部行数* param keyIndex 主键列 如果这个cell没有值,就跳过默认0* param needVerfiy 是否需要校验默认false* param pojoClass 数据类* return* throws Exception*/public static T ExcelImportResultT importExcel(MultipartFile file, Integer titleRows, Integer headerRows,Integer keyIndex, Boolean needVerfiy, ClassT pojoClass) throws Exception {if (file null) {return null;}ImportParams params new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setKeyIndex(keyIndex);params.setNeedVerify(needVerfiy);ExcelImportResultT rst null;try {rst ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}/*** importExcel 指定表单导入方法(bean加注解)** param file 导入文件* param titleRows 标题行数* param headerRows 头部行数* param startSheetIndex 表单序号* param sheetNum 表单数量* param keyIndex 主键列 如果这个cell没有值,就跳过\* param needVerfiy 是否需要校验默认false* param pojoClass 数据类* return* throws Exception*/public static T ExcelImportResultT importExcelCommon(MultipartFile file, Integer titleRows, Integer headerRows,Integer startSheetIndex, Integer sheetNum, Integer keyIndex, Boolean needVerfiy, ClassT pojoClass)throws Exception {if (file null) {return null;}ImportParams params new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setStartSheetIndex(startSheetIndex);params.setSheetNum(sheetNum);params.setKeyIndex(keyIndex);params.setNeedVerify(needVerfiy);ExcelImportResultT rst null;try {rst ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}/*** importExcel 根据文件路径导入excel(bean加注解)** param filePath* param titleRows* param headerRows* param pojoClass* param T* return* throws Exception*/public static T ExcelImportResultT importExcel(String filePath, Integer titleRows, Integer headerRows,ClassT pojoClass) throws Exception {if (StrUtil.isEmpty(filePath)) {return null;}ImportParams params new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);ExcelImportResultT rst null;try {rst ExcelImportUtil.importExcelMore(new File(filePath), pojoClass, params);} catch (Exception e) {e.printStackTrace();throw e;}return rst;}private static void defaultExport(List? list, Class? pojoClass, String fileName, HttpServletResponse response,ExportParams exportParams, ListListString customTitle, ListCellRangeAddress mergeList,ListExcelExportEntity entityList) throws Exception {exportParams.setFixedTitle(false);Workbook workbook;int titleLine 0;if (CollectionUtil.isNotEmpty(customTitle)) {titleLine customTitle.size();}// 自定义标题需要在标题下插入行ListObject t new ArrayList();while (titleLine 1 CollectionUtil.isNotEmpty(list)) {t.add(pojoClass.getDeclaredConstructor().newInstance());titleLine--;}t.addAll(list);if (pojoClass null) {workbook ExcelExportUtil.exportExcel(exportParams, entityList, t);} else {workbook ExcelExportUtil.exportExcel(exportParams, pojoClass, t);}if (workbook ! null) {// 自定义标题if (CollectionUtil.isNotEmpty(customTitle)) {Sheet sheet workbook.getSheetAt(0);if (sheet ! null) {// 自定义合并boolean mergeFlag CollectionUtil.isNotEmpty(mergeList);if (mergeFlag) {for (CellRangeAddress merge : mergeList) {sheet.addMergedRegion(merge);}}// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)int begin 0;int end 0;String tmpTitle null;// 已合并区域标识 ijListString mergedList new ArrayList();int i 0;if (exportParams.getTitle() ! null) {i;}if (exportParams.getSecondTitle() ! null) {i;}int line i;CellStyle cellStyle null;for (ListString strings : customTitle) {Row row sheet.getRow(i);if (row null) {row sheet.createRow(i);}int j 0;for (String s : strings) {Cell cell row.getCell(j);if (cell null) {cell row.createCell(j);cell.setCellStyle(cellStyle);} else if (cellStyle null) {cellStyle cell.getCellStyle();}cell.setCellValue(s);if (!mergeFlag) {// 第一行合并规则if (i line) {if (StrUtil.isEmpty(s)) {end j;}if (tmpTitle ! null StrUtil.isNotEmpty(s) !tmpTitle.equals(s)) {if (end begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f begin; f end; f) {mergedList.add(i f);}}begin j;tmpTitle s;}if (tmpTitle null) {begin j;tmpTitle s;}} else if (i line 1) {// 第二行向上合并if (StrUtil.isEmpty(s) !mergedList.contains((i - 1) j)) {sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));mergedList.add((i - 1) j);mergedList.add(i j);}}}j;}if (!mergeFlag) {// 第一行合并规则(换行前)if (i line) {if (strings.size() 0 end begin) {sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));for (int f begin; f end; f) {mergedList.add(i f);}}}}i;}}}downLoadExcel(fileName, response, workbook);}}private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)throws Exception {OutputStream ouputStream null;try {response.setCharacterEncoding(UTF-8);response.setHeader(content-Type, application/vnd.ms-excel);response.setHeader(Content-Disposition, attachment;filename URLEncoder.encode(fileName, UTF-8));response.setHeader(Pragma, No-cache);ouputStream response.getOutputStream();workbook.write(ouputStream);} catch (Exception e) {e.printStackTrace();throw e;} finally {if (ouputStream ! null) {try {ouputStream.flush();ouputStream.close();} catch (Exception e) {}}}}/*** 获取表单配置项** param title* param sheetName* param fileName* return*/public static ExportParams simpleParams(String title, String sheetName, String fileName) {// 根据fileName后缀确定excel文件是xls还是xlsxExcelType type ExcelType.HSSF;if (StrUtil.endWith(fileName, SUFFIX)) {type ExcelType.XSSF;}ExportParams exportParams new ExportParams(title, sheetName, type);exportParams.setFixedTitle(false);return exportParams;}/*** 获取表单配置对象** param title* param sheetName* param fileName* param pojoClass* param list* param customTitle 自定义表头* param mergeList 自定义合并* return*/public static MapString, Object simpleParamsMap(String title, String sheetName, String fileName, List? list,Class? pojoClass, ListListString customTitle, ListCellRangeAddress mergeList) throws Exception {MapString, Object map new HashMapString, Object();map.put(title, simpleParams(title, sheetName, fileName));map.put(entity, pojoClass);ListObject array new ArrayList();if (CollectionUtil.isNotEmpty(customTitle)) {int size customTitle.size();while (size 1) {array.add(pojoClass.getDeclaredConstructor().newInstance());size--;}}if (list ! null) {array.addAll(list);}map.put(data, array);map.put(customTitle, customTitle);map.put(mergeList, mergeList);map.put(sheetTitle, title);return map;}/*** 生成ExcelExportEntity** param name 名称* param value 属性* param merge 是否合并列* param list 下级entity* return*/public static ExcelExportEntity getExportEntity(String name, String value, Boolean merge, Double width, Integer orderNum,String format, ListExcelExportEntity list) {ExcelExportEntity entity new ExcelExportEntity(name, value);if (merge ! null) {entity.setNeedMerge(merge);}if (width ! null) {entity.setWidth(width);}if (orderNum ! null) {entity.setOrderNum(orderNum);}if (format ! null) {entity.setFormat(format);}if (CollectionUtil.isNotEmpty(list)) {entity.setList(list);}return entity;}public static ListExcelExportEntity classToEntityList(Class? pojoClass) {ListExcelExportEntity rst new ArrayList();try {Field[] fileds PoiPublicUtil.getClassFields(pojoClass);ExcelTarget etarget (ExcelTarget) pojoClass.getAnnotation(ExcelTarget.class);String targetId etarget null ? null : etarget.value();new ExcelExportService().getAllExcelField(null, targetId, fileds, rst, pojoClass, (List) null, (ExcelEntity) null);} catch (Exception e) {}return rst;}
}
这个工具类一般的格式都能使用的具体复杂的Excel表格样式还是使用Excel表格模板去做会更方便点工具类的方发也有说明大家自行查看哦。 本篇分享就到这里了如果有使用问题可以评论留言有不对的地方请批评指正谢谢
