easyExcell

easyexcell准备工作

用到的实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    private String name;
    private int age;
    private int weight;
    private String address;
}

导入相关依赖，此处只给easyexcell的依赖

 <!-- easyexcel依赖 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.0.5</version>
        </dependency>

导出测试

    @Test
    void test11() {
        Teacher t1 = new Teacher("1", 11, 22, "zz");
        Teacher t2 = new Teacher("2", 22, 33, "aa");
        Teacher t3 = new Teacher("3", 33, 25, "vv");
        Teacher t4 = new Teacher("4", 28, 11, "aa");
        Teacher t5 = new Teacher("1", 22, 11, "zz");
        Teacher t6 = new Teacher("1", 22, 11, "zz");
        //拿到的数据（真实情况是从db拿数据）
        ArrayList<Teacher> teachers = Lists.newArrayList(t1, t2, t3, t4, t5, t6);

        //1.xlsx为导出文件名字，应封装传参进行设置
        EasyExcel.write("D://1.xlsx")
                //excell表头，即实体类的属性名，在实体类注解定义的
                .head(Teacher.class)
                //导出格式，xlsx为07版excell文件后缀
                .excelType(ExcelTypeEnum.XLSX)
                //设置excell第一页sheet名称
                .sheet("老师信息")
        //设置标题，合并头行单元格，此处没用到
//              .registerWriteHandler(自定义处理器)
        //设置写信息从第几行开始
//              .relativeHeadRowIndex(2)
                //自定义表格策略
                .registerWriteHandler(createTableStyle())
                //设置列宽
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                //设置表头行高，内容行高
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)20,(short)20))
                //导出数据源
                .doWrite(teachers);
    }


    /**
     * 自定义表格策略
     */
    public static WriteHandler createTableStyle() {
        // 头的策略*************************************
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景
        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 表头边框:底边框,右边框,左边框,顶边框
        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        headWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 表头边框颜色:底边框颜色,右边框颜色,左边框颜色,顶边框颜色
        headWriteCellStyle.setBottomBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        headWriteCellStyle.setRightBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        headWriteCellStyle.setLeftBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        headWriteCellStyle.setTopBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        // 字体策略*************************************
        WriteFont headWriteFont = new WriteFont();
        // 表头字体大小
        headWriteFont.setFontHeightInPoints((short) 10);
        // 字体样式
        headWriteFont.setFontName("等线");
        // 默认关闭字体加粗 false
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景颜色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.index);
        // 内容策略*************************************
        WriteFont contentWriteFont = new WriteFont();
        // 内容 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        // 内容 字体样式
        contentWriteFont.setFontName("等线");
        // 内容 默认关闭字体加粗 false
        contentWriteFont.setBold(false);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 内容 水平对齐居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        // 内容 垂直对齐居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 边框样式*************************************
        // 底边框,右边框,左边框,顶边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        // 底边框颜色,右边框颜色,左边框颜色,顶边框颜色
        contentWriteCellStyle.setBottomBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        contentWriteCellStyle.setRightBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        contentWriteCellStyle.setLeftBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        contentWriteCellStyle.setTopBorderColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index);
        // 自动换行 默认关闭 false
        contentWriteCellStyle.setWrapped(false);
        // 文本收缩至合适 默认关闭 false
        contentWriteCellStyle.setShrinkToFit(false);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return horizontalCellStyleStrategy;
    }

导入测试

@Service
public class TeacherImport {

    public static void test() throws FileNotFoundException {
        //每一条数据的载体
        Teacher teacher = new Teacher();
        //拿到文件
        File file = new File("D://1.xlsx");
        //获取文件输入流
        FileInputStream fo = new FileInputStream(file);
        //读取文件
        ExcelReader build = EasyExcelFactory.read(fo)
                //按照实体类去读
                .head(Teacher.class)
                //导入需要自定义监听器
                .registerReadListener(new EasyExcelReadUtils.TeacherListener(teacher))
                .build();

        //读取第一个sheet
        ReadSheet readSheet = EasyExcelFactory.readSheet(0).build();
        build.read(readSheet);
    }

    public void insertMatnrGroup(List<Teacher> teacherList, Teacher teacher, Set<String> matnrSet, Map<String, String> errorFieldNameMap) {
        //业务处理,一般就是存到数据库，这里就直接打印了
        teacherList.stream().forEach(item->{
            System.out.println(item.toString());
        });
    }
}

其中用到的自定义监听器

package com.example.utils;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.metadata.data.ReadCellData;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.example.entity.Teacher;
import com.example.examples.TeacherImport;
import com.google.common.collect.Sets;

import javax.annotation.Resource;
import java.util.*;

public class EasyExcelReadUtils {

    /**
     * 组合零件维护监听器 matnrGroup
     */
    public static class TeacherListener implements ReadListener<Teacher> {

        @Resource
        private TeacherImport excelBll = SpringUtil.getBean(TeacherImport.class);

        /**
         * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
         */
        private static final int BATCH_COUNT = 100;
        /**
         * 缓存的数据
         */
        private List<Teacher> teacherList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        //全局set(业务需要，可不写)
        private Set<String> matnrSet = Sets.newHashSet();
        //存储异常信息（同上）
        Map<String, String> errorFieldNameMap = new HashMap<>(16);
        /**
         *
         */
        private Teacher teacher;

        /**
         * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
         *
         * @param
         */
        public TeacherListener(Teacher teacher) {
            this.teacher = teacher;
        }


        @Override
        public void invokeHead(Map<Integer, ReadCellData<?>> headMap, AnalysisContext context) {
//            List<String> list=new ArrayList<>();
//            list.add(SysConstants.MATNR_FIELD);
//            templateValid(headMap,list);
        }

        /**
         * 这个每一条数据解析都会来调用
         *
         * @param teacher    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
         * @param context
         */
        @Override
        public void invoke(Teacher teacher, AnalysisContext context) {
            teacherList.add(teacher);

            // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
            if (teacherList.size() >= BATCH_COUNT) {
                saveData();
                // 存储完成清理 list
                teacherList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
            }
        }

        /**
         * 所有数据解析完成了 都会来调用
         *
         * @param context
         */
        @Override
        public void doAfterAllAnalysed(AnalysisContext context) {
            // 这里也要保存数据，确保最后遗留的数据也存储到数据库
            saveData();
        }

        /**
         * 加上存储数据库
         */
        private void saveData() {
            excelBll.insertMatnrGroup(teacherList,teacher,matnrSet,errorFieldNameMap);
        }
    }
}

监听器中用到的SpringUtil类

package com.example.utils;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

@Component
class SpringUtil implements ApplicationContextAware {

    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringUtil.applicationContext = applicationContext;
    }

    /**
     * 通过class获取Bean
     *
     * @param clazz class
     * @param <T>   泛型
     * @return bean
     */
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }
}

测试一下

 @Test
    void test13(){
        try {
            TeacherImport.test();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        //打印结果
        //Teacher(name=1, age=11, weight=22, address=zz)
        //Teacher(name=2, age=22, weight=33, address=aa)
        //Teacher(name=3, age=33, weight=25, address=vv)
        //Teacher(name=4, age=28, weight=11, address=aa)
        //Teacher(name=1, age=22, weight=11, address=zz)
        //Teacher(name=1, age=22, weight=11, address=zz)
    }

其中要注意的是每次insert业务处理拿到的list不是全部数据，是定义的batch条数据，类似分页

多sheet导出

用到的实体类

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {

    //    @ExcelIgnore  此注释为导出时忽略本字段
//    @ColumnWidth(value = 40) 此注释可单独设置本字段单元格的宽度,高度也类似，自行百度
    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "年龄")
    private int age;

    @ExcelProperty(value = "体重")
    private int weight;

    @ExcelProperty(value = "地址")
    private String address;
}

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {

    @ExcelProperty(value = "姓名")
    private String name;

    @ExcelProperty(value = "年龄")
    private int age;

    @ExcelProperty(value = "成绩")
    private int score;

    @ExcelProperty(value = "班级")
    private String classNumber;
}

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class AllData {

    private List<Teacher> teacherList;

    private List<Student> studentsList;
}

导出测试（其中自定义策略在上面单sheet导出里）

@Test
    void test14(){
        //获得数据,真实环境应该从数据库读
        //创建一个AllData，其中两个属性分别是老师list和学生list
        AllData allData = new AllData();
        //捏造几条老师数据
        Teacher t1 = new Teacher("1", 11, 22, "zz");
        Teacher t2 = new Teacher("2", 22, 33, "aa");
        Teacher t3 = new Teacher("3", 33, 25, "vv");
        Teacher t4 = new Teacher("4", 28, 11, "aa");
        Teacher t5 = new Teacher("1", 22, 11, "zz");
        Teacher t6 = new Teacher("1", 22, 11, "zz");
        ArrayList<Teacher> teachers = Lists.newArrayList(t1, t2, t3, t4, t5, t6);
        //捏造几条学生数据
        Student s1 = new Student("11",11,342,"一班");
        Student s2 = new Student("22",22,314,"二班");
        Student s3 = new Student("33",33,421,"三班");
        Student s4 = new Student("44",44,521,"四班");
        Student s5 = new Student("55",55,123,"五班");
        Student s6 = new Student("66",66,234,"六班");
        ArrayList<Student> students = Lists.newArrayList(s1, s2, s3, s4, s5, s6);
        //放进alldata里面
        allData.setTeacherList(teachers);
        allData.setStudentsList(students);

        //1.xlsx为导出文件名字，应封装传参进行设置
        ExcelWriter excelWriter = EasyExcel.write("D://2.xlsx")
                //导出格式，xlsx为07版excell文件后缀
                .excelType(ExcelTypeEnum.XLSX)
                //设置写信息从第几行开始
                .relativeHeadRowIndex(1)
                //自定义表格策略
                .registerWriteHandler(createTableStyle())
                //设置列宽
                .registerWriteHandler(new SimpleColumnWidthStyleStrategy(20))
                //设置表头行高，内容行高
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short) 20, (short) 20))
                .build();

        excelWriter.write(allData.getTeacherList(), EasyExcel.writerSheet(0,"老师信息").head(Teacher.class).build())
                .write(allData.getStudentsList(), EasyExcel.writerSheet(1,"学生信息").head(Student.class).build());
        excelWriter.finish();
    }

效果