easypoi 批量导出_使用EasyPoi快速实现Excel的导入导出

excel.jpg

近期完成了后管系统报表的导入导出，完成之后做下总结

1、首先我们对采用的插件进行了筛选，最后选择EasyPoi，用的人也很多，算是对poi的简单封装

这里项目用的springboot，所以只用pom引入配置即可

cn.afterturn

easypoi-base

3.0.3

cn.afterturn

easypoi-web

3.0.3

cn.afterturn

easypoi-annotation

3.0.3

2、根据配置编写实体类

先简单介绍一下关于@Excel注解的一些常用属性

name：列名

orderNum：第几列

replace：值得替换 例：replace = {"身份证_1"} 数据库值为"1"，导出时会自动被"身份证"代替

@Excel(name = "姓名", orderNum = "0")

private String name;

@Excel(name = "证件类型", replace = {"身份证_1"}, orderNum = "1")

private String identifyType;

@Excel(name = "证件号码", orderNum = "2")

private String identifyNo;

@Excel(name = "手机号1", orderNum = "3")

private String phoneA;

@Excel(name = "手机号2", orderNum = "4")

private String phoneB;

@Excel(name = "手机号3", orderNum = "5")

private String phoneC;

@Excel(name = "固定电话", orderNum = "6")

private String telephone;

@Excel(name = "电子邮箱", orderNum = "7")

private String email;

@Excel(name = "身份证地址", orderNum = "8")

private String idcardAdress;

@Excel(name = "户籍地址", orderNum = "9")

private String householdAddress;

@Excel(name = "居住地址", orderNum = "10")

private String liveAddress;

@Excel(name = "工作地址", orderNum = "11")

private String workAddress;

3、整合导入导出方法

package com.***.common;

import java.io.File;

import java.io.IOException;

import java.net.URLEncoder;

import java.util.List;

import java.util.Map;

import java.util.NoSuchElementException;

import org.apache.commons.lang.StringUtils;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.Workbook;

import org.slf4j.Logger;

import org.slf4j.LoggerFactory;

import org.springframework.web.multipart.MultipartFile;

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;

public class FileWithExcelUtil {

private static final Logger log = LoggerFactory.getLogger(ExcelExporterUtils.class);

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

log.error("[monitor][IO][表单功能]", e);

}

}

private static void defaultExport(List> list, String fileName, HttpServletResponse response) {

Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);

if (workbook != null);

downLoadExcel(fileName, response, workbook);

}

public static List importExcel(String filePath,Integer titleRows,Integer headerRows, Class pojoClass){

if (StringUtils.isBlank(filePath)){

return null;

}

ImportParams params = new ImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

List list = null;

try {

list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);

}catch (NoSuchElementException e){

throw e;

} catch (Exception e) {

e.printStackTrace();

throw e;

}

return list;

}

public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass){

if (file == null){

return null;

}

ImportParams params = new ImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

List list = null;

try {

list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);

}catch (NoSuchElementException e){

throw e;

} catch (Exception e) {

e.printStackTrace();

log.error("[monitor][表单功能]", e);

}

return list;

}

}

4、导出操作

@RequestMapping("/exportExcel/model")

public ResponseModel export(HttpServletResponse response){

try {

//模拟从数据库获取需要导出的数据

List personList = new ArrayList<>();

FileWithExcelUtil.exportExcel(personList,"客户信息表","客户表",CustomerList.class,"客户表.xls",response);

return ResponseModel.success("操作成功");

} catch (Exception e) {

logger.info("getCustomerPage", e);

return ResponseModel.fail("导出模版失败");

// TODO: handle exception

}

}

5、导入操作

将导入的模版数据拿到，填充到自己要使用的实体类

ps：导入的数据模版一般都是平铺的，而我们的实体类一般都有层次，所以我们的excel模版实体往往不是我们数据库对应的bean，需要我们将其自行填充

@RequestMapping(value = "/importExcelForType", method = RequestMethod.POST)

public ResponseModel importExcel(@RequestParam("file") MultipartFile file,String customerType){

try {

// String filePath = "/UsersDownloads/response.xls";

//解析excel，

// List personList = FileWithExcelUtil.importExcel(filePath, 1, 1, CustomerList.class);

List personList = FileWithExcelUtil.importExcel(file, 1, 1, CustomerList.class);

//也可以使用MultipartFile,使用 FileUtil.importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class pojoClass)导入

System.out.println("导入数据一共【"+personList.size()+"】行");

for (int i = 0; i < personList.size(); i++) {

CustomerList excel = personList.get(i);

CmCustomerForExcel customer = customerListByExcel(excel);

customer.setCustomerType(customerType);

customerService.saveExcelList(customer);

}

logger.info(personList.toString());

return ResponseModel.success("操作成功");

} catch (Exception e) {

// TODO: handle exception

logger.error(e.toString());

return ResponseModel.fail("导入失败");

}

补充