Code/sms_water/src/main/java/com/huaxu/util/ExcelUtil.java

package com.huaxu.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import com.huaxu.common.ToolUtil;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @ClassName ExcelUtil
 * @Description: Excel导出通用工具类
 * @Author :lihui
 * @Date 2021/5/17
 * @Version V1.0
 **/
public class ExcelUtil {

    /**
     * 仅仅输出明细数据到excel文件, 增加自定义的头部
     */
    public static String writeSimpleExcelWithHeader(String path, String excelName, List<List<String>> head, List list) {
        String filePath = "";
        String fileName = DateFormatUtils.format(new Date(), "yyyy/MM/dd") + File.separator;
        fileName = fileName + excelName + System.currentTimeMillis() + ".xlsx";
        try {
            getAbsoluteFile(path, fileName);
            // 这里 需要指定写用哪个class去写，指定模板名称及数据
            EasyExcel.write(path + fileName).head(head).registerWriteHandler(new CustomizeColumnWidth())
                    .registerWriteHandler(getStyleStrategy()).sheet("sheet").doWrite(list);
            filePath = getPathFileName(path, fileName);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return filePath;
    }


    private static final String getPathFileName(String uploadDir, String fileName) throws IOException {
        String pathFileName = uploadDir  + "/" + fileName;
        return ToolUtil.path(pathFileName);
    }
    private static final File getAbsoluteFile(String uploadDir, String fileName) throws IOException {
        File desc = new File(uploadDir + File.separator + fileName);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return desc;
    }

    private static HorizontalCellStyleStrategy getStyleStrategy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 设置对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 背景色, 设置为白色，也是默认颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 字体
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteCellStyle.setWriteFont(headWriteFont);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, new WriteCellStyle());
    }

    /**
     * 自定义头部的 列的宽度设置 策略. .
     */
     static class CustomizeColumnWidth extends AbstractColumnWidthStyleStrategy {

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

        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean isHead) {
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(list);
            if (needSetWidth) {
                Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
                if (maxColumnWidthMap == null) {
                    maxColumnWidthMap = new HashMap(16);
                    CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
                }

                Integer columnWidth = this.dataLength(list, cell, isHead);
                if (columnWidth >= 0) {
                    if (columnWidth > 255) {
                        columnWidth = 255;
                    }

                    Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                    if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                        ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                    }

                }
            }
        }
        private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
            if (isHead) {
                return cell.getStringCellValue().getBytes().length;
            } else {
                CellData cellData = (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;
                    }
                }
            }
        }

        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
            // 设置行高测试
            short height = 600;
            row.setHeight(height);
        }
    }

}