EasyExcel的导入导出使用

EasyExcel的导入导出使用

/** * @ClassName: CellWidthStyleHandler * @Description: 设置表头的调整宽策略 * @Author: * @Date: */ public class CellWidthStyleHandler extends AbstractColumnWidthStyleStrategy { // 可以根据这里的最大宽度，按自己需要进行调整,搭配单元格样式实现类中的，自动换行，效果更好 private static final int MAX_COLUMN_WIDTH = 50; private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8); /** * 是否设置固定宽度 */ private boolean fixed; /** * 固定宽度 */ private int fixedWidth; public CellWidthStyleHandler() { } public CellWidthStyleHandler(boolean fixed, int fixedWidth) { this.fixed = fixed; this.fixedWidth = (fixedWidth == 0 ? 15 : fixedWidth); } @Override protected void setColumnWidth(CellWriteHandlerContext context) { boolean needSetWidth = context.getHead() || !CollUtil.isEmpty(context.getCellDataList()); if (!needSetWidth) { return; } WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder(); // 设置固定宽度 if (fixed) { Cell cell = context.getCell(); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), fixedWidth * 256); return; } // 设置自动调整宽度 Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo()); if (maxColumnWidthMap == null) { maxColumnWidthMap = new HashMap(16); CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap); } Cell cell = context.getCell(); int columnWidth = this.dataLength(context.getCellDataList(), cell, context.getHead()); if (columnWidth >= 0) { if (columnWidth > MAX_COLUMN_WIDTH) { columnWidth = MAX_COLUMN_WIDTH; } Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex()); if (maxColumnWidth == null || columnWidth > maxColumnWidth) { maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth); writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256); } } } private int dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) { if (isHead) { return cell.getStringCellValue().getBytes().length; } else { CellData cellData = cellDataList.get(0); CellDataTypeEnum type = cellData.getType(); if (type == null) { return -1; } else { switch (type) { case STRING: return cellData.getStringValue().getBytes().length; case BOOLEAN: return cellData.getBooleanValue().toString().getBytes().length; case NUMBER: return cellData.getNumberValue().toString().getBytes().length; default: return -1; } } } } } /** * @ClassName: DefaultExcelHeadHandler * @Description: 默认自定义表头样式拦截器 * @Author: * @Date: */ public class DefaultExcelHeadHandler extends HorizontalCellStyleStrategy { //设置头样式 @Override protected void setHeadCellStyle(CellWriteHandlerContext context) { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 11); headWriteFont.setColor(IndexedColors.BLACK.getIndex()); headWriteCellStyle.setWriteFont(headWriteFont); if (stopProcessing(context)) { return; } WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle()); } } /** * @ClassName: EasyExcelUtil * @Description: excel导入导出工具类 * @Author: * @Date: */ public class EasyExcelUtil { /** * 导入-读取一页sheet * * @param inputStream 文件流 * @param clazz 数据对象 * @param sheetName 要读取的sheet (不传，默认读取第一个sheet) * @throws Exception */ public static <T> List<T> importExcel(InputStream inputStream, Class<T> clazz, String sheetName, ReadExcelListener readExcelListener) throws Exception { ExcelReaderBuilder builder = EasyExcelFactory.read(inputStream, clazz, readExcelListener); if (StringUtils.isBlank(sheetName)) { builder.sheet().doRead(); } else { builder.sheet(sheetName).doRead(); } return readExcelListener.getList(); } /** * 导入-读取多个sheet * * @param inputStream 文件流 * @param sheetNum 需要读取的sheet个数(默认0开始，如果传入2，则读取0、1) * @param sheetObjList 每个sheet里面需要封装的对象(如果index为2，则需要传入对应的2个对象) * @param <T> * @return */ public static <T> List<List<T>> importExcels(InputStream inputStream, int sheetNum, List<T> sheetObjList, ReadExcelListener<T> readExcelListener) throws Exception { List<List<T>> resultList = new LinkedList<>(); for (int index = 0; index < sheetNum; index++) { Class<T> tClass = (Class<T>) sheetObjList.get(index).getClass(); EasyExcelFactory.read(inputStream, tClass, readExcelListener).sheet(index).doRead(); List<T> list = readExcelListener.getList(); resultList.add(list); } return resultList; } /** * 导出excel表格-列不确定情况;只导出模板【一个处理器】 * * @param response * @param headList 表头列表 * @param dataList 数据列表 * @param fileName 文件名称 * @param sheetName sheet名称 * @param writeHandler 处理器 * @throws Exception */ public static <T> void exportExcel(HttpServletResponse response, List<List<String>> headList, List<List<T>> dataList, String fileName, String sheetName, WriteHandler writeHandler) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); // 这里注意如果同一个sheet只要创建一次 ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet(sheetName).head(headList); if (null != writeHandler) { sheetBuilder.registerWriteHandler(writeHandler); } WriteSheet writeSheet = sheetBuilder.build(); excelWriter.write(dataList, writeSheet); excelWriter.finish(); } /** * 导出excel表格-列不确定情况;只导出模板【多个处理器】 * * @param response * @param headList 表头列表 * @param dataList 数据列表 * @param fileName 文件名称 * @param sheetName sheet名称 * @param writeHandlers 处理器集合 * @throws Exception */ public static <T> void exportExcel(HttpServletResponse response, List<List<String>> headList, List<List<T>> dataList, String fileName, String sheetName, List<WriteHandler> writeHandlers) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); // 这里注意如果同一个sheet只要创建一次 ExcelWriterSheetBuilder sheetBuilder = EasyExcel.writerSheet(sheetName).head(headList); if (CollUtil.isNotEmpty(writeHandlers)) { for (WriteHandler writeHandler : writeHandlers) { sheetBuilder.registerWriteHandler(writeHandler); } } WriteSheet writeSheet = sheetBuilder.build(); excelWriter.write(dataList, writeSheet); excelWriter.finish(); } /** * 导出excel表格-不支持设置样式 * * @param response * @param dataList 数据列表 * @param clazz 数据对象 * @param fileName 文件名称 * @param sheetName sheet名称 * @throws Exception */ public static <T> void exportExcel(HttpServletResponse response, List<T> dataList, Class<T> clazz, String fileName, String sheetName) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); EasyExcelFactory.write(response.getOutputStream(), clazz).sheet(sheetName).doWrite(dataList); } /** * 导出excel表格-支设置单元格样式 * * @param response * @param dataList 数据列表 * @param clazz 数据对象 * @param fileName 文件名称 * @param sheetName sheet名称 * @param writeHandler 处理器 * @throws Exception */ public static <T> void exportWriteHandlerExcel(HttpServletResponse response, List<T> dataList, Class<T> clazz, String fileName, String sheetName, WriteHandler writeHandler) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), clazz).build(); // 这里注意如果同一个sheet只要创建一次 WriteSheet writeSheet = EasyExcel.writerSheet(sheetName) .registerWriteHandler(writeHandler).build(); excelWriter.write(dataList, writeSheet); excelWriter.finish(); } /** * 导出多个sheet * * @param response * @param dataList 多个sheet数据列表 * @param clazzMap 对应每个sheet列表里面的数据对应的sheet名称 * @param fileName 文件名 * @param <T> * @throws Exception */ public static <T> void exportExcels(HttpServletResponse response, List<List<?>> dataList, Map<Integer, String> clazzMap, String fileName) throws Exception { response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setHeader("Content-disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build(); int sheetNum = dataList.size(); for (int i = 0; i < sheetNum; i++) { List<?> objects = dataList.get(i); Class<?> aClass = objects.get(0).getClass(); WriteSheet writeSheet0 = EasyExcel.writerSheet(i, clazzMap.get(i)).head(aClass).build(); excelWriter.write(objects, writeSheet0); } excelWriter.finish(); } /** * 根据模板将集合对象填充表格 * * @param inputStream 模板文件输入流 * @param response 模板文件输出流 * @param list 填充对象集合 * @param fileName 文件名称 * @param sheetName 需要写入的sheet(不传：填充到第一个sheet) * @throws Exception */ public static <T> void exportTemplateExcelList(InputStream inputStream, HttpServletResponse response, List<T> list, String fileName, String sheetName) throws Exception { // 全部填充：全部加载到内存中一次填充 if (StringUtils.isBlank(sheetName)) { EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet().doFill(list); } else { EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).sheet(sheetName).doFill(list); } } /** * 根据模板将集合对象填充表格-单个sheet * * @param inputStream 模板文件输入流 * @param list 填充对象集合-元素对应模板中的.{} * @param object 填充对象-元素对应模板中的{} * @param fileName 文件名称 * @throws Exception */ public static <T> void exportTemplateSheet(InputStream inputStream, HttpServletResponse response, List<T> list, Object object, String fileName) throws Exception { FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build(); WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0).build(); // 全部填充：全部加载到内存中一次填充 excelWriter.fill(object, fillConfig, writeSheet0); excelWriter.fill(list, fillConfig, writeSheet0); // 可分多次填充，使用文件缓存（省内存） // excelWriter.fill(list1, writeSheet); // excelWriter.fill(list2, writeSheet); excelWriter.finish(); } /** * 根据模板将集合对象填充表格-多个sheet * * @param inputStream 模板文件输入 * @param response 模板文件输出流 * @param list1 填充对象集合-元素对应模板中的.{} * @param list2 * @param object1 填充对象-元素对应模板中的{} * @param object2 * @param fileName 文件名称 * @throws Exception */ public static <T> void exportTemplateSheets(InputStream inputStream, HttpServletResponse response, List<T> list1, List<T> list2, Object object1, Object object2, String fileName) throws Exception { FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build(); ExcelWriter excelWriter = EasyExcelFactory.write(getOutputStream(fileName, response)).withTemplate(inputStream).build(); WriteSheet writeSheet0 = EasyExcelFactory.writerSheet(0).build(); WriteSheet writeSheet1 = EasyExcelFactory.writerSheet(1).build(); excelWriter.fill(object1, fillConfig, writeSheet0); excelWriter.fill(list1, fillConfig, writeSheet0); excelWriter.fill(object2, fillConfig, writeSheet1); excelWriter.fill(list2, fillConfig, writeSheet1); excelWriter.finish(); } /** * 构建输出流 * * @param fileName 文件名称 * @param response 模板文件输出流 * @param response * @return * @throws Exception */ private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception { fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name()); response.setContentType("application/vnd.ms-excel"); response.setCharacterEncoding(StandardCharsets.UTF_8.name()); response.setHeader("Content-Disposition", "attachment;filename=" + fileName + FileTypeEnum.TEMPLATE_SUFFIX.getDesc()); return response.getOutputStream(); } /** * 文件格式校验 * * @param file */ public static void checkFile(MultipartFile file) { if (file == null) { throw new RuntimeException("文件不能为空！"); } String fileName = file.getOriginalFilename(); if (StringUtils.isEmpty(fileName)) { throw new RuntimeException("文件名称不能为空！"); } if (!fileName.endsWith(FileTypeEnum.TEMPLATE_SUFFIX.getDesc()) && !fileName.endsWith(FileTypeEnum.TEMPLATE_SUFFIX_XLS.getDesc())) { throw new RuntimeException("请上传.xlsx或.xls文件！"); } } } /** * @ClassName: ExcelHeadStyles * @Description: excel表头颜色定义 * @Author: * @Date: */ @Data public class ExcelHeadStyles { /** * 表头横坐标 - 行 */ private Integer rowIndex; /** * 表头纵坐标 - 列 */ private Integer columnIndex; /** * 内置颜色 */ private Short indexColor; /** * 字体颜色 */ private Short fontColor; public ExcelHeadStyles(Integer columnIndex, Short fontColor) { this.columnIndex = columnIndex; this.fontColor = fontColor; } public ExcelHeadStyles(Integer rowIndex, Integer columnIndex, Short fontColor) { this.rowIndex = rowIndex; this.columnIndex = columnIndex; this.fontColor = fontColor; } } /** * @ClassName: FileTypeEnum * @Description: 文件类型枚举 * @Author: * @Date: */ @Getter public enum FileTypeEnum { /** * 模板格式 */ TEMPLATE_SUFFIX("xlsx", ".xlsx"), TEMPLATE_SUFFIX_XLS("xls", ".xls"), TEMPLATE_SUFFIX_DOCX("docx", ".docx"); private final String code; private final String desc; FileTypeEnum(String code, String desc) { this.code = code; this.desc = desc; } /** * 通过code获取msg * * @param code 枚举值 * @return */ public static String getMsgByCode(String code) { if (code == null) { return null; } FileTypeEnum enumList = getByCode(code); if (enumList == null) { return null; } return enumList.getDesc(); } public static String getCode(FileTypeEnum enumList) { if (enumList == null) { return null; } return enumList.getCode(); } public static FileTypeEnum getByCode(String code) { for (FileTypeEnum enumList : values()) { if (enumList.getCode().equals(code)) { return enumList; } } return null; } } /** * @ClassName: ReadExcelListener * @Description: 读取excel文件数据监听器 * @Author: * @Date: */ @Slf4j public abstract class ReadExcelListener<T> extends AnalysisEventListener<T> { private static final int BATCH_COUNT = 10000; /** * 数据集 */ private final List<T> list = new ArrayList<>(); /** * 表头数据 */ private Map<Integer, String> headMap = null; private Map<String, Object> params; // 获取数据集 public List<T> getList() { return this.list; } // 获取表头数据 public Map<Integer, String> getHeadMap() { return this.headMap; } /** * 每条数据都会进入 * * @param object * @param analysisContext */ @Override public void invoke(T object, AnalysisContext analysisContext) { this.list.add(object); // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM if (list.size() >= BATCH_COUNT) { saveData(); // 存储完成清理 list list.clear(); } } /** * 数据解析完调用 * * @param analysisContext */ @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { // 这里也要保存数据，确保最后遗留的数据也存储到数据库 saveData(); log.info("所有数据解析完成！"); } /** * 读取到的表头信息 * * @param headMap * @param context */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { this.headMap = headMap; } /** * 异常时调用 * * @param exception： * @param context： * @throws Exception */ @Override public void onException(Exception exception, AnalysisContext context) throws Exception { // 数据解析异常 if (exception instanceof ExcelDataConvertException) { ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception; throw new RuntimeException("第" + excelDataConvertException.getRowIndex() + "行" + excelDataConvertException.getColumnIndex() + "列" + "数据解析异常"); } // 其他异常... } /** * 数据存储到数据库 */ public abstract boolean saveData(); } /** * @ClassName: RowMergeStrategy * @Description: 自定义行合并策略 * @Author: * @Date: */ public class RowMergeStrategy extends AbstractMergeStrategy { /** * 分组，每几行合并一次 */ private List<Integer> exportFieldGroupCountList; /** * 目标合并列index */ private Integer targetColumnIndex; // 需要开始合并单元格的首行index private Integer rowIndex; // exportDataList为待合并目标列的值 public RowMergeStrategy(List<String> exportDataList, Integer targetColumnIndex) { this.exportFieldGroupCountList = getGroupCountList(exportDataList); this.targetColumnIndex = targetColumnIndex; } @Override protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) { if (null == rowIndex) { rowIndex = cell.getRowIndex(); } // 仅从首行以及目标列的单元格开始合并，忽略其他 if (cell.getRowIndex() == rowIndex && cell.getColumnIndex() == targetColumnIndex) { mergeGroupColumn(sheet); } } private void mergeGroupColumn(Sheet sheet) { int rowCount = rowIndex; for (Integer count : exportFieldGroupCountList) { if (count == 1) { rowCount += count; continue; } // 合并单元格 CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1, targetColumnIndex, targetColumnIndex); sheet.addMergedRegionUnsafe(cellRangeAddress); rowCount += count; } } // 该方法将目标列根据值是否相同连续可合并，存储可合并的行数 private List<Integer> getGroupCountList(List<String> exportDataList) { if (CollUtil.isEmpty(exportDataList)) { return new ArrayList<>(); } List<Integer> groupCountList = new ArrayList<>(); int count = 1; for (int i = 1; i < exportDataList.size(); i++) { if (exportDataList.get(i).equals(exportDataList.get(i - 1))) { count++; } else { groupCountList.add(count); count = 1; } } groupCountList.add(count); return groupCountList; } } /** * @ClassName: ImportDataListener * @Description: 导入数据监听器 * @Author: * @Date: */ @Component public class ImportDataListener extends ReadExcelListener { @Resource private IStudentService studentService; /** * 实现数据保存逻辑-直接操作表头和行数据 * * @return */ @Override public boolean saveData() { Map<Integer, String> headMap = getHeadMap(); List dataList = getList(); if (CollUtil.isEmpty(headMap) || CollUtil.isEmpty(dataList)) { return false; } return studentService.batchSaveStudent(dataList, headMap); } } /** * @ClassName: ImportStudentListener * @Description: 导入学生excel数据监听器 * @Author: * @Date: */ @Component public class ImportUserListener extends ReadExcelListener { @Resource private IStudentService studentService; @Resource private ITeacherService teacherService; /** * 实现数据保存逻辑 * * @return */ @Override public boolean saveData() { List dataList = getList(); if (CollUtil.isEmpty(dataList)) { return false; } Object obj = dataList.get(0); if (obj instanceof Student) { return studentService.batchSaveStudent(dataList); } else if (obj instanceof Teacher) { return teacherService.batchSaveTeacher(dataList); } return true; } } /** * @ClassName: UserExcelHeadHandler * @Description: 用户相关excel处理器 * @Author: * @Date: */ public class UserExcelHeadHandler extends HorizontalCellStyleStrategy { private ExcelHeadStyles excelHeadStyle; public UserExcelHeadHandler(ExcelHeadStyles excelHeadStyle) { this.excelHeadStyle = excelHeadStyle; } //设置头样式 @Override protected void setHeadCellStyle(CellWriteHandlerContext context) { int columnNo = excelHeadStyle.getColumnIndex(); WriteCellStyle headWriteCellStyle = new WriteCellStyle(); headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex()); WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 11); if (context.getColumnIndex() <= columnNo) { headWriteFont.setColor(excelHeadStyle.getFontColor()); } else { headWriteFont.setColor(IndexedColors.BLACK.getIndex()); } headWriteCellStyle.setWriteFont(headWriteFont); if (stopProcessing(context)) { return; } WriteCellData<?> cellData = context.getFirstCellData(); WriteCellStyle.merge(headWriteCellStyle, cellData.getOrCreateStyle()); } } /** * @ClassName: ExportController * @Description: 导出前端控制器 * @Author: * @Date: */ @Slf4j @RestController public class ExportController { @Resource private IStudentService studentService; @Resource private ITeacherService teacherService; private static final String[] USER_HEAD_FIELDS = new String[]{"序号", "学号", "姓名", "性别"}; /** * 导出学生数据-一个sheet * * @param response */ @GetMapping("/student/export/sheet") public void exportSheetStudent(HttpServletResponse response) { List<Student> students = studentService.listStudent(10L); log.info("单sheet学生数据开始导出..."); long startTimeL = System.currentTimeMillis(); try { EasyExcelUtil.exportExcel(response, students, Student.class, "导出学生数据-一个sheet", "第一页"); } catch (Exception e) { log.error("单sheet学生数据导出异常！", e); } log.info("单sheet学生数据导出完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 导出学生数据-多个sheet * * @param response */ @GetMapping("/student/export/sheets") public void exportSheetsStudent(HttpServletResponse response) { log.info("多sheet学生数据开始导出..."); long startTimeL = System.currentTimeMillis(); try { List<List<?>> sheetList = new ArrayList<>(); List<Student> students1 = studentService.listStudent(10L); List<Student> students2 = studentService.listStudent(20L); sheetList.add(students1); sheetList.add(students2); Map<Integer, String> clazzMap = new HashMap<>(); clazzMap.put(0, "sheet1"); clazzMap.put(1, "sheet2"); EasyExcelUtil.exportExcels(response, sheetList, clazzMap, "导出学生数据-多个sheet"); } catch (Exception e) { log.error("多sheet学生数据导出失败！", e); } log.info("多sheet学生数据导出完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 导出学生数据-重复行合并格式 * * @param response */ @GetMapping("/student/export/mergeRow") public void exportMergeRowStudent(HttpServletResponse response) { log.info("合并格式学生数据开始导出..."); long startTimeL = System.currentTimeMillis(); try { List<Student> students = studentService.listStudent(10L); List<String> mergeDataList = students.stream().map(Student::getSex).collect(Collectors.toList()); EasyExcelUtil.exportWriteHandlerExcel(response, students, Student.class, "学生重复数据表格合并", "第一页", new RowMergeStrategy(mergeDataList, 3)); } catch (Exception e) { log.error("合并格式学生数据导出失败！", e); } log.info("合并格式学生数据导出完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 下载导入学生数据的模板【一个自定义Handler】 * * @param response */ @GetMapping("/student/downTemplate001") public void downTemplateStudent001(HttpServletResponse response) { log.info("设置单元格宽度-学生数据模板开始下载..."); long startTimeL = System.currentTimeMillis(); try { List<List<String>> headList = Arrays.stream(USER_HEAD_FIELDS).map(field -> CollUtil.newArrayList(field)). collect(Collectors.toList()); CellWidthStyleHandler columnWidthHandler = new CellWidthStyleHandler(true, 15); EasyExcelUtil.exportExcel(response, headList, Collections.EMPTY_LIST, "学生数据导入模板-设置单元格宽度", "student", columnWidthHandler); } catch (Exception e) { log.error("设置单元格宽度-学生数据模板下载失败！", e); } log.info("设置单元格宽度-学生数据模板下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 下载导入学生数据的模板【多个自定义Handler】 * * @param response */ @GetMapping("/student/downTemplate002") public void downTemplateStudent002(HttpServletResponse response) { log.info("设置单元格宽度+字体-学生数据模板开始下载..."); long startTimeL = System.currentTimeMillis(); try { List<List<String>> headList = Arrays.stream(USER_HEAD_FIELDS).map(field -> CollUtil.newArrayList(field)). collect(Collectors.toList()); // 表头第一行的指定前3列字标红 ExcelHeadStyles excelHeadStyle = new ExcelHeadStyles(0, 3, IndexedColors.RED1.getIndex()); UserExcelHeadHandler headHandler = new UserExcelHeadHandler(excelHeadStyle); CellWidthStyleHandler columnWidthHandler = new CellWidthStyleHandler(true, 15); EasyExcelUtil.exportExcel(response, headList, Collections.EMPTY_LIST, "学生数据导入模板-设置单元格宽度+字体", "student", CollUtil.newArrayList(headHandler, columnWidthHandler)); } catch (Exception e) { log.error("设置单元格宽度+字体-学生数据模板下载失败！", e); } log.info("设置单元格宽度+字体-学生数据模板下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /**************以下填充模板数据的接口测试说明：可改为get请求，InputStream可使用本地文件输入流，然后变可通过浏览器下载。******************************/ /** * 根据模板将集合对象填充表格-list * * @param template * @param response */ @PostMapping("/student/template/fill-list") public void exportFillTemplateList(@RequestParam("template") MultipartFile template, HttpServletResponse response) { log.info("根据模板填充学生数据，list，文件开始下载..."); long startTimeL = System.currentTimeMillis(); try { List students = studentService.listStudent(10L); EasyExcelUtil.exportTemplateExcelList(template.getInputStream(), response, students, "填充学生模板数据-list", "students"); } catch (Exception e) { log.error("根据模板填充学生数据，list，文件下载失败！", e); } log.info("根据模板填充学生数据，list，文件下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 根据模板将集合对象填充表格-单个sheet * * @param template * @param response */ @PostMapping("/student/template/fill-sheet") public void exportFillTemplateSheet(@RequestParam("template") MultipartFile template, HttpServletResponse response) { log.info("根据模板填充学生数据，单个sheet，文件开始下载..."); long startTimeL = System.currentTimeMillis(); try { List<Student> students = studentService.listStudent(10L); Map<String, Object> map = new HashMap<>(3); map.put("startMonth", "2023-8-10"); map.put("endMonth", "2023-8-12"); EasyExcelUtil.exportTemplateSheet(template.getInputStream(), response, students, map, "填充学生模板数据-sheet"); } catch (Exception e) { log.error("根据模板填充学生数据，单个sheet，文件下载失败！", e); } log.info("根据模板填充学生数据，单个sheet，文件下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /** * 根据模板将集合对象填充表格-多个sheet * * @param template * @param response */ @PostMapping("/student/template/fill-sheets") public void exportFillTemplateSheets(@RequestParam("template") MultipartFile template, HttpServletResponse response) { log.info("根据模板填充数据，多个sheet，文件开始下载..."); long startTimeL = System.currentTimeMillis(); try { List students = studentService.listStudent(10L); Map<String, Object> map = new HashMap<>(2); map.put("startMonth", "2023-8-10"); map.put("endMonth", "2023-8-12"); List teachers = teacherService.listTeacher(10L); Map<String, Object> map2 = new HashMap<>(1); map2.put("illustrate", "老师人员列表"); EasyExcelUtil.exportTemplateSheets(template.getInputStream(), response, students, teachers, map, map2, "填充学生模板数据-sheets"); } catch (Exception e) { log.error("根据模板填充数据，多个sheet，文件下载失败！", e); } log.info("根据模板填充数据，多个sheet，文件下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } /********转为get请求测试示例********/ /** * get请求测试 * * @param response */ @GetMapping("/student/template/test") public void test(HttpServletResponse response) throws FileNotFoundException { File file = new File("C:\\Users\\Administrator\\Desktop\\test.xlsx"); FileInputStream inputStream = new FileInputStream(file); log.info("根据模板填充学生数据，单个sheet，文件开始下载..."); long startTimeL = System.currentTimeMillis(); try { List<Student> students = studentService.listStudent(10L); Map<String, Object> map = new HashMap<>(3); map.put("startMonth", "2023-8-10"); map.put("endMonth", "2023-8-12"); EasyExcelUtil.exportTemplateSheet(inputStream, response, students, map, "填充学生模板数据-sheet"); } catch (Exception e) { log.error("根据模板填充学生数据，单个sheet，文件下载失败！", e); } log.info("根据模板填充学生数据，单个sheet，文件下载完成：{}秒", (System.currentTimeMillis() - startTimeL) / 1000); } } /** * @ClassName: ImportController * @Description: 导入前端控制器 * @Author: * @Date: */ @Slf4j @RestController public class ImportController { @Resource private ImportUserListener importUserListener; /** * 导入一个sheet学生数据 * * @param file */ @PostMapping("/student/import/sheet") public boolean importSheetStudent(@RequestParam("file") MultipartFile file) { EasyExcelUtil.checkFile(file); log.info("一个sheet学生数据开始导入..."); long startTime = System.currentTimeMillis(); List<Student> dataList = null; try { dataList = EasyExcelUtil.importExcel(file.getInputStream(), null, null, importUserListener); // 数据录入 Assert.isFalse(CollUtil.isEmpty(dataList), "未解析到导入的数据！"); } catch (Exception e) { log.error("一个sheet学生数据导入失败！", e); } log.info("一个sheet学生数据导入成功，共：{}条，消耗总时间：{}秒", dataList.size(), ((System.currentTimeMillis() - startTime) / 1000)); return true; } /** * 导入多个sheet学生数据 * * @param file */ @PostMapping("/student/import/sheets") public boolean importSheets(@RequestParam("file") MultipartFile file) { EasyExcelUtil.checkFile(file); log.info("多个sheet数据开始导入..."); long startTime = System.currentTimeMillis(); List<Student> dataList = null; try { List sheetObjList = new ArrayList(2); sheetObjList.add(Student.class); sheetObjList.add(Teacher.class); dataList = EasyExcelUtil.importExcels(file.getInputStream(), 2, sheetObjList, importUserListener); // 数据录入 Assert.isFalse(CollUtil.isEmpty(dataList), "未解析到导入的数据！"); } catch (Exception e) { log.error("多个sheet数据导入失败！", e); } log.info("多个sheet数据导入成功，共：{}条，消耗总时间：{}秒", dataList.size(), ((System.currentTimeMillis() - startTime) / 1000)); return true; } } /** * @ClassName: Student * @Description: 学生 * @Author: * @Date: **/ @Data @NoArgsConstructor @AllArgsConstructor public class Student { @ExcelProperty("序号") private Integer num; @ExcelProperty("学号") private String sno; @ExcelProperty("学生名字") private String name; @ExcelProperty("性别") private String sex; } /** * @ClassName: Teacher * @Description: 老师 * @Author: * @Date: **/ @Data public class Teacher { @ExcelProperty("编号") private String sno; @ExcelProperty("老师名字") private String name; @ExcelProperty("性别") private String sex; } /** * @ClassName: TeacherServiceImpl * @Description: 老师相关接口实现 * @Author: * @Date: */ @Slf4j @Service public class TeacherServiceImpl implements ITeacherService { @Override public List<Teacher> listTeacher(Long limit) { List<Teacher> teachers = new ArrayList<>(); for (int i = 0; i < limit; i++) { Teacher teacher = new Teacher(); teacher.setSex("女"); teacher.setName("老师A" + i); teacher.setSno(UUID.fastUUID().toString()); teachers.add(teacher); } return teachers; } @Override public boolean batchSaveTeacher(List<Teacher> teachers) { // 调用dao层接口保存数据... log.info("批量保存老师数据，共：{}条数据", teachers.size()); return true; } } /** * @ClassName: StudentServiceImpl * @Description: 学生相关接口实现 * @Author: * @Date: */ @Slf4j @Service public class StudentServiceImpl implements IStudentService { @Override public List<Student> listStudent(Long limit) { List<Student> students = new ArrayList<>(); for (int i = 0; i < limit; i++) { Student student = new Student(); student.setSex("男"); student.setNum(i); student.setName("小A" + i); student.setSno(UUID.fastUUID().toString()); students.add(student); } return students; } @Override public boolean batchSaveStudent(List<Student> students) { // 调用dao层接口保存数据... log.info("批量保存学生数据，共：{}条数据", students.size()); return true; } @Override public boolean batchSaveStudent(List<Map<Integer, String>> dataList, Map<Integer, String> headMap) { // 这里可以针对读取到的表头数据和对应的行数据，这种应用场景适用于导入的excel中数据需要我们自己解析做其他业务处理，或者表头列是动态的，我们 // 没有办法使用一个固定的实体做映射 log.info("表头列数据：{}", headMap.values()); log.info("批量保存学生数据，共：{}条数据", dataList.size()); return true; } }