room_position_java/src/main/java/com/zhiyun/common/utils/EasyExcelDashboardUtils.java

package com.zhiyun.common.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
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.alibaba.excel.write.style.column.SimpleColumnWidthStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.net.URLEncoder;
import java.util.List;

/**
 * @author yxk
 * @since 2024/4/3 22:39
 */
@Slf4j
public class EasyExcelDashboardUtils {


    public static AbstractColumnWidthStyleStrategy getColumnWidthStrategy(int size) {
        return new AbstractColumnWidthStyleStrategy() {
            @Override
            protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
                boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
                if (needSetWidth) {
                    Sheet sheet = writeSheetHolder.getSheet();
                    for (int i = 0; i < size; i++) {
                        sheet.setColumnWidth(i + 2, 3000);
                    }
                }
            }
        };
    }


    /**
     * 导出Excel
     */
    public static void export(String fileName, List<List<String>> head, List<List<Object>> content, HttpServletResponse response) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            ExcelWriter writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new SimpleColumnWidthStyleStrategy(15)).registerWriteHandler(EasyExcelDashboardUtils.getCellStyleStrategy()).build();
            // 动态添加表头，适用一些表头动态变化的场景
            WriteSheet sheet = new WriteSheet();
            sheet.setSheetName("数据");
            // 创建一个表格，用于 Sheet 中使用
            WriteTable table = new WriteTable();
            table.setHead(head);
            // 写数据
            writer.write(content, sheet, table);
            writer.finish();
        } catch (Exception e) {
            log.error("导出{}异常:", fileName, e);
        }

    }

    /**
     * 样式策略
     */
    public static HorizontalCellStyleStrategy getCellStyleStrategy() {
        return new HorizontalCellStyleStrategy(headStyle(), contentStyle());
    }

    /**
     * 标题样式
     */
    private static WriteCellStyle headStyle() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //字体
        WriteFont writeFont = new WriteFont();
        // 加粗
        writeFont.setBold(false);
        //字体
        writeFont.setFontName("黑体");
        headWriteCellStyle.setWriteFont(writeFont);
        // 背景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
        return headWriteCellStyle;
    }

    /**
     * 内容样式
     */
    private static WriteCellStyle contentStyle() {
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 字体策略
        WriteFont writeFont = new WriteFont();
        writeFont.setFontName("黑体");
        writeFont.setFontHeightInPoints((short) 12);
        contentWriteCellStyle.setWriteFont(writeFont);
        //导出数据垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //导出数据水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //边框
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        return contentWriteCellStyle;
    }

}