EasyExcel 工具类

最近公司让修改一个excel的导出工具类，由原来的POI修改成阿里的easyexcel导出

最近也查了许多资料，也看了好多博客，最终自己整合了一个工具类的导出，话不多说，上图看代码，有写的不多的地方和需要优化的地方还请各位大佬指教！！！

import org.apache.commons.lang3.time.DateFormatUtils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class DateUtils extends org.apache.commons.lang3.time.DateUtils{
    private static String[] parsePatterns = {
            "yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd HH:mm", "yyyy-MM",
            "yyyy/MM/dd", "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd HH:mm", "yyyy/MM",
            "yyyy.MM.dd", "yyyy.MM.dd HH:mm:ss", "yyyy.MM.dd HH:mm", "yyyy.MM"};

    /**
     * 得到当前日期字符串 格式（yyyy-MM-dd）
     */
    public static String getDate() {
        return getDate("yyyy-MM-dd");
    }

    /**
     * 得到当前日期字符串 格式（yyyy-MM-dd） pattern可以为："yyyy-MM-dd" "HH:mm:ss" "E"
     */
    public static String getDate(String pattern) {
        return DateFormatUtils.format(new Date(), pattern);
    }

    /**
     * 得到日期字符串 默认格式（yyyy-MM-dd） pattern可以为："yyyy-MM-dd" "HH:mm:ss" "E"
     */
    public static String formatDate(Date date, Object... pattern) {
        if (date == null) {
            return null;
        }
        String formatDate = null;
        if (pattern != null && pattern.length > 0) {
            formatDate = DateFormatUtils.format(date, pattern[0].toString());
        } else {
            formatDate = DateFormatUtils.format(date, "yyyy-MM-dd");
        }
        return formatDate;
    }

    /**
     * 得到日期时间字符串，转换格式（yyyy-MM-dd HH:mm:ss）
     */
    public static String formatDateTime(Date date) {
        return formatDate(date, "yyyy-MM-dd HH:mm:ss");
    }

    /**
     * 得到当前时间字符串 格式（HH:mm:ss）
     */
    public static String getTime() {
        return formatDate(new Date(), "HH:mm:ss");
    }

    /**
     * 得到当前日期和时间字符串 格式（yyyy-MM-dd HH:mm:ss）
     */
    public static String getDateTime() {
        return formatDate(new Date(), "yyyy-MM-dd HH:mm:ss");
    }

    /**
     * 得到当前年份字符串 格式（yyyy）
     */
    public static String getYear() {
        return formatDate(new Date(), "yyyy");
    }

    /**
     * 得到当前月份字符串 格式（MM）
     */
    public static String getMonth() {
        return formatDate(new Date(), "MM");
    }

    /**
     * 得到当天字符串 格式（dd）
     */
    public static String getDay() {
        return formatDate(new Date(), "dd");
    }

    /**
     * 得到当前星期字符串 格式（E）星期几
     */
    public static String getWeek() {
        return formatDate(new Date(), "E");
    }

    /**
     * 日期型字符串转化为日期 格式
     * { "yyyy-MM-dd", "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", "yyyy.MM.dd HH:mm:ss", "yyyy.MM.dd HH:mm" }
     */
    public static Date parseDate(Object str) {
        if (str == null) {
            return null;
        }
        try {
            return parseDate(str.toString(), parsePatterns);
        } catch (ParseException e) {
            return null;
        }
    }

    /**
     * 获取过去的天数
     *
     * @param date
     * @return
     */
    public static long pastDays(Date date) {
        long t = System.currentTimeMillis() - date.getTime();
        return t / (24 * 60 * 60 * 1000);
    }

    /**
     * 获取过去的小时
     *
     * @param date
     * @return
     */
    public static long pastHour(Date date) {
        long t = System.currentTimeMillis() - date.getTime();
        return t / (60 * 60 * 1000);
    }

    /**
     * 获取过去的分钟
     *
     * @param date
     * @return
     */
    public static long pastMinutes(Date date) {
        long t = System.currentTimeMillis() - date.getTime();
        return t / (60 * 1000);
    }

    /**
     * 转换为时间（天,时:分:秒.毫秒）
     *
     * @param timeMillis
     * @return
     */
    public static String formatDateTime(long timeMillis) {
        long day = timeMillis / (24 * 60 * 60 * 1000);
        long hour = (timeMillis / (60 * 60 * 1000) - day * 24);
        long min = ((timeMillis / (60 * 1000)) - day * 24 * 60 - hour * 60);
        long s = (timeMillis / 1000 - day * 24 * 60 * 60 - hour * 60 * 60 - min * 60);
        long sss = (timeMillis - day * 24 * 60 * 60 * 1000 - hour * 60 * 60 * 1000 - min * 60 * 1000 - s * 1000);
        return (day > 0 ? day + "," : "") + hour + ":" + min + ":" + s + "." + sss;
    }

    /**
     * 获取两个日期之间的天数
     *
     * @param before
     * @param after
     * @return
     */
    public static double getDistanceOfTwoDate(Date before, Date after) {
        long beforeTime = before.getTime();
        long afterTime = after.getTime();
        return (afterTime - beforeTime) / (1000 * 60 * 60 * 24);
    }

    public static String getFirstDayOfMonth() {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        //获取当前月第一天：
        Calendar c = Calendar.getInstance();
        c.add(Calendar.MONTH, 0);
        c.set(Calendar.DAY_OF_MONTH, 1);//设置为1号,当前日期既为本月第一天
        String first = format.format(c.getTime());
        return first;
    }

    /**
     * 获取下一个月
     * @return
     */
    public static String getNextfMonth() {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        Calendar c = Calendar.getInstance();
        c.add(Calendar.MONTH, 1);
        String first = format.format(c.getTime());
        return first;
    }

    /**
     * 获取前/后N个月
     * @return
     */
    public static String getForwardMonth(int n) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM");
        Calendar c = Calendar.getInstance();
        c.add(Calendar.MONTH, n);
        String month = format.format(c.getTime());
        return month;
    }

 

/**
 * easyExcel 导入导出工具类
 *
 * @author edward.xiang
 * 2021/09/26
 */
public class EasyExcelUtil<T> {

    private static final Logger logger = LoggerFactory.getLogger(EasyExcelUtil.class);

    public final static String EXCEL_EXPORT_SUFFIX = ".xlsx";

    private Class<T> clazz;

    public EasyExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /*
     * 测试 : main方法直接运行,导出的excel在D盘可以查看。
     * 100万条数据测试  导出到excel耗时12秒
     */
    public static void main(String[] args) {
        EasyExcelUtil util = new EasyExcelUtil(ExportTest.class);
        List<ExportTest> dataList = new ArrayList<>();
        long currentTimeMillis = System.currentTimeMillis();
        System.out.println();
        for (int i = 0; i < 20; i++) {
            dataList.add(new ExportTest("1111", null, 1, 11));
        }
        //util.exportToExcel(null, null, "插入耗时测试", dataList, "sheet", "我是表头，我很长。。。。。000000000" , null);
        util.exportToExcel(null, null, "插入耗时测试", dataList, "sheet", "我 问我飒飒大苏打撒旦撒旦撒阿" +"\\"+
                "三大苏打实打实阿斯顿撒旦", null);
        long timeMillis = System.currentTimeMillis();
        System.out.println("插入耗时" + (timeMillis - currentTimeMillis) / 1000 + "秒");
    }

    /**
     * 将数据导出成 excel表格  单个sheet
     *
     * @param request   request
     * @param response  response
     * @param fileName  导出excel的文件名字
     * @param dataList  需要导出的数据
     * @param sheetName sheetName 默认是sheet
     * @param title     表头数据
     * @param headers   暂时未设定(传null)
     * @return
     */
    public Boolean exportToExcel(HttpServletRequest request, HttpServletResponse response, String fileName, List<T> dataList, String sheetName, String title, List<String> headers) {
        List<List<Object>> lists = new ArrayList<List<Object>>();
        for (Object obj : dataList) {
            List<Object> list = new ArrayList<>();
            //列数,表头数
            Field[] declaredFields = obj.getClass().getDeclaredFields();
            if (declaredFields != null && declaredFields.length > 0) {
                for (Field field : declaredFields) {
                    Export export = field.getAnnotation(Export.class);
                    if (export != null && export.exportFiled()) {
                        field.setAccessible(true);
                        try {
                            String str = String.valueOf(field.get(obj));
                            if (str.equals("null")) {
                                str = "";
                            }
                            list.add(str);
                        } catch (IllegalAccessException e) {
                            logger.error("e:{}", e.getMessage());
                        }
                    }
                }
            }
            lists.add(list);
        }
        try {
            ExcelWriter excelWriter = null;
            if (response == null) {
                //輸出到本地文件
                String outPath = "d:/" + fileName + ".xlsx";
                File file = new File(outPath);
                if (file.exists()) {
                    // 文件存在
                    file.delete();
                }
                excelWriter = EasyExcelFactory.getWriter(new FileOutputStream(outPath));
            } else {
                //輸出到response 前端界面
                setHeader(fileName, request, response);
                excelWriter = EasyExcelFactory.getWriter(response.getOutputStream());
            }
            //导出文件
            List<Map<String, Object>> rowHeightColWidthList = new ArrayList<>();
            //设置行高
            if (StringUtils.isEmpty(title)) {
                // 如果没有表头 , 设置第一行额高度
                rowHeightColWidthList.add(ExcelHeightColWidthStyleStrategy.createRowHeightMap(sheetName, 0, 20f));
            } else {
                // 表头，设置表头的高度
                rowHeightColWidthList.add(ExcelHeightColWidthStyleStrategy.createRowHeightMap(sheetName, 0, 60f));
            }
            WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName)
                    .registerWriteHandler(getStyleStrategy())
                    .registerWriteHandler(new ExcelHeightColWidthStyleStrategy(rowHeightColWidthList))
                    .registerWriteHandler(new ExcelWidthStyleStrategy())
                    .build();
            // 创建一个表格
            WriteTable table = new WriteTable();
            List<List<String>> headList = setHeadList(title, clazz);
            table.setHead(headList);
            excelWriter.write(lists, writeSheet, table);
            excelWriter.finish();
            logger.info("导出excel成功");
            // System.out.println("导出成功!");
        } catch (IOException e) {
            logger.error("e:{}", e.getMessage());
            return Boolean.FALSE;
        }
        return Boolean.TRUE;
    }

    //设置表头数据
    public static List<List<String>> setHeadList(String title, Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();
        List<Field> fieldList = new ArrayList<>();
        if (fields != null && fields.length > 0) {
            for (Field field : fields) {
                Export export = field.getAnnotation(Export.class);
                if (export != null) {
                    boolean exportFiled = export.exportFiled();
                    String name = field.getName();
                    String filedName = export.filedName();
                    if (!StringUtils.hasText(filedName)) {
                        throw new RuntimeException("字段:" + name + "无字段名");
                    }
                    fieldList.add(field);
                }
            }
        }
        //列数据保存
        List<List<String>> headList = Collections.emptyList();
        if (!CollectionUtils.isEmpty(fieldList)) {
            headList = new ArrayList<>();
            for (Field field : fieldList) {
                List<String> headTitle = new ArrayList<String>();
                if (!StringUtils.isEmpty(title)) {
                    //headTitle.add(title);
                    headTitle.add(title);
                }
                headTitle.add(field.getAnnotation(Export.class).filedName());
                headList.add(headTitle);
            }
        }
        return headList;
    }


    public static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        headWriteFont.setFontName("Frozen");
        //字体白色
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        //自动换行
        headWriteCellStyle.setWrapped(false);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
//        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 12);
        // 字体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    private static void setHeader(String fileName, HttpServletRequest request,
                                  HttpServletResponse response) {
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/octet-stream;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;"
                + getDownLoadFileName(fileName, request));
    }

    private static String getDownLoadFileName(String fileName,
                                              HttpServletRequest request) {
        String userAgent = request.getHeader("User-Agent");
        String name = null;
        try {
            name = URLEncoder.encode(fileName, "UTF-8");
        } catch (UnsupportedEncodingException e) {
            name = fileName;
        }
        if (StringUtils.hasText(userAgent)) {
            userAgent = userAgent.toLowerCase();
            name = name + DateUtils.getDate() + EXCEL_EXPORT_SUFFIX;
            if (userAgent.indexOf("opera") != -1) {
                name = "filename*=UTF-8''" + name;
            } else if (userAgent.indexOf("msie") != -1) {
                name = "filename=\"" + name + "\"";
            } else if (userAgent.indexOf("mozilla") != -1) {
                try {
                    name = "filename=\""
                            + new String(fileName.getBytes("gbk"), "ISO-8859-1") + DateUtils.getDate() + EXCEL_EXPORT_SUFFIX
                            + "\"";
                } catch (UnsupportedEncodingException e) {
                    name = "filename=\"" + name + "\"";
                }
            } else {
                name = "\"filename=" + name + "\"";
            }
        } else {
            name = "\"filename=" + name + "\"";
        }
        return name;
    }


    /**
     * 将excel表单数据源的数据导入到list  单个sheet
     *
     * @param sheetIndex 工作表的名称
     * @param file       文件
     * @throws Exception
     */
    public List<T> getExcelToList(Integer sheetIndex, MultipartFile file, List<ExcelLog> logs) throws Exception {
        if (file == null)
            logger.error("文件不能为空");
        String name = file.getOriginalFilename();
        if (!name.endsWith(".xls") && !name.endsWith(".xlsx"))
            logger.error("请上传xsl或xlsx格式文件");
        InputStream inputStream = null;
        try {
            inputStream = file.getInputStream();
        } catch (IOException e) {
            logger.error("导入表格失败, e:{}", e.getMessage());
        }
        List<T> list = null;
        try {
            list = EasyExcel.read(inputStream)
                    .head(clazz)
                    // 设置sheet,默认读取第一个
                    .sheet()
                    // 设置标题所在行数
                    .headRowNumber(3).doReadSync();
            for (int i = 0; i < list.size(); i++) {
                T t = list.get(i);
                Class<?> aClass = t.getClass();
                Field[] fields = aClass.getDeclaredFields();
                if (fields != null && fields.length > 0) {
                    for (Field field : fields) {
                        Export export = field.getAnnotation(Export.class);
                        if (!export.isCanEmpty()) {
                            ExcelLog excelLog = new ExcelLog();
                            excelLog.setMsg("第" + (i + 1) + "行，" + "列名：'" + export.name() + "'不能为空！");
                            logs.add(excelLog);
                        }
                    }
                }
            }
            return list;
        } catch (Exception e) {
            logger.error("读取excel中得数据失败");
            return null;
        }
    }
}

 

/**
 * 自定义行高列宽处理器
 */
public class ExcelHeightColWidthStyleStrategy extends AbstractRowWriteHandler {
    /**
     * sheet名称KEY
     */
    public static final String KEY_SHEET_NAME = "sheetName";
    /**
     * 行号key
     */
    public static final String KEY_ROW_INDEX = "rowIndex";
    /**
     * 列号key
     */
    public static final String KEY_COL_INDEX = "colIndex";
    /**
     * 行高key
     */
    public static final String KEY_ROW_HEIGHT = "rowHeight";
    /**
     * 列宽key
     */
    public static final String KEY_COL_WIDTH = "colWidth";
    /**
     * sheet页名称列表
     */
    private List<String> sheetNameList;
    /**
     * 列宽信息
     */
    private List<Map<String, Object>> colWidthList = new ArrayList<>();
 
    /**
     * 行高信息
     */
    private List<Map<String, Object>> rowHeightList = new ArrayList<>();
 
    /**
     * 创建行高信息
     *
     * @param sheetName sheet页名称
     * @param rowIndex  行号
     * @param rowHeight 行高
     * @return
     */
    public static Map<String, Object> createRowHeightMap(String sheetName, Integer rowIndex, Float rowHeight) {
        return createRowHeightColWidthMap(sheetName, rowIndex, rowHeight, null, null);
    }
 
    /**
     * 创建列宽信息
     *
     * @param sheetName sheet页名称
     * @param colIndex  列号
     * @param colWidth  列宽
     * @return
     */
    public static Map<String, Object> createColWidthMap(String sheetName, Integer colIndex, Integer colWidth) {
        return createRowHeightColWidthMap(sheetName, null, null, colIndex, colWidth);
    }
 
    /**
     * 创建行高列宽信息
     *
     * @param sheetName sheet页名称
     * @param rowIndex  行号
     * @param rowHeight 行高
     * @param colIndex  列号
     * @param colWidth  列宽
     * @return
     */
    public static Map<String, Object> createRowHeightColWidthMap(String sheetName, Integer rowIndex, Float rowHeight, Integer colIndex, Integer colWidth) {
        Map<String, Object> map = new HashMap<>();
        //sheet页名称
        map.put(KEY_SHEET_NAME, sheetName);
        //显示行号
        map.put(KEY_ROW_INDEX, rowIndex);
        //行高
        map.put(KEY_ROW_HEIGHT, rowHeight);
        //显示列号
        map.put(KEY_COL_INDEX, colIndex);
        //列宽
        map.put(KEY_COL_WIDTH, colWidth);
        return map;
    }
 
    /**
     * 自定义行高列宽适配器构造方法

     */
    public ExcelHeightColWidthStyleStrategy(List<Map<String, Object>> rowHeightColWidthList) {
        if (rowHeightColWidthList == null || rowHeightColWidthList.size() <= 0) {
            return;
        }
        rowHeightColWidthList = rowHeightColWidthList.stream().filter(x ->
                //判断sheet名称KEY是否存在
                x.keySet().contains(KEY_SHEET_NAME) && x.get(KEY_SHEET_NAME) != null
                        && StrUtil.isNotBlank(x.get(KEY_SHEET_NAME).toString())
                        //判断列索引KEY是否存在
                        && x.keySet().contains(KEY_COL_INDEX)
                        //判断行索引KEY是否存在
                        && x.keySet().contains(KEY_ROW_INDEX)
                        //判断行高KEY是否存在
                        && x.keySet().contains(KEY_ROW_HEIGHT)
                        //判断列宽KEY是否存在
                        && x.keySet().contains(KEY_COL_WIDTH)).collect(Collectors.toList());
        //填充行高信息
        this.rowHeightList = rowHeightColWidthList.stream().filter(x ->
                x.get(KEY_ROW_INDEX) != null && x.get(KEY_ROW_HEIGHT) != null).collect(Collectors.toList());
        //填充列宽信息
        this.colWidthList = rowHeightColWidthList.stream().filter(x ->
                x.get(KEY_COL_INDEX) != null && x.get(KEY_COL_WIDTH) != null).collect(Collectors.toList());
        //获取sheet页名称
        sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
        sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
        sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
    }
 
    @Override
    public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row
            , Integer relativeRowIndex, Boolean isHead) {
        Sheet sheet = writeSheetHolder.getSheet();
        //不需要设置行高列宽，或者当前sheet页不需要设置行高列宽
        if ((CollectionUtil.isEmpty(rowHeightList) && CollectionUtil.isEmpty(colWidthList))
                || sheetNameList.contains(sheet.getSheetName()) == false) {
            return;
        }
        //获取当前sheet页当前行的行高信息
        List<Map<String, Object>> sheetRowHeightMapList = rowHeightList.stream().filter(x ->
                StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())
                        && (int) x.get(KEY_ROW_INDEX) == relativeRowIndex).collect(Collectors.toList());
        for (Map<String, Object> map : sheetRowHeightMapList) {
            //行号
            Integer rowIndex = (Integer) map.get(KEY_ROW_INDEX);
            //行高
            Float rowHeight = (Float) map.get(KEY_ROW_HEIGHT);
            //设置行高
            if (rowIndex != null && rowHeight != null) {
                row.setHeightInPoints(rowHeight);
            }
        }
        //获取当前sheet页的列宽信息
        List<Map<String, Object>> sheetColWidthMapList = colWidthList.stream().filter(x ->
                StrUtil.equals(x.get(KEY_SHEET_NAME).toString(), sheet.getSheetName())).collect(Collectors.toList());
        for (Map<String, Object> map : sheetColWidthMapList) {
            //列号
            Integer colIndex = (Integer) map.get(KEY_COL_INDEX);
            //列宽
            Integer colWidth = (Integer) map.get(KEY_COL_WIDTH);
            //设置列宽
            if (colIndex != null && colWidth != null) {
                sheet.setColumnWidth(colIndex, colWidth * 256);
            }
        }
        //删除已添加的行高信息
        rowHeightList.removeAll(sheetRowHeightMapList);
        //删除已添加的列宽信息
        colWidthList.removeAll(sheetColWidthMapList);
        //重新获取要添加的sheet页姓名
        sheetNameList = this.rowHeightList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList());
        sheetNameList.addAll(this.colWidthList.stream().map(x -> x.get(KEY_SHEET_NAME).toString()).distinct().collect(Collectors.toList()));
        sheetNameList = sheetNameList.stream().distinct().collect(Collectors.toList());
    }
}

/**
 * 自定义列宽处理器
 */
public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {

    private static final int MAX_COLUMN_WIDTH = 50;

    private  final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
                                  Integer relativeRowIndex, Boolean isHead) {
        if (CollectionUtils.isEmpty(cellDataList))
            return;
        if (!isHead) {
            return;
        }
        Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
        if (maxColumnWidthMap == null) {
            maxColumnWidthMap = new HashMap<Integer, Integer>(10);
            CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
        }
        Integer columnWidth = dataLength(cellDataList, cell, isHead);
        if (columnWidth < 0) {
            return;
        }
        if (columnWidth > MAX_COLUMN_WIDTH) {
            columnWidth = MAX_COLUMN_WIDTH;
        }
        Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
        if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
            maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 100);
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        }
        CellData cellData = cellDataList.get(0);
        CellDataTypeEnum type = cellData.getType();
        if (type == null) {
            return -1;
        }
        switch (type) {
            case STRING:
                return cellData.getStringValue().getBytes().length;
            case BOOLEAN:
                return cellData.getBooleanValue().toString().getBytes().length;
            case NUMBER:
                return cellData.getNumberValue().toString().getBytes().length;
            default:
                return -1;
        }
    }
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Export {

    /**
     * 字段是否要導出
     */
    boolean exportFiled() default true;

    /**
     * 字段名
     */
    String filedName() default "";

    /**
     * 是否可以为空，默认可以为空
     */
    boolean isCanEmpty() default true;

    /**
     * Excel中的列名
     */
    String name() default "";

    int width() default 0;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class ExportTest {

    @Export(filedName = "姓名" ,width = 20)
    private String name;

    @Export( filedName = "年龄hh")
    private Integer age;

    @Export( filedName = "年级h")
    private Integer grade;

    @Export( filedName = "部门hhhhhhhhh")
    private Integer depart;
}