EasyExecl导出模板,实现动态下拉列

1.需要效果.

 

 

2.引入的jar包.

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.0.2</version>
        </dependency>

3.自定义注解.

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface DropDownSetField {

     /*
     * 定义固定下拉内容
     */
    String[] source() default {};
 
    /*
     * 列标号必须和字段下标一致
     */
    int indexNum() default 0;
}

4.实体类添加 easyexecl注释 和自定义注释.

 

 

5.controller.

    @GetMapping("/downloadTemplate")
    public void downloadTemplate(HttpServletResponse response) {
        try {
            Field[] fields = MerchantsCarrier.class.getDeclaredFields();
            Map<Integer, String[]> map = new HashMap<>();
            Field field = null;
            for (int i = 0; i < fields.length; i++) {
                field = fields[i];
                DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
                if (null != dropDownSetField) {
                    String[] name = dropDownSetField.source();
                    if (name != null) {
                        ExcelUtil.insertMap(map, name, dropDownSetField, i);
                    } else {
                        ExcelUtil.insertMap(map, null, dropDownSetField, i);
                    }

                }
            }
            String fileName = URLEncoder.encode("导入招商载体模板.xlsx", "UTF-8");
            OutputStream fileOutputStream = null;
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.setContentType("application/x-download");
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.flushBuffer();
            fileOutputStream = response.getOutputStream();
            ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, MerchantsCarrier.class)
                    .registerWriteHandler(new ProductCellWriteHandler(map)).build();
            WriteSheet sheet = EasyExcel.writerSheet(0, "导入招商载体模板").build();
            excelWriter.write(null, sheet);
            excelWriter.finish();
            fileOutputStream.flush();
            fileOutputStream.close();
        } catch (Exception e) {
            log.error("下载模板失败", e);
        }
    }

6.工具类

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.HorizontalAlignment;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;

public class ExcelUtil {

    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName,
            String sheetName, Class<?> clazz) throws Exception {
        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
                contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
                .registerWriteHandler(horizontalCellStyleStrategy).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();
    }
    
    public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
        if (!Optional.ofNullable(dropDownSetField).isPresent()) {
            return null;
        }
 
        // 获取固定下拉信息
        String[] source = dropDownSetField.source();
        if (null != source && source.length > 0) {
            return source;
        }
 
        if (null != strings && strings.length > 0) {
            try {
                String[] dynamicSource = strings;
                if (null != dynamicSource && dynamicSource.length > 0) {
                    return dynamicSource;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    
 
    //插入到map中
    public static void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
        String[] sources = ExcelUtil.resove(dropDownSetField, params);
        if (null != sources && sources.length > 0) {
            map.put(i, sources);
        }
    }
}

7.监听器

import java.util.Map;

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.springframework.stereotype.Component;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;

@Component
public class ProductCellWriteHandler implements SheetWriteHandler{

    private Map<Integer,String[]> map = null;
     
    public ProductCellWriteHandler(Map<Integer,String[]> map){
        this.map = map;
    }
 
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
 
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // k 为存在下拉数据集的单元格下表， v为下拉数据集
        map.forEach((k, v) -> {
            // 下拉列表约束数据
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
            // 设置下拉单元格的首行， 末行，首列， 末列
            CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
            // 设置约束
            DataValidation validation = helper.createValidation(constraint, rangeList);
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示","请输入正确的格式的值");
            sheet.addValidationData(validation);
        });
    }

}