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("導入失敗");

}

補充