easypoi一对多导入_easypoi 一行代码搞定excel导入导出

importcn.afterturn.easypoi.excel.ExcelExportUtil;importcn.afterturn.easypoi.excel.ExcelImportUtil;importcn.afterturn.easypoi.excel.annotation.Excel;importcn.afterturn.easypoi.excel.entity.ExportParams;importcn.afterturn.easypoi.excel.entity.ImportParams;importcn.afterturn.easypoi.excel.entity.enmus.ExcelType;importcom.fasterxml.jackson.annotation.JsonFormat;importcom.jn.ssr.superrescue.annotation.Translate;importcom.jn.ssr.superrescue.cache.DictCache;importorg.apache.commons.lang.StringUtils;importorg.apache.logging.log4j.LogManager;importorg.apache.logging.log4j.Logger;importorg.apache.poi.ss.usermodel.Workbook;importorg.springframework.web.multipart.MultipartFile;importjavax.servlet.http.HttpServletResponse;importjava.io.File;importjava.lang.reflect.Field;importjava.lang.reflect.InvocationHandler;importjava.lang.reflect.Proxy;importjava.net.URLEncoder;import java.util.*;importjava.util.stream.Collectors;public classFileUtil {private static Logger log = LogManager.getLogger(FileUtil.class);public static final int BIG_DATA_EXPORT_MIN = 50000;public static final int BIG_DATA_EXPORT_MAX = 2000000;//excel处理注解set集合

public static HashSet transClassSet = newHashSet();public static void exportExcel(List> list, String title, String sheetName, Class> pojoClass, String fileName, booleanisCreateHeader, HttpServletResponse response) {

ExportParams exportParams= newExportParams(title, sheetName);

exportParams.setCreateHeadRows(isCreateHeader);

defaultExport(list, pojoClass, fileName, response, title, sheetName);

}/*** 导出函数

*

*@paramlist 导出集合

*@paramtitle 标题

*@paramsheetName sheet名

*@parampojoClass 映射实体

*@paramfileName 文件名

*@paramresponse httpresponce

* size如果过大 需采用poi SXSSF*/

public static void exportExcel(List> list, String title, String sheetName, Class>pojoClass, String fileName, HttpServletResponse response) {//判断该类是否已经处理过excel注解

long startTime =System.currentTimeMillis();if (!transClassSet.contains(String.valueOf(pojoClass))) {

initProperties(pojoClass);

transClassSet.add(String.valueOf(pojoClass));

}

defaultExport(list, pojoClass, fileName, response, title, sheetName);

log.info("此文件[{}]导出耗时：{}ms", fileName, (System.currentTimeMillis() -startTime));

}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, String title, String sheetName) {

Workbook workbook= null;

ExportParams exportParams= newExportParams(title, sheetName);if (list != null && list.size() >BIG_DATA_EXPORT_MAX) {

sizeBeyondError(response);return;

}else if (list != null && list.size() >BIG_DATA_EXPORT_MIN) {

log.info("文件过大采用大文件导出：" +list.size());for (int i = 0; i < (list.size() / BIG_DATA_EXPORT_MIN + 1) && list.size() > 0; i++) {

log.info("当前切片：" + i * BIG_DATA_EXPORT_MIN + "-" + (i + 1) *BIG_DATA_EXPORT_MIN);

List> update = list.stream().skip(i *BIG_DATA_EXPORT_MIN).limit(BIG_DATA_EXPORT_MIN).collect(Collectors.toList());

exportParams.setCreateHeadRows(true);

exportParams.setMaxNum(BIG_DATA_EXPORT_MIN* 2 + 2);

workbook=ExcelExportUtil.exportBigExcel(exportParams, pojoClass, update);

}

ExcelExportUtil.closeExportBigExcel();

}else{

workbook= ExcelExportUtil.exportExcel(newExportParams(title, sheetName), pojoClass, list);

}if (workbook == null) return;

downLoadExcel(fileName, response, workbook);

}private static voiddownLoadExcel(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"));//workbooks.forEach(e -> e.write(response.getOutputStream()));

workbook.write(response.getOutputStream());

}catch(Exception e) {

e.printStackTrace();

response.setCharacterEncoding("UTF-8");

response.setContentType("application/json");try{

response.getWriter().println("{\"code\":597,\"message\":\"export error!\",\"data\":\"\"}");

response.getWriter().flush();

}catch(Exception e1) {

e1.printStackTrace();

}finally{

closeIo(response);

}

}

}/*** 文件过大，不允许导出

*

*@paramresponse*/

private static voidsizeBeyondError(HttpServletResponse response) {

response.setCharacterEncoding("UTF-8");

response.setContentType("application/json");try{

response.getWriter().println("{\"code\":599,\"message\":\"文件过大!\",\"data\":\"\"}");

response.getWriter().flush();

}catch(Exception e1) {

e1.printStackTrace();

}finally{

closeIo(response);

}

}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, ClasspojoClass) {if(StringUtils.isBlank(filePath)) {return null;

}

ImportParams params= newImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

List list = null;try{

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

}catch(NoSuchElementException e) {

e.printStackTrace();

System.out.println("模版为空");

}catch(Exception e) {

e.printStackTrace();

}returnlist;

}public static List importExcel(MultipartFile file, Integer titleRows, Integer headerRows, ClasspojoClass) {if (file == null) {return null;

}

ImportParams params= newImportParams();

params.setTitleRows(titleRows);

params.setHeadRows(headerRows);

List list = null;try{

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

}catch(NoSuchElementException e) {

e.printStackTrace();

System.out.println("文件为空");

}catch(Exception e) {

e.printStackTrace();

}returnlist;

}/*** 代理初始化该类的注解

*

*@paramcl*/

public synchronized static voidinitProperties(Class cl) {try{

Field[] fields=cl.getDeclaredFields();for(Field field : fields) {if (field.isAnnotationPresent(Excel.class)) {

field.setAccessible(true);

Excel excel= field.getAnnotation(Excel.class);

InvocationHandler h=Proxy.getInvocationHandler(excel);

Field hField= h.getClass().getDeclaredField("memberValues");//因为这个字段事 private final 修饰，所以要打开权限

hField.setAccessible(true);//获取 memberValues

Map memberValues =(Map) hField.get(h);//判断是否有转义注解，将字典添加到excel replace属性中

if (field.isAnnotationPresent(Translate.class)) {

Translate translate= field.getAnnotation(Translate.class);

String dicName=translate.dicName();

Map dicMap=DictCache.getProperties(dicName);if (dicMap == null) {continue;

}

String[] replace= newString[dicMap.size()];

List replaceList = new ArrayList<>();

dicMap.forEach((key, val)->{

replaceList.add(val+ "_" +key);

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

replace[i]=replaceList.get(i);

}

memberValues.put("replace", replace);

}//json格式化与JsonFormat统一，目前暂用于时间

if (field.isAnnotationPresent(JsonFormat.class)) {

JsonFormat jsonFormat= field.getAnnotation(JsonFormat.class);if(StringUtils.isNotEmpty(jsonFormat.pattern())) {

memberValues.put("format", jsonFormat.pattern());

}

}

}

}

}catch(Exception e) {

e.printStackTrace();

}

}/*** 关闭writer

*

*@paramresponse*/

private static voidcloseIo(HttpServletResponse response) {try{if (response.getWriter() != null) {

response.getWriter().close();

}

}catch(Exception e) {

e.printStackTrace();

}

}

}