EasyExcel组件

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

github地址：

坐标：

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

Bean:

读Excel文件，案例：

文件内容：

代码案例：

 package com.what21.easyexcel.demo;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.fastjson.JSON;
import com.what21.easyexcel.model.Student;

import java.util.ArrayList;
import java.util.List;

public class EasyExcelXlsxDemo {

    public static void main(String[] args) {
        // 读取文件（xls）
        // String fileName = "D:/demo.xls";
        // 读取文件（xlsx）
        String fileName = "D:/demo.xlsx";
        
        // 接收解析出的目标对象
        List<Student> studentList = new ArrayList<>();
        // 这里需要指定读用哪个class去读，然后读取第一个sheet文件流会自动关闭
        // excel中表的列要与对象的字段相对应
        EasyExcel.read(fileName, Student.class, new AnalysisEventListener<Student>() {

            @Override
            public void invoke(Student student, AnalysisContext analysisContext) {
                // 每解析一条数据都会调用该方法
                System.out.println("Student对象：" + JSON.toJSONString(student));
                studentList.add(student);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext analysisContext) {
                // 解析完毕的回调方法
                System.out.println("excel文件读取完毕！");
            }

        }).sheet().doRead();

        // 遍历访问
        for (Student student : studentList) {
            System.out.println(student);
        }
    }

}
  

写Excel文件，案例：

代码案例：

 package com.what21.easyexcel.demo;

import com.alibaba.excel.EasyExcel;
import com.what21.easyexcel.model.Student;

import java.util.ArrayList;
import java.util.List;

public class EasyExcelWriteDemo {

    public static void main(String[] args) {
        List<Student> dataList = new ArrayList<Student>();
        for (int i = 1; i <= 10; i++) {
            Student student = new Student();
            student.setSn(i);
            student.setStuName("name" + i);
            student.setAge(i + 20);
            student.setAddress("地址" + i);
            student.setMoney((float) i);
            dataList.add(student);
        }
        String fileName = "d://write.xlsx";
        EasyExcel.write(fileName, Student.class).sheet("写入内容").doWrite(dataList);

    }

}
  

文件内容：

填充Excel文件，案例：

文件模板：

代码案例：

 package com.what21.easyexcel.demo;

import com.alibaba.excel.EasyExcel;
import lombok.Data;

import java.util.HashMap;
import java.util.Map;

/**
 * 填充
 */public class EasyExcelFillDemo {

    public static void main(String[] args) {
        String templateFileName = "D:/template.xlsx";
        // 根据模板填充内容并输出
        String fileName = "D:/simple.1.xlsx";
        FillData fillData = new FillData();
        fillData.setId(1001);
        fillData.setName("名字");
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet(0).doFill(fillData);
        // 根据模板填充内容并输出
        String fileName2 = "D:/simple.2.xlsx";
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name", "张三");
        map.put("id", 1002);
        EasyExcel.write(fileName2).withTemplate(templateFileName).sheet().doFill(map);
    }

    @Data
    public static class FillData {
        private int id;
        private String name;
    }

}
  

下载Excel文件，案例

代码案例：

 package com.what21.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.what21.easyexcel.model.Student;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/download")
public class DownloadController {

    @GetMapping("/demo1")
    public void demo(HttpServletResponse response) throws IOException {
        // ==========================================================================//
        // ====HTTP头设置
        // ==========================================================================//
        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/vnd.ms-excel");
        String fileName = "测试下载";
        fileName = URLEncoder.encode(fileName, "UTF-8");
        fileName = fileName.replaceAll("\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        // ==========================================================================//
        // ====数据
        // ==========================================================================//
        List<Student> dataList = new ArrayList<Student>();
        for (int i = 1; i <= 10; i++) {
            Student student = new Student();
            student.setSn(i);
            student.setStuName("name" + i);
            student.setAge(i + 20);
            student.setAddress("地址" + i);
            student.setMoney((float) i);
            dataList.add(student);
        }
        // ==========================================================================//
        // ====下载输出
        // ==========================================================================//
        String sheetName = "学生列表";
        EasyExcel.write(response.getOutputStream(), Student.class).sheet(sheetName).doWrite(dataList);
    }

}
  

启动代码：

 package com.what21.easyexcel;

import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceTransactionManagerAutoConfiguration;
import org.springframework.boot.autoconfigure.orm.jpa.HibernateJpaAutoConfiguration;
import org.springframework.boot.autoconfigure.security.servlet.SecurityAutoConfiguration;

@SpringBootApplication(exclude = {
        DataSourceAutoConfiguration.class,
        DataSourceTransactionManagerAutoConfiguration.class,
        DruidDataSourceAutoConfigure.class,
        HibernateJpaAutoConfiguration.class,
        SecurityAutoConfiguration.class},
        scanBasePackages = {"com.what21.easyexcel"})
public class EasyExcelApplication {

    public static void main(String[] args) {
        SpringApplication.run(EasyExcelApplication.class, args);
    }

}