springboot+easypoi实现Excle导出导入

开发中经常会遇到excel的处理，导入导出解析等等，java中比较流行的用poi，但是每次都要写大段工具类来搞定这事儿，此处推荐一个别人造好的轮子【easypoi】，下面介绍下“轮子”的使用。

广大网友都是基于实体对象的导出，本文中包含基于List<Map<String，Object>>数据的导出示例，见文章最后Controller调用测试。

pom引入

不再需要其他jar

       <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>

编写实体类

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

public class ItemDTO {
	@Excel(name = "产品Id", orderNum = "0")
	private int itemId;
	@Excel(name = "产品名称",width=30, orderNum = "1")
	private String name; // 产品名称
	@Excel(name = "开始时间", width=15, exportFormat = "yyyy-MM-dd", orderNum = "2")
	private Date startDate; // 开始时间
	@Excel(name = "结束时间",width=15, exportFormat = "yyyy-MM-dd", orderNum = "3")
	private Date endDate; // 结束时间
	@Excel(name = "产品介绍",width=25, orderNum = "4")
	private String introduce; // 产品介绍
	@Excel(name = "状态",replace={"启用_1","停用_2"}, orderNum = "5")
    private int status; // 状态，1应用 2停用
省略get、set方法
}

导入导出公用方法

看起来代码挺多，其实是提供了多个导入导出方法而已

package com.qt.base.cpgl.util;

import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

import javax.servlet.http.HttpServletResponse;


import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.druid.util.StringUtils;
import com.qt.base.sys.exception.NormalException;

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 cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
/**
 * Excel导入导出工具类
 * @author Administrator
 *
 */
public class ExcelUtils {
    /**
     * 功能描述：导出Excel 1
     * @param list Excel对象数据List
     * @param title 表头名称
     * @param sheetName sheet名称
     * @param pojoClass Excel映射对象(DTO)
     * @param fileName Excle导出文件名
     * @param isCreateHeader 是否创建表头
     * @param response
     */
	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 2
	 * @param list Excel对象数据List
	 * @param title 表头名称
	 * @param sheetName sheet名称
	 * @param pojoClass Excel映射对象(DTO)
	 * @param fileName Excle导出文件名
	 * @param response
	 */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
    	ExportParams exportParams = new ExportParams(title, sheetName);
    	exportParams.setType(ExcelType.XSSF);//导出.xlsx格式
    	exportParams.setDynamicData(true);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }
    /**
     * 功能描述：导出Excel 3
     * @param list Excel数据List
     * @param fileName Excle导出文件名
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    /**
     * 功能描述：导出Excel 4
     * @param list Excel对象数据List
     * @param pojoClass Excel映射对象(DTO)
     * @param fileName Excle导出文件名
     * @param response 
     * @param exportParams  ExportParams封装实体  导入参数 
     */
    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 5
     * @param list
     * @param fileName
     * @param response
     */
    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);
    }
    /**
     * 导出Excel6 导出List<Map<String,object>>类型的数据
     * @param exportParams
     * @param entityList 
     * @param dataSet 导出数据集合
     * @param fileName
     * @param response
     */
    public static void exportExcel6( List<ExcelExportEntity> entityList,
            Collection<?> dataSet,String fileName,HttpServletResponse response){
    	ExportParams exportParams = new ExportParams();
    	exportParams.setType(ExcelType.XSSF);//导出.xlsx格式
    	Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entityList,dataSet);
        downLoadExcel(fileName, response, workbook);
    }
    /**
     * 功能描述：下载Excel
     * @param fileName
     * @param response
     * @param workbook
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
//            response.setHeader("content-Type", "application/vnd.ms-excel");//.xls
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");  //.xlsx
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    	/**
         * 功能描述：根据文件路径来导入Excel
         * @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) throws NormalException{
    	 //判断文件是否存在
    	if (StringUtils.isEmpty(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;
    }
    	/**
         * 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
         * @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) throws NormalException{
        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调用测试

 @RequestMapping("export")
/**
	 * 导出Excel  List<实体对象>
	 * @param itemIds
	 * @param response
	 */
	@PostMapping(value ="/exportexcel", produces = "text/plain;charset=UTF-8")
	public void exportExcel(String itemIds,HttpServletResponse response) {
		try {
			// 从数据库获取需要导出的数据
//			itemIds = "155,156,157,158";
			List<ItemDTO> itemList = itemService.selectBatchByIds(itemIds);
			if (itemList != null & !itemList.isEmpty()) {
				// 导出操作
				ExcelUtils.exportExcel(itemList, "产品报名系统-产品导出表", "产品信息", ItemDTO.class, "产品报名系统-产品导出表.xlsx", response);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	
	/**
	 * 导出Excel 导出List<Map<String,Object>>数据
	 * @param itemIds
	 * @param response
	 */
	@RequestMapping(value ="/exportexcel2", produces = "text/plain;charset=UTF-8")
	public void exportExcel2(String appIds,HttpServletResponse response) {
		try {
			// 从数据库获取需要导出的数据
//			itemIds = "155,156,157,158";
			List<Map<String, Object>> applist = applicationinfoService.selectAppInfoByIds(appIds);
			if (applist != null & !applist.isEmpty()) {
				//一个ExcelExportEntity对应一列，相当于导出对象的一个属性的所有设置
				List<ExcelExportEntity> entityList = new ArrayList<ExcelExportEntity>();
				// 取第一条的key作为标题行
				Map<String, Object> map = applist.get(0);
				for (String key : map.keySet()) {
					entityList.add(new ExcelExportEntity(key, key));
				}
				ExcelUtils.exportExcel6(entityList, applist, "产品报名系统-报名信息导出表.xlsx", response);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * 导入Excel 
	 * @param file
	 * @param response
	 */
    @RequestMapping("importExcel")
    public void importExcel(MultipartFile file){
        String filePath = "F:\\产品报名系统-产品导出表.xlsx";
        //解析excel，
        List<ItemDTO> itemList =  ExcelUtils.importExcel(filePath,1,1,ItemDTO.class)//导入
        System.out.println("导入数据一共【"+itemList.size()+"】行");

        //TODO 保存数据库
    }