EasyExcel

1、自动换行

/**
     * 导出 Excel，多个 sheet
     *
     * @param response    response
     * @param fileName    文件名
     * @param head        表头
     * @param sheetMap    sheetName -> 数据
     */
    public static void exportExcelMultiSheet(HttpServletResponse response,
                                             String fileName,
                                             List<List<String>> head,
                                             Map<String, List<List<Object>>> sheetMap) {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");

        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 设置自动换行，前提内容中需要加「\n」才有效
        contentWriteCellStyle.setWrapped(true);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(null, contentWriteCellStyle);

        ExcelWriter excelWriter = null;
        try {
            // 防止乱码
            String finalFileName = URLEncoder.encode(fileName, "UTF-8").replace("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + finalFileName);
            excelWriter = EasyExcelFactory
                    .write(response.getOutputStream())
                    .registerWriteHandler(horizontalCellStyleStrategy)
                    .build();
            for (Map.Entry<String, List<List<Object>>> entry : sheetMap.entrySet()) {
                WriteSheet writeSheet = EasyExcelFactory.writerSheet(entry.getKey()).head(head).build();
                excelWriter.write(entry.getValue(), writeSheet);
            }
            excelWriter.finish();
        } catch (Exception e) {
            if (excelWriter != null) {
                excelWriter.finish();
            }
            log.error("Excel 导出失败", e);
        }
    }

2、easyexcel，导出Excel，设置单元格样式,

easyexcel 2.2版本

((XSSFCellStyle) cellStyle) 强转类型后设置背景色，调用父类的方法

try {
            response.setContentType(ReportMapKeyConstant.CONTENT_TYPE_EXCEL.getValue());
            String fileName = "年度考核得分总体组.xlsx";
            response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));

            ExcelWriter writer = EasyExcelFactory.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcelFactory.writerSheet("sheet")
                    .head(head)
                    .registerWriteHandler(new AssessmentOverallCellStyleStrategy())
                    .registerWriteHandler(new MyWidthStrategy(widthMap))
                    .registerWriteHandler(new SimpleRowHeightStyleStrategy(null, (short) 19))
                    .build();
            writer.write(exportList, writeSheet);
            writer.finish();
        } catch (Exception e) {
            response.setStatus(SC_BAD_REQUEST);
        }

行高设置

　　​new SimpleRowHeightStyleStrategy(null, (short) 19)​

宽度设置

package com.unicom.diamond.report.excel;

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.List;
import java.util.Map;

/**
 * @author wc
 * @since 2021-10-11
 */
@RequiredArgsConstructor
public class MyWidthStrategy extends AbstractColumnWidthStyleStrategy {
    private final Map<Integer, Integer> widthMap;

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        Sheet sheet = writeSheetHolder.getSheet();

        int i = cell.getColumnIndex();

        sheet.setColumnWidth(i, 256 * widthMap.get(i) + 184);
    }
}

样式设置

package com.unicom.diamond.report.excel;

import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.AbstractCellStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

import java.awt.Color;
import java.util.ArrayList;

/**
 * @Description 年度考核-总体组-导出样式策略
 * @Author kj
 * @Date 2023/12/14
 */
public class AssessmentOverallCellStyleStrategy extends AbstractCellStyleStrategy {
    private Workbook workbook;

    @Override
    protected void initCellStyle(Workbook workbook) {
        this.workbook = workbook;
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, this.getHeadStyle());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 设置背景色
        ArrayList<Integer> blueList = CollectionUtil.newArrayList(0, 1, 9, 23, 32, 33, 34);
        Color color;
        if (blueList.contains(cell.getColumnIndex())) {
            color = new Color(91, 155, 213);
        } else {
            color = new Color(152, 210, 119);
        }
        ((XSSFCellStyle) cellStyle).setFillForegroundColor(new XSSFColor(color, null));
        cell.setCellStyle(cellStyle);
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {

    }

    private WriteCellStyle getHeadStyle() {
        // 表头字体样式
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("微软雅黑");
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteFont.setColor(IndexedColors.WHITE.getIndex());
        // 表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 无背景颜色
        headWriteCellStyle.setFillPatternType(FillPatternType.NO_FILL);
        // 无边框
        headWriteCellStyle.setBorderBottom(BorderStyle.NONE);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setWriteFont(headWriteFont);

        return headWriteCellStyle;
    }
}

大文件读取

流式

使用 poi的 XSSFReader​ 与SAX模式解析，但是目前还没找到处理图片的方法

普通版本

先限制文件的大小

可以对文件进行切分，暂时没实现

　　‍