easypoi – 源码巴士

1.官网和文档介绍

1.官网地址

http://easypoi.mydoc.io/

2.介绍

介绍easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板语言(熟悉的表达式语法),完成以前复杂的写法。

2.使用

介绍部分使用，具体使用可查看官网；

1.在`pom.xml`中加入依赖

<!-- easypoi start -->
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.2.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.2.0</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.2.0</version>
    </dependency>
 <!-- easypoi end -->

2.编写实体类

在要导入导出的字段前加注解@Excel，并添加属性

@Excel注解的一些常用属性
name：列名

orderNum：第几列

width:列宽

public class MdCheliangEntity implements java.io.Serializable {
	/**主键*/
	private String id;
	/**创建人名称*/
	private String createName;
	/**创建人登录名称*/
	private String createBy;
	/**创建日期*/
	private Date createDate;
	/**更新人名称*/
	private String updateName;
	/**更新人登录名称*/
	private String updateBy;
	/**更新日期*/
	private Date updateDate;
	/**所属部门*/
	private String sysOrgCode;
	/**所属公司*/
	private String sysCompanyCode;
	/**流程状态*/
	private String bpmStatus;
	/**车牌号*/
    @Excel(name="车牌号",width=15,orderNum = "0")
	private String chepaihao;
	/**车型*/
    @Excel(name="车型",width=15,orderNum = "1")
	private String chexing;
	/**最大体积*/
    @Excel(name="最大体积",width=15,orderNum = "2")
	private String zuidatiji;
	/**载重*/
    @Excel(name="载重",width=15,orderNum = "3")
	private String zaizhong;
	/**载人数*/
    @Excel(name="载人数",width=15,orderNum = "4")
	private String zairen;
	/**准假驾照*/
    @Excel(name="准假驾照",width=15,orderNum = "5")
	private String jiazhao;
	/**是否可用*/
    @Excel(name="是否可用",width=15,orderNum = "6")
	private String zhuangtai;
	/**备注*/
    @Excel(name="备注",width=15,orderNum = "7")
	private String beizhu;
	/**默认司机*/
    @Excel(name="默认司机",width=15,orderNum = "8")
	private String username;
	/**gps*/
    @Excel(name="gps",width=15,orderNum = "9")
	private String gpsid;
	@Excel(name="区域",width=15,orderNum = "10")
	private String quyu;

	//查询条件
	private Integer page = 1;
	private Integer rows = 10;
	private Integer pagerows;
	private String searchKey;
	private String searchKey2;
	private String searchKey3;
}

3.导入导出工具类

package com.lwh.easypoi.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.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;

/**
 * @Auther:
 * @Date:
 * @Description: Excle 文件导入导出Util(easypoi)
 */
public class EasyPoiUtil {
    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名。创建表头
     *
     * @author
     * @date
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param isCreateHeader 是否创建表头
     * @param fileName
     * @param response
     * @return
     */
    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);
    }


    /**
     * 功能描述：复杂导出Excel，包括文件名以及表名,不创建表头
     *
     * @author
     * @date
     * @param list 导出的实体类
     * @param title 表头名称
     * @param sheetName sheet表名
     * @param pojoClass 映射的实体类
     * @param fileName
     * @param response
     * @return
     */
    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));
    }

    /**
     * 功能描述：Map 集合导出
     *
     * @author
     * @date
     * @param list 实体集合
     * @param fileName 导出的文件名称
     * @param response
     * @return
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        defaultExport(list, fileName, response);
    }

    /**
     * 功能描述：默认导出方法
     *
     * @author
     * @date
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param pojoClass pojo实体
     * @param exportParams ExportParams封装实体
     * @param response
     * @return
     */
    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);
        }
    }

    /**
     * 功能描述：Excel导出
     *
     * @author
     * @date
     * @param fileName 文件名称
     * @param response
     * @param workbook Excel对象
     * @return
     */
    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  RuntimeException(e);
        }
    }

    /**
     * 功能描述：默认导出方法
     *
     * @author
     * @date
     * @param list 导出的实体集合
     * @param fileName 导出的文件名
     * @param response
     * @return
     */
    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);
    }


    /**
     * 功能描述：根据文件路径来导入Excel
     *
     * @author
     * @date
     * @param filePath 文件路径
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    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 RuntimeException("模板不能为空");
        } catch (Exception e) {
            e.printStackTrace();

        }
        return list;
    }

    /**
     * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
     *
     * @author
     * @date
     * @param file 上传的文件
     * @param titleRows 表标题的行数
     * @param headerRows 表头行数
     * @param pojoClass Excel实体类
     * @return
     */
    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 RuntimeException("excel文件不能为空");
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());

        }
        return list;
    }


}

4.导出操作

1.controller层

	// 导出
	@RequestMapping("/exportExcel")
	@ResponseBody
	public void outExcel(MdCheliangEntity mdCheliangEntity, HttpServletResponse response){

		String result = null;
		try {
			String fileName = "车辆管理";
			List<MdCheliangEntity> list = mdCheliangService.outExcel(mdCheliangEntity);
			EasyPoiUtil.exportExcel(list,fileName,fileName,MdCheliangEntity.class,fileName+".xls",true,response);
			result = "success";
		} catch (Exception e) {
			e.printStackTrace();
			result = "fail";
		}finally {

		}
	}

2.service层

@Override
public List<MdCheliangEntity> outExcel(MdCheliangEntity mdCheliangEntity) {

  List<MdCheliangEntity> list = mdCheliangMapper.outExcel(mdCheliangEntity);
  return list;
}

3.dao层

List<MdCheliangEntity> outExcel(MdCheliangEntity query);

5.导入操作

1.controller层

// 导入
@RequestMapping("/importExcel")
@ResponseBody
public String importExcel(MultipartFile uploadFile){

  String result = mdCheliangService.importExcel(uploadFile);

  return result;

}

2.service层

@Override
    public String importExcel(MultipartFile uploadFile) {
        List<MdCheliangEntity> mdList = new ArrayList<MdCheliangEntity>();
        IdWorker idWorker=new IdWorker(0,0);
        String result = null;
        try {
            String filename = uploadFile.getOriginalFilename();
            if(filename != null && (filename.toLowerCase().endsWith(".xls") || filename.toLowerCase().endsWith(".xlsx"))){
                List<MdCheliangEntity> list = EasyPoiUtil.importExcel(uploadFile,1,1,MdCheliangEntity.class);
                for(MdCheliangEntity md : list){
                   MdCheliangEntity mdpo = new MdCheliangEntity();
                   String id = String.valueOf(idWorker.nextId());
                   mdpo.setId(id);
                   mdpo.setCreateDate(new Date());
                   mdpo.setChepaihao(md.getChepaihao());
                   mdpo.setChexing(md.getChexing());
                   mdpo.setZuidatiji(md.getZuidatiji());
                   mdpo.setZaizhong(md.getZaizhong());
                   mdpo.setZairen(md.getZairen());
                   mdpo.setJiazhao(md.getJiazhao());
                   mdpo.setZhuangtai(md.getZhuangtai());
                   mdpo.setBeizhu(md.getBeizhu());
                   mdpo.setUsername(md.getUsername());
                   mdpo.setGpsid(md.getGpsid());
                   mdpo.setQuyu(md.getQuyu());
                   mdList.add(mdpo);
                }
                mdCheliangMapper.importExcel(mdList);
                result = "success";
            }else{
                result = "导入的文件不是excel文件";
            }

        } catch (Exception e) {
            e.printStackTrace();
            result = "fail";
        }finally {

        }
        return result;
    }

3.dao层

void importExcel(List<MdCheliangEntity> list);

3.效果

导出excel效果：

在这里插入图片描述

前端界面效果：
在这里插入图片描述

4.源代码地址

https://gitee.com/lin8081/LWH06

原文链接：https://blog.csdn.net/lin_dark/article/details/104217967

1.官网和文档介绍

1.官网地址

2.介绍

2.使用

1.在pom.xml中加入依赖

2.编写实体类

3.导入导出工具类

4.导出操作

1.controller层

2.service层

3.dao层

5.导入操作

1.controller层

2.service层

3.dao层

3.效果

4.源代码地址

1.在`pom.xml`中加入依赖