时间戳

首页 > 技术文章 > SpringBoot+easyPoi实现导入导出

SpringBoot+easyPoi实现导入导出

thcy1314 2018-08-28 17:08 原文

一.pom引入

<!--easypoi导出excel start-->
<!--easypoi-base 导入导出的工具包,可以完成Excel导出,导入,Word的导出,Excel的导出功能-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.3</version>
</dependency>
<!--easypoi-web  耦合了spring-mvc 基于AbstractView,极大的简化spring-mvc下的导出功能-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>3.0.3</version>
</dependency>
<!--easypoi-annotation 基础注解包,作用与实体对象上,拆分后方便maven多工程的依赖管理-->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.3</version>
</dependency>
<!--easypoi导出excel end-->
<!-- 文件上传 start-->
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.1</version>
</dependency>
<!-- 文件上传 end-->

二.编写实体类（easyPoi注解看官网 http://easypoi.mydoc.io/）

  • 此处注意必须要有空构造函数，否则会报错“对象创建错误”
  • 关于注解@Excel，其他还有@ExcelCollection，@ExcelEntity ，@ExcelIgnore，@ExcelTarget等，此处我们用不到，可以去官方查看更多
package com.lyancafe.material.model;

import cn.afterturn.easypoi.excel.annotation.Excel;

/**
 * @author 孙创业
 */
public class UserModel {
    @Excel(name = "用户Id", orderNum = "0")
    private int  id;
    @Excel(name = "用户名", orderNum = "1")
    private String userName;
    @Excel(name = "密码", orderNum = "2")
    private String password;
    @Excel(name = "姓名", orderNum = "3")
    private String realName;
    @Excel(name = "电话", orderNum = "4")
    private String tel;
    @Excel(name = "年龄", orderNum = "5")
    private int age;
    @Excel(name = "地址", orderNum = "6")
    private String address;

    public UserModel() {
    }
    public UserModel(int id) {
        this.id = id;
    }
    public UserModel(int id, String userName, String password, String realName, String tel, int age, String address) {
        this.id = id;
        this.userName = userName;
        this.password = password;
        this.realName = realName;
        this.tel = tel;
        this.age = age;
        this.address = address;
    }

    public String getTel() {
        return tel;
    }

    public void setTel(String tel) {
        this.tel = tel;
    }

    public int getId() {
        return id;
    }

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

    public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName = userName;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getRealName() {
        return realName;
    }

    public void setRealName(String realName) {
        this.realName = realName;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    @Override
    public String toString() {
        return "UserModel{" +
                "id=" + id +
                ", userName='" + userName + '\'' +
                ", password='" + password + '\'' +
                ", realName='" + realName + '\'' +
                ", tel='" + tel + '\'' +
                ", age=" + age +
                ", address='" + address + '\'' +
                '}';
    }
}

三.写FileUtil工具类（导入导出公用方法）

package com.lyancafe.material.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 org.apache.commons.lang.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;

/**
 * @author scy 2018/8/26
 */
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;
    }
}

四.Controller中写导入导出的方法

/**
 * SpringBoot+easyPoi一行代码搞定excel导出
 * @return
 */
@RequestMapping("export")
public void export(HttpServletResponse response){
    //从数据库获取需要导出的数据
    List<UserModel> userModelList =userBo.getExportDatas();
    //导出操作
    FileUtil.exportExcel(userModelList,"用户信息","用户制作scy",UserModel.class,"用户.xls",response);
}

/**
 * SpringBoot+easyPoi一行代码搞定excel导入
 * @return
 */
@RequestMapping("importExcel")
public String importExcel(){
    String filePath = "F:\\用户.xls";
    //解析excel，
    List<UserModel> userModelList= FileUtil.importExcel(filePath,1,1,UserModel.class);
    //也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)导入
    System.out.println("导入数据一共【"+userModelList.size()+"】行");

    //TODO 保存数据库.是个集合需要遍历
    for(UserModel userModelLists : userModelList) {
        String userName = userModelLists.getUserName();
        String password = userModelLists.getPassword();
        //数据库查重（写个查询的方法），名字和密码重复就不插入，否则插入
        UserModel byNameAndPassword = userBo.findByNameAndPassword(userName, password);
        if (byNameAndPassword==null){
            userBo.saveImport(userModelLists);
        }
    }
    return "redirect:/user/getAllUser";
}

五.ServiceImpl(我写的boImpl)层

/**
 * SpringBoot+easyPoi一行代码搞定excel导入导出
 */
@Override
public List<UserModel> getExportDatas(){
    List<UserModel> userModels = userDao.getExportDatas();
    return userModels;
}

/**
 * 保存导入的用户
 */
@Override
public void saveImport(UserModel userModel){
    userDao.saveImport(userModel);
}

六.service(我写的bo)层

/**
 * SpringBoot+easyPoi一行代码搞定excel导入导出
 * @return
 */
List<UserModel> getExportDatas();

/**
 * 保存导入的用户
 * @param userModel
 * @return
 */
void saveImport(UserModel userModel);

七.dao层


/**
 * SpringBoot+easyPoi一行代码搞定excel导入导出
 * @return
 */
List<UserModel> getExportDatas();

/**
 * 保存导入的用户
 * @param userModel
 * @return
 */
void saveImport(UserModel userModel);

八.dao.xml层

<!--SpringBoot+easyPoi一行代码搞定excel导入导出-->
<select id="getExportDatas"  resultType="com.lyancafe.material.model.UserModel">
    select * from t_user
</select>

<!--保存导入的用户-->
<insert id="saveImport" parameterType="com.lyancafe.material.model.UserModel">
    insert into t_user(userName, age, realName, tel, address)
    values(#{userName}, #{age}, #{realName},#{tel},#{address})
</insert>

九.allUser.jsp层（页面只需要写这两句）

<div><a href="<%=basePath%>user/export">导出用户信息</a></div>
<div><a href="<%=basePath%>user/importExcel">导入用户信息</a></div>

十.导入导出的页面展示

 

十一.导出的模板展示

 

有什么不懂得可以问我，如果恢复不及时加QQ：501397578

 

推荐阅读