1,Utils:
package com.osrmt.util;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Excel处理工具类
*
*/
public class ExcelUtil {
/**
* 导出excel
* @param headNameList 文件字段头显示名字
* @param headField 文件字段头数据字段
* @param listData 数据集合
* @param stream 流
* @throws Exception 异常
*/
public static void exportExcel(List headNameList,List headField,List
2,实体类:
package com.osrmt.entity;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@TableName(value = "xq_nenglishuxing")
public class XqNengliShuxing {
@TableId(type= IdType.INPUT)
@ApiModelProperty("主键id")
private Long id;
@TableField(value="code")
@ApiModelProperty("目标编号")
private String code;
@TableField(value="description")
@ApiModelProperty("能力属性描述信息")
private String description;
@TableField(value="priority")
@ApiModelProperty("优先级(0低 1中 2高)")
private Integer priority;
@TableField(value="is_disable")
@ApiModelProperty("是否禁用(0启用 1禁用)")
private Integer isDisable;
@TableField(value="creator")
@ApiModelProperty("创建人")
private String creator;
@TableField(value = "createtime")
@ApiModelProperty("创建时间")
private Long createTime;
@TableField(value="nlqd_id")
@ApiModelProperty("所属能力清单的id")
private Long nlqdId;
@TableField(value = "nengli_mubiao_value")
@ApiModelProperty("能力目标值")
private String nengliMubiaoValue;
}
3,接口实现
@GetMapping("/exportExcel")
@ApiOperation("导出能力清单能力属性excel")
public Response exportExcel(@RequestParam("nlqdId") Long nlqdId,
HttpServletResponse response) throws Exception {
QueryWrapper queryWrapper = new QueryWrapper<>();
queryWrapper.lambda().eq(XqNengliShuxing::getNlqdId, nlqdId);
List list = nengliShuxingService.list(queryWrapper);
List> data = new ArrayList<>();
for (int i = 0; i < list.size(); i++) {
Map map = JSON.parseObject(JSON.toJSONString(list.get(i)), Map.class);
data.add(map);
}
Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
List headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());
List headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
System.out.println(properties);
try {
ExcelUtil.exportExcel(headNameList, headFieldList, data, response.getOutputStream());
} catch (Exception e) {
e.printStackTrace();
throw new Exception("导出excel失败");
}
return Response.success();
}
/** 功能描述:
* 获取类字段ApiModelProperty注解value值(中文)
* @param classPath: 类路径
* @author: zl
* @date: 2022/2/17 17:10
*/
private Properties getApiModelProperty(String classPath){
Properties p = new Properties();
try {
// 1.根据类路径获取类
Class<?> c = Class.forName(classPath);
// 2.获取类的属性
Field[] declaredFields = c.getDeclaredFields();
// 3.遍历属性,获取属性上ApiModelProperty的值,属性的名,存入Properties
if (declaredFields.length != 0) {
for (Field field : declaredFields) {
if (field.getAnnotation(ApiModelProperty.class) != null) {
// key和value可根据需求存
// 这存的key为类属性名,value为注解的值
p.put(field.getName(), field.getAnnotation(ApiModelProperty.class).value());
}
}
return p;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
return p;
}
@PostMapping("/importExcel")
@ApiOperation("导入能力清单能力属性excel")
public Response importExcel(MultipartFile file, Long nlqdId) {
Properties properties = getApiModelProperty("com.osrmt.entity.XqNengliShuxing");
List headNameList = properties.values().stream().map(String::valueOf).collect(Collectors.toList());
List headFieldList = properties.keySet().stream().map(String::valueOf).collect(Collectors.toList());
try {
HSSFWorkbook workbook =new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
List> maps = ExcelUtil.analysisExcel(workbook, headNameList, headFieldList);
for (int i = 0; i < maps.size(); i++) {
XqNengliShuxing xqNengliShuxing = JSON.parseObject(JSON.toJSONString(maps.get(i)), XqNengliShuxing.class);
NengliShuxingProperty nengliShuxingProperty = new NengliShuxingProperty();
BeanUtils.copyProperties(xqNengliShuxing, nengliShuxingProperty);
nengliShuxingProperty.setNlqdId(nlqdId);
addNengliShuxing(nengliShuxingProperty);
}
} catch (IOException e) {
e.printStackTrace();
}
return Response.success();
}
4,导出样例: