Java EXCEL 表格导入导出(带下拉选-带VLOOKUP函数封装)

Java EXCEL 表格导入导出(带下拉选-带VLOOKUP函数封装)

对于excel Java POI 使用
目前简单导出导入功能网上很多,但是对于有下拉选,样式等缺点却是最大硬伤,故此封装一个通用点的Excel 表格工具类,方便大家使用!

• 模板数据填充
• 下拉选数据规则
• 导出实体类映射

本次工具类设计工作原理

1.你需要有一个模板 把样式设置好
2.需要有三个工作簿

2.1第一个是用户使用的添加数据的, 第二个是下拉选项,第三个是列对应后台实体类属性,只不过是第二个和第三个工作薄在代码中读取后会自动为您隐藏

直接上图

• 一定要看下边俩个图哦.

表格工具类

package com.techhero.base.exam.utils.excel;

import com.techhero.base.exam.model.abs.ExcelList;
import com.techhero.base.exam.model.abs.ExcelTemplateModel;
import com.techhero.base.exam.model.abs.OptionNode;
import com.techhero.base.exam.model.abs.SelectOption;
import com.techhero.common.utils.req.CMap;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.Converter;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDataValidation;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationErrorStyle;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.STDataValidationType;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
public class ExcelUtils {
    
    /**
     * 导入数据
     * 根据传入的实体对象,将数据构建成一个List
     */
    public static <T> ExcelList<T> importData(MultipartFile multipartFile, Integer readFirstIndex, Class clazz, String... validName) {
        Workbook workBook = getWorkbook(multipartFile);
        
        /*列名工作区*/
        assert workBook != null;
        Sheet colNameSheet = workBook.getSheetAt(2);
        
        /*第一行*/
        Row row = colNameSheet.getRow(0);
        
        /*获取表格中定义的列名称(一般对应Class中的字段)*/
        List<String> excelColNames = new ArrayList<>(row.getLastCellNum());
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i);
            excelColNames.add(cell.getStringCellValue());
        }
        
        /*数据工作薄*/
        Sheet dataSheet = workBook.getSheetAt(0);
        
        int           lastRowNum   = dataSheet.getLastRowNum();
        ExcelList<T>  list         = new ExcelList<>();
        List<Integer> errorRowNums = new ArrayList<>();
        validFor:
        for (int i = readFirstIndex - 1; i < lastRowNum; i++) {
            Row dataRow = dataSheet.getRow(i);
            
            /*Map对应一行数据*/
            Map<String, String> beanMap = new HashMap<>(excelColNames.size());
            for (int n = 0; n < excelColNames.size(); n++) {
                Cell   cell      = dataRow.getCell(n);
                String cellValue = getCellValue(cell);
                
                if (n == 0 && StringUtils.isBlank(cellValue)) {
                    break validFor;
                }
                
                beanMap.put(excelColNames.get(n), cellValue);
            }
            
            /*校验数据为空合法性*/
            if (validName != null) {
                for (int k = 0; k < validName.length; k++) {
                    if (beanMap.get(validName[k]) == null && StringUtils.isNotEmpty(beanMap.get(validName[k]))) {
                        errorRowNums.add(i);
                        continue validFor;
                    }
                }
            }
            
            try {
                T o = (T) clazz.newInstance();
                
                ConvertUtils.register(new Converter() {
                    public Object convert(Class type, Object value) {
                        if (value != null) {
                            try {
                                return DateUtils.parseDate(value.toString(),
                                        "yyyy-MM-dd HH:mm:ss",
                                        "yyyy-MM-dd HH:mm",
                                        "yyyy-MM-dd",
                                        "yyyy/MM/dd HH:mm:ss",
                                        "yyyy/MM/dd HH:mm",
                                        "yyyy/MM/dd",
                                        "yyyyMMdd HH:mm:ss",
                                        "yyyyMMdd HH:mm",
                                        "yyyyMMdd",
                                        "yyyy年MM月dd日 HH:mm:ss",
                                        "yyyy年MM月dd日 HH:mm",
                                        "yyyy年MM月dd日",
                                        "yyyyMMddHHmmss");
                            } catch (ParseException e) {
                                log.info("[时间转换失败]");
                                return null;
                            }
                        }
                        return null;
                    }
                }, Date.class);
                
                /*将Map中的属性拷贝到实体类中*/
                BeanUtils.populate(o, beanMap);
                list.add(o);
            } catch (Exception e) {
                log.info("[数据导入-{}条数据有问题]", i, e);
            }
        }
        
        list.setErrorRows(errorRowNums);
        
        Row    supperDataRow = dataSheet.getRow(1);
        Cell   cell          = supperDataRow.getCell(0);
        String supperData    = cell.getStringCellValue();
        
        list.setSupperData(supperData);
        return list;
    }
    
    public static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                //short s = cell.getCellStyle().getDataFormat();
                if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
                    SimpleDateFormat sdf;
                    // 验证short值
                    if (cell.getCellStyle().getDataFormat() == 14) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd");
                    } else if (cell.getCellStyle().getDataFormat() == 21) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    } else if (cell.getCellStyle().getDataFormat() == 22) {
                        sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
                    } else {
                        sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    }
                    Date date = cell.getDateCellValue();
                    cellValue = sdf.format(date);
                } else if (cell.getCellStyle().getDataFormat() == 0) {//处理数值格式
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getRichStringCellValue().getString());
                }
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: // Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: // 公式
                try {
                    cellValue = String.valueOf(cell.getStringCellValue());
                } catch (Exception exception) {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            case BLANK: // 空值
                cellValue = null;
                break;
            case ERROR: // 故障
                cellValue = "非法";
                break;
            default:
                cellValue = "未知";
                break;
        }
        return cellValue;
    }
    
    /*获取工作簿*/
    public static Workbook getWorkbook(MultipartFile multipartFile) {
        String originalFilename = multipartFile.getOriginalFilename();
        String fileType         = originalFilename.substring(originalFilename.lastIndexOf("."));
        try {
            return getWorkbook(multipartFile.getInputStream(), fileType);
        } catch (IOException e) {
            log.error("EXCEL 导入 操作失败!", e);
            return null;
        }
    }
    
    /*获取工作簿*/
    public static Workbook getWorkbook(InputStream inStr, String fileName) {
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        try {
            if (".xls".equals(fileType)) {
                return new XSSFWorkbook(inStr);
            }
            return new XSSFWorkbook(inStr);
        } catch (IOException e) {
            return null;
        }
    }
    
    /*Excel模板下载*/
    public static void downLoadExcelTemplate(HttpServletResponse response, ExcelTemplateModel model) {
        log.info("[EXCEL TOOL BUILDER -- BUILDER PARAMS LOOK]\n{}", model);
        
        /*初始化模板行*/
        model.initTemplateInitRowSize();
        Workbook workbook = downLoadExcelTemplate(model);
        
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "utf-8");
            response.addHeader("Pragma", "no-cache");
            String encodeName = URLEncoder.encode(model.getExcelName(), StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
            
            assert workbook != null;
            workbook.write(outputStream);
        } catch (IOException e) {
            log.info("[下载Excel模板失败!]", e);
        }
    }
    
    /*获取下载Excel模板*/
    public static Workbook downLoadExcelTemplate(ExcelTemplateModel model) {
        /*创建工作薄*/
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(model.getResourceTemplateFile());
            
            /*头部设置*/
            if (model.isHasHand()) {
                handHandler(workBook, model);
            }
            
            /*下拉设定*/
            if (model.isHasSelectOptions()) {
                /*列名称处理*/
                colNamesHandler(workBook, model);
                
                /*下拉选数据填充*/
                loadSelectOptionHandler(workBook, model);
                
                /*绑定下拉选*/
                bindSelectOptionHandler(workBook, model);
                
                /*绑定下拉选对应编码函数*/
                bindSelectOptionCodeHandler(workBook, model);
                
                /*编码列隐藏处理*/
                hiddenColHandler(workBook, model);
                
            }
            
            return workBook;
        } catch (Exception exception) {
            log.error("[下载模板-{}]==>异常", model.getFilePath(), exception);
        }
        
        return null;
        
    }
    
    /*编码列隐藏处理*/
    private static void hiddenColHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        /*模板工作薄*/
        XSSFSheet templateSheet = workBook.getSheetAt(0);
        
        if (model.isHasSelectOptions()) {
            model.getSelectOptions().forEach(selectOption -> templateSheet.setColumnHidden(selectOption.getColNameCodeIndex(), selectOption.isCodeColHiddenFlag()));
        }
        
    }
    
    /*绑定下拉选对应编码函数*/
    private static void bindSelectOptionCodeHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        
        final String[] colNames = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        
        /*模板工作薄*/
        XSSFSheet templateSheet = workBook.getSheetAt(0);
        
        /*下拉选数据工作簿*/
        XSSFSheet optionDataSheet = workBook.getSheetAt(1);
        String    sheetName       = optionDataSheet.getSheetName();
        
        int                templateInitRowSize = model.getTemplateInitRowSize();
        List<SelectOption> selectOptions       = model.getSelectOptions();
        
        if (model.isHasSelectOptions()) {
            for (int i = model.getSelectOptionRowNum() - 1; i < templateInitRowSize; i++) {
                /*创建行*/
                XSSFRow row    = templateSheet.createRow(i);
                int     finalI = i + 1;
                selectOptions.forEach(selectOption -> {
                    
                    if (selectOption.isNeedBindCodeFunction()) {
                        /*创建对应的编码列*/
                        XSSFCell cell = row.createCell(selectOption.getColNameCodeIndex());
                        
                        String cellFormula = "VLOOKUP({0},{1},2,0)";
                        
                        /*当前模板工作薄名称对应列下标*/
                        int colNameIndex = selectOption.getColNameIndex();
                        
                        /*当前模板工作薄名称对应列下标字母*/
                        String colNameForTemplateSheet = colNames[colNameIndex];
                        
                        /*通过那个数据列的数据关联当前表格*/
                        String sourceDataCol = colNameForTemplateSheet + finalI;
                        
                        int    keyColIndex   = selectOption.getKeyColIndex();
                        String keyColName    = colNames[keyColIndex];
                        int    valueColIndex = selectOption.getValueColIndex();
                        String valueColName  = colNames[valueColIndex];
                        
                        String dataArea = sheetName + "!" + valueColName + ":" + keyColName;
                        
                        MessageFormat format = new MessageFormat(cellFormula);
                        cellFormula = format.format(new String[]{sourceDataCol, dataArea});
                        cell.setCellFormula(cellFormula);
                    }
                    
                });
            }
        }
        
    }
    
    /*绑定下拉选*/
    private static void bindSelectOptionHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        
        /*模板工作薄*/
        XSSFSheet templateSheet = workBook.getSheetAt(0);
        
        /*下拉选数据工作簿*/
        XSSFSheet optionDataSheet = workBook.getSheetAt(1);
        String    sheetName       = optionDataSheet.getSheetName();
        
        List<SelectOption> selectOptions = model.getSelectOptions();
        
        selectOptions.forEach(selectOption -> templateSheet.addValidationData(getValidationData(sheetName, model, selectOption)));
        
    }
    
    /*设置有效性*/
    private static DataValidation getValidationData(String sheetName, ExcelTemplateModel model, SelectOption selectOption) {
        
        int firstRowNum         = model.getSelectOptionRowNum() - 1;
        int templateInitRowSize = model.getTemplateInitRowSize();
        
        /*对应模板工作薄列下标*/
        int colNameIndex = selectOption.getColNameIndex();
        
        /*唯一属性对应模板工作薄列*/
        int maxRowNum = selectOption.getMaxRowNum();
        
        /*值对应数据薄列*/
        int valueColIndex = selectOption.getValueColIndex();
        
        final String[] colNames = {"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
        String         colName  = colNames[valueColIndex];
        
        /*有效性表达式*/
        String strFormula = sheetName + "!$" + colName + "$1:$" + colName + "$" + maxRowNum;
        
        XSSFDataValidationConstraint xssfDataValidationConstraint = new XSSFDataValidationConstraint(0x03, strFormula);
        
        /*设置数据有效性加载在哪个单元格上。四个参数分别是：起始行、终止行、起始列、终止列*/
        CellRangeAddressList regions = new CellRangeAddressList(firstRowNum, templateInitRowSize, colNameIndex, colNameIndex);
        
        CTDataValidation   newDataValidation  = CTDataValidation.Factory.newInstance();
        CellRangeAddress[] cellRangeAddresses = regions.getCellRangeAddresses();
        List<String>       ref                = new ArrayList<String>();
        for (int i = 0; i < cellRangeAddresses.length; i++) {
            CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
            ref.add(cellRangeAddress.formatAsString());
        }
        
        newDataValidation.setSqref(ref);
        
        newDataValidation.setType(STDataValidationType.LIST);
        newDataValidation.setFormula1(strFormula);
        newDataValidation.setAllowBlank(true);
        newDataValidation.setErrorStyle(STDataValidationErrorStyle.STOP);
        
        return new XSSFDataValidation(xssfDataValidationConstraint, regions, newDataValidation);
    }
    
    /*下拉选数据填充*/
    private static void loadSelectOptionHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        /*下拉选数据工作簿*/
        XSSFSheet optionDataSheet = workBook.getSheetAt(1);
        
        /*设置工作簿名称*/
        workBook.setSheetName(1, "DATA");
        /*设置工作簿隐藏*/
        workBook.setSheetHidden(1, true);
        
        /*下拉选数据*/
        List<SelectOption> selectOptions = model.getSelectOptions();
        
        /*获取最大行号*/
        AtomicInteger maxRowNumAtomicInteger = new AtomicInteger();
        selectOptions.forEach(selectOption -> {
            List<OptionNode> options = selectOption.getOptions();
            if (selectOption.getMaxRowNum() > maxRowNumAtomicInteger.get()) {
                maxRowNumAtomicInteger.set(options.size());
            }
        });
        
        List<List<OptionNode>> rowOptions = new ArrayList<>();
        for (int i = 0; i < maxRowNumAtomicInteger.get(); i++) {
            List<OptionNode> rowOption = new ArrayList<>();
            int              finalI    = i;
            selectOptions.forEach(selectOption -> {
                List<OptionNode> options = selectOption.getOptions();
                if (finalI < options.size()) {
                    rowOption.add(options.get(finalI));
                } else {
                    rowOption.add(OptionNode.builder().build());
                }
            });
            rowOptions.add(rowOption);
        }
        
        log.info("[拼装表格下拉选数据]#{}", rowOptions);
        
        for (int i = 0; i < rowOptions.size(); i++) {
            XSSFRow          row         = optionDataSheet.createRow(i);
            List<OptionNode> optionNodes = rowOptions.get(i);
            for (int k = 0, x = 0; k < optionNodes.size() * 2; k = k + 2, x++) {
                OptionNode optionNode = optionNodes.get(x);
                
                if (optionNode.isAddCol()) {
                    XSSFCell valueCell = row.createCell(k);
                    valueCell.setCellType(CellType.STRING);
                    XSSFCell keyCell = row.createCell(k + 1);
                    keyCell.setCellType(CellType.STRING);
                    
                    keyCell.setCellValue(optionNode.getKey());
                    valueCell.setCellValue(optionNode.getValue());
                }
                
            }
        }
        
    }
    
    /*列名称处理*/
    private static void colNamesHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        
        /*列名工作区*/
        XSSFSheet colNameSheet = workBook.getSheetAt(2);
        
        /*设置工作簿名称*/
        workBook.setSheetName(2, "COLNAMES");
        
        /*设置工作簿隐藏*/
        workBook.setSheetHidden(2, true);
        
        /*第一行*/
        XSSFRow row = colNameSheet.getRow(0);
        
        /*下拉选项*/
        List<SelectOption> selectOptions = model.getSelectOptions();
        
        /*数据薄角标标记*/
        int index = 0;
        
        for (SelectOption selectOption : selectOptions) {
            
            selectOption.setKeyAndValueIndex(index);
            
            String colName     = selectOption.getColName();
            String colNameCode = selectOption.getColNameCode();
            
            rowFor:
            for (int i = 0; i < row.getLastCellNum(); i++) {
                XSSFCell cell = row.getCell(i);
                if (StringUtils.isNotBlank(cell.getStringCellValue()) && cell.getStringCellValue().trim().equals(colName)) {
                    selectOption.setColNameIndex(i);
                    break rowFor;
                }
            }
            
            if (selectOption.isNeedBindCodeFunction()) {
                rowFor:
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    XSSFCell cell = row.getCell(i);
                    if (StringUtils.isNotBlank(cell.getStringCellValue()) && cell.getStringCellValue().trim().equals(colNameCode)) {
                        selectOption.setColNameCodeIndex(i);
                        break rowFor;
                    }
                }
            }
            
            index += 2;
        }
    }
    
    /*头部数据处理*/
    private static void handHandler(XSSFWorkbook workBook, ExcelTemplateModel model) {
        /*第一个工作区*/
        XSSFSheet templateSheet = workBook.getSheetAt(0);
        /*标题行*/
        XSSFRow titleRow = templateSheet.getRow(0);
        /*标题行第一列*/
        XSSFCell titleRowCell = titleRow.getCell(0);
        /*头信息值*/
        String handValue = titleRowCell.getStringCellValue();
        /*替换占位符数据*/
        MessageFormat format = new MessageFormat(handValue);
        handValue = format.format(model.getHandData());
        
        /*将数据回填头部第一列中*/
        titleRowCell.setCellValue(handValue);
        
        /*回显数据设置*/
        if (model.isHasSupperData()) {
            Row  row  = templateSheet.getRow(1);
            Cell cell = row.getCell(0);
            
            cell.setCellValue(model.getSupperData());
        }
        
    }
    
    /*测试模板生成函数*/
    public static void downLoadExcelTemplateToLocal(ExcelTemplateModel model) {
        Workbook workbook = downLoadExcelTemplate(model);
        File     file     = new File("/Users/cnnoter/Data/TempData/CsTemplate2.xlsx"); //写文件
        try (FileOutputStream out = new FileOutputStream(file)) {
            out.flush();
            assert workbook != null;
            workbook.write(out);
        } catch (Exception e) {
            log.info("测试失败", e);
        }
        
    }
 
    /*数据导出*/
    public static void exportData(HttpServletResponse response, ExcelTemplateModel model) {
        try (InputStream in = new FileInputStream(model.getFilePath())) {
            
            Workbook workBook = getWorkbook(in, model.getFilePath());
            
            /*第一个工作区*/
            assert workBook != null;
            Sheet templateSheet = workBook.getSheetAt(0);
            /*标题行*/
            Row titleRow = templateSheet.getRow(0);
            /*标题行第一列*/
            Cell titleRowCell = titleRow.getCell(0);
            /*头信息值*/
            String handValue = titleRowCell.getStringCellValue();
            /*替换占位符数据*/
            MessageFormat format = new MessageFormat(handValue);
            handValue = format.format(model.getHandData());
            
            /*将数据回填头部第一列中*/
            titleRowCell.setCellValue(handValue);
            
            /*列名工作区*/
            Sheet colNameSheet = workBook.getSheetAt(1);
            
            /*设置工作簿名称*/
            workBook.setSheetName(1, "COLNAMES");
            
            /*设置工作簿隐藏*/
            workBook.setSheetHidden(1, true);
            
            /*第一行*/
            Row row = colNameSheet.getRow(0);
            
            short lastCellNum = row.getLastCellNum();
            
            /*列名*/
            Map<String, Integer> colNames = new HashMap<>();
            for (int i = 0; i < lastCellNum; i++) {
                Cell   cell    = row.getCell(i);
                String colName = cell.getStringCellValue();
                colNames.put(colName, i);
            }
            
            Set<String> colNameSet = colNames.keySet();
            
            List exportData = model.getExportData();
            
            for (int i = 0; i < exportData.size(); i++) {
                Object obj= exportData.get(i);
                
                CMap data = CMap.builderParamByBean(obj);
                
                Row exportRow = templateSheet.createRow(i + model.getExportFirstRowNum() - 1);
                
                /*序号列*/
                Cell cell = exportRow.createCell(0);
                cell.setCellValue(i + 1);
                
                /*数据列*/
                colNameSet.forEach(colName -> {
                    Integer indexCell = colNames.get(colName);
                    Cell    dataCell     = exportRow.createCell(indexCell+1);
                    dataCell.setCellValue(data.getValue(colName));
                });
            }
    
    
            log.info("[EXCEL TOOL BUILDER -- BUILDER PARAMS LOOK]\n{}", model);
    
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                response.setContentType("application/vnd.ms-excel");
                response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
                response.addHeader("charset", "utf-8");
                response.addHeader("Pragma", "no-cache");
                String encodeName = URLEncoder.encode(model.getExcelName(), StandardCharsets.UTF_8.toString());
                response.setHeader("Content-Disposition", "attachment; filename=\"" + encodeName + "\"; filename*=utf-8''" + encodeName);
        
                workBook.write(outputStream);
                outputStream.flush();
            } catch (IOException e) {
                log.info("[下载Excel模板失败!]", e);
            }
            
        } catch (Exception e) {
            log.info("导出模板失败", e);
        }
        
    }
}

参数封装对象

package com.techhero.base.exam.model.abs;

import com.techhero.common.utils.StringUtil;
import lombok.*;

import java.io.File;
import java.io.Serializable;
import java.util.List;

/*Excel 创建模板数据封装对象*/
@Data
@ToString
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class ExcelTemplateModel implements Serializable {
    
    /*模板路径*/
    private String filePath;
    
    /*表格名称*/
    private String excelName;
    
    /*下拉选从第几行设置*/
    private int selectOptionRowNum;
    
    /*导出表格时从第几行开始添加数据*/
    private int exportFirstRowNum;
    
    /*初始化模板工作薄行数*/
    private int templateInitRowSize;
    
    /*头部数据-使用占位符代替*/
    private String[] handData;
    
    /*存放一些导入有关的数据*/
    private String supperData;
    
    /*选项*/
    private List<SelectOption> selectOptions;
    
    /*导出数据*/
    private List<?> exportData;
    
    /*获取文件路径*/
    public File getResourceTemplateFile(){
        return new File(this.filePath);
    }
    
    /*是否有头信息更换*/
    public boolean isHasHand(){
        return handData!=null&& handData.length>0;
    }
    
    /*是否有下拉选*/
    public boolean isHasSelectOptions(){
        return selectOptions!=null && !selectOptions.isEmpty();
    }
    
    /*是否需要填充回显数据*/
    public boolean isHasSupperData(){
        return StringUtil.isNotEmpty(supperData);
    }
    
    public void initTemplateInitRowSize(){
       if(this.templateInitRowSize==0){
           this.templateInitRowSize=5000;
       }
    }
    
}

package com.techhero.base.exam.model.abs;

import lombok.Data;
import lombok.ToString;
import org.apache.commons.lang3.StringUtils;

import java.io.Serializable;
import java.util.List;

@Data
@ToString
public class SelectOption implements Serializable {
    
    /*列名称(对应英文名称)*/
    private String colName;
    
    /*列名称代码(对应英文名称代码)*/
    private String colNameCode;
    
    /*列隐藏标记*/
    private boolean codeColHiddenFlag;
    
    /*对应模板工作薄名称列下标*/
    private int colNameIndex;
    
    /*对应模板工作薄名称代码列下标*/
    private int colNameCodeIndex;
    
    /*唯一属性对应模板工作薄列*/
    private int keyColIndex;
    
    /*值对应数据薄列*/
    private int valueColIndex;
    
    /*选项*/
    private List<OptionNode> options;
    
    /*私有化全参构造函数*/
    private SelectOption(String colName, List<OptionNode> options) {
        this.colName = colName;
        this.options = options;
    }
    
    /*私有化全参构造函数*/
    private SelectOption(String colName,String colNameCode, List<OptionNode> options,boolean codeColHiddenFlag) {
        this.colName = colName;
        this.options = options;
        this.colNameCode= colNameCode;
        this.codeColHiddenFlag=codeColHiddenFlag;
    }
    
    public static SelectOption builder(String colName, List<OptionNode> options) {
        return new SelectOption(colName,options);
    }
    
    public static SelectOption builder(String colName,String colNameCode, List<OptionNode> options,boolean codeColHiddenFlag) {
        return new SelectOption(colName,colNameCode,options, codeColHiddenFlag);
    }
    
    public static SelectOption builder(String colName,String colNameCode, List<OptionNode> options) {
        return new SelectOption(colName,colNameCode,options, true);
    }
    
    /*设置数据薄列对应管理*/
    public void setKeyAndValueIndex(int index){
        this.valueColIndex=index;
        this.keyColIndex=index+1;
    }
    
    /*是否需要绑定编码函数*/
    public boolean isNeedBindCodeFunction(){
        return StringUtils.isNotBlank(this.colNameCode);
    }
    
    /*获取最大行号*/
    public int getMaxRowNum(){
        if(this.options!=null){
            return this.options.size();
        }
        return 0;
    }
    
}

package com.techhero.base.exam.model.abs;

import lombok.*;
// 使用map有点不方便,还不如自己封装一个key-value 对象
@Data
@AllArgsConstructor
@ToString
@NoArgsConstructor
@Builder
public class OptionNode {
    
    /*唯一数据*/
    private String key;
    
    /*唯一值*/
    private String value;
 
    public boolean isAddCol(){
        return key!=null&&value!=null;
    }
    
}

package com.techhero.common.utils.req;
import com.alibaba.fastjson.JSONObject;
import com.fasterxml.jackson.annotation.JsonIgnore;
import com.techhero.common.model.base.ToDate;
import com.techhero.common.utils.DateUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;

import java.beans.BeanInfo;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
//其实就是一个HashMap 但是里边有很多工具方法,方便使用
@Slf4j
public class CMap extends HashMap<String, Object> implements Serializable {
    
    /*分页页码标记*/
    public static final String PAGE_NUM = "pageNum";
    
    /*分页步长*/
    public static final String PAGE_SIZE = "pageSize";
    
    /* 当前时间 */
    public static final String CURRENT_DATE = "CURRENT_DATE";
    
    /*获取分页页码*/
    public Integer getPageNum() {
        return getPageParam(PAGE_NUM);
    }
    
    /*获取分页步长*/
    public Integer getPageSize() {
        return getPageParam(PAGE_SIZE);
    }
    
    private Integer getPageParam(String pageParamType) {
        try {
            Object v = this.get(pageParamType);
            if (v == null)
                return -1;
            return Integer.valueOf(v.toString());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return -1;
    }
    
    /**
     * 获取字符串值
     */
    public String getValue(String key) {
        Object v = this.get(key);
        if (v == null) return null;
        return v.toString();
    }
    
    /*获取Integer数据*/
    public Integer getIntegerValue(String key) {
        try {
            Object v = this.get(key);
            if (v == null)
                return null;
            return Integer.valueOf(v.toString());
        } catch (Exception e) {
            log.info("cMap数据转换Integer 错误");
        }
        return null;
    }
    
    /*带默认值获取Integer数据*/
    public Integer getIntegerValue(String key, Integer defaultValue) {
        Integer value = getIntegerValue(key);
        return value == null ? defaultValue : value;
    }
    
    /*获取Short数据*/
    public Short getShortValue(String key) {
        try {
            Object v = this.get(key);
            if (v == null)
                return null;
            return Short.valueOf(v.toString());
        } catch (Exception e) {
            log.info("cMap数据转换Short 错误");
        }
        return null;
    }
    
    /*获取带默认值Short数据*/
    public Short getShortValue(String key, Short defaultValue) {
        Short value = getShortValue(key);
        return defaultValue == null ? value : defaultValue;
    }
    
    /*获取Date类型数据*/
    public Date getDateValue(String key) {
        Object value = get(key);
        if (value == null) return null;
        
        String dateStr = value.toString();
        
        long ltime;
        Date date  = null;
        if (StringUtils.isNotEmpty(dateStr)) {
            try {
                ltime = Long.parseLong(dateStr);
                date = new Date(ltime);
            } catch (Exception e) {
                try {
                    date = DateUtils.parseDate(dateStr);
                } catch (Exception ex) {
                    log.error("DATE 类型转换失败", ex);
                }
            }
        }
        return date;
    }
    
    /**
     * 获取Double数据
     */
    public Double getDoubleValue(String key) {
        try {
            Object v = this.get(key);
            if (v == null)
                return null;
            return Double.valueOf(v.toString());
        } catch (Exception e) {
            log.info("cMap数据转换Double 错误");
        }
        return null;
    }
    
    /**
     * 获取当前时间,前端使用获取本人写的获取请求参数时候回自动在该map中放入一个时间. 此时间是为了全程统一
     */
    public Date getCurrentDate() {
        try {
            Object v = this.get(CURRENT_DATE);
            if (v == null) {
                Date date = new Date();
                super.put(CURRENT_DATE, date);
                return date;
            }
            return (Date) v;
        } catch (Exception e) {
            log.info("cMap数据转换Date 错误");
        }
        return null;
    }
    
    /*换PUT方法可以连缀*/
    public CMap putValue(String key, Object value) {
        super.put(key, value);
        return this;
    }
    
    /*换PUT方法可以连缀*/
    public CMap putDateValue(String key, Object value) {
        if (value instanceof java.sql.Timestamp) {
            java.sql.Timestamp o       = (java.sql.Timestamp) value;
            String             dateStr = com.techhero.common.utils.DateUtils.formatDate(new Date(o.getTime()));
            super.put(key, dateStr);
            return this;
        } else if (value instanceof Date) {
            Date   date    = (Date) value;
            String dateStr = com.techhero.common.utils.DateUtils.formatDate(date);
            super.put(key, dateStr);
            return this;
        }
        
        log.info("日期数据保存失败");
        return this;
    }
    
    /*换PUT方法可以连缀*/
    public CMap putDateTimeValue(String key, Object value) {
        if (value instanceof java.sql.Timestamp) {
            java.sql.Timestamp o       = (java.sql.Timestamp) value;
            String             dateStr = com.techhero.common.utils.DateUtils.formatDateTime(new Date(o.getTime()));
            super.put(key, dateStr);
            return this;
        } else if (value instanceof Date) {
            Date   date    = (Date) value;
            String dateStr = com.techhero.common.utils.DateUtils.formatDateTime(date);
            super.put(key, dateStr);
            return this;
        }
        
        log.info("日期数据保存失败");
        return this;
    }
    
    /**
     * 主要针对数据MYBATIS返回数据,对类型java.sql.Timestamp进行转换为String类型
     * 如果你想要保存一个原生类型,你可以选择CMap.putValue(String key, Object value);
     * 作者: 吴 波
     * 时间: 2020-03-06 13:43
     * 笔名: 那年秋天的茄子^^
     */
    @Override
    public Object put(String key, Object value) {
        if (value instanceof java.sql.Timestamp) {
            java.sql.Timestamp o       = (java.sql.Timestamp) value;
            String             dateStr = DateUtils.formatDateTime(new Date(o.getTime()));
            super.put(key, dateStr);
            return dateStr;
        }
        return super.put(key, value);
    }
    
    /*通过一个Map构建为当前类*/
    public static CMap builder(Map<? extends String, ?> map) {
        CMap param = new CMap();
        param.putAll(map);
        return param;
    }
    
    /*通过一个Bean对象转换为Param对象*/
    public static CMap builderParamByBean(Object bean) {
        if (bean == null) return null;
        CMap param = new CMap();
        try {
            BeanInfo             beanInfo            = Introspector.getBeanInfo(bean.getClass());
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            
            for (PropertyDescriptor property : propertyDescriptors) {
                String key = property.getName();
                /*过滤class属性*/
                if (!key.equals("class")) {
                    /*得到property对应的getter方法*/
                    Method getter = property.getReadMethod();
                    Object value  = getter.invoke(bean);
                    if(value!=null&&value instanceof Date){
                        Field declaredField = bean.getClass().getDeclaredField(key);
                        log.debug("[当前为日期类型-反射获取是否有ToDate注解]");
                        if(declaredField.isAnnotationPresent(ToDate.class)){
                            ToDate annotation = declaredField.getAnnotation(ToDate.class);
                            String format = annotation.value();
                            value= DateUtils.formatDate((Date) value, format);
                        }
                    }
                    
                    param.put(key, value);
                }
            }
        } catch (Exception e) {
            log.error("转换数据错误!",e);
        }
        return param;
    }
    
    /**
     * 如果你在其中放入的是一个实例自定义对象,
     * 则你可以通过这个方法获取那个bean对象
     */
    public <T> T getBean(String key, Class<T> t) {
        Object v = this.get(key);
        if (v != null) {
            if (t.isAssignableFrom(v.getClass())) {
                try {
                    @SuppressWarnings("unchecked")
                    T newInstance = (T) v;
                    return newInstance;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }
    
    /**
     * 获取某前缀开始的数据集
     */
    public CMap getComMapByPre(String pre) {
        CMap        param  = new CMap();
        Set<String> keySet = this.keySet();
        for (String key : keySet) {
            if (key.startsWith(pre)) {
                Object v      = super.get(key);
                String newKey = key.substring(pre.length());
                param.put(newKey, v);
            }
        }
        return param;
    }
    
    /*获取布尔值数据默认为False*/
    public Boolean getBooleanValue(String key) {
        Object v = this.get(key);
        if (v == null)
            return false;
        if (v instanceof Boolean) {
            return (Boolean) v;
        }
        return false;
    }
    
    /*获取BigDecimal数据*/
    public BigDecimal getBigDecimalValue(String key) {
        Double doubleValue = this.getDoubleValue(key);
        if (doubleValue != null) return new BigDecimal(doubleValue);
        return null;
    }
    
    /**
     * 获取对象
     */
    public <T> T getMapBean(Class<T> t) {
        return transMapToBean(this, t);
    }
    
    /**
     * 利用org.apache.commons.beanutils
     */
    public static <T> T transMapToBean(Map<?, ?> map, Class<T> clazz) {
        T object = null;
        try {
            object = clazz.newInstance();
            if (map == null || map.size() < 1) {
                return object;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(clazz); // 获取类属性
            
            /*给 JavaBean 对象的属性赋值*/
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            
            StringBuffer sb = new StringBuffer(clazz.getName());
            sb.append("\n");
            
            for (PropertyDescriptor descriptor : propertyDescriptors) {
                String propertyName = descriptor.getName();
                
                sb.append(propertyName);
                sb.append("=");
                
                if (map.containsKey(propertyName)) {
                    // 下面一句可以 try 起来，这样当一个属性赋值失败的时候就不会影响其他属性赋值。
                    Object   value        = map.get(propertyName);
                    Class<?> propertyType = descriptor.getPropertyType();
                    Object[] args         = new Object[1];
                    try {
                        if (value != null) {
                            if (propertyType.equals(Integer.class)) {
                                value = Integer.valueOf(value.toString());
                            } else if (propertyType.equals(Short.class)) {
                                value = Short.valueOf(value.toString());
                            } else if (propertyType.equals(Long.class)) {
                                value = Long.valueOf(value.toString());
                            } else if (propertyType.equals(BigDecimal.class)) {
                                value = new BigDecimal(value.toString());
                            } else if (propertyType.equals(Double.class)) {
                                value = Double.valueOf(value.toString());
                            } else if (propertyType.equals(Float.class)) {
                                value = Float.valueOf(value.toString());
                            } else if (propertyType.equals(Date.class)) {
                                if (value.getClass().equals(String.class)) {
                                    try {
                                        value = DateUtils.parseDate(value.toString());
                                    } catch (Exception e) {
                                        log.info("转换失败",e);
                                    }
                                }
                            }
                        }
                        args[0] = value;
                        sb.append(value);
                        sb.append("\n");
                        Method writeMethod = descriptor.getWriteMethod();
                        if (writeMethod == null) {
                            continue;
                        }
                        writeMethod.invoke(object, args);
                    } catch (Exception e) {
                        log.info("[CMap 转换异常]\n{}",sb, e);
                    }
                }
            }
            return object;
            
        } catch (Exception e) {
            log.info("转换失败",e);
        }
        return object;
    }
    
    /*构造函数*/
    public CMap(int initialCapacity, float loadFactor) {
        super(initialCapacity, loadFactor);
    }
    
    public CMap(int initialCapacity) {
        super(initialCapacity, 1);
    }
    
    public CMap() {
    }
    
    public CMap(Map<? extends String, ?> m) {
        super(m);
    }
    
    @Override
    public String toString() {
        return JSONObject.toJSONString(this);
    }
    
}

使用方式如下:

public static void main(String[] args) {
       /*岗位*/
        List<OptionNode> gwOptions = new ArrayList<>();
        gwOptions.add(OptionNode.builder().key("1").value("考区负责人").build());
        gwOptions.add(OptionNode.builder().key("2").value("考点负责人").build());
        gwOptions.add(OptionNode.builder().key("3").value("考场负责人").build());
        gwOptions.add(OptionNode.builder().key("4").value("钥匙保管员").build());
        gwOptions.add(OptionNode.builder().key("5").value("武警").build());
        
        /*没有编码列*/
         SelectOption gw = SelectOption.builder("GW", gwOptions);
        
       /* 性别类型*/
        List<OptionNode> xbOptions = new ArrayList<>();
        xbOptions.add(OptionNode.builder().key("A").value("男").build());
        xbOptions.add(OptionNode.builder().key("B").value("女").build());
        xbOptions.add(OptionNode.builder().key("C").value("未知").build());
        
        /*有编码列-且隐藏编码列*/
        SelectOption xb = SelectOption.builder("XB", "XBBM", xbOptions, true);
        
        /*证件类型*/
        List<OptionNode> typeOptions = new ArrayList<>();
        typeOptions.add(OptionNode.builder().key("A1").value("身份证").build());
        typeOptions.add(OptionNode.builder().key("B2").value("军官证").build());
        typeOptions.add(OptionNode.builder().key("C3").value("驾驶证").build());
        
        /*有编码列-且不隐藏编码列*/
        SelectOption type = SelectOption.builder("ZJLX", "ZJLXBM", typeOptions, false);
        
        List<SelectOption> selectOptions = new ArrayList<>();
        selectOptions.add(xb);
        selectOptions.add(type);
        selectOptions.add(gw);
        
        ExcelTemplateModel build = ExcelTemplateModel.builder()
                /*文件路劲*/
                .filePath("/Users/cnnoter/Data/TempData/CsTempldate.xlsx")
                /*默认初始化行数*/
                .templateInitRowSize(5000)
                /*头部信息渲染*/
                .handData(new String[]{"湖北省考试院", "KS1X03"})
                /*下拉选项*/
                .selectOptions(selectOptions)
                /*其实行未知*/
                .selectOptionRowNum(3)
                .build();
        
        downLoadExcelTemplateToLocal(build);

下边是service中直接调用的一个函数(参考)

/*考点工作人员模板下载*/
    private void examPointWorkPersonTemplateDownLoad(String code,String examPlanCode, HttpServletResponse response) {
        
        /*考试管理机构*/
        ZhJgKsgljgxxb examMngOrg = zhJgKsgljgxxbMapper.selectOne(ZhJgKsgljgxxb.builder().ksgljgid(code).build());
        
        /*头部替换[考试管理机构名称/管理机构标识]*/
        String[] handData = new String[]{examMngOrg.getKsgljgmc(), examMngOrg.getKsgljgid()};
        
        /*下拉选数据*/
        List<SelectOption> selectOptions = new ArrayList<>();
        
        
        /*性别*/
        List<Dict>       xbDicts       = dictMapper.select(Dict.builder().sjlx("zd_xbdm").build());
        List<OptionNode> xbOptionNodes = xbDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("xb", "xbm", xbOptionNodes));
        
        /*获取岗位*/
        List<Job>        jobs           = jobMapper.select(Job.builder().gwflDm(JobTypeEnum.KS_JOB.getOrgCode()).build());
        List<OptionNode> jobOptionNodes = jobs.stream().map(job -> OptionNode.builder().key(job.getGwDm()).value(job.getGwmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("gwzz", "gwzzm", jobOptionNodes));
        
        /*是否在编*/
        List<OptionNode> isOpenList = new ArrayList<>();
        isOpenList.add(OptionNode.builder().key("0").value("否").build());
        isOpenList.add(OptionNode.builder().key("1").value("是").build());
        selectOptions.add(SelectOption.builder("sfzb", isOpenList));
        
        /*学历*/
        List<Dict>       xlDicts       = dictMapper.select(Dict.builder().sjlx("xl").build());
        List<OptionNode> xlOptionNodes = xlDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("xl", "xlbm", xlOptionNodes));
        
        /*职称*/
        List<Dict>       zcDicts       = dictMapper.select(Dict.builder().sjlx("zc").build());
        List<OptionNode> zcOptionNodes = zcDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("zc", "zcbm", zcOptionNodes));
        
        /*考点*/
        List<DcKdxxb>    examPoints           = dcKdxxbMapper.select(DcKdxxb.builder().kdxzqhm(code).ksjhbm(examPlanCode).build());
        List<OptionNode> examPointOptionNodes = examPoints.stream().filter(dcKdxxb -> StringUtil.isNotBlank(dcKdxxb.getBzhkdid())&&StringUtil.isNotBlank(dcKdxxb.getKdmc())).map(dcKdxxb -> OptionNode.builder().key(dcKdxxb.getId()).value(dcKdxxb.getKdmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("bzhkdmc", "bzhkdid", examPointOptionNodes));
        
        /*培训考核情况*/
        List<Dict>       pxkhqkDicts       = dictMapper.select(Dict.builder().sjlx("pxkhqk").build());
        List<OptionNode> pxkhqkOptionNodes = pxkhqkDicts.stream().map(dict -> OptionNode.builder().key(dict.getDm()).value(dict.getDmmc()).build()).collect(Collectors.toList());
        selectOptions.add(SelectOption.builder("pxkhqk", "pxkhqkbm", pxkhqkOptionNodes));
        
        String supperData = handData[1] + ",1,身份证," + code;
        
        ExcelUtils.downLoadExcelTemplate(response,
                ExcelTemplateModel.builder()
                        .handData(handData)
                        .supperData(supperData)
                        .selectOptionRowNum(4)
                        .templateInitRowSize(5000)
                        .selectOptions(selectOptions)
                        .filePath(templatePath.getExamPointWorkPersonPath())
                        .excelName(examMngOrg.getKsgljgmc() + "考点工作人员.xlsx")
                        .build());
        
    }