Easypoi导出、导入excel

• 所需pom依赖：

      <!-- 引入EasyPoi包 -->
	  <dependency>
		  <groupId>cn.afterturn</groupId>
		  <artifactId>easypoi-spring-boot-starter</artifactId>
		  <version>4.1.0</version>
	  </dependency>

	  <dependency>
		  <groupId>commons-fileupload</groupId>
		  <artifactId>commons-fileupload</artifactId>
		  <version>1.4</version>
	  </dependency>

• 编写导入导出工具类：

/**

• Excel枚举类型
  */

public enum ExcelTypeEnum {
    XLS("xls"), XLSX("xlsx");

    private String value;

    private ExcelTypeEnum(String value) {
        this.value = value;
    }

    public String getValue() {
        return value;
    }

    public void setValue(String value) {
        this.value = value;
    }
}

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

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

/**
 * Excel导出工具类
 */
@Component
public class ExcelExportUtils {

    @Autowired
    private HttpServletResponse response;

    /**
     * 导出excel
     * @param list 泛型数据
     * @param title 标题
     * @param sheetName sheet的名称
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @param isCreateHeader 是否创建表头
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String title, String sheetName, String fileName,
                            boolean isCreateHeader) throws IOException {
        final ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        baseExport(list, pojoClass, fileName, exportParams);
    }

    /**
     * 导出excel
     * @param list 泛型数据
     * @param title 标题
     * @param sheetName sheet的名称
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @param response
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String title, String sheetName, String fileName)
            throws IOException {
        baseExport(list, pojoClass, fileName, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * 导出excel
     * @param list 泛型数据
     * @param pojoClass 需要导出的对象
     * @param fileName 文件名称
     * @param exportParams 文件书香
     * @param response
     * @throws IOException void
     */
    public void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams)
            throws IOException {
        baseExport(list, pojoClass, fileName, exportParams);
    }

    /**
     * 多个sheet导出
     * @param list
     * @param fileName
     * @throws IOException void
     */
    public void exportExcel(List<Map<String, Object>> list, String fileName) throws IOException {
        baseExport(list, fileName);
    }

    /**
     * 最基础的对象导出
     * @param list 数据列表
     * @param pojoClass 导出对象
     * @param fileName 文件名称
     * @param exportParams 导出文件属性
     * @throws IOException void
     */
    private void baseExport(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams)
            throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, workbook);
    }

    /**
     * 最基础的多sheet导出
     * @param list 多个不同数据对象的列表
     * @param fileName 文件名称
     * @throws IOException void
     */
    private void baseExport(List<Map<String, Object>> list, String fileName) throws IOException {
        final Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, workbook);
    }

    /**
     * 文件下载
     * @param fileName 文件名称
     * @param workbook exce对象
     * @throws IOException void
     */
    private void downLoadExcel(String fileName, Workbook workbook) throws IOException {
        ServletOutputStream output = null;
        try {
            final String downloadName = URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + downloadName);
            output = response.getOutputStream();
            workbook.write(output);
        }
        catch (final Exception e) {
            throw new IOException(e.getMessage());
        }
        finally {
            if (output != null) {
                output.flush();
                output.close();
            }
        }
    }
}

import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.InputStream;
import java.util.List;

/**
 * Excel导入工具类
 */
@Component
public class ExcelImportUtils {
    /**
     * 从指定位置获取文件后进行导入
     * @param filePath 文件路径
     * @param titleRows 表格标题行数,默认0
     * @param headerRows 表头行数,默认1
     * @param pojoClass 上传后需要转化的对象
     * @return
     * @throws IOException List<T>
     */
    public <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass)
            throws Exception {
        if (StringUtils.isEmpty(filePath)) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            // 表格标题行数,默认0
            params.setTitleRows(titleRows);
            // 表头行数,默认1
            params.setHeadRows(headerRows);
            // 是否需要保存上传的Excel
            params.setNeedSave(true);
            // 保存上传的Excel目录
            params.setSaveUrl("/excel/");
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }
    }

    /**
     * 上传文件导入
     * @param file
     * @param titleRows 标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass 导入的对象
     * @return
     * @throws Exception List<T>
     */
    public <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy,
                                   Class<T> pojoClass) throws Exception {
        if (file == null) {
            return null;
        } else {
            return baseImport(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        }

    }

    /**
     * 最基础导入
     * @param inputStream
     * @param titleRows 表格标题行数,默认0
     * @param headerRows 表头行数,默认1
     * @param needVerify 是否需要检测excel
     * @param pojoClass 导入的对象
     * @return
     * @throws IOException List<T>
     */
    private <T> List<T> baseImport(InputStream inputStream, Integer titleRows, Integer headerRows,
                                   boolean needVerify, Class<T> pojoClass) throws Exception {
        if (inputStream == null) {
            return null;
        } else {
            final ImportParams params = new ImportParams();
            params.setTitleRows(titleRows);
            params.setHeadRows(headerRows);
            params.setSaveUrl("/excel/");
            params.setNeedSave(true);
            params.setNeedVerify(needVerify);
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        }

    }
}

• 编写导入导出对象：

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

import java.io.Serializable;
import java.text.NumberFormat;
import java.util.Date;

public class User implements Serializable {

    // 数字格式化
    private NumberFormat nf = NumberFormat.getNumberInstance();

    private static final long serialVersionUID = 1L;

    @Excel(name = "用户id", orderNum = "0", width = 15)
    private long userId;

    @Excel(name = "性别", orderNum = "1", width = 15, replace = { "男_1", "女_2" }, suffix = "孩")
    private int sex;

    @Excel(name = "金钱", orderNum = "2", width = 15)
    private double money;

    @Excel(name = "用户信息", orderNum = "3", width = 15)
    private String userName;

    @Excel(name = "价格", orderNum = "4", width = 15)
    private float price;

    @Excel(name = "时间", orderNum = "5", width = 15, format = "yyyy-MM-dd")
    private Date now;

    public long getUserId() {
        return userId;
    }

    public void setUserId(long userId) {
        this.userId = userId;
    }

    public int getSex() {
        return sex;
    }

    public void setSex(int sex) {
        this.sex = sex;
    }

    public String getMoney() {
        return nf.format(money);
    }

    public void setMoney(double money) {
        this.money = money;
    }

    public String getUserName() {
        return userName;
    }

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

    public float getPrice() {
        return price;
    }

    public void setPrice(float price) {
        this.price = price;
    }

    public Date getNow() {
        return now;
    }

    public void setNow(Date now) {
        this.now = now;
    }
}

• 编写测试方法：

import com.demo.service.pojo.User;
import com.demo.service.util.ExcelExportUtils;
import com.demo.service.util.ExcelImportUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.*;

@Controller
@EnableTransactionManagement
@RequestMapping("/user")
public class ExcelAction {

	@Autowired
	private ExcelExportUtils excelExportUtils;

	@Autowired
	private ExcelImportUtils excelImportUtils;

	@RequestMapping(value="/exportExcel")
	public void exportExcel() throws IOException {
		final List<User> userList = new ArrayList<>();
		for (int i = 0; i < 10; i++) {
			final User user = new User();
			user.setUserId(i);
			user.setSex(1);
			user.setMoney(953 + i);
			user.setUserName("李明" + i);
			user.setPrice(23.1f + i);
			user.setNow(new Date());
			userList.add(user);
		}
		excelExportUtils.exportExcel(userList, User.class, "用户信息", "员工信息的sheet", "用户信息表");
	}

	/**
	 * 导入用户信息
	 * @param file
	 * @return
	 * @throws IOException Object
	 */
	@RequestMapping(value="/importExcel")
	public @ResponseBody Map<String,Object> importExcel(@RequestParam("file") MultipartFile file) throws Exception {
		Map map = new HashMap<String, Object>();

		final List<User> userList = excelImportUtils.importExcel(file, 1, 1, false, User.class);

		map.put("data", userList);
		return map;
	}
}

• 测试导出：
  
  
• 测试导入：