hutool导出excel支持下拉框数据超255字符

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import cn.hutool.poi.excel.StyleSet;
import com.ibmp.common.annotation.Excel;
import com.ibmp.common.constant.ExcelConsts;
import com.ibmp.common.exception.CustomException;
import com.ibmp.common.utils.file.FileTypeUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
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.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author Mr丶s
 * @date 2021/7/26 2:18 下午
 * @description
 */
@Slf4j
public class ExcelUtils {

    /**
     * 导出
     *
     * @param mergeColumn 合并多少列
     * @param mergeText   合并单元格内文字
     * @param list        需要导出的list
     * @param excelName   导出的文件名称
     * @param clazz       实体类 对应list
     * @param response
     */
    public static void export(int mergeColumn, String mergeText, List<Map<Object, Object>> maps, List<?> list, String excelName, Class clazz, HttpServletResponse response) {
        try {
            if (!list.isEmpty()) {
                if (!list.get(0).getClass().equals(clazz)) {
                    log.error("数据类型与传入的集合数据类型不一致！数据类型：{}; 集合数据类型：{}", clazz, list.get(0).getClass());
                    throw new Exception("数据类型与传入的集合数据类型不一致！");
                } else {
                    ExcelWriter writer = ExcelUtil.getBigWriter();
                    // 获取当前类字段
                    Field[] fields = clazz.getDeclaredFields();
                    // 字段名称集合
                    List<String> fieldNames = new ArrayList<>();
                    // 字段中文名称集合（获取实体中@Excel注解name的值）
                    List<String> cnNames = new ArrayList<>();
                    for (Field field : fields) {
                        if (!field.isAccessible()) {
                            // 关闭反射访问安全检查，为了提高速度
                            field.setAccessible(true);
                        }
                        String fieldName = field.getName();
                        // 排除ID和序号
                        if (!"sid".equals(fieldName) && !"serialVersionUID".equals(fieldName) && !"ordernum".equals(fieldName)) {
                            fieldNames.add(fieldName);
                        }
                        // 判断是否有@Excel
                        boolean annotationPresent = field.isAnnotationPresent(Excel.class);
                        if (annotationPresent && !"sid".equals(fieldName)) {
                            String name = field.getAnnotation(Excel.class).name();
                            cnNames.add(name);
                        }
                    }
                    String[] fs = fieldNames.toArray(new String[0]);
                    String[] ns = cnNames.toArray(new String[0]);
                    for (int i = 0; i < ns.length; i++) {
                        // 设置表头及字段名
                        writer.addHeaderAlias(fs[i], ns[i]);
                    }
                    // 自动换行
                    Workbook workbook = writer.getWorkbook();
//                    Map<Object, Object>map1 = new HashMap<>();
//                    map1.put("cellNo",14);
//                    List<String> strings = new ArrayList<>();
//                    strings.add("cc1");
//                    strings.add("cc2");
//                    strings.add("cc3");
//                    strings.add("cc4");
//                    strings.add("cc5");
//                    map1.put("cellList", strings);
//                    maps.set(6, map1);
                    //设置下拉数据
                    if (CollectionUtils.isNotEmpty(maps)) {
                        int index = 0;
                        for (Map<Object, Object> map : maps) {
                            Integer cellNo = (int) map.get("cellNo");
                            List<Object> cellList = (List<Object>) map.get("cellList");
                            index = index + 1;
                            selectListMore("sheet00" + cellNo, index, workbook, cellNo, cellNo, cellList.toArray(new String[cellList.size()]));
                        }

                    }
                    //合并单元格
                    if (mergeColumn != -1) {
                        writer.merge(mergeColumn, mergeText);
                    }

                    CellStyle headCellStyle = writer.getCellStyle();
                    //设置内容字体
                    Font font = writer.createFont();
                    //设置字体
                    font.setFontName("宋体");
                    //加粗
                    // font.setBold(true);
                    //设置标题字体大小
                    font.setFontHeightInPoints((short) 15);
                    headCellStyle.setFont(font);

                    StyleSet styleSet = new StyleSet(workbook);
                    styleSet.setWrapText();
                    writer.setStyleSet(styleSet);

                    writer.write(list, true);
                    writer.autoSizeColumnAll();
                    //冻结行数 2-前两行
                    writer.setFreezePane(2);
                    ServletOutputStream out = response.getOutputStream();
                    try {
                        for (int i = 0; i < fieldNames.size(); i++) {
                            writer.setColumnWidth(i, 23);
                        }
                        //设置头部
                        response.setContentType(ExcelConsts.CONTENT_TYPE);

                        String ecodeFileName = URLEncoder.encode(excelName, "UTF-8");
                        response.setHeader(ExcelConsts.HEADER_S, ExcelConsts.RESPONSE_HEADER + ecodeFileName + ".xlsx");
                        writer.flush(out, true);
                        writer.close();
                        IoUtil.close(out);
                    } catch (IOException e) {
                        log.error(e.getMessage());
                        e.printStackTrace();
                    }
                }
            } else {
                log.error("数据集合为空");
                throw new Exception("数据集合为空");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * excel下拉框
     *
     * @param sheetName
     * @param index     隐藏的sheet编号（例如1,2,3），多个下拉数据不能使用同一个
     * @param workbook
     * @param firstCol  区域中第一个单元格的列号 (下标0开始)
     * @param lastCol   区域中最后一个单元格的列号
     * @param dataArray 下拉内容
     */
    public static void selectListMore(String sheetName, Integer index, Workbook workbook, int firstCol, int lastCol, String[] dataArray) {
        //将下拉框数据放到新的sheet里，然后excle通过新的sheet数据加载下拉框数据
        Sheet hidden = workbook.createSheet(sheetName);
        //创建单元格对象
        Cell cell = null;
        //遍历我们上面的数组，将数据取出来放到新sheet的单元格中
        for (int i = 0, length = dataArray.length; i < length; i++) {
            //取出数组中的每个元素
            String name = dataArray[i];
            //根据i创建相应的行对象（说明我们将会把每个元素单独放一行）
            Row row = hidden.createRow(i);
            //创建每一行中的第一个单元格
            cell = row.createCell(0);
            //然后将数组中的元素赋值给这个单元格
            cell.setCellValue(name);
        }
        // 创建名称，可被其他单元格引用
        Name namedCell = workbook.createName();
        namedCell.setNameName(sheetName);
        // 设置名称引用的公式
        namedCell.setRefersToFormula(sheetName + "!$A$1:$A$" + dataArray.length);
        String range = sheetName + "!$A$1:$A$" + dataArray.length;
        //加载数据,将名称为hidden的sheet中的数据转换为List形式
        // DVConstraint constraint = DVConstraint.createFormulaListConstraint(sheetName);

        // 设置第一列的3-65534行为下拉列表
        // (3, 65534, 2, 2) ====> (起始行,结束行,起始列,结束列)
        CellRangeAddressList regions = new CellRangeAddressList(0, 1000000, firstCol, lastCol);
        DataValidationHelper dataValidationHelper = hidden.getDataValidationHelper();
        // 数据长度低于100正常录入 超过100 改为编写公式引用
        if (dataArray.length < 100 ) {
            // 将设置下拉选的位置和数据的对应关系 绑定到一起
            // DataValidation dataValidation = new HSSFDataValidation(regions, constraint);
            DataValidationConstraint constraint = dataValidationHelper.createExplicitListConstraint(dataArray);
            DataValidation dataValidation = dataValidationHelper.createValidation(constraint, regions);

            //将数据赋给下拉列表
            workbook.getSheetAt(0).addValidationData(dataValidation);
        }else {
            DataValidationConstraint dvConstraint = dataValidationHelper.createFormulaListConstraint(range);
            DataValidation dataValidation = dataValidationHelper.createValidation(dvConstraint, regions);
            workbook.getSheetAt(0).addValidationData(dataValidation);
        }
        //将第二个sheet设置为隐藏
        workbook.setSheetHidden(index, true);
    }


    /**
     * 导入
     *
     * @param file
     * @param sheet
     * @param clazz
     * @return
     */
    public static ExcelReader read(MultipartFile file, Integer sheet, Class clazz) {
        return readCommon(file, sheet, clazz);
    }

    /**
     * 导入
     *
     * @param file
     * @param clazz
     * @return
     */
    public static ExcelReader read(MultipartFile file, Class clazz) {
        return readCommon(file, 0, clazz);
    }

    /**
     * 导入
     *
     * @param file
     * @param clazz
     * @return
     */
    public static ExcelReader readCommon(MultipartFile file, Integer sheet, Class clazz) {
        try {
            InputStream inputStream = file.getInputStream();
            if (!isExcelFile(file)) {
                throw new CustomException("文件名格式不正确, 请使用后缀名为.xlsx的文件");
            }
            ExcelReader reader = ExcelUtil.getReader(inputStream, sheet);

            // 获取当前类字段
            Field[] fields = clazz.getDeclaredFields();
            // 字段名称集合
            List<String> fieldNames = new ArrayList<>();
            // 字段中文名称集合（获取实体中@Excel注解name的值）
            List<String> cnNames = new ArrayList<>();
            for (Field field : fields) {
                if (!field.isAccessible()) {
                    // 关闭反射访问安全检查，为了提高速度
                    field.setAccessible(true);
                }
                String fieldName = field.getName();
                // 排除ID和序号
                if (!"sid".equals(fieldName) && !"serialVersionUID".equals(fieldName) && !"ordernum".equals(fieldName)) {
                    fieldNames.add(fieldName);
                }
                // 判断是否有@Excel
                boolean annotationPresent = field.isAnnotationPresent(Excel.class);
                if (annotationPresent && !"sid".equals(fieldName)) {
                    String name = field.getAnnotation(Excel.class).name();
                    cnNames.add(name);
                }
            }
            String[] fs = fieldNames.toArray(new String[0]);
            String[] ns = cnNames.toArray(new String[0]);
            for (int i = 0; i < fs.length; i++) {
                // 设置表头及字段名
                reader.addHeaderAlias(ns[i], fs[i]);
            }
            return reader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取sheet数
     *
     * @param file
     * @return
     */
    public static Workbook readExcelSheet(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            if (!isExcelFile(file)) {
                throw new CustomException("文件名格式不正确, 请使用后缀名为.xlsx的文件");
            }
            ExcelReader reader = ExcelUtil.getReader(inputStream);
            return reader.getWorkbook();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 是否为Excel文件
     *
     * @param file 表单文件
     * @return 文件格式是否正确
     */
    public static boolean isExcelFile(MultipartFile file) throws IOException {
        if (file.isEmpty()) {
            throw new CustomException("文件不能为空");
        }
        String filename = file.getOriginalFilename();
        String header = FileTypeUtils.getFileHeader(file.getInputStream());
        return (filename.endsWith(ExcelConsts.EXCEL_VERSION_XLSX) || filename.endsWith(ExcelConsts.EXCEL_VERSION_XLS)) &&
                (header.contains(ExcelConsts.XLS_HEADER) || header.contains(ExcelConsts.XLSX_HEADER));
    }


    public static Workbook getXSSFWorkbook(String sheetName, String[] title, String[][] values, Workbook wb) {
        return getXSSFWorkbook(sheetName, title, null, values, wb);
    }

    public static Workbook getXSSFWorkbook(String sheetName, String[] title, int[] width, String[][] values, Workbook wb) {

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new XSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        Sheet sheet = wb.createSheet(sheetName);
        sheet.createFreezePane(0, 1, 0, 1);
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        Row row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setBold(true);
        style.setFont(font);

        //声明列对象
        Cell cell;

        //创建标题
        for (int i = 0; i < title.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
            if (width != null) {
                sheet.setColumnWidth(i, width[i]);
            }
        }
        String s;
        //创建内容
        for (int i = 0; i < values.length; i++) {
            row = sheet.createRow(i + 1);
            for (int j = 0; j < values[i].length; j++) {
                //将内容按顺序赋给对应的列对象
                s = values[i][j];
                s = StringUtils.isBlank(s) ? "" : (StringUtils.equals("null", s) ? "" : s);
                row.createCell(j).setCellValue(s);
            }
        }
        return wb;
    }

    public static void margeCell(Sheet sheet, int startRow, int colNum) {
        String content = "";
        int index = startRow;
        for (int i = startRow; i <= sheet.getLastRowNum(); i++) {
            String value = sheet.getRow(i).getCell(colNum).getStringCellValue();
            if (!StringUtils.equals(content, value)) {
                if (i - index > 1) {
                    CellRangeAddress region = new CellRangeAddress(index, i - 1, colNum, colNum);
                    sheet.addMergedRegion(region);
                }
                index = i;
                content = value;
            } else {
                if (i == sheet.getLastRowNum() && i - index > 1) {
                    CellRangeAddress region = new CellRangeAddress(index, i, colNum, colNum);
                    sheet.addMergedRegion(region);
                }
            }
        }
    }


    /**
     * 设置 response
     *
     * @param response
     * @param fileName
     * @throws UnsupportedEncodingException
     */
    public static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        // 配置文件下载
        response.setHeader("content-type", "application/octet-stream");
        response.setContentType("application/octet-stream");
        // 下载文件能正常显示中文
        String _filaName = new String((fileName + ".xlsx").getBytes("gb2312"), "iso8859-1");
        response.setHeader("Content-disposition", "attachment;filename=" + _filaName);
    }
}