Excel自定义导出字段,单表 多表
Excel自定义导出字段
记录一下,试了半天才搞出来的。支持 excel 的 多个表格、单个表格、多个表头 查询数据库多张表, 这些都可以
需要的Jar包
<!-- Hutool工具包 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.15</version>
</dependency>
<!--这个我也不确定要不要导入,我测试的别的导出用到的,先放上来,包不报错就不导入-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.1.0</version>
</dependency>
我用的是mybatis-plus得查询方式,查询的是三张表,查到有数据就放到List里的Map里
/**
* 数据导出
*
* @param response
* @param particularsVo
* @throws IllegalAccessException
*/
@RequestMapping(value = "/exportList", method = RequestMethod.POST)
@ApiOperation(value = "自定义字段Excel导出")
@ApiImplicitParams({
@ApiImplicitParam(name = "names", value = "需要导出的字段", required = true, dataType = "string", paramType = "query"),
@ApiImplicitParam(name = "Ids", value = "需要导出的数据Id", required = true, dataType = "string", paramType = "query")
})
public Result<String> exportList(HttpServletResponse response, @RequestBody ParticularsVo particularsVo) throws IllegalAccessException {
List<String> names = particularsVo.getNames();
List<String> ids = particularsVo.getIds();
//A表
LambdaQueryWrapper<ReportCustomer> queryWrapper = new LambdaQueryWrapper<>();
queryWrapper.in(ReportCustomer::getId, ids);
List<ReportCustomer> customers = customerService.list(queryWrapper);
if (null == customers) {
return new ResultUtil<String>().setErrorMsg("无数据,检查Id是否正确");
}
// 封装 hutool 导出需要的 数据格式。String:表头;Object:数据
//A信息
List<Map<String, Object>> customerMaps = new LinkedList<>();
//B信息
List<Map<String, Object>> guarantorMaps = new LinkedList<>();
//C信息
List<Map<String, Object>> repayerMaps = new LinkedList<>();
for (ReportCustomer customer : customers) {
String reportId = customer.getReportId();
//AMap
Map<String, Object> customerMap = EntityTo.entityToMap(customer, names);
customerMaps.add(customerMap);
//BMap
LambdaQueryWrapper<ReportGuarantor> guarantorWrapper = new LambdaQueryWrapper<>();
guarantorWrapper.eq(ReportGuarantor::getReportId, reportId);
ReportGuarantor guarantor = guarantorService.getOne(guarantorWrapper);
if (null != guarantor) {
Map<String, Object> guarantorMap = EntityTo.entityToMap(guarantor, names);
guarantorMaps.add(guarantorMap);
}
//CMap
LambdaQueryWrapper<ReportRepayer> repayerWrapper = new LambdaQueryWrapper<>();
repayerWrapper.eq(ReportRepayer::getReportId, reportId);
ReportRepayer repayer = repayerService.getOne(repayerWrapper);
if (null != repayer) {
Map<String, Object> repayerMap = EntityTo.entityToMap(repayer, names);
repayerMaps.add(repayerMap);
}
}
HuToolExcelUtil.universalExcelExport(response, customerMaps,guarantorMaps,repayerMaps);
return null;
}
接收的vo,names是自定义字段的名称,ids是自定义要导出的数据id(比如说一个列表里,勾选了你要导出的一条条数据,再选你要导出的字段名称,懂了吧),这里可以换成你自己要接收的数据
package XXX.XXX.vo;
import lombok.Data;
import java.util.List;
@Data
public class ParticularsVo {
private List<String> names;
private List<String> ids;
}
我的实体类例子:
@ApiModelProperty(value = "姓名")
private String name;
@ApiModelProperty(value = "身份证号")
private String idcard;
@ApiModelProperty(value = "年龄")
private String age;
@ApiModelProperty(value = "性别")
private String sex;
@ApiModelProperty(value = "民族")
private String nationality;
必须有一个可以写名称的注解,因为要取出名称取比较,不想用我这个注解的可以自定义一个,但我考虑单你都找到这了肯定不想写自定义注解,我就放出来了
用了自定义注解,你就在实体类上@HeaderName(“名称”),懂了吧
import java.lang.annotation.*;
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface HeaderName {
String value() default "";
}
用到的比较的工具类,这里的思路是从数据库查出来的数据给到实体类,然后获取你实体类里的注解名称,然后去跟前端传给你的字段名去比较,比较一致,说明这个数据是你要导出的,不一致说明这个字段你不导出
package xxx.xxx.utli;
import io.swagger.annotations.ApiModelProperty;
import java.lang.reflect.Field;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
public class EntityTo {
/**
* 数据封装
*
* @param album
* @param names
* @return
* @throws IllegalAccessException
*/
public static Map<String, Object> entityToMap(Object album, List<String> names) throws IllegalAccessException {
Map<String, Object> resMap = new LinkedHashMap<>();
Class<?> albumClass = album.getClass();
// 获取 实体类 中所有的字段
Field[] declaredFields = albumClass.getDeclaredFields();
for (Field declaredField : declaredFields) {
// 这里设置为 true 才可以访问到 实体类中的 private 字段
declaredField.setAccessible(true);
// 获取字段所对应的注解
ApiModelProperty annotation = declaredField.getAnnotation(ApiModelProperty.class);
if (null != annotation) {
// 有注解 则 获取 注解的值(表头名称)
String value = annotation.value();
// 再判断 实体类中是否有这个表头
if (names.contains(value)) {
resMap.put(value, declaredField.get(album));
}
}
}
return resMap;
}
}
导出的工具类:
如果你只是,单张表操作,只传一个List<Map<String, Object>> 就行,多张表,就跟我一样
package xxx.xxx.utli;
import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelUtil;
import io.netty.util.internal.StringUtil;
import org.apache.commons.lang3.time.DateFormatUtils;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Date;
import java.util.List;
import java.util.Map;
public class HuToolExcelUtil {
/**
* 封装 通用导出方法
*
* @param response
* @param
*/
public static void universalExcelExport(HttpServletResponse response,
List<Map<String, Object>> customerMaps,
List<Map<String, Object>> guarantorMaps,
List<Map<String, Object>> repayerMaps,
) {
BigExcelWriter writer = (BigExcelWriter) ExcelUtil.getBigWriter();
ServletOutputStream out = null;
try {
if (customerMaps.size() > 0) {
//自定义表头
writer.merge(customerMaps.size(), "A信息");
//为rtue说明你要把map里的key当作表头,比如姓名,年龄这些,false反之
writer.write(customerMaps, true).passCurrentRow();
}
if (guarantorMaps.size() > 0) {
writer.merge(guarantorMaps.size(), "B信息");
writer.write(guarantorMaps, true).passCurrentRow();
}
if (repayerMaps.size() > 0) {
writer.merge(repayerMaps.size(), "C信息");
writer.write(repayerMaps, true).passCurrentRow();
}
/* writer.write(dataList, true);*/
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
//设置文件标题
String dateTime = DateFormatUtils.format(new Date(), "yyyyMMddHHmm");
String fileName = "详细信息" + dateTime + ".xls";
response.setHeader("content-disposition", "attachment;filename="
+ URLEncoder.encode(fileName, "UTF-8"));
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
e.printStackTrace();
} finally {
writer.close();
IoUtil.close(writer);
}
}
}
导出效果图:
传的Json:
{
"names": [
"教育",
"住宅状况",
"姓名",
"关系"
],
"ids": [
"1",
"2"
]
}