easyexcel 自定义样式

最新推荐文章于 2025-09-11 10:19:39 发布

confused_kitten

最新推荐文章于 2025-09-11 10:19:39 发布

阅读量2k

点赞数 1

CC 4.0 BY-SA版权

分类专栏： java之小白历险记文章标签： java easyexcel 自定义样式

本文链接：https://blog.csdn.net/confused_kitten/article/details/113494310

java之小白历险记专栏收录该内容

14 篇文章

订阅专栏

本文介绍如何使用Java通过单元格合并、设置表格样式、多行表头颜色及输出Excel文件来美化表格。涵盖单元格合并策略、字体边框背景色设置、多行表头颜色调整等内容。

1.单元格合并

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;

public class ReportMergeStrategy extends AbstractMergeStrategy {

    //合并坐标集合
    private List<CellRangeAddress> cellRangeAddresss;

    //构造
    public ReportMergeStrategy(int num) {
        List<CellRangeAddress> list = new ArrayList<>();
        for (int k = 0; k < num; k++) {
            int i = k * 3 + 2;
            list.add(new CellRangeAddress(i, i + 2, 0, 0));
            list.add(new CellRangeAddress(i, i + 2, 5, 5));
            list.add(new CellRangeAddress(i, i + 2, 6, 6));
            list.add(new CellRangeAddress(i, i + 2, 7, 7));
            list.add(new CellRangeAddress(i, i + 2, 8, 8));
            list.add(new CellRangeAddress(i, i + 2, 9, 9));
            list.add(new CellRangeAddress(i, i + 2, 10, 10));
            list.add(new CellRangeAddress(i, i + 2, 11, 11));
            list.add(new CellRangeAddress(i, i + 2, 12, 12));
        }
        this.cellRangeAddresss = list;
    }

    /**
     * merge
     *
     * @param sheet
     * @param cell
     * @param head
     * @param relativeRowIndex
     */
    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {
        if (CollectionUtils.isNotEmpty(cellRangeAddresss)) {
            if (cell.getRowIndex() == 2 && cell.getColumnIndex() == 0) {
                for (CellRangeAddress item : cellRangeAddresss) {
                    sheet.addMergedRegionUnsafe(item);
                }
            }
        }
    }
}

2. 表格样式（字体/边框/背景色等）

import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
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;

public class CellStyleStrategy {

    /**
     * 设置表头  和内容样式
     *
     * @return
     */
    public static HorizontalCellStyleStrategy StyleStrategy() {
        // 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.SEA_GREEN.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //2 内容样式策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 头默认了 FillPatternType所以可以不指定。
        //  contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //设置水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);

        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }
}

3.多行表头颜色设置

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
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 org.apache.poi.ss.usermodel.*;

import java.util.List;

public class RowWriteHandler implements CellWriteHandler {
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                 Head head, Integer integer, Integer integer1, Boolean aBoolean) {

    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                       CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {

    }

    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                 List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        int i = cell.getColumnIndex();
        writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (2.34 * 256));
        if (1 == cell.getRowIndex()) {
            // 根据单元格获取workbook
            Workbook workbook = cell.getSheet().getWorkbook();
            //设置行高
//            writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) (1.4 * 256));
            writeSheetHolder.getSheet().setColumnWidth(i, (short) (13.18 * 256));
            // 单元格策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 设置背景颜色白色
            contentWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
            WriteFont contentWriteFont = new WriteFont();
            //内容字体大小
            contentWriteFont.setFontName("宋体");
            contentWriteFont.setBold(false);
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, contentWriteCellStyle);
            //设置当前行第i列的样式
            cell.getRow().getCell(i).setCellStyle(cellStyle);
        }
    }
}

4.输出excel

   List<List<String>> head = ExcelHead();
 
        EasyExcel.write(fileUrl).head(head).sheet(sheetname)
                .registerWriteHandler(StyleStrategy())
                .registerWriteHandler(new ReportMergeStrategy(devicecheck.size()))//自定义合并 单元格
                .registerWriteHandler(new RowWriteHandler())
                .doWrite(tosave);