阿里easyExcel -- excel下载/导出/读取 (单元格自定义下拉选择、不支持图片)

仗剑弑天下

已于 2022-04-02 14:02:10 修改

阅读量2.6k

点赞数 6

文章标签： java

于 2021-06-05 12:14:47 首次发布

本文链接：https://blog.csdn.net/jingxin_123/article/details/117588887

版权

本文介绍了如何使用阿里巴巴的EasyExcel库在Excel导入导出中设置单元格下拉列表，包括自定义注解@ExcelDropdown的使用，实体类的注解配置，以及自定义的AnnotationUtil和ExcelImportUtils工具类来动态修改下拉列表和日期校验。

摘要由CSDN通过智能技术生成

主要包

        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

使用准备

1、自定义注解@ExcelDropdown

用于设置单元格下拉列表的值 （如你更换注解名称、下面的工具使用到此注解的也得改）

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDropdown {
    String[] value() default {};  //下拉列表
    boolean isAllowOtherValue() default false;   //是否允许设置其他的值。false：只能是下拉列表的值；true：允许列表之外的值
}

2、定义实体类，给需要下拉列表的属性加上注解@ExcelDropdown

注解@ExcelProperty说明：
0、阿里easyExcel包自带
1、与实体类的字段对应（读取excel时可以按名称 (value) ，或者位置 (index) 读取，导出也一样）
2、导出到表格时显示的第一行（标题）的名称和位置

注解@ExcelDropdown说明： 用于设置下拉列表（设置下拉时不允许手填）

注解@DateTimeFormat说明： 用于设置Date类型的属性（工具累会对该注解的属性进行日期格式校验）

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.uwa.srm.common.annotation.ExcelDropdown;
import lombok.Data;
import java.io.Serializable;
import java.sql.Date;

@Data
public class User implements Serializable {
    private static final long serialVersionUID = 1L;
    //不设置下拉列表
    @ExcelProperty(value = "名字", index = 0)
    private String name;
    
    //可以后面动填设置下拉列表,isAllowOtherValue 允许设置其他值（可选可填）
    @ExcelDropdown(isAllowOtherValue = true)
    @ExcelProperty(value = "爱好", index = 1)
    private String likes;
    
    //也可以写死，后面也可以更改，isAllowOtherValue 默认只能选
    @ExcelDropdown({"男","女"})
    @ExcelProperty(value = "性别", index = 2)
    private String sex;
    
    @ExcelProperty(value = "年龄", index = 3)
    private String age;
	
	//日期校验
	@DateTimeFormat(value = "yyyy/MM/dd")
	@ExcelProperty(value = "出生日期", index = 3)
	private Date borth;
}

自定义工具类（最主要）

1、定义AnnotationUtil工具类

说明：动态修改某个对象属性上某个注解的属性值（主要用于前面说的动态设置下拉列表）。

import lombok.Data;
import lombok.ToString;
import lombok.experimental.Accessors;
import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Proxy;
import java.util.HashMap;
import java.util.Map;

/**
 * * @projectName
 * * @title AnnotationUtil
 * * @package
 * * @description注解工具类
 * * @author IT_CREAT
 * * @date2019 2019/9/14 21:16
 * * @version V1.0.0
 */
@Data
@Accessors(chain = true)
@ToString
public class AnnotationUtil<T> {

    public Class<T> clazz;

    public AnnotationUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
     *
     * @param fieldName       对象属性名称
     * @param annotationClass 注解class
     * @param attrName        注解属性名称
     * @param attrValue       注解属性值
     * @return 本工具类实例
     * @throws Exception 异常
     */
    public AnnotationUtil updateAnnoAttrValue(String fieldName, Class<? extends Annotation> annotationClass, String attrName, Object attrValue) throws Exception {
        Field[] declaredFields = this.clazz.getDeclaredFields();
        if (null != declaredFields && declaredFields.length != 0) {
            for (int i = 0; i < declaredFields.length; i++) {
                Field declaredField = declaredFields[i];
                if (fieldName.equals(declaredField.getName())) {
                    InvocationHandler invocationHandler = Proxy.getInvocationHandler(declaredField.getAnnotation(annotationClass));


                    Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
                    hField.setAccessible(true);
                    Map memberValues = (Map) hField.get(invocationHandler);
                    memberValues.put(attrName, attrValue);
                    break;
                }
            }
        }
        return this;
    }

    /**
     * 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
     *
     * @param fieldNames      字段数组
     * @param annotationClass 注解
     * @param attrNames       属性名数组-和字段依次匹配
     * @param attrValues      属性值数组-和字段依次匹配
     * @return
     * @throws Exception
     */
    public AnnotationUtil updateAnnoAttrValue(String[] fieldNames, Class<? extends Annotation> annotationClass, String[] attrNames, Object[] attrValues) throws Exception {
        if (fieldNames == null || fieldNames.length == 0 || attrNames == null || attrValues == null) {
            throw new Exception("参数错误！");
        }

        Field[] declaredFields = this.clazz.getDeclaredFields();
        if (null != declaredFields && declaredFields.length != 0) {
            HashMap<String, Field> map = new HashMap<>();
            for (int i = 0; i < declaredFields.length; i++) {
                Field declaredField = declaredFields[i];
                map.put(declaredField.getName(), declaredField);
            }

            for (int j = 0; j < fieldNames.length; j++) {
                if (!map.containsKey(fieldNames[j])) {
                    throw new Exception("字段名错误");
                }
                if(map.get(fieldNames[j]).getAnnotation(annotationClass) == null){
                    throw new Exception("该属性上无此注解");
                }
                InvocationHandler invocationHandler = Proxy.getInvocationHandler(map.get(fieldNames[j]).getAnnotation(annotationClass));
                Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
                hField.setAccessible(true);
                Map memberValues = (Map) hField.get(invocationHandler);
                memberValues.put(attrNames[j], attrValues[j]);
            }
        }
        return this;
    }

    public static void main(String[] args) throws Exception {

    }
}

2、定义ExcelImportUtils工具类

说明：用户封装excel读取、下载方法，设置下拉列表，（ 设置下来列表时需配合上面的两个注解使用 )

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.uwa.srm.common.annotation.ExcelDropdown;
import lombok.SneakyThrows;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
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;

@Slf4j
public class ExcelImportUtils<T extends Object> {

    private Class<T> clazz;
    // 对应列的下拉列表
    Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();

    //对应日期位置列表
    List<Integer> dateList = new ArrayList<>();


    public ExcelImportUtils(Class<T> clazz) {
        this.clazz = clazz;
    }

    //导入
    public List<T> excelImport(InputStream stream) throws Exception {

        List<T> list = new ArrayList<>();

        EasyExcel.read(stream, Object.class, new AnalysisEventListener<T>() {
            @SneakyThrows
            @Override
            public void invoke(T o, AnalysisContext analysisContext) {
                list.add(o);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                log.debug("导入完成！");
            }
        }).sheet().doRead();

        return list;
    }

    /**
     * 下载
     *
     * @param res       响应
     * @param data      下载的数据
     * @param fileName  文件名
     * @param sheetName 表名
     * @throws Exception
     */
    public void downLoad(HttpServletResponse res, List<T> data, String fileName, String sheetName) throws Exception {
        setMapDropDown(this.clazz);
        DropdownWriteHandler dropdownWriteHandler = new DropdownWriteHandler();
        EasyExcelFactory.write(getOutputStream(fileName, res), this.clazz).sheet(sheetName).registerWriteHandler(dropdownWriteHandler).doWrite(data);
    }

    //响应头封装
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    //自定义处理器：单元格下拉列表格式
    class DropdownWriteHandler implements SheetWriteHandler {

        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

        }

        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

            Sheet sheet = writeSheetHolder.getSheet();
            ///开始设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
            for (Map.Entry<Integer, Map<String, Object>> entry : mapDropDown.entrySet()) {
                /***起始行、终止行、起始列、终止列**/
                CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, entry.getKey(), entry.getKey());  // 检查的区域
                /***设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint((String[]) entry.getValue().get("val"));
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
                /***处理Excel兼容性问题**/
                if (dataValidation instanceof XSSFDataValidation) {
                    dataValidation.setSuppressDropDownArrow(true);  // 验证输入数据是否真确
                    dataValidation.setShowErrorBox(!(boolean) entry.getValue().get("isAllow"));  // 输入无效值时是否显示错误框
                    dataValidation.setShowPromptBox(!(boolean) entry.getValue().get("isAllow"));  // 设置无效值时 是否弹出提示框
                    dataValidation.createPromptBox("温馨提示", "只能选择列表中的值!!!");   // 设置无效值时的提示框内容
                    sheet.addValidationData(dataValidation);
                } else {
                    dataValidation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(dataValidation);
            }

            /***时间格式校验**/
            for (int i : dateList) {
                DataValidationConstraint constraint2 = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)", "Date(2100, 12, 31)", "yyyy/MM/dd");
                DataValidation dataValidation2 = helper.createValidation(constraint2, new CellRangeAddressList(1, 100000, i, i));

                //校验时间
                dataValidation2.setSuppressDropDownArrow(true);  // 验证输入数据是否真确
                dataValidation2.setShowErrorBox(true);  // 输入无效值时是否显示错误框
                dataValidation2.setShowPromptBox(true);  // 设置无效值时 是否弹出提示框
                dataValidation2.createPromptBox("温馨提示", "请输入[yyyy-MM-dd]格式日期!!!");   // 设置无效值时的提示框内容
                sheet.addValidationData(dataValidation2);
            }

            //下面定时样式的
            Row row = sheet.getRow(0);
            if (row != null) {
                Workbook workbook = writeWorkbookHolder.getWorkbook();
                row.setHeight((short) 500);
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    sheet.setColumnWidth(i, 5000);
                    Cell cell = row.getCell(i);
                    cell.setCellStyle(setStyle(workbook));
                }
                row.setHeight((short) (205 * 7));
            }
        }

        //设置单元格样式
        public CellStyle setStyle(Workbook wb) {
            Font dataFont = wb.createFont();
            dataFont.setColor(HSSFColor.RED.index);
            dataFont.setFontName("宋体");
            dataFont.setFontHeight((short) 240);
            dataFont.setBold(true);
            dataFont.setFontHeightInPoints((short) 10);
            CellStyle dataStyle = wb.createCellStyle();
            dataStyle.setFont(dataFont);
            dataStyle.setWrapText(true);
            dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            dataStyle.setAlignment(HorizontalAlignment.CENTER);
            return dataStyle;
        }

    }

    //下拉列表
    private void setMapDropDown(Class clazz) throws Exception {
        Field[] fields = clazz.getDeclaredFields();
        if (fields == null) {
            throw new Exception("属性为空");
        }
        for (Field field : fields) {
            //DateTimeFormat注解
            if (field.isAnnotationPresent(DateTimeFormat.class)) {
                int index = field.getAnnotation(ExcelProperty.class).index(); //位置
                dateList.add(index);
            }

            //判断这个字段上是否有相应的注解信息（ExcelDropdown.class）
            if (field.isAnnotationPresent(ExcelDropdown.class) && field.isAnnotationPresent(ExcelProperty.class)) {
                int index = field.getAnnotation(ExcelProperty.class).index(); //位置
                String[] values = field.getAnnotation(ExcelDropdown.class).value(); //下拉列表的value内容
                boolean allowOtherValue = field.getAnnotation(ExcelDropdown.class).isAllowOtherValue();  //下拉列表的isAllowOtherValue值

                Map<String, Object> map = new HashMap<>();
                map.put("val", values);
                map.put("isAllow", allowOtherValue);
                mapDropDown.put(index, map);
            }
        }
    }

    //设置对应属性的@ExcelDropdown注解的对应属性的对应值。
    // 如：fieldNames[0] 对应 attrNames[0] 对应 attrValues[0]， 否则出错。
    public void setExcelDropdownValue(String[] fieldNames, Object[] attrValues) throws Exception {
        AnnotationUtil<T> s = new AnnotationUtil<T>(this.clazz);
        String[] attrNames = new String[fieldNames.length]; //ExcelDropdown注解只有value属性
        for (int i = 0; i < attrNames.length; i++) {
            attrNames[i] = "value";
        }
        //更新字段上ExcelDropdown注解的value属性的值
        s.updateAnnoAttrValue(fieldNames, ExcelDropdown.class, attrNames, attrValues);
    }

使用

1、下载|导出

    //导出文件
    public void excelExport(HttpServletResponse res) throws Exception {
        String fileName = "供应商模板"; //文件名
        String sheetName = "供应商资料表"; //表名

        //设置User属性的下拉列表内容
        String[] fieldNames = {"likes", "sex"};  //字段名
        int length = fieldNames.length;
        Object[] attrValues = new Object[length];
		attrValues[0] = {"球类","棋类","美女","牌类","王者","游泳","躺尸玩手机"};  //设置第一个字段的下拉列表
		attrValues[1] = {"男","女","男人妖","女人妖","雌性","雄性","全性","无性","未知"};  //设置第二个字段的下拉列表
        //要导出的数据
        List<User> userList = new ArrayList<>();
		
        //导出-下载
        ExcelImportUtils<User> excelImportUtils = new ExcelImportUtils<>(User.class);
        //如下面两个方法颠倒位置，则下拉无效，先设置下拉，再下载。
        excelImportUtils.setExcelDropdownValue(fieldNames, attrValues); //设置对应字段的下拉列表的值 
        excelImportUtils.downLoad(res, userList, fileName, sheetName);  //下载
    }

2、上传|导入

    //导入文件
    @Override
    public void excelImport(MultipartFile file) throws Exception {
        //读取 - 按ExcelProperty注解的名字读取，excel文件的单元格标题名字不是ExcelProperty注解的名字时，则无法读取此单元格
        ExcelImportUtils<User> excelImportUtils = new ExcelImportUtils<>(User.class);
        List<User> users = excelImportUtils.excelImport(file.getInputStream()); //返回读取到的数据列表
		//对User数据进行操作......
    }