最近在做根据前端选择导出字段,然后根据选择的字段,动态的导出对应信息。
/**
* 多sheet导出
* @param title 标题
* @param secTitle 副标题
* @param sheetName sheet名称
* @param data 数据list
* @param fields 导出字段集合
* @param clazz 导出对象class
* @return
* @throws Exception
*/
public static byte[] exportMultiSheetExcel(Object title,Object secTitle,String sheetName, List<?> data, List<String> fields, Class<?> clazz) throws Exception{
byte[] content = null;
// 把查询到的数据按设置的sheet的容量进行切割
List<List<?>> lists = SplitList.splitList(data, PAGE_SIZE);
//List<ExcelExportEntity> entityList = dynamicExportFields(clazz,fields);
List<ExcelExportEntity> entityList = dynamicComplexFields(clazz,fields);
@Cleanup
ByteArrayOutputStream bos = new ByteArrayOutputStream();
String firstTitle = ObjectUtil.isEmpty(title) ? null : title.toString();
String secondTitle = ObjectUtil.isEmpty(secTitle) ? null : secTitle.toString();
try{
Workbook workbook = new XSSFWorkbook();
// 遍历sheet
for (int i = 1; i <= lists.size(); ++i) {
String sheetNamed = sheetName+i;
ExcelExportService server = new ExcelExportService();
ExportParams exportParams = new ExportParams(firstTitle, sheetNamed, ExcelType.XSSF);
exportParams.setSecondTitle(secondTitle);
// excel样式
exportParams.setStyle(ExcelExportTitleStyle.class);
//server.createSheetForMap(workbook, exportParams, entityList,objectToMap(lists.get(i)));
server.createSheetForMap(workbook, exportParams, entityList,lists.get(i-1));
}
workbook.write(bos);
workbook.close();
content = bos.toByteArray();
/*// 文件落地,用来测试文件的格式和数据的完整性
@Cleanup InputStream is = new ByteArrayInputStream(content);
@Cleanup FileOutputStream fileOutputStream = new FileOutputStream("d:/data/hahaha.xlsx");
@Cleanup BufferedInputStream bis = new BufferedInputStream(is);
@Cleanup BufferedOutputStream bos1 = new BufferedOutputStream(fileOutputStream);
byte[] buff = new byte[2048];
int bytesRead;
while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
bos1.write(buff, 0, bytesRead);
}
log.info("文件落地磁盘");*/
}catch (Exception e){
log.error("导出失败-----------------------------------",e);
throw new Exception("导出失败!");
}
return content;
}
private static List<ExcelExportEntity> dynamicComplexFields(Class<?> clazz, List<String> fields) {
List<ExcelExportEntity> beanList = new ArrayList<>();
try {
for (String field:fields) {
Field f = clazz.getDeclaredField(field);
f.setAccessible(true);
ExcelExportEntity entity = dynamicFields(f,clazz);
if(ObjectUtil.isEmpty(entity)){
continue;
}else{
beanList.add(entity);
}
}
}catch (NoSuchFieldException e){
e.printStackTrace();
}
return beanList;
}
private static ExcelExportEntity dynamicFields(Field f,Class<?> clazz){
ExcelExportEntity entity = null;
if(f.isAnnotationPresent(Excel.class)){
entity = dynamicSingleFields(f,clazz);
}else if(f.isAnnotationPresent(ExcelCollection.class)){
entity = dynamicCollFields(f,clazz);
}
return entity;
}
private static ExcelExportEntity dynamicSingleFields(Field f,Class<?> clazz) {
ExcelExportEntity entity = new ExcelExportEntity();
f.setAccessible(true);
Excel annotation = f.getAnnotation((Excel.class));
String comment = annotation.name();
Double width = annotation.width();
entity.setKey(f.getName());
entity.setWidth(width.intValue());
entity.setHeight(annotation.height());
entity.setName(comment);
entity.setType(annotation.type());
entity.setMethod(PoiReflectorUtil.fromCache(clazz).getGetMethod(f.getName()));
entity.setExportImageType(annotation.imageType());
entity.setNeedMerge(annotation.needMerge());
entity.setMergeVertical(annotation.mergeVertical());
entity.setMergeRely(annotation.mergeRely());
entity.setFormat(annotation.format());
entity.setOrderNum(Integer.parseInt(annotation.orderNum()));
return entity;
}
private static ExcelExportEntity dynamicCollFields(Field field,Class<?> clazz) {
ExcelExportEntity entity = new ExcelExportEntity();
field.setAccessible(true);
ExcelCollection annotation = field.getAnnotation((ExcelCollection.class));
String comment = annotation.name();
entity.setKey(field.getName());
entity.setName(comment);
entity.setOrderNum(Integer.parseInt(annotation.orderNum()));
entity.setMethod(PoiReflectorUtil.fromCache(clazz).getGetMethod(field.getName()));
Type genericType = field.getGenericType();
ParameterizedType pt = (ParameterizedType) genericType;
Class<?> aClass = (Class<?>)pt.getActualTypeArguments()[0];
Field[] fields = aClass.getDeclaredFields();
List<ExcelExportEntity> list = new ArrayList<>();
for (Field f:fields) {
f.setAccessible(true);
ExcelExportEntity entity1 = dynamicFields(f,aClass);
list.add(entity1);
}
entity.setList(list);
return entity;
}
数据分页工具:
/**
* 数据拆分分页
*
* @Author EDZ
* @Date 2022/9/22
*/
public class SplitList {
/**
* 切割查询的数据
* @param list 需要切割的数据
* @param len 按照什么长度切割
* @param <T>
* @return
*/
public static List<List<?>> splitList(List<?> list, int len) {
if (list == null || list.size() == 0 || len < 1) {
return null;
}
List<List<?>> result = new ArrayList<List<?>>();
int size = list.size();
int count = (size + len - 1) / len;
for (int i = 0; i < count; i++) {
List<?> subList = list.subList(i * len, (Math.min((i + 1) * len, size)));
result.add(subList);
}
return result;
}
/**
* 集合平均分组
* @param source 源集合
* @param n 分成n个集合
* @param <T> 集合类型
* @return 平均分组后的集合
*/
public static <T> List<List<T>> groupList(List<T> source, int n) {
if (source == null || source.size() == 0 || n < 1) {
return null;
}
if (source.size() < n) {
return Collections.singletonList(source);
}
List<List<T>> result = new ArrayList<List<T>>();
int number = source.size() / n;
int remaider = source.size() % n;
// 偏移量,每有一个余数分配,就要往右偏移一位
int offset = 0;
for (int i = 0; i < n;i++) {
List<T> list1 = null;
if (remaider > 0){
list1 = source.subList(i * number + offset,(i + 1) * number + offset + 1);
remaider--;
offset++;
}else {
list1 = source.subList(i * number + offset, (i+1) * number + offset);
}
result.add(list1);
}
return result;
}
}