背景
有个功能需要导入导出多sheet页的Excel,以前用poi搞,想试下用hutool处理一下。接口已弄完,简单总结一下。
导入
controller
正常使用就行,header的参数用来确认租户
@ApiOperation(value = "导入字段分组excel", notes = "")
@PostMapping(value = "/import/field")
public R importFieldGroupModel(MultipartFile file, String ruleId, String mapId, @RequestHeader(BasePlatformConstant.TENANT_ID) String tenantId) {
return pmFieldGroupService.importExcel(file, ruleId, mapId,tenantId);
}
service
我是通过制定sheet页的顺序,来读取sheet内容
ExcelReader sheetReader = ExcelUtil.getReader(file.getInputStream(), i);
官方给出的文档:
https://doc.hutool.cn/pages/ExcelReader/
https://doc.hutool.cn/pages/ExcelUtil
/**
* 导入字段分组Excel模板
* @param file 字段分组Excel
* @param ruleId 规则id
* @param mapId 地图id
* @param tenantId 租户id
*/
@Override
public R importExcel(MultipartFile file, String ruleId, String mapId, String tenantId) {
String aliasNameException = "";
String sheetNameException = "";
try {
InputStream inputStream = file.getInputStream();
ExcelReader readerAll = ExcelUtil.getReader(inputStream);
int sheetCount = readerAll.getSheetCount();
List<String> sheetNameList = readerAll.getSheetNames();
for (int i = 0; i < sheetCount; i++) {
// 获取图层名称
String sheetName = sheetNameList.get(i);
sheetNameException = sheetName;
ExcelReader sheetReader = ExcelUtil.getReader(file.getInputStream(), i);
List<Map<String, Object>> sheetFieldGroup = sheetReader.readAll();
List<PmFieldGroupVO> targetPpmFieldGroupVOList = new ArrayList<>();
for (Map fieldInfo : sheetFieldGroup) {
// 遍历sheet页中记录,构造需要导入的对象
PmFieldGroupVO pmFieldGroupVO = new PmFieldGroupVO();
pmFieldGroupVO.setRuleId(ruleId);
pmFieldGroupVO.setLayerId(layerId);
pmFieldGroupVO.setFieldName(fieldname);
pmFieldGroupVO.setGroupId(baseGroup.getId());
pmFieldGroupVO.setOrders(order == null ? 10 : order);
targetPpmFieldGroupVOList.add(pmFieldGroupVO);
}
save(targetPpmFieldGroupVOList);
}
} catch (Exception exception) {
exception.printStackTrace();
log.error(exception.getMessage());
log.error("异常图层名称" + sheetNameException);
log.error("异常字段名称:" + aliasNameException);
return R.error(exception.getMessage());
}
return R.ok();
}
导出
controller
@ApiOperation(value = "导出字段分组excel", notes = "")
@PostMapping(value = "/export/field")
public void exportFieldGroupModel(@RequestBody PmLayerVO vo, HttpServletResponse response) {
pmFieldGroupService.exportExcel(vo,response);
}
service
ExcelWriter对象默认使用的是第一个sheet页。因此需要根据实际情况决定是否需要重命名
writer.renameSheet(layersEntityList.get(0).getName());//重命名sheet页
切换sheet页也是创建sheet页。可以直接指定sheet页名称
writer.setSheet(layersEntityList.get(i).getName());
注
需要注意的是,切换sheet页后需要设置样式。
public void exportExcel(PmLayerVO vo, HttpServletResponse response) {
try {
ExcelWriter writer = ExcelUtil.getWriter(true);
//单元格宽度高度
writer.setColumnWidth(-1, 18);
writer.setRowHeight(-1, 20);
writer.setOnlyAlias(true);
// 定义单元格背景色
StyleSet style = writer.getStyleSet();
// 第二个参数表示是否也设置头部单元格背景
style.setBackgroundColor(IndexedColors.WHITE, true);
Map<String, String> apiModelProperty = ClassPropertyCommentsUtils.getApiModelProperty(new FieldGroupVO());
// 第一个图层/sheet页需要特殊处理
List<FieldGroupVO> firstLayerFieldGroupList = getFieldGroupByLayerIdRuleId(vo.getRuleId(),
layersEntityList.get(0).getId());
writer.renameSheet(layersEntityList.get(0).getName());
if (firstLayerFieldGroupList.size() > 0) {
apiModelProperty.forEach((k, v) -> writer.addHeaderAlias(v, k));
writer.write(firstLayerFieldGroupList, true);
}
// 按图层构建分组信息
for (int i = 1; i < layersEntityList.size(); i++) {
// 切换sheet页
writer.setSheet(layersEntityList.get(i).getName());
List<FieldGroupVO> layerFieldGroupList = getFieldGroupByLayerIdRuleId(vo.getRuleId(),
layersEntityList.get(i).getId());
apiModelProperty.forEach((k, v) -> writer.addHeaderAlias(v, k));
//单元格宽度高度
writer.setColumnWidth(-1, 18);
writer.setRowHeight(-1, 20);
writer.setOnlyAlias(true);
writer.write(layerFieldGroupList, true);
}
ExportFileUtil.setResponseHeader(response, "信息表" + DateTime.now().getTime() + ".xlsx");
OutputStream os = response.getOutputStream();
writer.flush(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
工具类
获取对象的ApiModelProperty注释
/**
* 获取类注释
*
*/
public class ClassPropertyCommentsUtils {
public static Map<String, String> getApiModelProperty(Object obj) {
try {
// 1.根据类路径获取类
Class<?> c = obj.getClass();
// 2.获取类的属性
Field[] declaredFields = c.getDeclaredFields();
Map<String, String> map = new LinkedHashMap<>(declaredFields.length);
// 3.遍历属性,获取属性上ApiModelProperty的值,属性的名,存入Properties
if (declaredFields.length != 0) {
for (Field field : declaredFields) {
if (field.getAnnotation(ApiModelProperty.class) != null) {
map.put(field.getAnnotation(ApiModelProperty.class).value(), field.getName());
}
}
return map;
}
} catch (Exception e) {
}
return null;
}
public static void nullifyStrings(Object o) {
for (Field f : o.getClass().getDeclaredFields()) {
f.setAccessible(true);
try {
if (f.getType().equals(String.class)) {
String value = (String) f.get(o);
if (value != null && value.trim().isEmpty()) {
f.set(o, null);
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
}
}
导出Excel工具类中设置响应头的函数
/*
设置浏览器下载响应头
*/
public static void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}