Java创建Excel文档（自定义格式）

最新推荐文章于 2024-08-26 16:23:34 发布

普通还不自信的程序员

最新推荐文章于 2024-08-26 16:23:34 发布

阅读量2.2k

点赞数

分类专栏：代码文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/qq_41841482/article/details/121082101

版权

代码专栏收录该内容

94 篇文章 7 订阅

订阅专栏

创建文档时，将图片放入单元格，随着单元格宽与高的改变，图片大小也跟着变化

导入依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.4</version>
        </dependency>

实体类

public class UserExcel {
 
    @ExcelProperty(value = "序号",index = 0)
    private Integer id;
 
    @ExcelProperty("姓名")
    private String name;
 
    @ExcelProperty("照片")
    private File img;
 
    public UserExcel() {
    }
 
    //get set method
}

拦截器

通过拦截器，设置excel格式

public class CellHandler implements CellWriteHandler {

    /**
     * map key:第i行 value：第i行中单元格索引集合
     */
    private HashMap<Integer, List<Integer>> map;

    /**
     * 颜色
     */
    private Short colorIndex;

    public CellHandler() {
    }

    public CellHandler(HashMap<Integer, List<Integer>> map, Short colorIndex) {
        this.map = map;
        this.colorIndex = colorIndex;
    }

    @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) {
        // 当前行的第i列
        int i = cell.getColumnIndex();

        if (cell.getRowIndex() != 0) {
            // 不处理第1行
            List<Integer> integers = map.get(cell.getRowIndex());
            if (integer != null && integers.size() > 0) {
                if (integers.contains(i)) {
                    // 根据单元格获取workbook
                    Workbook workbook = cell.getSheet().getWorkbook();
                    // 设置行高
                    writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) 80);
                    // 单元格策略
                    WriteCellStyle writeCellStyle = new WriteCellStyle();
                    // 设置背景色为白色
                    writeCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                    // 设置垂直居中为居中对齐
                    writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    // 设置左右对齐为居中对齐
                    writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                    // 设置单元格上下左右边框为细边框
                    writeCellStyle.setBorderBottom(BorderStyle.MEDIUM);
                    writeCellStyle.setBorderLeft(BorderStyle.MEDIUM);
                    writeCellStyle.setBorderRight(BorderStyle.MEDIUM);
                    writeCellStyle.setBorderTop(BorderStyle.MEDIUM);
                    // 创建字体实例
                    WriteFont writeFont = new WriteFont();
                    writeFont.setFontName("宋体");
                    writeFont.setFontHeightInPoints((short) 14);
                    // 设置字体颜色
                    writeFont.setColor(colorIndex);
                    // 单元格颜色
                    writeCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                    writeCellStyle.setWriteFont(writeFont);

                    CellStyle cellStyle = StyleUtil.buildContentCellStyle(workbook, writeCellStyle);
                    // 设置单元格样式
                    cell.getRow().getCell(i).setCellStyle(cellStyle);
                }
            }
        }
    }
}

向excel中插入图片时设置图片格式

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.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.List;
import java.util.Objects;

/**
 * @author huangzixiao
 * @Description
 * @date 2021/11/1
 */
public class ImageHandler extends AbstractCellWriteHandler {

    /**
     * 图片行列跨度
     */
    private int colSpan = 1;
    private int rowSpan = 1;

    /**
     * 左侧右侧边框粗细
     */
    private int borderPixelX1Y1 = 5;
    private int borderPixelX2Y2 = 5;

    /**
     * 可以随着单元格一起移动，改变大小
     */
    private ClientAnchor.AnchorType anchorType = ClientAnchor.AnchorType.MOVE_AND_RESIZE;


    /**
     * 单元格数据转换后调用
     */
    @Override
    public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        // 不处理表头，不处理不含图片的
        boolean noImageValue = Objects.isNull(cellData)||Objects.isNull(cellData.getImageValue());
        if (Objects.equals(Boolean.TRUE,isHead)||noImageValue) {
            return;
        }
        // 设置单元格类型为EMPTY 让easyExcel不去处理该单元格
        cellData.setType(CellDataTypeEnum.EMPTY);
    }

    /**
     * 在单元格上的所有操作完成后调用
     */
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (cell.getColumnIndex()==2) {
            // 设置第三列的宽度
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(),10000);
        }
        if (!isHead) {
            // 设置首行外的列的高度
            writeSheetHolder.getSheet().getRow(cell.getRowIndex()).setHeight((short) 2000);
        }
        if (CollectionUtils.isEmpty(cellDataList)||Objects.equals(Boolean.TRUE,isHead)) {
            return;
        }
        CellData cellData = cellDataList.get(0);
        if (Objects.isNull(cellData)||Objects.isNull(cellData.getImageValue())) {
            return;
        }
        setImageValue(cellData,cell);
    }

    private void setImageValue(CellData cellData,Cell cell){
        Sheet sheet = cell.getSheet();
        int index = sheet.getWorkbook().addPicture(cellData.getImageValue(), XSSFWorkbook.PICTURE_TYPE_PNG);
        Drawing<?> drawing = sheet.getDrawingPatriarch();
        if (drawing == null) {
            drawing = sheet.createDrawingPatriarch();
        }
        CreationHelper helper = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = helper.createClientAnchor();
        // 图片边距
        final int borderWidth1 = Units.pixelToEMU(borderPixelX1Y1);
        final int borderWidth2 = Units.pixelToEMU(borderPixelX2Y2);
        // 图片左上角偏移量
        anchor.setDx1(borderWidth1);
        anchor.setDy1(borderWidth2);
        // 图片右下角偏移量
        anchor.setDx2(Math.negateExact(borderWidth1));
        anchor.setDy2(Math.negateExact(borderWidth2));
        // 图片行列
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex()+colSpan);
        anchor.setRow1(cell.getRowIndex());
        anchor.setRow2(cell.getRowIndex()+rowSpan);
        anchor.setAnchorType(anchorType);
        drawing.createPicture(anchor,index);

    }
}

主方法

public class Test03 {

    public static List<UserExcel> userExcelList;

    static {
        userExcelList = new ArrayList<UserExcel>();
        Collections.addAll(userExcelList,
                new UserExcel(1,"hzx01",new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01\\image\\Git配置.png")),
                new UserExcel(2,"hzx02",new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01\\image\\Git配置.png")),
                new UserExcel(3,"hzx03",new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01\\image\\Git配置.png"))
                );
    }

    public static void main(String[] args) {
        SimpleDateFormat sf = new SimpleDateFormat("HHmmss");
        String fileName = "D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01\\excel\\testExcel"+sf.format(new Date())+".xlsx";
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = defaultStyle();
        // 设置默认样式
        ExcelWriterSheetBuilder builder = EasyExcel.write(fileName, UserExcel.class).sheet("列表").registerWriteHandler(horizontalCellStyleStrategy);

        builder.registerWriteHandler(new ImageHandler());

        builder.doWrite(userExcelList);
    }
    /**
     * 默认样式
     */
    public static HorizontalCellStyleStrategy defaultStyle(){
        // 标头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        // 标头居中对齐
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 表头字体
        WriteFont headFont = new WriteFont();
        headFont.setBold(true);
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short)12);
        headStyle.setWriteFont(headFont);

        // 内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 背景为白色
        contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 内容字体
        WriteFont contentFont = new WriteFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short)11);
        contentStyle.setWriteFont(contentFont);

        // 初始化样式
        return new HorizontalCellStyleStrategy(headStyle, contentStyle);
    }
}