常用的EasyExcel表格处理（设置批注、下拉选）

本文主要是介绍常用的EasyExcel表格处理（设置批注、下拉选），希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

EasyExcel官网：点击查看

1、模板表头设置批注

此处主要根据自定义处理类CustomCellWriteHandler进行处理。

1.1 前端调用controller

 @PostMapping("/download/template")public void toDoExport(HttpServletResponse response) {// 设置模拟表头（此处为多表头，也可以传入单表头）List<List<String>> headList = new ArrayList<>();List<String> heads1 = new ArrayList<>();heads1.add("总表头");heads1.add("表头1");List<String> heads2 = new ArrayList<>();heads2.add("总表头");heads2.add("表头2");List<String> heads3 = new ArrayList<>();heads3.add("总表头");heads3.add("特殊表头");headList.add(heads1);headList.add(heads2);headList.add(heads3);// 设置模拟内容（不需要data可为null）List<List<String>> data = new ArrayList<>();List<String> data1 = new ArrayList<>(); // 第1行的内容data1.add("内容1-1");data1.add("内容1-2");data1.add("内容1-3");List<String> data2 = new ArrayList<>();// 第2行的内容data2.add("内容2-1");data2.add("内容2-2");data2.add("内容2-3");data.add(data1);data.add(data2);try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 防止中文乱码String fileName = URLEncoder.encode("导入模板名称", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");ServletOutputStream outputStream = response.getOutputStream();ExcelWriter writer = EasyExcel.write(outputStream).build();WriteSheet sheet = EasyExcel.writerSheet(0).needHead(Boolean.FALSE).sheetName("sheet页名称").build();WriteTable table1 = EasyExcel.writerTable(1).head(headList).registerWriteHandler(new AutoHeadColumnWidthStyleStrategy()) // 自动宽度.registerWriteHandler(new CustomCellWriteHandler())//CustomCellWriteHandler进行处理.needHead(Boolean.TRUE).build();writer.write(data, sheet, table1); // data 如果不需要可以为nullwriter.finish();} catch (IOException e) {e.printStackTrace();}}

1.2 批注渲染处理类（CustomCellWriteHandler）

package com.xjj.task.util;import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;import java.util.List;public class CustomCellWriteHandler implements CellWriteHandler {@Overridepublic void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {}@Overridepublic void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {}@Overridepublic void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {Workbook workbook = writeSheetHolder.getSheet().getWorkbook();CellStyle cellStyle = workbook.createCellStyle();// 居中/换行cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setWrapText(true);cellStyle.setFillBackgroundColor(IndexedColors.PALE_BLUE.getIndex());Font font = workbook.createFont();if (aBoolean) { // 判断是否为表头，true则是// 首行字体略大Integer row = cell.getRowIndex();// 行号（从0开始）if (row == 0) {font.setFontHeightInPoints((short) 15); // 首行设置字体大小为15} else {font.setFontHeightInPoints((short) 13);String stringCellValue = cell.getStringCellValue();int columnIndex = cell.getColumnIndex();String txt = "此为批注内容";if (columnIndex == 0 || stringCellValue.contains("特殊")) { // 设置第1列或包含“特殊”内容的列表头加批示Sheet sheet = writeSheetHolder.getSheet();Drawing draw = sheet.createDrawingPatriarch();Comment comment = draw.createCellComment(new XSSFClientAnchor(1, 1, 1, 1, (short) columnIndex, 1, 1, 1));// 批注内容comment.setString(new XSSFRichTextString("表头" + (columnIndex + 1) + "的批注：" + txt));cell.setCellComment(comment);}}// 字体样式font.setFontName("宋体");font.setBold(true);} else {font.setFontHeightInPoints((short) 11);// 边框cellStyle.setBorderLeft(BorderStyle.NONE);//细实线cellStyle.setBorderTop(BorderStyle.NONE);cellStyle.setBorderRight(BorderStyle.NONE);cellStyle.setBorderBottom(BorderStyle.NONE);}cellStyle.setFont(font);cell.setCellStyle(cellStyle);}}

1.3 效果
在这里插入图片描述

2、导入模板设置下拉选择

此处主要根据自定义处理类SpinnerWriteHandler进行处理。

2.1 前端调用controller

/*** 下载模板*/@PostMapping("/downLoad/template")public void export(HttpServletResponse response) {try {response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");String fileName = URLEncoder.encode("导入模板", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");//1-TrainWay 为具体的业务类，可根据实际业务替换//2-SpinnerWriteHandler为自定义的下拉选处理类EasyExcel.write(response.getOutputStream(), TrainWay.class).registerWriteHandler(new SpinnerWriteHandler()).sheet("模板名称").doWrite(new ArrayList<>());} catch (IOException e) {e.printStackTrace();}}

2.2 下拉选处理类（SpinnerWriteHandler）

package jnpf.util;import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;/*** @author Han* @Description* @date 2024/2/8*/
public class SpinnerWriteHandler implements SheetWriteHandler {@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {List<String> wayList = new ArrayList<>(); // 自定义的下拉选选项wayList.add("方式1");wayList.add("方式2");List<String> orgList = new ArrayList<>(); // 自定义的下拉选选项orgList.add("单位1");orgList.add("单位2");Map<Integer, String[]> mapDropDown = new HashMap<>(); // 需要渲染的下拉选对象if(CollUtil.isNotEmpty(wayList)){String[] wayArray =  wayList.toArray(new String[wayList.size()]); // 数组类型mapDropDown.put(1, wayArray); // 此处1为对应选择的列数（从0列开始计算），wayArray为可以下拉选择的列}if(CollUtil.isNotEmpty(orgList)){String[] orgArray =  wayList.toArray(new String[orgList.size()]); // 数组类型mapDropDown.put(5, orgArray);// 此处5为对应选择的列数（从0列开始计算），orgArray为可以下拉选择的列}Sheet sheet = writeSheetHolder.getSheet();/// 开始设置下拉框DataValidationHelper helper = sheet.getDataValidationHelper();for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {//设置起始行为1（从0行开始）、终止行为1000CellRangeAddressList addressList = new CellRangeAddressList(1, 1000, entry.getKey(), entry.getKey());//设置下拉框数据 DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());DataValidation dataValidation = helper.createValidation(constraint, addressList);//处理Excel兼容性问题if (dataValidation instanceof XSSFDataValidation) {dataValidation.setSuppressDropDownArrow(true);dataValidation.setShowErrorBox(true);} else {dataValidation.setSuppressDropDownArrow(false);}sheet.addValidationData(dataValidation);}}
}