SSM框架整合EasyExcel以及写入实操

有没有觉得用java操作excel耗时费力？有没有在为读写excel时出现oom而发愁？小编在这里为哥哥们推荐一款操作excel的神器：EasyExcel。

EasyExcel是一个基于Java的简单、省内存的读写Excel的开源项目。在尽可能节约内存的情况下支持读写百M的Excel。由阿里巴巴提供。

重要的话写在前面：经小编测试，目前EasyExcel不兼容poi3.18-poi3.9这个区间段内的poi版本（闭区间），如果原来系统用的poi版本是这个区间段内的，则需要调整poi版本，不然无法使用EasyExcel

1：搭建ssm框架，整合阿里巴巴的EasyExcel

2：EasyExcel实操

搭建ssm框架，整合EasyExcel

a：新建一个maven项目，如下图所示

项目建好之后如下图所示

b：完善maven项目，搭建成ssm框架以及整合EasyExcel

如何搭建ssm框架小编就不说了，有需要整套框架代码的哥哥们可以去gitee下载，小编会在末尾提供gitee的地址。ssm框架搭建完成如下图所示

整合EasyExcel非常简单，只需要在pom文件中引入依赖

<!--alibaba.easyexcel-->
<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.1.1</version>
</dependency>

EasyExcel实操

a：根据实体写入excel

定义实体

/**
 * 学生实体
 * Created by  
 * 2021/8/19 11:00
 */
@Data
public class Student {

    @ExcelIgnore
    private String id;

    //@ExcelProperty(value = "姓名", index = 0)
    @ExcelProperty(value = {"学生信息", "姓名"}, converter =         CustomStringStringWriteConverter.class)  //字符串起前面加上"自定义："三个字
    private String name;

    //@ExcelProperty(value = "年龄", index = 1)
    @ExcelProperty(value = {"学生信息","年龄"} )
    private Integer age;

    //@ExcelProperty(value = "性别", index = 2)
    @ExcelProperty(value = {"学生信息", "性别"})
    private String sex;

    //@ExcelProperty(value = "出生", index = 3)
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")//自定义时间格式
    @ExcelProperty(value = {"学生信息", "出生"})
    private Date birthday;

    //@ExcelProperty(value = "班级", index = 4)
    @ExcelProperty(value = {"班级信息", "班级"})
    private String clazz;

    //@ExcelProperty(value = "胜率", index = 5)
    @NumberFormat("#.##%")//用百分比输出
    @ExcelProperty(value = {"比赛信息", "胜率"})
    private Double sl;

    //@ExcelProperty(value = "学校", index = 6)
    @ExcelProperty(value = {"学校信息", "学校"})
    private String school;

    public Student() {}


    public Student(String name, Integer age, String sex, Date birthday, String clazz,         Double sl, String school) {
        this.name = name;
        this.age = age;
        this.sex = sex;
        this.birthday = birthday;
        this.clazz = clazz;
        this.sl = sl;
        this.school = school;
    }
}

@ExcelProperty(value = "姓名", index = 0)，value为表头，index=0代表excel第一列数据，依此类推

@ExcelProperty(value = {"学生信息","年龄"} )，这种写法代表有多级表头，一级表头为“学生信息”，二级表头为“年龄”

@NumberFormat("#.##%")//用百分比输出

@ExcelProperty(value = {"比赛信息", "胜率"})，用百分比展示胜率

@DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")//自定义时间格式

@ExcelProperty(value = {"学生信息", "出生"})，定义出生日期格式，这里的日期必须为Date类型，不能为String

编写测试类

/**
 * easyexcel  单元测试
 * Created by  
 * 2021/8/19 14:25
 */
public class EasyexcelUtilTest {

    /**
     * 依靠实体写入excel
     * @throws ParseException
     */
    @Test
    public void simpleWrite() throws ParseException {
        //请确保文件路径存在
        String fileName = "C:\\Users\\花花很Nice\\Desktop\\easyexcel\\学生表.xlsx";
        String sheetName = "信息汇总";
        EasyexcelUtil.simpleWrite(getStudentData(), Student.class, fileName,         sheetName);
    }


    /**
     * student data
     * @return data.student
     */
    public static List<Student> getStudentData() throws ParseException {
        //设置写入集合
        List<Student> list = new ArrayList<>();
        //创建写入对象
        Student student1 = new Student("张三", 12, "男", 
        new SimpleDateFormat("yyyy-MM-dd").parse("1997-12-18"),
        "A班", 0.78, "王者白银班");
        Student student2 = new Student("李四", 13, "女",
        new SimpleDateFormat("yyyy-MM-dd").parse("2000-1-2"),
        "B班", 0.67, "王者青铜班");
        list.add(student1);
        list.add(student2);
        return list;
    }
}

效果图如下：

b：无实体（自定义数据）+ 自定义头部写入

/**
 * easyexcel  单元测试
 * Created by  
 * 2021/8/19 14:25
 */
public class EasyexcelUtilTest {
    
    /**
     * 自定义头部信息 + 无实体写入excel
     */
    @Test
    public void dynamicHeadAndNoModelWrite() {
        String fileName = "C:\\Users\\花花很Nice\\Desktop\\easyexcel\\地区街道.xlsx";
        String sheetName = "长沙的街道";
        EasyexcelUtil.dynamicHeadAndNoModelWrite(getAddressNoModelData(), getHeadData(), fileName, sheetName);
    }
    
    
    /**
     * excel动态头部
     * @return List<List<String>>
     */
    private static List<List<String>> getHeadData() {
        List<List<String>> list = new ArrayList<>();
        List<String> head0 = new ArrayList<>();
        head0.add("地区");
        head0.add("天心区");
        List<String> head1 = new ArrayList<>();
        head1.add("地区");
        head1.add("芙蓉区");
        List<String> head2 = new ArrayList<>();
        head2.add("工作地区");
        head2.add("岳麓区");
        list.add(head0);
        list.add(head1);
        list.add(head2);
        return list;
    }

    /**
     * address
     * no model data, object
     * @return list
     */
    public static List<List<Object>> getAddressNoModelData() {

        List<List<Object>> list = new ArrayList<>();

        List<Object> data1 = new ArrayList<>();
        data1.add("友谊路");
        data1.add("五一路");
        data1.add("麓谷路");
        List<Object> data2 = new ArrayList<>();
        data2.add("韶山路");
        data2.add("黄兴路");
        data2.add("梅溪路");
        list.add(data1);
        list.add(data2);
        return list;
    }

}

效果如图所示：

c：分批次写入excel（在数据量过大时，边查询边写入非常重要）

/**
 *
 * Created by  longHe
 * 2021/8/20 18:01
 */
@Slf4j
@Controller
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    /**
     * 实现分批次写入
     * @param object {pageNum:页码,pageSize:分批条数,fileName:文件名称,sheetName:页签名称}
     */
    @RequestMapping(value = "/repeatedWrite15w", method = RequestMethod.POST)
    @ResponseBody
    public ResultVO<Object> repeatedWrite15w(@RequestBody JSONObject object) {
        try {
            //获取页码，默认第一页
            int pageNum = object.get("pageNum") != null ? Integer.parseInt(object.getString("pageNum")) : 1;
            //获取分批条数，默认2000
            int pageSize = object.get("pageSize") != null ? Integer.parseInt(object.getString("pageSize")) : 20;
            //获取查询条件
            String school = object.get("school") != null ? object.get("school").toString() : "麓谷外星人小学";
            //获取excel相关条件
            String fileName = object.get("fileName") != null ? object.get("fileName").toString() : "E:\\data.xlsx";
            String sheetName = object.get("sheetName") != null ? object.get("sheetName").toString() : "sheet1";

            long start = System.nanoTime();

            Map<String, String> paramMap = new HashMap<>();
            paramMap.put("school", school);
            //第一次查询作用：获取pages
            Page<?> page = PageHelper.startPage(pageNum, pageSize, true);
            studentService.findeStudentPage(paramMap);
            //获取总页数，用于分批查询和分批生成excel
            long pages = page.getPages();

            List<List<String>> headList = null;

            //easyexcel 实现分批次写入 ---> 当查询第一页之后写入第一页，当查询完第二页之后写入第二页 .....
            ExcelWriter excelWriter = EasyExcel.write(fileName).registerWriteHandler(new                 LongestMatchColumnWidthStyleStrategy()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(headList).build();
            for(int i = 0; i < pages; i ++) {
                PageHelper.startPage(i + 1, pageSize, true);
                List<Map<String, Object>> mapList = studentService.findeStudentPage(paramMap);
                List<List<Object>> list = new ArrayList<>();
                mapList.forEach(map -> {
                    List<Object> objectList = new ArrayList<>();
                    for(String key : map.keySet()) {
                        objectList.add(map.get(key).toString());
                    }
                    list.add(objectList);
                });
                //分批次写入
                excelWriter.write(list, writeSheet);
            }
            excelWriter.finish();

            long end = System.nanoTime();

            System.out.println("生成excel共耗时：" + (end - start)/1000/1000/1000 + "秒，约：" + (end -                 start)/1000/1000/1000/60 + "分钟");

            return ResultVOUtil.success();
        } catch (Exception e) {
            log.error("生成excel失败,function=repeatedWrite15w,失败原因：{}", e.toString());
            e.printStackTrace();
            return ResultVOUtil.error();
        }

    }

}

小编今天就介绍到这里了

EasyExcel官网：https://alibaba-easyexcel.github.io/

小编的项目地址：https://gitee.com/dragon_crane/ssm-easyexcel.git