springboot+EasyPoi 实现Excel的导入导出

• List item

springboot+ElementUI+EasyPOI的导入导出

转载自：https://www.jianshu.com/p/5d67fb720ece

官 网：http://easypoi.mydoc.io/

依赖：

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>

JavaBean：

private Long id;// 学生id
@Excel(name = "姓名", orderNum = "0")
private String name;// 学生名称
@Excel(name = "性别", replace = {"男_1", "女_2"}, orderNum = "1")
private Integer sex;//性别，1男，2女
@Excel(name = "年龄", orderNum = "2")
private Integer age;//年龄
@Excel(name = "体重", orderNum = "3")
private Double weight;//体重
@Excel(name = "爱好",orderNum = "4")
private String hobby;//爱好
private String img;//图片
@DateTimeFormat(pattern = "yyyy-MM-dd")
@JsonFormat(shape = JsonFormat.Shape.STRING, pattern ="yyyy-MM-dd", timezone = "GMT+8")
@Excel(name = "出生日期",width = 20,exportFormat = "yyyy-MM-dd",importFormat = "yyyy-MM-dd" ,orderNum = "5")
private Date birthday;//出生日期

@Excel(name = "班级", replace = {"一班_1", "二班_2"}, orderNum = "6")
private Long gradeId;// 班级id
private String gradeName;//  班级名称

@Excel(name = "地区", replace = {"河南_1", "河北_2"}, orderNum = "7")
public Long areaId;// 地区id
public String areaName;//  地区名称

//生成有参无参构造
public Student(Long id, String name, Integer sex, Integer age, Double weight, String hobby, Date birthday, String img, Long gradeId, String gradeName, Long areaId, String areaName) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.weight = weight;
        this.hobby = hobby;
        this.birthday = birthday;
        this.img = img;
        this.gradeId = gradeId;
        this.gradeName = gradeName;
        this.areaId = areaId;
        this.areaName = areaName;
    }

    public Student() {
    }

公共的导入导出方法

package com.fh.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.fh.exception.NormalException;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

public class FileUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            throw new NormalException(e.getMessage());
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null);
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new NormalException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new NormalException(e.getMessage());
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            throw new NormalException("excel文件不能为空");
        } catch (Exception e) {
            throw new NormalException(e.getMessage());
        }
        return list;
    }
}

NormalException异常

转自：https://gitee.com/QuanZhanZhiLu/easy-boot

package com.fh.exception;

public class NormalException extends RuntimeException{

   private static final long serialVersionUID = 1L;

   public NormalException(String message) {
        super(message);
    }

}

项目中的使用

Controller层

//导出
@RequestMapping("export")
public void export(HttpServletResponse response){
    //测试
    /* List<Student> studentList = new ArrayList<>();
    Student student1 = new Student("小明1",41,31);
    Student student2 = new Student("小明2",42,32);
    Student student3 = new Student("小明3",43,33);
    Student student4 = new Student("小明4",44,34);
    studentList.add(student1);
    studentList.add(student2);
    studentList.add(student3);
    studentList.add(student4);*/
    
    //去数据库中查询数据 返回一个list集合
    List<Student> studentList = service.queryList();
    FileUtil.exportExcel(studentList,"表名","工作表标签名称",Student.class,"文件名.xls",response);
    }

    //导入
	//从前端获取文件路径 file
    @RequestMapping("importExcel")
    public void importExcel(@RequestParam("file") MultipartFile file){
        //String file = "F:\\文件名.xls";
        //解析excel，
        List<Student> list = FileUtil.importExcel(file,1,1,Student.class);
        //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
        System.out.println("导入数据一共【"+list.size()+"】行");
        //保存到数据库中
        service.insertBatch(list);
    }

Service层

@Override
public void insertBatch(List<Student> list) {
    for (int i = 0; i <list.size() ; i++) {
        Student student = new Student();
        student=list.get(i);
        studentMapper.addData(student);
        System.out.println(list.get(i));
    }
}

前端

前端采用的vue+Element-UI

<el-button type="danger" round @click="exportExcel">导出Excel</el-button>

//导出Excel
exportExcel(){
    location.href="http://localhost:80/studentController/export"
},

//导入
    <el-form-item label="" prop="resource">
          <el-col :span="12">
            <el-upload
              class="upload-demo"
              action="http://localhost:80/studentController/importExcel"
              list-type="picture">
              <el-button size="small" type="primary">点击上传Excel</el-button>
            </el-upload>
          </el-col>
        </el-form-item>
// action是上传的文件路径

---

侵删