springboot整合Excel导入导出

目录

引入依赖

读Excel

excel文件 

编写导出数据的实体

输出 

读取Excel文件

写Excel

编写导出数据的实体

准备数据并写入到文件

导出Excel文件

文件上传

导入依赖

 controller

 监听器

实现类StudentServiceImpl

文件下载

controller

---

引入依赖

<!-- EasyExcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.2.6</version>
</dependency>
<!-- lombok 优雅编程 -->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.10</version>
</dependency>
<!-- junit -->
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>

读Excel

excel文件 

编写导出数据的实体

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

import java.util.Date;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    /**
     * 学生姓名
     */
    @ExcelProperty("学生姓名")
    private String name;
    /**
     * 学生性别
     */
    @ExcelProperty("学生性别")
    private String gender;

    /**
     * 学生出生日期
     */
    @ExcelProperty("学生出生日期")
    private Date birthday;
    /**
     * id
     */
    private String id;
}

输出 

读取Excel文件

调用EasyExcel的API读取的Excel文件的测试类StudentReadDemo

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.example.demo.component.StudentReadListener;
import com.example.demo.model.Student;


import java.io.FileNotFoundException;


public class StudentReadDemo {
    public static void main(String[] args) throws FileNotFoundException {
        // 读取文件，读取完之后会自动关闭
        /*
        	head			每行数据对应的实体；Student.class
        	readListener	读监听器，每读一样就会调用一次该监听器的invoke方法
        
        	sheet方法参数： 工作表的顺序号（从0开始）或者工作表的名字，不传默认为0
        */
        // 封装工作簿对象
        ExcelReaderBuilder workBook = EasyExcel.read("src/main/resources/excel/student.xlsx", Student.class, new StudentReadListener());

        // 封装工作表
        ExcelReaderSheetBuilder sheet1 = workBook.sheet();
        // 读取
        sheet1.doRead();
    }
}

 读取Excel的监听器，用于处理读取产生的数据

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.demo.model.Student;


/**
 * @Author Vsunks.v
 * @Date 2020/3/11 23:12
 * @Description:
 */
public class StudentReadListener extends AnalysisEventListener<Student> {
    // 每读一样，会调用该invoke方法一次
    @Override
    public void invoke(Student data, AnalysisContext context) {
        System.out.println("data = " + data);
        
    }

    // 全部读完之后，会调用该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // TODO......
    }
}

写Excel

编写导出数据的实体

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;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ColumnWidth(20)
public class Student {


    /**
     * id
     */
    //@ExcelProperty(value = "编号",index = 3)
    @ExcelIgnore
    private String id;
    /**
     * 学生姓名
     */
    @ExcelProperty(value = "学生姓名", index = 0)
    //@ColumnWidth(30)
    private String name;
    /**
     * 学生性别
     */
    @ExcelProperty(value = "学生性别", index = 2)
    private String gender;

    /**
     * 学生出生日期
     */
    @ExcelProperty(value = "学生出生日期", index = 1)
    //@ColumnWidth(20)
    private Date birthday;
}

准备数据并写入到文件

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.example.demo.model.Student;


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

/**
 * @Author Vsunks.v
 * @Date 2020/3/11 23:27
 * @Description:
 */
public class StudentWriteDemo {
    public static void main(String[] args) {

        List<Student> students = initData();
        /*
            String pathName 写入文件的路径
            Class head      写入文件的对象类型
            默认写入到07的xlsx中，如果想要写入xls，可以指定类型（待验证）
         */

        ExcelWriterBuilder workBook = EasyExcel.write("src/main/resources/excel/student-temp.xlsx", Student.class);

        // sheet方法参数： 工作表的顺序号（从0开始）或者工作表的名字
        workBook.sheet().doWrite(students);
    }

    private static List<Student> initData() {
        ArrayList<Student> students = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            Student data = new Student();
            data.setName("学生" + i);
            data.setBirthday(new Date());
            if (i%2==0){
             data.setGender("女");
            }
            data.setGender("男");
            students.add(data);
        }
        return students;
    }
}

导出Excel文件

文件上传

导入依赖

<!-- SpringMVC（Spring） -->
<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring-webmvc</artifactId>
    <version>5.0.5.RELEASE</version>
</dependency>
<!-- Servlet -->
<dependency>
    <groupId>javax.servlet</groupId>
    <artifactId>javax.servlet-api</artifactId>
    <version>4.0.1</version>
    <scope>provided</scope>
</dependency>
<!-- 文件上传 -->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.4</version>
</dependency>

 controller

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.example.demo.component.StudentReadListener;
import com.example.demo.model.Student;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@Controller
public class WebUploadAndDownload {
    /**
     * 文件上传
     * 1. 编写excel中每一行对应的实体类
     * 2. 由于默认异步读取excel，所以需要逐行读取的回调监听器
     * 3. 开始读取Excel
     */
    @PostMapping("upload")
    @ResponseBody
    public String upload(MultipartFile file) throws IOException {
        ExcelReaderBuilder workBook = EasyExcel.read(file.getInputStream(), Student.class,new StudentReadListener());
        workBook.sheet().doRead();
        return "success";
    }
}

 监听器

@Component
@Scope("prototype")	// 作者要求每次读取都要使用新的Listener
public class StudentReadListener extends AnalysisEventListener<Student> {

    @Autowired
    private StudentService studentService;

    private final int BATCH_SAVE_NUM = 5;
    ArrayList<Student> students = new ArrayList<>();


    private int count = 0;

    // 每读一样，会调用该invoke方法一次
    @Override
    public void invoke(Student data, AnalysisContext context) {
        students.add(data);
        if (++count % BATCH_SAVE_NUM == 0) {
            studentService.save(students);
            students.clear();
        }
    }

    // 全部读完之后，会调用该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // TODO......
    }
}

实现类StudentServiceImpl

public interface StudentService {
    void save(ArrayList<Student> students);
}

@Service
public class StudentServiceImpl implements StudentService {
    @Override
    public void save(ArrayList<Student> students) {
        System.out.println("students in service = " + students);
    }
}

文件下载

controller

public class WebUploadAndDownload {

    /**
     * 文件下载
     * 1. 编写实体类并创建对象以便写入表格
     * 2. 设置响应参数：文件的ContentType和文件名，同时设置编码避免乱码
     * 3. 直接写，内部会调用finish方法自动关闭OutputStream
     */
    @GetMapping("download")
    public void download(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        // 防止中文乱码 
        String fileName = URLEncoder.encode("测试", "UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + fileName + ".xlsx");
        ExcelWriterBuilder workBook = EasyExcel.write(response.getOutputStream(), Student.class);

        ExcelWriterSheetBuilder sheet = workBook.sheet("模板");
        
        sheet.doWrite(initData());
    }
}