Java创建Excel（汇总）

已于 2024-01-19 13:44:28 修改
阅读量5k 收藏 22
点赞数 4
分类专栏： 代码 文章标签： java
于 2021-12-30 16:02:42 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41841482/article/details/122237886
版权

通过easyExcel将数据动态写入excel表格中。并灵活设置单元格、表头等内容。

本文通过向表格中插入用户名称、照片等数据，说明如何使用easyExcel生成excel文件

easyExcel官方文档

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

默认格式

实体类

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

    public UserExcel() {
    }
}

 测试代码

public class Test02 {
    public static void main(String[] args) {
        String fileName = "D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\excel\\testExcel02.xlsx";

        EasyExcel.write(fileName, UserExcel.class).sheet("用户").doWrite(initData());
    }

    private static List<UserExcel> initData(){
        ArrayList<UserExcel> userList = new ArrayList<UserExcel>();
        UserExcel userExcel = new UserExcel();

        userExcel.setId(1);
        userExcel.setName("hzx");
        userExcel.setImg(new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\image\\Git配置.png"));

        userList.add(userExcel);
        return userList;
    }
}

效果

 设置单元格格式

实体类

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

    public UserExcel() {
    }
}

设置全部单元格格式

public class DefaultHandler {

    /**
     * 默认样式
     */
    public static HorizontalCellStyleStrategy defaultStyle(){
        // 标头样式
        WriteCellStyle headStyle = new WriteCellStyle();
        // 标头居中对齐
        headStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
        // 表头字体
        WriteFont headFont = new WriteFont();
        headFont.setBold(true);
        headFont.setFontName("宋体");
        headFont.setFontHeightInPoints((short)12);
        headStyle.setWriteFont(headFont);
        headStyle.setBorderBottom(BorderStyle.THIN);
        headStyle.setBorderLeft(BorderStyle.THIN);
        headStyle.setBorderRight(BorderStyle.THIN);
        headStyle.setBorderTop(BorderStyle.THIN);

        // 内容样式
        WriteCellStyle contentStyle = new WriteCellStyle();
        // 背景为白色
        contentStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 垂直居中
        contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 水平居中
        contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        contentStyle.setWrapped(true);
        // 内容字体
        WriteFont contentFont = new WriteFont();
        contentFont.setFontName("宋体");
        contentFont.setFontHeightInPoints((short)11);
        contentStyle.setWriteFont(contentFont);

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

 设置图片单元格

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\\test01Excel\\image\\Git配置.png")),
                new UserExcel(2,"hzx02",new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\image\\Git配置.png")),
                new UserExcel(3,"hzx03",new File("D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\image\\Git配置.png"))
                );
    }

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

        builder.registerWriteHandler(new ImageHandler());

        builder.doWrite(userExcelList);
    }
}

 效果

动态表头

之前通过实体类中的ExcelProperty注解规定表头的内容与位置，但如果要实现动态表头，则需要通过嵌套集合设置表头内容。

实现代码

    private static List<List<String>> getHearder() {
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String title = "C808:" + sf.format(new Date());
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add(title);
        head0.add("序号");

        List<String> head1 = new ArrayList<>();
        head1.add(title);
        head1.add("姓名");

        List<String> head2 = new ArrayList<>();
        head2.add(title);
        head2.add("照片");

        Collections.addAll(list, head0, head1, head2);
        return list;
    }

 效果

多个sheet

实体类

public class UserExcel03 {
    private Integer id;
    private String name;
    private File img;

    public UserExcel03() {
    }
}

实现代码

public class Test05 {
    private static List<UserExcel03> userExcelList;

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

    public static void main(String[] args) {
        String fileName = "D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\excel\\testExcel05.xlsx";
        ExcelWriter excelWriter = null;
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = DefaultHandler.defaultStyle();
        try {
            excelWriter = EasyExcel.write(fileName).build();
            // 实现多个sheet
            for (int i = 0; i <= 3; i++) {
                WriteSheet writeSheet = null;
                writeSheet = EasyExcel.writerSheet(i, "用户组" + i).head(getHearder()).registerWriteHandler(DefaultHandler.defaultStyle()).registerWriteHandler(new ImageHandler()).build();
                excelWriter.write(userExcelList, writeSheet);
            }
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }

    private static List<List<String>> getHearder() {
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String title = "C808:" + sf.format(new Date());
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add(title);
        head0.add("序号");

        List<String> head1 = new ArrayList<>();
        head1.add(title);
        head1.add("姓名");

        List<String> head2 = new ArrayList<>();
        head2.add(title);
        head2.add("照片");

        Collections.addAll(list, head0, head1, head2);
        return list;
    }
}

效果

一个sheet中有多个table 

实体类

public class UserExcel02 {
    @ExcelProperty(value = {"用户","序号"})
    private Integer id;
    @ExcelProperty(value = {"用户","姓名"})
    private String name;

    public UserExcel02() {
    }
}
public class StudentExcel {
    @ExcelProperty(value = {"学生","序号"},index = 0)
    private Integer id;
    @ExcelProperty(value = {"学生","姓名"},index = 1)
    private String studentName;
    @ExcelProperty(value = {"学生","性别"},index = 2)
    private String sex;
}

实现代码

public class Test06 {
    private static List<UserExcel02> userExcelList;

    private static List<StudentExcel> studentExcelList;

    static {
        userExcelList = new ArrayList<UserExcel02>();
        Collections.addAll(userExcelList, new UserExcel02(1, "hzx01"), new UserExcel02(2, "hzx02"), new UserExcel02(3, "hzx03"), new UserExcel02());

        studentExcelList = new ArrayList<>();
        Collections.addAll(studentExcelList, new StudentExcel(1, "stu000000000000000001", "男"), new StudentExcel(2, "stu02", "女"), new StudentExcel());
    }

    public static void main(String[] args) {
        String fileName = "D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\excel\\testExcel06.xlsx";
        ExcelWriter excelWriter = null;
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = DefaultHandler.defaultStyle();
        try {
            excelWriter = EasyExcel.write(fileName).build();
            WriteSheet writeSheet = null;
            // sheet0
            writeSheet = EasyExcel.writerSheet(0, "总览").build();
            WriteTable writeTable0 = EasyExcel.writerTable(0).head(UserExcel02.class).registerWriteHandler(DefaultHandler.defaultStyle()).build();
            WriteTable writeTable1 = EasyExcel.writerTable(1).head(StudentExcel.class).registerWriteHandler(DefaultHandler.defaultStyle()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).build();
            excelWriter.write(userExcelList, writeSheet, writeTable0);
            excelWriter.write(studentExcelList, writeSheet, writeTable1);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

效果

合并单元格

实体类

public class UserExcel02 {
    @ExcelProperty(value = {"用户","序号"})
    private Integer id;
    @ExcelProperty(value = {"用户","姓名"})
    private String name;

    public UserExcel02() {
    }
}
public class StudentExcel {
    @ExcelProperty(value = {"学生","序号"},index = 0)
    private Integer id;
    @ExcelProperty(value = {"学生","姓名"},index = 1)
    private String studentName;
    @ExcelProperty(value = {"学生","性别"},index = 2)
    private String sex;
}

单元格合并

public class CellMergeHandler extends AbstractMergeStrategy {

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

    public CellMergeHandler(List<CellRangeAddress> cellRangeAddress) {
        this.cellRangeAddress = cellRangeAddress;
    }

    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {
        if (!CollectionUtils.isEmpty(cellRangeAddress)) {
            for (CellRangeAddress item : cellRangeAddress) {
                sheet.addMergedRegionUnsafe(item);
            }
        }
    }
}

 实现代码

public class Test07 {
    private static List<UserExcel02> userExcelList;

    private static List<StudentExcel> studentExcelList;

    static {
        userExcelList = new ArrayList<UserExcel02>();
        Collections.addAll(userExcelList, new UserExcel02(1, "hzx01"), new UserExcel02(2, "hzx02"), new UserExcel02(3, "hzx03"), new UserExcel02());

        studentExcelList = new ArrayList<>();
        Collections.addAll(studentExcelList, new StudentExcel(1, "stu000000000000000001", "男"), new StudentExcel(2, "stu02", "女"), new StudentExcel());
    }

    public static void main(String[] args) {
        String fileName = "D:\\Java Code\\testmaven00\\src\\main\\java\\com\\test01Excel\\excel\\testExcel07.xlsx";
        ExcelWriter excelWriter = null;

        // 设置合并的单元格
        List<CellRangeAddress> cellRangeList = new ArrayList<>();
        cellRangeList.add(new CellRangeAddress(0, 0, 1, 2));
        cellRangeList.add(new CellRangeAddress(1, 1, 1, 2));
        cellRangeList.add(new CellRangeAddress(2, 2, 1, 2));
        cellRangeList.add(new CellRangeAddress(3, 3, 1, 2));
        cellRangeList.add(new CellRangeAddress(4, 4, 1, 2));

        try {
            excelWriter = EasyExcel.write(fileName).build();
            WriteSheet writeSheet = null;
            // sheet0
            writeSheet = EasyExcel.writerSheet(0, "总览").build();
            WriteTable writeTable0 = EasyExcel.writerTable(0).head(UserExcel02.class).registerWriteHandler(DefaultHandler.defaultStyle()).registerWriteHandler(new CellMergeHandler(cellRangeList)).build();
            WriteTable writeTable1 = EasyExcel.writerTable(1).head(StudentExcel.class).registerWriteHandler(DefaultHandler.defaultStyle()).build();
            excelWriter.write(userExcelList, writeSheet, writeTable0);
            excelWriter.write(studentExcelList, writeSheet, writeTable1);
        } finally {
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
}

效果

单元格背景颜色对照

对于要求不太严格的可以直接使用注解EasyExcel注解fillForegroundColor提供的60余种颜色

普通还不自信的程序员
关注
专栏目录
java生成Excel文件
weixin_42236797的博客
12-13 2万+
添加依赖 <!-- 使用 xls 格式 --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.14</version> </dependency> <!-- 使用 xlsx 格式 --> <dependency> .
Java 创建一个excel
Dave0002的博客
07-07 708
java 创建一个excel
Java中的Excel生成
weixin_46372265的博客
07-17 1834
Apache POI (Poor Obfuscation Implementation) 是一个强大的Java库，允许开发者读取和写入Microsoft Office格式的文件。特别是，它支持操作Excel文件的HSSF和XSSF两个模块，分别用于处理.xls和.xlsx文件格式。通过本文的介绍，你应该已经对如何在Java中使用Apache POI生成和操作Excel文件有了一个全面的了解。
运用java对Excel文件进行解析与创建
qq_59616295的博客
07-13 2493
通过POI来进行Excel文件的导入和导出操作
java 操作 Excel
最新发布
m0_62056932的博客
07-25 1286
因此这两种文件格式的处理方式并不一样。
java 创建EXCEL文件，并向EXCEL中写入数据
05-13
java向EXCEL中写入数据。里面有一个ExcelWrite.java的文件和一个jxl的jar包。
Java创建Excel文件
小达哥的博客
03-12 4913
Java创建Excel文件
JAVA生成EXCEL文件
南国浪子 的专栏
04-12 5201
使用JXL2_5_9包创建EXCEL文件:public void createXLS() {  try  {   //打开文件   WritableWorkbook book=   Workbook.createWorkbook(new File("JAVA生成EXCEL测试.xls"));      //生成名为"第一页"的工作表，参数0表示这是第一页   WritableSheet s
java生成excel报表
01-19
用java ，apache poi生成excel报表
Java在Excel中创建透视表方法解析
08-19
Java在Excel中创建透视表方法解析 Java在Excel中创建透视表方法解析是指通过Java程序在Excel表格中根据数据来创建透视表的方法。透视表是一种特殊的表格，能够根据选择的行、列和值字段对数据进行汇总和分析，从而...
Java通过POI读取Excel遍历数据，批量生成word文档
04-09
接下来，我们需要创建一个Java程序来读取Excel文件。使用POI的`XSSFWorkbook`类打开Excel文件，然后通过` XSSFSheet `获取工作表，并使用` XSSFRow `和` XSSFCell `遍历单元格数据： ```java import org.apache.poi...
java实现生成Excel（默认.xls，可自己修改文件类型）文件程序
12-01
java实现生成Excel（.xls）文件程序，并封装了从浏览器下载的方法，压缩包内含有使用说明，希望大神们指教，互相学习
java生成excel
10-07
利用xml文件作为记录中间信息的媒介，利用java读取和生成excel文件
java 生成Excel文件
06-21
java 生成Excel文件 java 生成Excel文件 java 生成Excel文件
java生成excel表格
12-29
java生成excel表格代码示例，下载后可以直接使用
java 生成并下载excel
10-13
java 生成excel下载 java 生成excel下载 java 生成excel下载 java 生成excel下载
java解析excel并做数据有效性校验
04-26
可以创建一个错误列表，将每个不合规的单元格和错误信息添加到列表中，最后汇总反馈。 8. **工具类和实用方法** 在实际项目中，可以封装一个工具类，包含读取Excel、数据校验等功能，这样可以使代码更加整洁，复用...
java-excel导入导出注解版内用教程说明
05-14
Java中的Excel导入导出是开发过程中常见的需求，特别是在数据处理、报表生成以及数据交换时。Apache POI 是一个流行的开源库，它允许Java开发者读写Microsoft Office格式的文件，包括Excel（.xlsx 和 .xls）。本教程...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值