demo

 /**
     * 多个 sheet 的写
     */
    @Test
    public void multiSheetWrite() {
        String fileName = "E:/test" + File.separator + "multiSheetWrite" + System.currentTimeMillis() + ".xlsx";
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
            for (int i = 0; i < 3; i++) {
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "sheet-" + i).head(DemoData.class).build();
                excelWriter.write(data, writeSheet);
            }
        }
    }

    /**
     * 指定要忽略的字段(对应的也可以指定只包含的字段: includeColumnFiledNames)
     */
    @Test
    public void excludeOrIncludeWrite() {
        String fileName = "E:/test" + File.separator + "excludeOrIncludeWrite" + System.currentTimeMillis() + ".xlsx";
        // 指定要忽略的字段
        Set<String> excludeColumnFiledNames = new HashSet<>();
        excludeColumnFiledNames.add("name");
        EasyExcel.write(fileName).head(DemoData.class).excludeColumnFieldNames(excludeColumnFiledNames).sheet("sheet-1").doWrite(data);
    }

    /**
     * 复杂的写
     */
    @Test
    public void complexWrite() {

        // 指定范围合并
        //OnceAbsoluteMergeStrategy secondHeadMaleMergeStrategy = new OnceAbsoluteMergeStrategy(1, 1, 1, 2);

        // 设置表头样式
        WriteCellStyle headCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headCellStyle.setWriteFont(headWriteFont);
        headCellStyle.setFillForegroundColor(GREY_25_PERCENT.index);
        headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        // 设置内容样式
        WriteCellStyle contentCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setColor(RED.getIndex());
        contentCellStyle.setWriteFont(contentWriteFont);
        contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);

        String fileName = "E:/test" + File.separator + "complexWrite" + System.currentTimeMillis() + ".xlsx";
        EasyExcel.write(fileName).head(getHeads()).sheet("sheet-1")
                .registerWriteHandler(horizontalCellStyleStrategy)
                .doWrite(getRows());
    }

    private List<List<String>> getHeads() {
        List<List<String>> headOfColumns = ListUtils.newArrayList();

        /// 第 0 列表头(值相同时会自动合并)
        List<String> headOfColumn0 = ListUtils.newArrayList();
        // 第 0 列,第 0 行
        headOfColumn0.add("姓名");
        // 第 0 列,第 1 行
        headOfColumn0.add("姓名");

        List<String> headOfColumn1 = ListUtils.newArrayList();
        headOfColumn1.add("性别");
        headOfColumn1.add("性别");

        /// 第 2,3,4 列,在第 1 行的值相同,都是[成绩],自动合并
        List<String> headOfColumn2 = ListUtils.newArrayList();
        headOfColumn2.add("成绩");
        headOfColumn2.add("语文");

        List<String> headOfColumn3 = ListUtils.newArrayList();
        headOfColumn3.add("成绩");
        headOfColumn3.add("数学");

        List<String> headOfColumn4 = ListUtils.newArrayList();
        headOfColumn4.add("成绩");
        headOfColumn4.add("英语");

        headOfColumns.add(headOfColumn0);
        headOfColumns.add(headOfColumn1);
        headOfColumns.add(headOfColumn2);
        headOfColumns.add(headOfColumn3);
        headOfColumns.add(headOfColumn4);

        return headOfColumns;
    }

    private List<List<String>> getRows() {
        List<List<String>> rows = ListUtils.newArrayList();

        // 第 0 行数据
        List<String> row0 = ListUtils.newArrayList();
        row0.add("andy");
        row0.add("male");
        row0.add("80");
        row0.add("90");
        row0.add("100");

        // 第 1 行数据
        List<String> row1 = ListUtils.newArrayList();
        row1.add("sara");
        row1.add("female");
        row1.add("100");
        row1.add(null);
        row1.add("100");

        rows.add(row0);
        rows.add(row1);

        return rows;
    }

``

@Test
    public void noModelMultiSheetRead() {
        String fileName = FILE_DIR + File.separator + "demo1.xlsx";
        try (ExcelReader excelReader = EasyExcel.read(fileName).build()) {
            ReadSheet readSheet1 = EasyExcel.readSheet(0).registerReadListener(new NoModelDataListener()).build();
            // 不同 sheet 一般使用不同的 listener,这里为方便,使用同一个
            ReadSheet readSheet2 = EasyExcel.readSheet(1).registerReadListener(new NoModelDataListener()).build();
            excelReader.read(readSheet1, readSheet2);
        }
    }

import lombok.Getter;

import java.util.*;

public class CollectionUtils {

    // ElementComparator 参考了 Comparator 中的 compare 方法
    //
    //    @FunctionalInterface
    //    public interface Comparator<T> {
    //        int compare(T o1, T o2);
    //    }
    //
    // compare 方法的返回值:
    //    o1 < o2 --> a negative integer
    //    o1 = o2 --> zero
    //    o1 > o2 --> a positive integer
    //
    // 而比较是否是同一个元素时,只关心是否相等,并不关心谁大谁小,因此自定义了一个比较器接口,与直接使用 Comparator 相比,更优雅一点
    @FunctionalInterface
    public interface ElementComparator<T> {
        boolean isEqual(T o1, T o2);
    }

    /**
     * 比较新旧集合中的元素差异
     * 使用场景: 批量更新
     * 方式-1: 先全部删除,再新增.【实现简单】
     * ------- 很多时候,删除操作不是物理删除,而是软删除,在此场景下,先全部删除,会产量大量被标识为已删除的数据
     * ------- 若 id 是关联字段,还需要重新处理关联关系
     * ------- 不会记录更新人,只有创建人
     * 方式-2: 比较新数据和旧数据的差异,分别处理.【实现复杂】
     * ------- 软删除场景不会产生大量被标识为已删除的数据
     *
     * @param newElements 新集合(一般来源于前端)
     * @param oldElements 旧集合(一般来源于数据库)
     * @param comparator  定义集合元素比较规则
     * @return
     */
    public static <T> CompareResult<T> compare(List<T> newElements, List<T> oldElements, ElementComparator<T> comparator) {
        List<T> duplicatedElementsFromNew = new ArrayList<>();
        List<T> duplicatedElementsFromOld = new ArrayList<>();
        Iterator<T> newIterator = newElements.iterator();
        while (newIterator.hasNext()) {
            T newObj = newIterator.next();
            Iterator<T> oldIterator = oldElements.iterator();
            while (oldIterator.hasNext()) {
                T oldObj = oldIterator.next();
                if (comparator.isEqual(newObj, oldObj)) {
                    duplicatedElementsFromNew.add(newObj);
                    duplicatedElementsFromOld.add(oldObj);
                    try {
                        oldIterator.remove();
                        newIterator.remove();
                    } catch (UnsupportedOperationException e) {
                        throw new RuntimeException("请使用可变集合");
                    }
                }
            }
        }
        return new CompareResult<>(newElements, oldElements, duplicatedElementsFromNew, duplicatedElementsFromOld);
    }

    @Getter
    public static class CompareResult<T> {
        /**
         * 新集合中独有的元素,该集合一般用于【新增】操作
         */
        private final List<T> uniqueElementsFromNew;
        /**
         * 旧集合中独有的元素,该集合一般用于【删除】操作
         */
        private final List<T> uniqueElementsFromOld;
        /**
         * 新旧集合中都存在的元素(元素来自新集合),该集合一般用于【更新】操作
         * Comparator 一般根据 id 等字段判断是否为相同的元素,对于新旧集合中的相同元素,id 虽然没变,但是其他字段可能改变了,因此可用该集合做更新操作
         */
        private final List<T> duplicatedElementsFromNew;
        /**
         * 新旧集合中都存在的元素(元素来自旧集合)
         */
        private final List<T> duplicatedElementsFromOld;

        public CompareResult(List<T> uniqueElementsFromNew, List<T> uniqueElementsFromOld, List<T> duplicatedElementsFromNew, List<T> duplicatedElementsFromOld) {
            this.uniqueElementsFromNew = uniqueElementsFromNew;
            this.uniqueElementsFromOld = uniqueElementsFromOld;
            this.duplicatedElementsFromNew = duplicatedElementsFromNew;
            this.duplicatedElementsFromOld = duplicatedElementsFromOld;
        }
    }

    public static void main(String[] args) {

        User user1 = new User(1L, 10, "Allen");
        User user2 = new User(2L, 10, "Brian");
        User user3_1 = new User(3L, 10, "Caroline");
        User user3_2 = new User(3L, 20, "Caroline New");
        User user4 = new User(4L, 10, "David");
        User user5 = new User(5L, 10, "Elisa");

        // Arrays.asList 返回的列表是不可变集合,因此需要转为可变集合(允许动态地添加、删除或修改其元素的集合)
        List<User> newElements = new ArrayList<>(Arrays.asList(user4, user5, user3_2));
        List<User> oldElements = new ArrayList<>(Arrays.asList(user1, user2, user3_1));

        System.out.println("newElements:               " + newElements);
        System.out.println("oldElements:               " + oldElements);

        CompareResult<User> compareResult = compare(newElements, oldElements, (o1, o2) -> o1.getId().equals(o2.getId()));

        System.err.println("uniqueElementsFromNew:     " + compareResult.getUniqueElementsFromNew());
        System.err.println("uniqueElementsFromOld:     " + compareResult.getUniqueElementsFromOld());
        System.err.println("duplicatedElementsFromNew: " + compareResult.getDuplicatedElementsFromNew());
        System.err.println("duplicatedElementsFromOld: " + compareResult.getDuplicatedElementsFromOld());

    }


}
@Test
    public void complexWrite() {

        // 指定范围合并
        //OnceAbsoluteMergeStrategy secondHeadMaleMergeStrategy = new OnceAbsoluteMergeStrategy(1, 1, 1, 2);

        //// 设置表头样式
        WriteCellStyle headCellStyle = new WriteCellStyle();
        /// 字体样式
        WriteFont headWriteFont = new WriteFont();
        // 粗体
        headWriteFont.setBold(true);
        // 字体高度
        headWriteFont.setFontHeightInPoints(Short.valueOf("20"));
        headCellStyle.setWriteFont(headWriteFont);
        // 单元格填充颜色
        headCellStyle.setFillForegroundColor(GREY_25_PERCENT.index);
        // 水平居中
        headCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

        //// 设置内容样式
        WriteCellStyle contentCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setColor(RED.getIndex());
        contentCellStyle.setWriteFont(contentWriteFont);
        contentCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);

        String firstSheetName = "sheet-1";

        /// 自定义样式
        CellWriteHandler cellWriteHandler = new CellWriteHandler() {

            /**
             * 在单元格创建之后执行的操作: 遍历所有 sheet 的所有 cell
             */
            @Override
            public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                        Head head, Integer relativeRowIndex, Boolean isHead) {
                Sheet sheet = writeSheetHolder.getSheet();
                if (sheet.getSheetName().equals(firstSheetName)) {
                    int columnIndex = cell.getColumnIndex();
                    // 设置第 0 列的宽度为 20 个字符
                    if (columnIndex == 0) {
                        //Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
                        //CellStyle cellStyle = workbook.createCellStyle();
                        //cellStyle.setAlignment(HorizontalAlignment.LEFT);
                        //cell.setCellStyle(cellStyle);
                        sheet.setColumnWidth(columnIndex, 20 * 256);
                    }
                }

            }
        };

        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headCellStyle, contentCellStyle);

        String fileName = "test.xlsx";
        EasyExcel.write(fileName).head(getHeads()).sheet(firstSheetName)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .registerWriteHandler(cellWriteHandler)
                .doWrite(getRows());
    }
posted @ 2025-03-06 22:24  凛冬雪夜  阅读(29)  评论(0)    收藏  举报