1. 首页
  2. 文章中心
  3. 代码

Java创建Excel（汇总）

66 阅读 0 评论
我是靠谱客的博主 调皮棒棒糖，最近开发中收集的这篇文章主要介绍Java创建Excel（汇总），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

通过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();
            }
        }
    }
}

效果

最后

以上就是调皮棒棒糖为你收集整理的Java创建Excel（汇总）的全部内容，希望文章能够帮你解决Java创建Excel（汇总）所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
点赞(44)

相关文章

java 复制excel_利用Java实现复制Excel工作表功能
java 复制excel_利用Java实现复制Excel工作表功能
java实现把数据写进Excel表格
java实现把数据写进Excel表格
Java中生成excel，并进行数据的写入读出
Java中生成excel，并进行数据的写入读出
Java中导入、导出Excel——HSSFWorkbook 使用一、介绍二、使用Jakarta POI导入、导出Excel 
Java中导入、导出Excel——HSSFWorkbook 使用一、介绍二、使用Jakarta POI导入、导出Excel 
Java创建Excel（汇总）
Java创建Excel（汇总）
Java使用自定义Excel模板填充数据
Java使用自定义Excel模板填充数据
Excel：VBA编程入门（一）打开Excel中的VBA编程界面写一个简单的VBA程序
Excel：VBA编程入门（一）打开Excel中的VBA编程界面写一个简单的VBA程序
vba宏语言_Excel VBA(1) – VBA 简介及录制宏
vba宏语言_Excel VBA(1) – VBA 简介及录制宏

评论列表共有 0 条评论

立即
投稿
返回
顶部