smsb-customer-manager-84/smsb-customer-manager-adapter/src/main/java/com/inspur/customer/utils/EasyExcelUtil.java

package com.inspur.customer.utils;

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.WriteHandler;
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.LongestMatchColumnWidthStyleStrategy;
import com.google.common.collect.Lists;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.springframework.core.annotation.AnnotationUtils;
import org.springframework.util.ReflectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

/**
 * @author zengweijie
 * @version 1.0
 * @date 2022/12/12
 **/
public class EasyExcelUtil {

    /**
     * 导出 Excel ：一个 sheet，带表头.
     *
     * @param response  HttpServletResponse
     * @param data      数据 list，每个元素为一个 BaseRowModel
     * @param fileName  导出的文件名
     * @param sheetName 导入文件的 sheet 名
     * @param model     映射实体类，Excel 模型
     * @throws Exception 异常
     */
    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> model, boolean putExportTimeToHead) throws Exception {
        writeExcel(response, data, fileName, sheetName, model, null, putExportTimeToHead);
    }

    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, Class<?> model, List<WriteHandler> writeHandlerList, boolean putExportTimeToHead) throws Exception {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 16);
        // 字体
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(true);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容靠中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        try (OutputStream outputStream = getOutputStream(fileName, response)) {
            ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcelFactory.write(outputStream, model).excelType(ExcelTypeEnum.XLSX).sheet(sheetName);
            if (writeHandlerList != null && !writeHandlerList.isEmpty()) {
                for (WriteHandler writeHandler : writeHandlerList) {
                    excelWriterSheetBuilder.registerWriteHandler(writeHandler);
                }
            }
            if (putExportTimeToHead) {
                excelWriterSheetBuilder.head(getHeadWithExportTime(model));
            }
            excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(data);
        }
    }

    public static void writeExcel(HttpServletResponse response, List<? extends Object> data, String fileName, String sheetName, List<List<String>> head, List<WriteHandler> writeHandlerList, boolean putExportTimeToHead) throws Exception {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 颜色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 16);
        // 字体
        headWriteCellStyle.setWriteFont(headWriteFont);
        headWriteCellStyle.setWrapped(true);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置内容靠中对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        try (OutputStream outputStream = getOutputStream(fileName, response)) {
            ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcelFactory.write(outputStream).excelType(ExcelTypeEnum.XLSX).sheet(sheetName);
            if (writeHandlerList != null && !writeHandlerList.isEmpty()) {
                for (WriteHandler writeHandler : writeHandlerList) {
                    excelWriterSheetBuilder.registerWriteHandler(writeHandler);
                }
            }
            if (putExportTimeToHead) {
                excelWriterSheetBuilder.head(getHeadWithExportTime(head));
            }
            excelWriterSheetBuilder.registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).doWrite(data);
        }
    }

    /**
     * 导出文件时为Writer生成OutputStream.
     *
     * @param fileName 文件名
     * @param response response
     * @return ""
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("utf8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx");
            response.setHeader("Pragma", "public");
            response.setHeader("Cache-Control", "no-store");
            response.addHeader("Cache-Control", "max-age=0");
            return response.getOutputStream();
        } catch (IOException e) {
            throw new Exception("导出excel表格失败!", e);
        }
    }

    private static List<List<String>> getHeadWithExportTime(Class<?> clazz) {
        List<List<String>> head = new ArrayList<>();
        List<Field> fieldList = new ArrayList<>();
        ReflectionUtils.doWithFields(clazz, fieldList::add);
        List<ExcelProperty> annotationList = new ArrayList<>();
        for (Field field : fieldList) {
            ExcelProperty annotation = AnnotationUtils.getAnnotation(field, ExcelProperty.class);
            if (annotation != null) {
                annotationList.add(annotation);
            }
        }
        annotationList.sort(Comparator.comparing(ExcelProperty::index));
        for (ExcelProperty excelProperty : annotationList) {
            head.add(Lists.newArrayList(excelProperty.value()));
        }
        String top = String.format("导出时间:%s", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        for (List<String> strings : head) {
            strings.add(0, top);
        }
        return head;
    }

    private static List<List<String>> getHeadWithExportTime(List<List<String>> head) {
        String top = String.format("导出时间:%s", LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
        for (List<String> strings : head) {
            strings.add(0, top);
        }
        return head;
    }
}