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;
    }
}