14、SpringBoot-easyexcel导入-导出excle

系列导航

完结

本文主要描述，springboot如何下载excle，上传解析excle，以及把上传excle中错误的数据在返回给用户一个并告知数据哪里错误这样一个功能。

1、pom.xml文件

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

2、配置文件

# 应用名称
spring.application.name=demo
# 应用服务 WEB 访问端口
server.port=8080

3、工程结构

4、源码

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

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

}

package com.example.demo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.example.demo.domain.Student;
import com.example.demo.service.ExeclService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

@Controller
@RequestMapping("/excle")
public class ExcelController {

    @Autowired
    private ExeclService execlService;

    //将数据写入到excle文档里并且下载
    @GetMapping("/down")
    @ResponseBody
    public void test(HttpServletResponse response) throws IOException {
        List<Student> list = new ArrayList<>();
        list.add(new Student("001","张三"));
        list.add(new Student("002","李四"));
        list.add(new Student("003","王五"));
        //返回输出流。格式Excel
        response.setContentType("application/vnd.ms-excel");
        //设置表名称
        String name = "学生信息表";
        try {
            //设置编码格式
            name = URLEncoder.encode(name, StandardCharsets.UTF_8.toString());
            // 设置输出名字  attachment附件
            response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");

            //将输出流封装到excelWriter
            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();

            //定义工作表对象
            WriteSheet sheet = EasyExcel.writerSheet(0,"sheet").head(Student.class).build();
            //sheet.setTableStyle();
            //往excel写入
            excelWriter.write(list,sheet);
            //关闭输出流
            excelWriter.finish();

        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }

    }

    //上传excle并解析
    @PostMapping("/upload")
    @ResponseBody
    public void upload(@RequestParam("file") MultipartFile multipartFile, HttpServletResponse response) throws IOException {
         execlService.upFile(multipartFile);
    }


    //上传excle并解析，而且将有错误的数据返回给用户
    @PostMapping("/uploadAndReturnError")
    @ResponseBody
    public void uploadAndReturnError(@RequestParam("file") MultipartFile multipartFile, HttpServletResponse response) throws IOException {
        String result = execlService.upFileAndReturn(multipartFile,response);
        System.out.println(result);
    }
}

package com.example.demo.domain;


public class Employee {

    private String noId;

    private String empName;

    private Integer empAge;

    private String text;

    public String getNoId() {
        return noId;
    }

    public void setNoId(String noId) {
        this.noId = noId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getEmpAge() {
        return empAge;
    }

    public void setEmpAge(Integer empAge) {
        this.empAge = empAge;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "noId='" + noId + '\'' +
                ", empName='" + empName + '\'' +
                ", empAge=" + empAge +
                ", text='" + text + '\'' +
                '}';
    }
}

package com.example.demo.domain;

import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;

@ContentRowHeight(15)   // 文本行高度
@HeadRowHeight(20)      // 标题高度
@ColumnWidth(25)        // 默认列宽度
public class EmployeeOther {
    @ExcelProperty("员工id")
    private String noId;

    @ExcelProperty("员工姓名")
    private String empName;

    @ExcelProperty("员工年龄")
    private Integer empAge;

    @ExcelProperty("错误提示")
    private String text;

    public String getNoId() {
        return noId;
    }

    public void setNoId(String noId) {
        this.noId = noId;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public Integer getEmpAge() {
        return empAge;
    }

    public void setEmpAge(Integer empAge) {
        this.empAge = empAge;
    }

    public String getText() {
        return text;
    }

    public void setText(String text) {
        this.text = text;
    }

    @Override
    public String toString() {
        return "EmployeeOther{" +
                "noId='" + noId + '\'' +
                ", empName='" + empName + '\'' +
                ", empAge=" + empAge +
                ", text='" + text + '\'' +
                '}';
    }
}

package com.example.demo.domain;

 
import com.alibaba.excel.annotation.ExcelProperty;
 


public class Student {

    @ExcelProperty("学号")//指定字段名字
    //@ExcelIgnore//忽略该字段
    private String id;

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

    public Student( String id,String name ) {
        this.id = id;
        this.name = name;
    }

    public Student() {
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    @Override
    public String toString() {
        return "Studen{" +
                "name='" + name + '\'' +
                ", id='" + id + '\'' +
                '}';
    }
}

package com.example.demo.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

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

public class ReadListener extends AnalysisEventListener {

    private static List<Object> list = new ArrayList<>();
    // 每读一行，会调用该invoke方法一次
    @Override
    public void invoke(Object data, AnalysisContext analysisContext) {
        list.add(data);
        System.out.println("解析到一条数据：" + data);

    }

    // 全部读完之后，会调用该方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("全部解析完成");
    }

    /**
     * 返回读取到的数据集合
     *
     */
    public static List<Object> getList() {
        return list;
    }

}

package com.example.demo.service;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.example.demo.domain.Employee;
import com.example.demo.domain.EmployeeOther;
import com.example.demo.listener.ReadListener;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

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

@Component
public class ExeclService {


    //解析上传的excle数据
    public void upFile(MultipartFile multipartFile) throws IOException {

        //headRowNumber(1)意思是第一行是表头
        ExcelReaderBuilder workBook = EasyExcel.read(multipartFile.getInputStream(), Employee.class, new ReadListener()).headRowNumber(1);
        // 封装工作表
        ExcelReaderSheetBuilder sheet1 = workBook.sheet();
        // 读取
        sheet1.doRead();

        List<Object> list = ReadListener.getList();
        int size = list.size();

        for (int i = 0; i < list.size(); i++) {
            Employee employee = (Employee) list.get(i);

            String noId = employee.getNoId();
            String empName = employee.getEmpName();
            Integer empAge = employee.getEmpAge();
            String text = employee.getText();

            System.out.println("noId:" + noId);
            System.out.println("empName:" + empName);
            System.out.println("empAge:" + empAge);
            System.out.println("text:" + text);


        }
    }

    //解析上传的excle数据，并将错误数据返回给用户
    public String upFileAndReturn(MultipartFile multipartFile, HttpServletResponse response) throws IOException {
        //导入正确的数据
        int correctNum = 0;
        //导入错误的数据
        int wrongNum = 0;

        //headRowNumber(1)意思是第一行是表头
        ExcelReaderBuilder workBook = EasyExcel.read(multipartFile.getInputStream(), EmployeeOther.class, new ReadListener()).headRowNumber(1);
        // 封装工作表
        ExcelReaderSheetBuilder sheet1 = workBook.sheet();
        // 读取
        sheet1.doRead();

        //获取读取的数据
        List<Object> list = ReadListener.getList();

        int size = list.size();
        //错误数据里列表
        List<EmployeeOther>  wrongList = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            EmployeeOther employeeOther = (EmployeeOther) list.get(i);

            String noId = employeeOther.getNoId();
            String empName = employeeOther.getEmpName();
            Integer empAge = employeeOther.getEmpAge();
            String text = employeeOther.getText();

            //设置noId 等于1或者2 是错误数据
            if ("1".equals(noId)||"2".equals(noId) ){
                employeeOther.setText("该数据错误");
                wrongList.add(employeeOther);
                wrongNum++;
            }else {
                System.out.println("noId:" + noId);
                System.out.println("empName:" + empName);
                System.out.println("empAge:" + empAge);
                System.out.println("text:" + text);
                correctNum++;
            }
        }
        //清理list里的数据，不清理下一次之前导入的数据依然存在
        list.clear();
        //将错误信息导出
        if(wrongList.size()!=0) {
            String name = "errorDataList";

            //生成错误数据的返回文件
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");// 设置文本内省
            response.setCharacterEncoding("utf-8");// 设置字符编码
            name = URLEncoder.encode(name, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-disposition", "attachment;filename="+name+".xlsx"); // 设置响应头
            EasyExcel.write(response.getOutputStream(), EmployeeOther.class).sheet(name).doWrite(wrongList); //用io流来写入数据
            response.getOutputStream().flush();

        }
        System.out.println("导入成功数据" + correctNum + "条，错误数据" + wrongNum + "条，错误数据请下载修改后重新上传!");
        return "导入成功数据" + correctNum + "条，错误数据" + wrongNum + "条，错误数据请下载修改后重新上传!";
    }
}