package com.kidgrow.common.utils;
|
|
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;
|
import com.google.common.collect.Lists;
|
import com.kidgrow.common.utils.excelModel.ExportField;
|
import lombok.extern.slf4j.Slf4j;
|
import org.apache.commons.lang3.StringUtils;
|
import org.apache.poi.ss.usermodel.Workbook;
|
import org.springframework.util.CollectionUtils;
|
import org.springframework.web.multipart.MultipartFile;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.File;
|
import java.io.IOException;
|
import java.io.InputStream;
|
import java.lang.reflect.Method;
|
import java.net.URLEncoder;
|
import java.util.*;
|
|
/**
|
* @author : heyanfeng
|
* create at: 2019-05-27 19:11
|
* @description: excel
|
*/
|
@Slf4j
|
public class ExcelUtils {
|
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<Map<String, Object>> list, String fileName, HttpServletResponse response){
|
defaultExport(list, fileName, response);
|
}
|
public static void exportExcel(String fileName,ExportParams exportParams, List<ExcelExportEntity> entities,List<Map<String, Object>> list,HttpServletResponse response){
|
defaultExport(fileName,exportParams,entities,list,response);
|
}
|
|
private static void defaultExport(String fileName,ExportParams exportParams, List<ExcelExportEntity> entities,List<Map<String, Object>> list,HttpServletResponse response ){
|
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entities, list);
|
if (workbook != null){
|
downLoadExcel(fileName, response, workbook);}
|
}
|
|
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("excel异常:{}",e.getMessage());
|
}
|
}
|
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);}
|
}
|
public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass){
|
ImportParams params = new ImportParams();
|
params.setTitleRows(titleRows);
|
params.setHeadRows(headerRows);
|
List<T> list = null;
|
try {
|
File file = new File(filePath);
|
list = ExcelImportUtil.importExcel(file, pojoClass, params);
|
}catch (NoSuchElementException e){
|
throw new IllegalArgumentException("模板不能为空");
|
} catch (Exception e) {
|
log.error("excel异常:{}",e.getMessage());
|
}
|
return list;
|
}
|
|
public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, Class<T> pojoClass){
|
ImportParams params = new ImportParams();
|
params.setTitleRows(titleRows);
|
params.setHeadRows(headerRows);
|
List<T> list = null;
|
try {
|
list = ExcelImportUtil.importExcel(inputStream, pojoClass, params);
|
}catch (NoSuchElementException e){
|
throw new IllegalArgumentException("模板不能为空");
|
} catch (Exception e) {
|
log.error("excel异常:{}",e.getMessage());
|
}
|
return list;
|
}
|
public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
|
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 IllegalArgumentException("excel文件不能为空");
|
} catch (Exception e) {
|
log.error("excel异常:{}",e.getMessage());
|
}
|
return list;
|
}
|
|
|
/**
|
* description: 动态生成excel 列
|
* create by heyanfeng at 2019-05-29 18:02
|
* @param ef
|
* @param entities
|
*/
|
public static void dynamicNewAddExcel(List<ExportField> ef, List<ExcelExportEntity> entities) {
|
//单元格的excel 表头
|
ef.forEach(item -> {
|
//需要合并单元格的表头
|
List<ExportField> children = item.getChildren();
|
if(!CollectionUtils.isEmpty(children)){
|
ExcelExportEntity parent = new ExcelExportEntity(item.getFiledChineseName(), item.getEntityAttrName());
|
List<ExcelExportEntity> entitiesChildren = Lists.newArrayList();
|
children.forEach(e -> {
|
entitiesChildren.add(new ExcelExportEntity(e.getFiledChineseName(),e.getEntityAttrName(),30));
|
});
|
parent.setNeedMerge(true);
|
parent.setList(entitiesChildren);
|
entities.add(parent);
|
}else{
|
entities.add(new ExcelExportEntity(item.getFiledChineseName(),item.getEntityAttrName(),30));
|
}
|
});
|
}
|
|
|
/**
|
* description: 根据属性名称 获取属性的值
|
* create by heyanfeng at 2019-05-29 19:26
|
* @return Object
|
* @param fieldName
|
* @param o
|
*/
|
public static Object getFieldValueByName(String fieldName,Object o){
|
try{
|
String firstLetter = fieldName.substring(0, 1).toUpperCase();
|
String getter = "get" + firstLetter + fieldName.substring(1);
|
Method method = o.getClass().getMethod(getter, new Class[] {});
|
Object value = method.invoke(o, new Object[]{});
|
return value;
|
}catch (Exception e){
|
log.error(e.getMessage(),e);
|
return null;
|
}
|
}
|
|
/**
|
* description: 组装excel 数据
|
* create by heyanfeng at 2019-05-29 20:30
|
* @return List<Map<String, Object>>
|
* @param ef
|
* @param statisData
|
*/
|
public static List<Map<String, Object>> dynamicListDataByKey(List<ExportField> ef, List<?> statisData) {
|
//最终的数据
|
List<Map<String, Object>> datas = new ArrayList<>();
|
Map map;
|
for (Object t : statisData) {
|
map = new HashMap();
|
for (int j = 0; j < ef.size(); j++) {
|
List<ExportField> children = ef.get(j).getChildren();
|
if(!CollectionUtils.isEmpty(children)){
|
//遍历需要合并单元格的子列
|
traversechildren(map, t, children,ef.get(j).getEntityAttrName());
|
}else if(StringUtils.isNotBlank(ef.get(j).getEntityAttrName())){
|
map.put(ef.get(j).getEntityAttrName(),getFieldValueByName(ef.get(j).getEntityAttrName(),t));
|
}
|
}
|
datas.add(map);
|
}
|
return datas;
|
}
|
|
/**
|
* description: 遍历需要合并单元格的子列
|
* create by heyanfeng at 2019-05-31 14:19
|
*/
|
private static void traversechildren(Map map, Object t, List<ExportField> children,String entityAttrName) {
|
ArrayList<Map<String,Object>> childrenMaps = Lists.newArrayList();
|
Map<String,Object> childrenMap= new HashMap();
|
for (int k = 0; k < children.size(); k++) {
|
if(StringUtils.isNotBlank(children.get(k).getEntityAttrName())){
|
childrenMap.put(children.get(k).getEntityAttrName(),getFieldValueByName(children.get(k).getEntityAttrName(),t));
|
}
|
}
|
childrenMaps.add(childrenMap);
|
map.put(entityAttrName,childrenMaps);
|
}
|
|
|
/**
|
* description: 组装sql 查询条件
|
* create by heyanfeng at 2019-05-30 10:09
|
*/
|
// public static String getExcelSql(List<ExportField> exportField) {
|
// String fileds;
|
// StringBuilder stringBuilder = new StringBuilder();
|
// exportField.forEach(item -> {
|
// //如果存在需要合并表头的列 则遍历
|
// List<ExportField> children = item.getChildren();
|
// if(!CollectionUtils.isEmpty(children)){
|
// children.forEach(e -> {
|
// if(StringUtils.isNotBlank(e.getFiled())){
|
// stringBuilder.append(e.getFiled() + GlobalConstant.PIC_SPLIT);
|
// }
|
// });
|
// }else if(StringUtils.isNotBlank(item.getFiled())){
|
// stringBuilder.append(item.getFiled() + GlobalConstant.PIC_SPLIT);
|
// }
|
// });
|
// stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(GlobalConstant.PIC_SPLIT));
|
// fileds = stringBuilder.toString();
|
// return fileds;
|
// }
|
|
/**
|
* description: 组装sql 查询条件
|
* create by heyanfeng at 2019-05-30 10:09
|
*/
|
// public static String getGroupBySql(List<ExportField> exportField) {
|
// String fileds;
|
// //是否加入 group by
|
// Boolean[] flag = {false};
|
// StringBuilder stringBuilder = new StringBuilder();
|
// exportField.forEach(item -> {
|
// if(item.getGroupBy() == null && StringUtils.isNotBlank(item.getFiled())){
|
// stringBuilder.append(item.getFiled() + GlobalConstant.PIC_SPLIT);
|
// }else if(item.getGroupBy() != null && item.getGroupBy() == YESNOEnum.YES.getValue()){
|
// flag[0] = true;
|
// }
|
// });
|
// stringBuilder.deleteCharAt(stringBuilder.lastIndexOf(GlobalConstant.PIC_SPLIT));
|
// fileds = stringBuilder.toString();
|
// if (flag[0]){
|
// fileds = " group by " + fileds;
|
// return fileds;
|
// }
|
// return "";
|
// }
|
|
|
/**
|
* description: 检查导出字段是否为空
|
* create by heyanfeng at 2019-05-30 10:10
|
*/
|
public static void checkExportField( List<ExportField> exportField) {
|
if(CollectionUtils.isEmpty(exportField)){
|
throw new IllegalArgumentException("导出的字段不能为空");
|
}
|
}
|
|
/**
|
* description: 动态导出生成excel
|
* create by heyanfeng at 2019-05-30 10:37
|
*/
|
public static void DynamicExcel(String fileName, String title,String sheet,List<ExportField> exportField, HttpServletResponse response, List<?> list) {
|
//生成动态列
|
List<ExcelExportEntity> entities = Lists.newArrayList();
|
ExcelUtils.dynamicNewAddExcel(exportField, entities);
|
//组装数据entities
|
List<Map<String, Object>> maps = ExcelUtils.dynamicListDataByKey(exportField, list);
|
ExcelUtils.exportExcel(fileName,new ExportParams(title, sheet),entities,maps,response);
|
}
|
}
|
