EasyExcelUtil导出

package com.istrong.seatom.utils;

import cn.hutool.core.collection.ListUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import io.micrometer.core.lang.NonNull;
import org.apache.poi.ss.usermodel.Cell;

import java.util.*;

/**
 * @program: sxsoft_expert
 * @ClassName DownLoadFileUtil
 * @description:
 * @author: 黄涛
 * @create: 2023-07-17 16:17
 * @Version 1.0
 **/
public class EasyExcelUtil {

    /**
     * 无实体导出List<Map<String,Object>>
     * @param path 文件路径
     * @param dataList 数据
     */
    public static void EasyExcelNoModel(String path,List<Map<String,Object>> dataList) {
        String sheetName="Sheet1";
        if(!dataList.isEmpty()) {

            //获取首个Map对象的key，用于作为表头
            Set<String> keySet = dataList.get(0).keySet();
            List<List<String>> headList = new ArrayList<>();
            for (String key : keySet) {
                List<String> headTitleList = new ArrayList<>();
                headTitleList.add(key);
                headList.add(headTitleList);

            }
            List<List<Object>> bodyList = new ArrayList<List<Object>>();
            //设置导出的数据内容
            for (Map<String, Object> m : dataList) {
                List<Object> data = new ArrayList<Object>();
                for (int i = 0; i < headList.size(); i++) {
                    data.add(m.get(headList.get(i).get(0)));
                }
                bodyList.add(data);
            }

            EasyExcel.write(path).head(headList).registerConverter(new TimestampConverter())
                    .registerWriteHandler(new AutoColumnWidthWriteHandler())
                    .sheet(sheetName).doWrite(bodyList);
        }


    }

    /**
     * 无实体导出,列排序
     * @param path
     * @param bodyList 注意add顺序必须和heads一致
     * @param heads 表头排序顺序
     */
    public static void EasyExcelNoModel(String path,List<List<Object>> bodyList, List<String> heads) {
        String sheetName= "Sheet1";
        if(!bodyList.isEmpty()) {
            final List<List<String>> headList = ListUtil.toList();
            heads.stream().forEach(key->{
                List<String> headTitleList =  ListUtil.toList();
                headTitleList.add(key);
                headList.add(headTitleList);
            });
            EasyExcel.write(path).head(headList).registerConverter(new TimestampConverter())
                    .registerWriteHandler(new AutoColumnWidthWriteHandler())
                    .sheet(sheetName).doWrite(bodyList);
        }
    }



    /**
     * 无实体导出,列排序
     * @param path
     * @param bodyList 注意add顺序必须和heads一致
     */
    public static void EasyExcelProject(String path,List<List<Object>> bodyList) {
        String sheetName= "Sheet1";
        if(!bodyList.isEmpty()) {
            final List<List<String>> headList = ListUtil.toList();

            List<String> headTitle0 = new ArrayList<String>();
            List<String> headTitle1 = new ArrayList<String>();
            List<String> headTitle2 = new ArrayList<String>();
            List<String> headTitle3 = new ArrayList<String>();
            List<String> headTitle4 = new ArrayList<String>();
            List<String> headTitle5 = new ArrayList<String>();
            List<String> headTitle6 = new ArrayList<String>();
            List<String> headTitle7 = new ArrayList<String>();
            List<String> headTitle8 = new ArrayList<String>();
            List<String> headTitle9 = new ArrayList<String>();
            List<String> headTitle10 = new ArrayList<String>();

            headTitle0.add("序号");
            headTitle1.add("地区");
            headTitle2.add("项目数量");
            headTitle3.add("占比");
            headTitle4.add("已完成项目数量");
            headTitle5.add("进行中项目数量");
            headTitle6.add("进行中项目数量");
            headTitle7.add("进行中项目数量");
            headTitle8.add("进行中项目数量");
            headTitle9.add("进行中项目数量");
            headTitle10.add("进行中项目数量");

            headTitle0.add("序号");
            headTitle1.add("地区");
            headTitle2.add("项目数量");
            headTitle3.add("占比");
            headTitle4.add("已完成项目数量");
            headTitle5.add("总数量");
            headTitle6.add("合同签订");
            headTitle7.add("项目组成立");
            headTitle8.add("从业告知");
            headTitle9.add("现场勘验/检测检验");
            headTitle10.add("其他");
            headList.add(headTitle0);
            headList.add(headTitle1);
            headList.add(headTitle2);
            headList.add(headTitle3);
            headList.add(headTitle4);
            headList.add(headTitle5);
            headList.add(headTitle6);
            headList.add(headTitle7);
            headList.add(headTitle8);
            headList.add(headTitle9);
            headList.add(headTitle10);

            EasyExcel.write(path).head(headList).registerConverter(new TimestampConverter())
                    .registerWriteHandler(new AutoColumnWidthWriteHandler())
                    .sheet(sheetName).doWrite(bodyList);
        }
    }

    //自适应列宽
    private final static class AutoColumnWidthWriteHandler extends AbstractColumnWidthStyleStrategy {

        private static final int MAX_COLUMN_WIDTH = 255;
        // 因为在自动列宽的过程中，有些设置地方让列宽显得紧凑，所以做出了个判断
        private static final int COLUMN_WIDTH = 20;

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

        protected void setColumnWidth(@NonNull WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, @NonNull Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetNo(), k -> new HashMap<>());

                Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > MAX_COLUMN_WIDTH) {
                        columnWidth = MAX_COLUMN_WIDTH;
                    } else {
                        if (columnWidth < COLUMN_WIDTH) {
                            columnWidth = columnWidth * 2;
                        }
                    }
                    Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }
                }
            }
        }

        private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, @NonNull Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                WriteCellData<?> cellData = cellDataList.get(0);
                CellDataTypeEnum type = cellData.getType();
                if (type == null) {
                    return -1;
                } else {
                    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;
                    }
                }
            }
        }

    }
}