记一个带批注、表头样式的导入导出excel方法（基于easyexcel）

技术栈：easyexcel-2.2.10，poi-4.1.2，lombok，hutool-5.8.19；公司自用导入导出方法，可能不是那么的优雅，但胜在稳定实用。

/*** @Author 955* @Date 2023-10-10 11:52* @Description 错误批注信息对象*/
@Data
public class ErrorTypeNewVo implements Serializable {public ErrorTypeNewVo(String name, String message) {this.name = name;this.errors = message;}public ErrorTypeNewVo(String message, String name, int rownum, int cellnum) {this.name = name;this.errors = message;this.lineNum = rownum;this.cellnum = cellnum;}private String name;private String errors;private int lineNum;private int cellnum;}

一些要用到的工具类或方法

    /*** 取得cell内的值** @param cell* @return*/public static String getStrByCell(Cell cell) {if (cell == null)return null;String temp = null;switch (cell.getCellType()) {case BLANK:case STRING:temp = cell.getStringCellValue();break;case FORMULA:try {temp = cell.getStringCellValue();} catch (IllegalStateException e) {temp = cell.getNumericCellValue() + "";}break;case NUMERIC:boolean remar = false;try {remar = DateUtil.isCellDateFormatted(cell);} catch (Exception e) {// TODO: handle exception}if (remar) {double d = cell.getNumericCellValue();Date date = DateUtil.getJavaDate(d);SimpleDateFormat a = new SimpleDateFormat("yyyy-MM-dd");temp = a.format(date);} else {temp = String.valueOf(cell.getNumericCellValue());}break;default:temp = cell.getStringCellValue();break;}if (temp.trim().equals(""))temp = null;elsetemp = temp.trim();return temp;}/*** 设置单元格样式** @param workbook* @param cell* @param col* @param fg*/public void setStyle(XSSFWorkbook workbook, Cell cell, String col, XSSFColor fg) {XSSFCellStyle style = workbook.createCellStyle();style.setFillForegroundColor(fg);// style.setFillPattern(CellStyle.SOLID_FOREGROUND);cell.setCellStyle(style);cell.setCellValue(col);}/*** 表头样式** @return*/public static WriteCellStyle getHeadWriteCellStyle() {WriteCellStyle headWriteCellStyle = new WriteCellStyle();headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.index);headWriteCellStyle.setBottomBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setLeftBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setRightBorderColor(IndexedColors.BLACK.index);headWriteCellStyle.setTopBorderColor(IndexedColors.BLACK.index);return headWriteCellStyle;}/*** 内容样式** @return*/public static WriteCellStyle getContentWriteCellStyle() {WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setWrapped(true);
//        contentWriteCellStyle.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.index);
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
//        contentWriteCellStyle.setWriteFont();contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);return contentWriteCellStyle;}/*** 设置表头数据** @return*/private List<List<String>> head(List<String> titleName) {List<List<String>> list = new ArrayList<List<String>>();if (CollUtil.isNotEmpty(titleName)) {for (String s : titleName) {List<String> head = new ArrayList<String>();head.add(s);list.add(head);}}return list;}/*** @content 得到不同的时间类型* @author liuxd* @time 2011/07/24 22:00*/public static String getNow(int type) {SimpleDateFormat sdf = null;switch (type) {case 0:sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");break;case 1:sdf = new SimpleDateFormat("yyyyMMdd");break;case 2:sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");break;case 3:sdf = new SimpleDateFormat("yyyyMM");break;case 4:sdf = new SimpleDateFormat("yyyy年MM月dd日");break;case 5:sdf = new SimpleDateFormat("yyyy-MM-dd");break;case 6:sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS"); //取到毫秒break;case 7:sdf = new SimpleDateFormat("yyyyMMddHHmmss"); //取到秒break;default:sdf = new SimpleDateFormat("yyyy-M-D");break;}return sdf.format(new Date());}

导出方法体

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractHeadColumnWidthStyleStrategy;
import lombok.AllArgsConstructor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.stereotype.Service;import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;private static int index = 500000;/*** * @param titleName sheet名称* @param tittleNames 标题集合* @param vos 内容详情集合* @param errorListVo 错误批注集合* @return*/public String newCreateExcelTemple_Object(String titleName, List<String> tittleNames, List<List<Object>> vos, List<ErrorTypeNewVo> errorListVo) {String fileName = OrgTools.getNow(6) + ".xlsx";//本地文件名随意取String locaPath = path + fileName;OutputStream out = null;try {out = new FileOutputStream(locaPath);} catch (FileNotFoundException e) {e.printStackTrace();}try {// 设置数据集合List<List<String>> dataList = new ArrayList<List<String>>();if (CollUtil.isNotEmpty(vos)) {for (List<Object> objects : vos) {List<String> strs = new ArrayList<>();for (Object obj : objects) {String str = null;if (obj != null) {if (obj instanceof Date) {SimpleDateFormat ff = new SimpleDateFormat("yyyy-MM-dd");str = ff.format(obj);} else {str = obj.toString();}}strs.add(str);}dataList.add(strs);}ExcelWriterBuilder writerBuilder = EasyExcel.write();writerBuilder.file(out);writerBuilder.excelType(ExcelTypeEnum.XLSX);writerBuilder.head(head(tittleNames));ExcelWriter excelWriter = writerBuilder.build();// 写第一个sheet, sheet1 数据全是List<String> 无模型映射关系long total = dataList.size();long count = total / 500000;if (total % 500000 > 0) {count++;}WriteCellStyle headWriteCellStyle = getHeadWriteCellStyle();WriteCellStyle contentWriteCellStyle = getContentWriteCellStyle();//这个策略是 头是头的样式 内容是内容的样式HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);for (int i = 0; i < count; i++) {List<List<String>> temp = new ArrayList<List<String>>();if (i == count - 1) {temp = dataList.subList(i * index, dataList.size());} else {temp = dataList.subList(i * index, (i + 1) * index);}WriteSheet sheet1 = EasyExcel.writerSheet(i, titleName + i).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new AbstractHeadColumnWidthStyleStrategy() {@Overrideprotected Integer columnWidth(Head head, Integer columnIndex) {if (head == null) {return 17;}// 简单根据列头名称长度进行调整列的宽度// 当遍历完所有指定的head列头后// 第一行数据的第一列也会进入到这个逻辑， 此时head 为 nullreturn head.getHeadNameList().get(0).length() * 10;}}).build();excelWriter.write(temp, sheet1);}excelWriter.finish();}} catch (Exception e) {e.printStackTrace();} finally {try {if (out != null) {out.close();}} catch (IOException e) {e.printStackTrace();}}InputStream inputStream = null;try {inputStream = new FileInputStream(locaPath);XSSFWorkbook wb = new XSSFWorkbook(inputStream);XSSFSheet sheet = wb.getSheetAt(0);for (ErrorTypeNewVo errorTypevo : errorListVo) {Row row = sheet.getRow(errorTypevo.getLineNum());Cell cell = row.getCell(errorTypevo.getCellnum());if (cell == null) {cell = row.getCell(errorTypevo.getCellnum() - 1);}String name = errorTypevo.getName();String messageAll = errorTypevo.getErrors();String message = "";if (StrUtil.isNotEmpty(name)) {message += "错误类型：" + name;}if (StrUtil.isNotEmpty(messageAll)) {message += ",详细错误：" + messageAll;}String t1 = "";try {t1 = getStrByCell(cell);} catch (Exception e) {e.printStackTrace();}XSSFColor color = new XSSFColor(java.awt.Color.red);if (t1 != null) {setStyle(wb, cell, t1, color);} else {t1 = "";if (StrUtil.isNotEmpty(name)) {t1 += "错误类型：" + name;}if (StrUtil.isNotEmpty(messageAll)) {t1 += ",详细错误：" + messageAll;}setStyle(wb, cell, t1, color);}if (cell != null) {//设置批注:能解决Multiple cell comments in one cell are not allowed, cell: xx问题Comment mainComment = cell.getCellComment();if (ObjectUtil.isEmpty(mainComment)) {mainComment = sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(), cell.getColumnIndex() + 2, cell.getRowIndex() + 3));mainComment.setString(new XSSFRichTextString(message));cell.setCellComment(mainComment);sheet.autoSizeColumn(cell.getColumnIndex());}}}// 临时缓冲区ByteArrayOutputStream out2 = new ByteArrayOutputStream();// 创建临时文件wb.write(out2);byte[] bookByteAry = out2.toByteArray();inputStream = new ByteArrayInputStream(bookByteAry);try {//只需替换这里的系统文件导出方法即可fileTemplate.putObject("桶名", fileName, inputStream);} catch (Exception e) {e.printStackTrace();}} catch (FileNotFoundException e1) {e1.printStackTrace();} catch (IOException e) {e.printStackTrace();} finally {if (inputStream != null) {try {inputStream.close();} catch (IOException e) {e.printStackTrace();} finally {File file1 = new File(locaPath);if (file1.exists() && file1.isFile()) {file1.delete();}}}}return fileName;}

导入vo对象示例

@Data
@ColumnWidth(value = 30)
public class ExamExcelVo {@ExcelProperty(value = "考试题目", index = 0)private String questionStem;@ExcelProperty(value = "答题类型", converter = EasyExcelTopicTypeConverter.class, index = 1)private String questionType;@ExcelProperty(value = "正确答案", index = 2)private String answer;@ExcelProperty(value = "考题标签", index = 3)private String major;@ExcelProperty(value = "题要求", converter = EasyExcelDemandConverter.class, index = 4)private String demand;@ExcelProperty(value = "选项A", index = 5)private String optionA;@ExcelProperty(value = "选项B", index = 6)private String optionB;@ExcelProperty(value = "选项C", index = 7)private String optionC;@ExcelProperty(value = "选项D", index = 8)private String optionD;@ExcelProperty(value = "选项E", index = 9)private String optionE;/*** 判断正确选项,反射得到字段值(非必要)** @return*/public List<String> concatenateFields() {List<String> values = new ArrayList<>();try {if (StrUtil.isNotEmpty(answer)) {answer = answer.replaceAll(" ", "");// 使用 HashSet 来存储不重复的字母Set<Character> uniqueLetters = new HashSet<>();// 使用正则表达式匹配 A 到 Z 和 a 到 z 的字母Pattern pattern = Pattern.compile("[a-zA-Z]");Matcher matcher = pattern.matcher(answer);while (matcher.find()) {char letter = matcher.group().charAt(0);// 使用大写字母存储，以忽略大小写uniqueLetters.add(Character.toUpperCase(letter));}List<String> r = new ArrayList<>();for (char letter : uniqueLetters) {r.add(letter + "");}for (String fieldName : r) {Field field = this.getClass().getDeclaredField("option" + fieldName);String value = (String) field.get(this);values.add(value);}}} catch (Exception e) {}return values;}
}

字段处理示例

/*** @Author 955* @Date 2023-09-21 18:28* @Description 字段处理*/
public class EasyExcelTopicTypeConverter implements Converter<String> {@Overridepublic Class<String> supportJavaTypeKey() {return String.class;}@Overridepublic CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}@Overridepublic String convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {if (StrUtil.isNotEmpty(cellData.getStringValue())) {//处理逻辑。。。//最终返回的值return xxx;}return null;}@Overridepublic CellData convertToExcelData(String value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {if (StrUtil.isNotEmpty(value)) {String name = CourseBaseEnum.ExamType.getNameById(value);return new CellData(name);}return new CellData("");}
}

工具方法

    /*** 剔除全部字段为null数据对象** @param excelVo*/private void eliminateNullList(List<ExamExcelVo> excelVo) {if (excelVo != null) {Iterator<ExamExcelVo> iterator = excelVo.iterator();while (iterator.hasNext()) {ExamExcelVo next = iterator.next();if (isAllFieldNull(next)) {iterator.remove();}}}}/***  获取表头集合* @param t* @param objmap* @return*/public Map<String, Integer> getFieldAnnotations(Class t, Map<String, Integer> objmap) {try {Map<String, Integer> heads = new LinkedHashMap<>();Field[] fields = t.getDeclaredFields();for (Field field : fields) {ExcelProperty excelpro = (ExcelProperty) field.getAnnotation(ExcelProperty.class);heads.put(excelpro.value()[0], excelpro.index());
//                heads.add(excelpro.value()[0]);objmap.put(field.getName(), excelpro.index());}return heads;} catch (Exception e) {e.printStackTrace();}return null;}

模版导入返回带批注的错误信息方法

    public R<?> importExams(List<ExamExcelVo> excelVo) {List<List<Object>> errorList = new ArrayList<List<Object>>();eliminateNullList(excelVo);try {List<ErrorTypeNewVo> errorListVo = new ArrayList<>();Map<String, Integer> objmap = new LinkedHashMap<>();Map<String, Integer> heads = getFieldAnnotations(ExamExcelVo.class, objmap);for (int i = 0; i < excelVo.size(); i++) {List<ErrorTypeNewVo> errorTypeVos = new ArrayList<ErrorTypeNewVo>();ExamExcelVo vo = excelVo.get(i);String questionStem = vo.getQuestionStem();ExaminationQuestion question = new ExaminationQuestion();if (StrUtil.isEmpty(questionStem)) {errorTypeVos.add(new ErrorTypeNewVo("题目内容为空", "请重新填写", errorList.size() + 1, heads.get("考试题目")));} else {question.setQuestionStem(questionStem);}//.......等等等等判断if (CollUtil.isEmpty(errorTypeVos)) {//没有错误信息操作.......} else {//有错误信息操作List<Object> o = new ArrayList<>();Map<String, Object> map = BeanUtil.beanToMap(vo);for (Map.Entry<String, Integer> entry : objmap.entrySet()) {if (map.get(entry.getKey()) != null) {o.add(map.get(entry.getKey()));} else {o.add("");}}errorList.add(o);errorListVo.addAll(errorTypeVos);}}if (errorListVo.size() > 0) {//生成错误文件String XSSFSheetName = "导入错误信息" + System.currentTimeMillis();List<String> collect = heads.keySet().stream().collect(Collectors.toList());//调用上方导出方法String BUCKET_NAME = newCreateExcelTemple_Object(XSSFSheetName, collect, errorList, errorListVo);return 导入有错误信息;}} catch (Exception e) {e.printStackTrace();}return 导入成功信息;}

导入接口示例：

    @ApiOperation("批量导入试题")@PostMapping(value = "/importExams")public 返回参数 importExams(@RequestExcel List<ExamExcelVo> excelVo) {return importExams(excelVo);}