excel生成单元格带下拉选项的模板 + 数据导入

1.
@AutoLog(value = "excel文件模板下载", operateType = CommonConstant.OPERATE_TYPE_2)
    @ApiOperation("下载模板")
    @PostMapping("/downloadExcel")
    public void downloadExcel(HttpServletResponse response) {
        List<ExcelFileVO> list = new ArrayList<>();
        String sheetName = "excel文件";

        // 创建下拉列表数据
        // key 就是 第几列（从0开始），value 就是这一列 你的下拉数据
        Map<Integer, List<String>> valList = new HashMap<>(16);
        valList.put(0, Arrays.asList("111", "222", "333"));

        ExcelUtil.writeExcel(response, list, sheetName + "导入模板", sheetName, ExcelFileVO.class,valList);
    }

    @AutoLog(value = "excel文件导入", operateType = CommonConstant.OPERATE_TYPE_2)
    @ApiOperation("excel文件导入")
    @PostMapping("/importExcel")
    public SjcResult<?> importExcel(@RequestParam(value = "file") MultipartFile file, @RequestParam(value = "recordYear",required = false) String recordYear) {
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }

        ExcelFileListener excelListener = new ExcelFileListener(recordYear);
        EasyExcel.read(inputStream, null, excelListener).sheet(0).headRowNumber(1).doRead();
        List<ExcelFileVO> result = excelListener.getDatas();
        for (ExcelFileVO excelFileVO : result) {
            log.info(excelFileVO.toString());
        }
        return SjcResult.OK(result);
    }


2. 
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import lombok.Data;


/**
 * @author admin
 */
@Data
@ApiModel(value = "ExcelFileVO")
public class ExcelFileVO {

    @ExcelProperty(index = 0, value = "分类ID")
    private String businessId;

    @ExcelProperty(index = 1, value = "文件名称")
    private String fileName;

    @ExcelProperty(index = 2, value = "标签")
    private String labelName;

    @ExcelProperty(index = 3, value = "文件大小(M)")
    private String fileSize;

    @ExcelProperty(index = 4, value = "上传时间")
    private String upTime;

    @ExcelProperty(index = 5, value = "上传单位")
    private String upUnit;

    @ExcelProperty(index = 6, value = "文件单位")
    private String fileUnit;

    @ExcelProperty(index = 7, value = "文件描述")
    private String fileDesc;


}
 

3.
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.FileMagic;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.schinta.modules.system.util.easyexcel.common.CustomHandler;
import org.schinta.modules.system.util.easyexcel.common.CustomSheetWriteHandler;
import org.schinta.modules.system.util.easyexcel.common.TableData;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;

import static com.alibaba.excel.support.ExcelTypeEnum.XLS;
import static com.alibaba.excel.support.ExcelTypeEnum.XLSX;

/**
 * @author wongH
 * @date 2020/5/26 11:31
 * @Version 1.0
 * @Source https://alibaba-easyexcel.github.io/index.html
 */
@Slf4j
public class ExcelUtil {

    /**
     * 设置HttpServletResponse 格式
     *
     * @param fileName 文件名
     * @param response response
     * @return
     */
    public static OutputStream getOutputStream(String fileName, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.name());
            //fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            //response.setHeader("fileName", fileName + "|" + ".xlsx");
            //response.setHeader("Access-Control-Expose-Headers", "fileName");
            return response.getOutputStream();
        } catch (IOException e) {
            log.error(e.toString());
        }
        return null;
    }


    /**
     * 根据模板导出   Excel
     *
     * @param response  输出流
     * @param data      数据集合
     * @param fileName  文件名称
     * @param sheetName sheet名称
     * @param clazz     解析模板类型
     * @param valList   列选择项
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class clazz,
                                  Map<Integer, List<String>> valList) {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        OutputStream outputStream = getOutputStream(fileName, response);
        EasyExcel.write(outputStream, clazz)
                .excelType(XLSX)
                .sheet(sheetName)
                .registerWriteHandler(new CustomHandler())
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(new CustomSheetWriteHandler(valList))
                .doWrite(data);
    }


    /**
     * 动态导出，灵活表头
     *
     * @param fileName  文件名称
     * @param sheetName sheet名称
     * @param heads     自定义表头
     * @param dataList  数据集合
     * @param response  response
     */
    public static void dynamicExport(String fileName, String sheetName, List<List<String>> heads, List<List<Object>> dataList, HttpServletResponse response) {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容居中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response))
                .head(heads)
                .excelType(XLSX)
                .sheet(sheetName)
                .registerWriteHandler(new CustomHandler())
                .registerWriteHandler(horizontalCellStyleStrategy)
                .doWrite(dataList);
    }


    /**
     * 导入 Excel
     * 同步无模型读（默认读取sheet0,从第2行开始读）
     *
     * @param file          文件流
     * @param clazz         模板类，用哪个模板文件去解析这个文件
     * @param headRowNumber 标题头行数
     *                      https://www.cnblogs.com/proper128/p/12827495.html
     */
    public static <T> List<T> importDataByMoreSheet(MultipartFile file, Class<T> clazz, int headRowNumber) {
        List<T> list = null;
        try {
            if (clazz != null) {
                list = EasyExcel.read(file.getInputStream(), clazz, null)
                        .sheet()
                        .headRowNumber(headRowNumber)
                        .doReadSync();
            } else {
                list = EasyExcel.read(file.getInputStream())
                        .sheet()
                        .doReadSync();
            }
        } catch (Exception e) {
            log.warn("excel通用导入方法错误", e);
        }
        return list;
    }

    /**
     * 无模型 导入 Excel
     * 读取全部行，包含头
     *
     * @param file 文件流
     */
    public static <T> List<T> importDataByMoreSheetTwo(MultipartFile file) {
        List<T> list = null;
        try {
            list = EasyExcel.read(file.getInputStream())
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (Exception e) {
            log.warn("excel通用导入方法错误", e);
        }
        return list;
    }

    /**
     * 判断是否是excel文件
     *
     * @param inputStream in
     */
    public static boolean isExcelFile(InputStream inputStream) {
        boolean result = false;
        try {
            FileMagic fileMagic = FileMagic.valueOf(new BufferedInputStream(inputStream));
            if (Objects.equals(fileMagic, FileMagic.OLE2) || Objects.equals(fileMagic, FileMagic.OOXML)) {
                result = true;
            }
        } catch (IOException e) {
            log.warn(e.toString());
        }
        return result;
    }

    /**
     * 根据文件后缀名类型获取对应的工作簿对象
     *
     * @param path
     * @return 包含文件数据的工作簿对象
     */
    public static Workbook getWorkbook(String path) throws IOException {
        int indexOf = path.lastIndexOf('.');
        String type = path.substring(indexOf);
        Workbook workbook = null;
        if (type.equalsIgnoreCase(XLS.getValue())) {
            workbook = new HSSFWorkbook(Files.newInputStream(Paths.get(path)));
        } else if (type.equalsIgnoreCase(XLSX.getValue())) {
            workbook = new XSSFWorkbook(path);
        }
        return workbook;
    }

    /**
     * 获取Java模板 对应的标题头
     *
     * @param myClass 模板文件
     * @return 包含文件数据的工作簿对象
     */
    public static List<TableData> getHeader(Class myClass) {
        List<TableData> list = new ArrayList<>(15);
        Field[] declaredFields = myClass.getDeclaredFields();
        for (Field f : declaredFields) {
            //1、获取属性上的指定类型的注解
            ExcelProperty annotation = f.getAnnotation(ExcelProperty.class);
            if (annotation != null && !StringUtils.isEmpty(annotation.value()[0])) {
                TableData tableData = new TableData(f.getName(), annotation.value()[0]);
                list.add(annotation.index(), tableData);
            }
        }
        return list;
    }


}

 
4.
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.extern.slf4j.Slf4j;
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 java.util.*;

/**
 * 自定义拦截器.对第一列第一行和第二行的数据新增下拉框，显示 测试1 测试2
 *
 * @author 7788
 */
@Slf4j
public class CustomSheetWriteHandler implements SheetWriteHandler {

    private Map<Integer, List<String>> optionGroupMap;

    public CustomSheetWriteHandler(Map<Integer, List<String>> mapList) {
        Map<Integer, List<String>> data = new HashMap<>(mapList);
        optionGroupMap = data;
    }

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

    }

    /**
     * 自定义拦截器.填充下拉选项
     * key 就是 第几列（从0开始），value 就是这一列 你的下拉数据
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        // 区间设置 第一列第一行和第二行的数据。由于第一行是头，所以第一、二行的数据实际上是第二三行
        Optional.ofNullable(optionGroupMap).orElse(new HashMap<>(16)).forEach((columnIndex, options) -> {
            // 区间设置，哪些行需要填充
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
            // helper设置
            DataValidationHelper helper = sheet.getDataValidationHelper();
            DataValidationConstraint constraint = helper.createExplicitListConstraint(options.toArray(new String[options.size()]));
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            // 这行代码是用于设置数据验证时是否显示错误提示框的,为true，如果用户在单元格中输入了不符合数据验证条件的数值，会弹出提示
            dataValidation.setShowErrorBox(true);
            // 这行代码是用于设置数据验证中的错误提示框的样式。当设置为DataValidation.ErrorStyle.STOP时，表示如果用户输入了不符合数据验证条件的数值，将阻止用户继续输入并弹出错误提示框，防止不合规范的数据被输入。
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            // 这行代码表示设置数据验证时是否允许空单元格。当将参数设置为false时，即不允许空单元格
            // dataValidation.setEmptyCellAllowed(false);
            // 这行代码表示设置数据验证时是否显示下拉箭头。当将参数设置为false时，会在具有数据验证的单元格中显示下拉箭头，以提示用户可以从预定义的选项中进行选择。如果设置为true，则不会显示下拉箭头。
            // dataValidation.setSuppressDropDownArrow(false);
            // 这行代码表示设置数据验证时是否显示提示框。当将参数设置为true时，如果用户选择了拥有数据验证的单元格，会弹出一个提示框，向用户提供关于该单元格数据输入的指导或说明
            // dataValidation.setShowPromptBox(true);
            // 这行代码是用于创建数据验证的错误提示框。其中，第一个参数"错误"表示错误提示框的标题，第二个参数"请从下拉列表中选择一个选项！"是具体的错误提示信息。当用户输入了不符合数据验证条件的数值时，将会弹出这个错误提示框，提醒用户输入的数据不符合规定，并给出相应的错误说明。
            dataValidation.createErrorBox("错误", "请从下拉列表中选择一个选项！");
            // 这行代码用于设置数据验证的标题和提示信息。第一个参数"下拉选择限制"表示数据验证对话框的标题，第二个参数"请在下拉列表中选择一个选项！"是具体的提示信息。当用户在单元格输入了不符合数据验证条件的数值时，会弹出一个警告框，其中包含了设置的标题和提示信息，以提醒用户必须从下拉列表中进行选择。
            // 有的版本可能没有这个设置，需要升级到对应的版本
            // dataValidation.setTitle("下拉选择限制", "请在下拉列表中选择一个选项！");
            writeSheetHolder.getSheet().addValidationData(dataValidation);
        });
    }
}


5.
import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.schinta.modules.system.vo.ExcelFileVO;

import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * @author 7788
 */
@Slf4j
public class ExcelFileListener extends AnalysisEventListener {


    /**
     * 计划年份
     */
    private String recordYear;

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

    public ExcelFileListener(String recordYear) {
        this.recordYear = recordYear;
    }

    @Override
    public void invoke(Object data, AnalysisContext analysisContext) {
        LinkedHashMap<Integer, String> value = (LinkedHashMap) data;

        ExcelFileVO excelFileVO = new ExcelFileVO();
        excelFileVO.setBusinessId(value.get(0));
        excelFileVO.setFileName(value.get(1));
        excelFileVO.setLabelName(value.get(2));
        excelFileVO.setFileSize(value.get(3));
        excelFileVO.setUpTime(value.get(4));
        excelFileVO.setUpUnit(value.get(5));
        excelFileVO.setFileUnit(value.get(6));
        excelFileVO.setFileDesc(value.get(7));

        if(StrUtil.isNotBlank(excelFileVO.getBusinessId())){
            list.add(excelFileVO);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("所有数据解析完成！");
    }

    public List<ExcelFileVO> getDatas() {
        return list;
    }
}