免费的网站域名和空间,网络优化工程师能干一辈子吗,公司怎么做网站需要多少钱,网站可以做充值吗背景
有个功能需要导入导出多sheet页的Excel#xff0c;以前用poi搞#xff0c;想试下用hutool处理一下。接口已弄完#xff0c;简单总结一下。
导入
controller
正常使用就行#xff0c;header的参数用来确认租户 ApiOperation(value 导入字段分组excel,…背景
有个功能需要导入导出多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*/Overridepublic 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();ListString sheetNameList readerAll.getSheetNames();for (int i 0; i sheetCount; i) {// 获取图层名称String sheetName sheetNameList.get(i);sheetNameException sheetName; ExcelReader sheetReader ExcelUtil.getReader(file.getInputStream(), i); ListMapString, Object sheetFieldGroup sheetReader.readAll(); ListPmFieldGroupVO 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);MapString, String apiModelProperty ClassPropertyCommentsUtils.getApiModelProperty(new FieldGroupVO());// 第一个图层/sheet页需要特殊处理ListFieldGroupVO 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());ListFieldGroupVO 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 MapString, String getApiModelProperty(Object obj) {try {// 1.根据类路径获取类Class? c obj.getClass();// 2.获取类的属性Field[] declaredFields c.getDeclaredFields();MapString, String map new LinkedHashMap(declaredFields.length);// 3.遍历属性获取属性上ApiModelProperty的值属性的名存入Propertiesif (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;charsetUTF-8);response.setHeader(Content-Disposition, attachment;filename fileName);response.addHeader(Pargam, no-cache);response.addHeader(Cache-Control, no-cache);} catch (Exception ex) {ex.printStackTrace();}}
