封装导出功能（注解，反射）
- 工具类
/**
* @description: 有关excel工具类
*/
public class ExcelsUtil<T> {
private static final Logger log = LoggerFactory.getLogger(ExcelsUtil.class);
/**
* 转化的属性List
* */
public List converterExpList;
/**
* 转化的属性Map
* */
public Map converterExpMap;
/**
* 实体对象
*/
public Class<T> clazz;
public ExcelsUtil(Class<T> clazz)
{
this.clazz = clazz;
this.converterExpList = new ArrayList();
this.converterExpMap = new HashMap();
this.initConverterExpList();
}
/**
* @param response 响应
* @param list 数据
*/
public void exportExcel(HttpServletResponse response, List list) {
ExcelWriter writer = null;
Map<String, String> excelMap = this.createExcelField();
try {
writer = ExcelUtil.getWriter(true);
for (String key : excelMap.keySet()) {
writer.addHeaderAlias(key, excelMap.get(key));
}
writer.write(list, true);
response.setContentType("application/force-download");
response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("test.xlsx", "UTF-8"));
OutputStream out = response.getOutputStream();
writer.flush(out);
out.flush();
out.close();
} catch (Exception e) {
log.error("劵管理导出异常:{}", JacksonUtils.obj2json(e));
}
}
/**
* 初始化转换字段
* */
private void initConverterExpList() {
log.info("==excel导出初始化开始==");
Map<String, String> map = new HashMap<>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
tempFields.stream().forEach(field -> {
// 单注解
if (field.isAnnotationPresent(Excel.class))
{
Excel attr = field.getAnnotation(Excel.class);
if(!StrUtil.isBlankIfStr(attr.readConverterExp())) {
String key = field.getName();
this.converterExpList.add(key);
this.converterExpMap.put(key, attr.readConverterExp());
}
}
});
log.info("==excel导出初始化结束==");
}
/**
* @param field 属性名
* @return Boolean
*/
public Boolean isConverterExpList(Field field) {
return this.converterExpList.contains(field)? true : false;
}
/**
* 配置excel导出模板所有定义字段
*
*/
private Map createExcelField() {
log.info("==excel导出定义模板开始==");
Map<String, String> map = new HashMap<>();
List<Field> tempFields = new ArrayList<>();
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
tempFields.stream().forEach(field -> {
// 单注解
if (field.isAnnotationPresent(Excel.class))
{
Excel attr = field.getAnnotation(Excel.class);
String value = attr.name();
String key = field.getName();
map.put(key, value);
if(StrUtil.isBlankIfStr(attr.readConverterExp())) {
this.converterExpList.add(key);
}
}
});
log.info("==excel导出定义模板结束:{}==", JacksonUtils.obj2json(map));
return map;
}
/**
* 过滤数据
* @param list 需要过滤的原始数据
* */
public List filterData(List list) {
log.info("==excel导出过滤数据开始==");
Map<String, String> converterExpMap = this.converterExpMap;
list.stream().forEach(item -> {
Class aClass = item.getClass();
Field[] declaredFields = aClass.getDeclaredFields();
for (Field field : declaredFields) {
if (field.isAnnotationPresent(Excel.class)) {
Excel annotation = field.getAnnotation(Excel.class);
if(StrUtil.isBlankIfStr(annotation.readConverterExp())) {
String name = field.getName();
String gm = this.converterMethod(name, 0);
try {
Method getMethod = aClass.getMethod(gm);
String value =(String) getMethod.invoke(item);
String expValue = this.reverseByExp(value, converterExpMap.get(name));
String sm = this.converterMethod(name, null);
Method setMethod = aClass.getMethod(sm, String.class);
setMethod.invoke(item, expValue);
} catch (Exception e) {
log.error("导出解析异常:{}", JacksonUtils.obj2json(e));
e.printStackTrace();
}
}
}
}
});
log.info("==excel导出过滤数据结束:{}==", JacksonUtils.obj2json(list));
return list;
}
/**
* 转换
* */
public String converterMethod(String name, Integer type) {
String pre = type == null? "set" : "get";
return pre + name.substring(0,1).toUpperCase() + name.substring(1);
}
/**
* @param propertyValue 参数值
* @param converterExp 翻译注解
* @return 解析后值
* @throws Exception
*/
public String reverseByExp(String propertyValue, String converterExp) throws Exception
{
try
{
String[] convertSource = converterExp.split(",");
for (String item : convertSource)
{
String[] itemArray = item.split("=");
if (itemArray[0].equals(propertyValue))
{
return itemArray[1];
}
}
}
catch (Exception e)
{
throw e;
}
return propertyValue;
}
}
- 注解Excel
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
/**
* 导出到Excel中的名字.
*/
public String name() default "";
/**
* 日期格式, 如: yyyy-MM-dd
*/
public String dateFormat() default "";
/**
* 读取内容转表达式 (如: 0=男,1=女,2=未知)
*/
public String readConverterExp() default "";
/**
* 导出时在excel中每个列的高度 单位为字符
*/
public double height() default 14;
/**
* 导出时在excel中每个列的宽 单位为字符
*/
public double width() default 16;
/**
* 文字后缀,如% 90 变成90%
*/
public String suffix() default "";
/**
* 当值为空时,字段的默认值
*/
public String defaultValue() default "";
/**
* 提示信息
*/
public String prompt() default "";
/**
* 设置只能选择不能输入的列内容.
*/
public String[] combo() default {};
/**
* 是否导出数据,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写.
*/
public boolean isExport() default true;
/**
* 另一个类中的属性名称,支持多级获取,以小数点隔开
*/
public String targetAttr() default "";
/**
* 字段类型（0：导出导入；1：仅导出；2：仅导入）
*/
Type type() default Type.ALL;
public enum Type
{
ALL(0), EXPORT(1), IMPORT(2);
private final int value;
Type(int value)
{
this.value = value;
}
public int value()
{
return this.value;
}
}
}
excels
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Excels {
Excel[] value();
}
- DTO
@Setter
@Getter
@NoArgsConstructor
@ToString
public class GetCouponsTypeListReqDTO {
/**
* 券类型编号
*/
private String atno;
/**
* 批量查询 券批次编号
*/
private List<String> atnos;
/**
* 券类型名称
* 如果请求参数是at_name，fastjson首先找at_name，没找到再赋值给atName
*/
@JsonProperty(value = "at_name")
private String atName;
/**
* 别名
*/
private String alias;
/**
* 券类型
* 01 折
* 02 减
* 03 返
* 04 赠
*/
@JsonProperty(value = "cny_type")
private String cnyType;
@JsonProperty(value = "plan_id")
private String planId;
/**
* 状态
* state=1,进行中的券，查询条件 and act.end_time >= now() and act.ing_num > 0
*/
private Integer state;
/**
* 券商编号
*/
private String ino;
/**
* 发券模式
* 1 折上折 2优惠购 3 权益工具包 4 CPS 5 营销活动（仅入库不销售） 6 个人发放（仅入库不销售）7 分销发放（仅入库不销售）
*/
@JsonProperty(value = "pool")
private Integer pool;
/**
* CPS序号
*/
@JsonProperty(value = "cps_no")
private String cpsNo;
/**
* 仅入库不销售 1 是
*/
@JsonProperty("no_sale")
private Integer noSale;
/**
* 券有效期的开始时间，yyyy-MM-dd HH:mm:ss
*/
@JsonProperty("start_time")
private String startTime;
/**
* 券有效期的结束时间，yyyy-MM-dd HH:mm:ss
*/
@JsonProperty("end_time")
private String endTime;
public void setAtno(String atno) {
this.atno = atno;
if(StrUtil.isNotBlank(atno)){
String[] split = atno.replaceAll("，", ",").split(",");
if(split == null || split.length == 0) return;
List<String> strings = Arrays.asList(split);
List<String> collects = strings.stream().filter(string -> !string.isEmpty()).collect(Collectors.toList());
if(collects == null || collects.size() == 0) return;
this.atnos = collects;
}
}
}
- controller
@RequestMapping(value = "exportCoupon", method = RequestMethod.POST)
public void exportCoupon(HttpServletRequest request,
HttpServletResponse response, @RequestBody ReqDTO<GetCouponsTypeListReqDTO> reqDTO) {
GetCouponsTypeListReqDTO req = new GetCouponsTypeListReqDTO();
List<AcctypeDTO> list = null;
try {
list = acctypeMapper.selectAccTypePage(req, 1, 2);
} catch (Exception e) {
log.error("劵管理导出，查询劵批次异常:{}", JacksonUtils.obj2json(e));
}
ExcelsUtil<AcctypeDTO> excelsUtil = new ExcelsUtil<>(AcctypeDTO.class);
List resList = excelsUtil.filterData(list);
excelsUtil.exportExcel(response, resList);
}
- 前端请求(必须用原生的请求)
// 导出
function exportHandle() {
var json = {
"req_no": "123","method":"mgr.exportCoupon",
"biz_params":JSON.stringify({'atno': param })
};
var biz_params = JSON.stringify(getParams(json))
downLoadFile({
data:biz_params,
url: "http://xxx/mgr/exportCoupon"
// url:biz_url
})
}
function downLoadFile(options) {
var sendData = options.data;
var ajaxRequest = new XMLHttpRequest();
ajaxRequest.responseType = 'arraybuffer';
ajaxRequest.onreadystatechange = function () {
if (ajaxRequest.readyState == 4 && ajaxRequest.status == 200) {
// debugger;
ajaxRequest.response.head
var blob = new Blob([ajaxRequest.response], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
var objectUrl = URL.createObjectURL(blob);
var a = document.createElement("a");
document.body.appendChild(a);
a.style = "display: none";
a.href = objectUrl;
// a.download = fileName;
a.download = "test.xlsx";
a.click();
document.body.removeChild(a);
}
else if (ajaxRequest.status == 409 || ajaxRequest.status == 500 || ajaxRequest.status == 204) {
}
}
ajaxRequest.open("POST", options.url, true);
ajaxRequest.setRequestHeader("Content-Type", "application/json");
ajaxRequest.send(sendData);
}
网友评论