From 1abd541be8d02dabaa77d3c6d2fecde5ef0e4b5b Mon Sep 17 00:00:00 2001
From: linwei <872216696@qq.com>
Date: Thu, 29 Jan 2026 17:02:50 +0800
Subject: [PATCH] 区划改造
---
drone-service/drone-system/src/main/java/org/sxkj/system/excel/DeptImportExcel.java | 4
drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/RegionSheetWriteHandler.java | 16 ++-
drone-service/drone-system/src/main/java/org/sxkj/system/service/IRegionService.java | 3
drone-service/drone-system/src/main/java/org/sxkj/system/controller/RegionController.java | 38 ++++++-
drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImporter.java | 4
drone-service/drone-system/src/main/java/org/sxkj/system/controller/DeptController.java | 6 -
drone-service/drone-system/src/main/java/org/sxkj/system/service/impl/RegionServiceImpl.java | 52 +++++++++-
drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImportExcel.java | 70 ++++++++++++++
drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/DeptSheetWriteHandler.java | 77 +++++++++++++++
drone-service/drone-system/src/main/java/org/sxkj/system/mapper/RegionMapper.xml | 2
10 files changed, 241 insertions(+), 31 deletions(-)
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/controller/DeptController.java b/drone-service/drone-system/src/main/java/org/sxkj/system/controller/DeptController.java
index 0a8e9c0..6405060 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/controller/DeptController.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/controller/DeptController.java
@@ -367,12 +367,6 @@
/**
* 导出模板
- * <p>
- * 为区域列添加下拉选择功能，限制用户只能选择预设的区域名称
- * </p>
- *
- * @param response HTTP响应对象
- * @throws IOException 导出异常
*/
@GetMapping("export-template")
@ApiOperationSupport(order = 16)
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/controller/RegionController.java b/drone-service/drone-system/src/main/java/org/sxkj/system/controller/RegionController.java
index fb60c74..a449be8 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/controller/RegionController.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/controller/RegionController.java
@@ -16,6 +16,7 @@
*/
package org.sxkj.system.controller;
+import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
@@ -32,11 +33,14 @@
import org.springblade.core.tool.utils.BeanUtil;
import org.springblade.core.tool.utils.DateUtil;
import org.sxkj.system.entity.Region;
+import org.sxkj.system.excel.DeptImportExcel;
import org.sxkj.system.excel.RegionExcel;
+import org.sxkj.system.excel.RegionImportExcel;
import org.sxkj.system.excel.RegionImporter;
import org.sxkj.system.param.RegionAddParam;
import org.sxkj.system.param.RegionPageParam;
import org.sxkj.system.service.IRegionService;
+import org.sxkj.system.util.excel.RegionSheetWriteHandler;
import org.sxkj.system.vo.RegionVO;
import org.sxkj.system.vo.TreeVo;
import org.sxkj.system.wrapper.RegionWrapper;
@@ -46,6 +50,10 @@
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
+import java.io.IOException;
+import java.io.UnsupportedEncodingException;
+import java.net.URLEncoder;
+import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@@ -197,7 +205,7 @@
@ApiOperation(value = "导入行政区划", notes = "传入excel")
public R importRegion(MultipartFile file, Integer isCovered) {
RegionImporter regionImporter = new RegionImporter(regionService, isCovered == 1);
- ExcelUtil.save(file, regionImporter, RegionExcel.class);
+ ExcelUtil.save(file, regionImporter, RegionImportExcel.class);
return R.success("操作成功");
}
@@ -216,12 +224,32 @@
/**
* 导出模板
*/
+ // @GetMapping("export-template")
+ // @ApiOperationSupport(order = 12)
+ // @ApiOperation(value = "导出模板")
+ // public void exportUser(HttpServletResponse response) {
+ // List<RegionImportExcel> list = new ArrayList<>();
+ // ExcelUtil.export(response, "行政区划模板", "行政区划表", list, RegionImportExcel.class);
+ // }
+
@GetMapping("export-template")
- @ApiOperationSupport(order = 12)
+ @ApiOperationSupport(order = 16)
@ApiOperation(value = "导出模板")
- public void exportUser(HttpServletResponse response) {
- List<RegionExcel> list = new ArrayList<>();
- ExcelUtil.export(response, "行政区划模板", "行政区划表", list, RegionExcel.class);
+ public void exportUser(HttpServletResponse response) throws IOException {
+ // 设置响应头
+ response.setContentType("application/vnd.ms-excel");
+ response.setCharacterEncoding(StandardCharsets.UTF_8.name());
+ String fileName = URLEncoder.encode("行政区划模板", StandardCharsets.UTF_8.name());
+ response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+
+ // 准备空数据列表
+ List<RegionImportExcel> list = new ArrayList<>();
+
+ // 使用EasyExcel导出，并注册RegionSheetWriteHandler
+ EasyExcel.write(response.getOutputStream(), RegionImportExcel.class)
+ .registerWriteHandler(new RegionSheetWriteHandler())
+ .sheet("行政区划表")
+ .doWrite(list);
}
/**
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/excel/DeptImportExcel.java b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/DeptImportExcel.java
index 2d6d641..95c7964 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/excel/DeptImportExcel.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/DeptImportExcel.java
@@ -44,10 +44,8 @@
@ColumnWidth(20)
private String deptName;
- @ExcelProperty(value = "所属区划", converter = ExcelDictConverter.class)
+ @ExcelProperty(value = "所属区划编码")
@ColumnWidth(20)
- @ExcelDictItemLabel(type = "regionName")
- @ExcelDictItem(type = "regionName")
private String areaCode;
@ExcelProperty("机构描述")
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImportExcel.java b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImportExcel.java
new file mode 100644
index 0000000..00248a1
--- /dev/null
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImportExcel.java
@@ -0,0 +1,70 @@
+/*
+ * Copyright (c) 2018-2028, Chill Zhuang All rights reserved.
+ *
+ * Redistribution and use in source and binary forms, with or without
+ * modification, are permitted provided that the following conditions are met:
+ *
+ * Redistributions of source code must retain the above copyright notice,
+ * this list of conditions and the following disclaimer.
+ * Redistributions in binary form must reproduce the above copyright
+ * notice, this list of conditions and the following disclaimer in the
+ * documentation and/or other materials provided with the distribution.
+ * Neither the name of the dreamlu.net developer nor the names of its
+ * contributors may be used to endorse or promote products derived from
+ * this software without specific prior written permission.
+ * Author: Chill 庄骞 (smallchill@163.com)
+ */
+package org.sxkj.system.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import com.alibaba.excel.annotation.write.style.ContentRowHeight;
+import com.alibaba.excel.annotation.write.style.HeadRowHeight;
+import lombok.Data;
+import org.sxkj.system.util.excel.ExcelDictConverter;
+import org.sxkj.system.util.excel.ExcelDictItem;
+import org.sxkj.system.util.excel.ExcelDictItemLabel;
+
+import java.io.Serializable;
+
+/**
+ * RegionExcel
+ *
+ * @author Chill
+ */
+@Data
+@ColumnWidth(16)
+@HeadRowHeight(20)
+@ContentRowHeight(18)
+public class RegionImportExcel implements Serializable {
+ private static final long serialVersionUID = 1L;
+
+ @ColumnWidth(20)
+ @ExcelProperty("区划编号")
+ private String code;
+
+ @ColumnWidth(20)
+ @ExcelProperty("区划名称")
+ private String name;
+
+ @ColumnWidth(20)
+ @ExcelProperty("上级区划编码")
+ private String parentCode;
+
+ @ColumnWidth(20)
+ @ExcelProperty(value = "层级", converter = ExcelDictConverter.class)
+ @ExcelDictItemLabel(type = "regionLevel")
+ @ExcelDictItem(type = "regionLevel")
+ private String regionLevel;
+
+ @ColumnWidth(20)
+ @ExcelProperty("排序")
+ private Integer sort;
+
+ @ColumnWidth(20)
+ @ExcelProperty(value = "机构状态", converter = ExcelDictConverter.class)
+ @ExcelDictItemLabel(type = "institutionStatus")
+ @ExcelDictItem(type = "institutionStatus")
+ private String status;
+
+}
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImporter.java b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImporter.java
index 6d655f6..7d1b8eb 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImporter.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/excel/RegionImporter.java
@@ -28,13 +28,13 @@
* @author Chill
*/
@RequiredArgsConstructor
-public class RegionImporter implements ExcelImporter<RegionExcel> {
+public class RegionImporter implements ExcelImporter<RegionImportExcel> {
private final IRegionService service;
private final Boolean isCovered;
@Override
- public void save(List<RegionExcel> data) {
+ public void save(List<RegionImportExcel> data) {
service.importRegion(data, isCovered);
}
}
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/RegionMapper.xml b/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/RegionMapper.xml
index be51313..81ffd85 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/RegionMapper.xml
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/mapper/RegionMapper.xml
@@ -164,7 +164,7 @@
and code like concat(concat('%', #{param2.cityCode}),'%')
</if>
<if test="param2.districtName!=null and param2.districtName!=''">
- and district_name like concat(concat('%', #{param2.districtName}),'%')
+ and name like concat(concat('%', #{param2.districtName}),'%')
</if>
<if test="param2.parentName!=null and param2.parentName!=''">
and (
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/service/IRegionService.java b/drone-service/drone-system/src/main/java/org/sxkj/system/service/IRegionService.java
index d137ac0..74c209e 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/service/IRegionService.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/service/IRegionService.java
@@ -21,6 +21,7 @@
import com.baomidou.mybatisplus.extension.service.IService;
import org.sxkj.system.entity.Region;
import org.sxkj.system.excel.RegionExcel;
+import org.sxkj.system.excel.RegionImportExcel;
import org.sxkj.system.param.RegionPageParam;
import org.sxkj.system.vo.RegionVO;
import org.sxkj.system.vo.TreeVo;
@@ -85,7 +86,7 @@
* @param isCovered
* @return
*/
- void importRegion(List<RegionExcel> data, Boolean isCovered);
+ void importRegion(List<RegionImportExcel> data, Boolean isCovered);
/**
* 导出区划数据
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/service/impl/RegionServiceImpl.java b/drone-service/drone-system/src/main/java/org/sxkj/system/service/impl/RegionServiceImpl.java
index 9aab317..14ad7e0 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/service/impl/RegionServiceImpl.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/service/impl/RegionServiceImpl.java
@@ -27,8 +27,10 @@
import org.springblade.core.log.exception.ServiceException;
import org.springblade.core.tool.utils.*;
import org.sxkj.common.func.Streams;
+import org.sxkj.system.cache.RegionCache;
import org.sxkj.system.entity.Region;
import org.sxkj.system.excel.RegionExcel;
+import org.sxkj.system.excel.RegionImportExcel;
import org.sxkj.system.mapper.RegionMapper;
import org.sxkj.system.param.RegionPageParam;
import org.sxkj.system.service.IRegionService;
@@ -124,11 +126,47 @@
return baseMapper.lazyTree(parentCode, param);
}
+ // WHEN
+ // region_level = '5' THEN town_name
+ // WHEN
+ // region_level = '4' THEN district_name
+ // WHEN
+ // region_level = '3' THEN city_name
+ // WHEN
+ // region_level = '2' THEN province_name
@Override
- public void importRegion(List<RegionExcel> data, Boolean isCovered) {
+ public void importRegion(List<RegionImportExcel> data, Boolean isCovered) {
List<Region> list = new ArrayList<>();
data.forEach(regionExcel -> {
Region region = BeanUtil.copy(regionExcel, Region.class);
+ region.setRegionLevel(Integer.valueOf(regionExcel.getRegionLevel()));
+ region.setCityName("吉安市");
+ region.setCityCode("360800000000");
+ region.setProvinceCode("360000000000");
+ region.setProvinceName("江西省");
+ if (region.getRegionLevel().equals(DISTRICT_LEVEL)) {
+ region.setDistrictCode(region.getCode());
+ region.setDistrictName(region.getName());
+ }
+ if (region.getRegionLevel().equals(TOWN_LEVEL)) {
+ String parentCode = region.getParentCode();
+ Region byCode = getByCode(parentCode);
+ region.setDistrictCode(byCode.getCode());
+ region.setDistrictName(byCode.getName());
+ region.setTownCode(region.getCode());
+ region.setTownName(region.getName());
+ }
+ if (region.getRegionLevel().equals(VILLAGE_LEVEL)) {
+ String parentCode = region.getParentCode();
+ Region byCode = getByCode(parentCode);
+ region.setTownCode(byCode.getCode());
+ region.setTownName(byCode.getName());
+ Region byCode2 = getByCode(byCode.getParentCode());
+ region.setDistrictCode(byCode2.getCode());
+ region.setDistrictName(byCode2.getName());
+ region.setVillageCode(region.getCode());
+ region.setVillageName(region.getName());
+ }
list.add(region);
});
if (isCovered) {
@@ -197,15 +235,15 @@
@Override
public TreeVo buildRegionTree(String parentCode) {
- if(StringUtil.isBlank(parentCode)){
+ if (StringUtil.isBlank(parentCode)) {
return null;
}
LambdaQueryWrapper<Region> query = Wrappers.<Region>lambdaQuery()
.le(Region::getRegionLevel, 3)
.likeRight(Region::getParentCode, parentCode.replaceAll("0+$", ""))
.or(regionLambdaQueryWrapper ->
- regionLambdaQueryWrapper.eq(Region::getCode, parentCode)
- );
+ regionLambdaQueryWrapper.eq(Region::getCode, parentCode)
+ );
List<Region> list = list(query);
List<TreeVo> regionVOS = Streams.toList(list, region -> {
TreeVo vo = new TreeVo();
@@ -220,7 +258,7 @@
List<TreeVo> regionVOS1 = buildRegionTree(regionVOS, parentCode);
TreeVo treeVo = map.get(parentCode);
- if(Objects.nonNull(treeVo)){
+ if (Objects.nonNull(treeVo)) {
treeVo.setChildrens(regionVOS1);
}
@@ -240,7 +278,7 @@
* @return List of RegionVO trees starting from the specified parentCode
*/
public List<TreeVo> buildRegionTree(List<TreeVo> allRegions, String parentCode) {
- if(CollectionUtil.isEmpty(allRegions)){
+ if (CollectionUtil.isEmpty(allRegions)) {
return Collections.EMPTY_LIST;
}
@@ -255,7 +293,7 @@
/**
* Recursive method to build the region tree
*
- * @param regionMap Map of regions grouped by parentCode
+ * @param regionMap Map of regions grouped by parentCode
* @param currentCode Current region code to process
* @return List of RegionVOs with children populated
*/
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/DeptSheetWriteHandler.java b/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/DeptSheetWriteHandler.java
new file mode 100644
index 0000000..1d668f9
--- /dev/null
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/DeptSheetWriteHandler.java
@@ -0,0 +1,77 @@
+package org.sxkj.system.util.excel;
+
+import com.alibaba.excel.write.handler.SheetWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+/**
+ * 区域数据验证处理器
+ * <p>
+ * 用于为Excel导出模板中的区域列添加下拉选择功能
+ * </p>
+ *
+ * @author Chill
+ */
+public class DeptSheetWriteHandler implements SheetWriteHandler {
+
+ /**
+ * 区域名称列表
+ * <p>
+ * 这里需要根据实际情况从区域数据源获取
+ * </p>
+ */
+
+ // 启用 禁用
+ private static final String[] ENABLE_DISABLE = { "启用", "禁用" };
+
+ @Override
+ public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
+ // 工作表创建前的操作，暂时不需要
+ }
+
+ @Override
+ public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
+ Workbook workbook = writeWorkbookHolder.getWorkbook();
+ Sheet sheet = writeSheetHolder.getSheet();
+
+ // 为区域列添加下拉选择
+ // 假设区域列是第3列（索引为1），从第2行开始（索引为1）
+ addRegionValidation(workbook, sheet, 5, 1, 1000, ENABLE_DISABLE);
+ }
+
+ /**
+ * 为区域列添加数据验证，设置下拉选择
+ *
+ * @param workbook 工作簿
+ * @param sheet 工作表
+ * @param column 列索引
+ * @param startRow 开始行索引
+ * @param endRow 结束行索引
+ */
+ private void addRegionValidation(Workbook workbook, Sheet sheet, int column, int startRow, int endRow, String[] regionNames) {
+ // 创建数据验证助手
+ DataValidationHelper helper = sheet.getDataValidationHelper();
+
+ // 创建下拉列表约束
+ DataValidationConstraint constraint = helper.createExplicitListConstraint(regionNames);
+
+ // 创建数据验证区域
+ CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, column, column);
+
+ // 创建数据验证
+ DataValidation validation = helper.createValidation(constraint, addressList);
+
+ // 设置验证错误提示
+ validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
+ validation.setShowErrorBox(true);
+ validation.setSuppressDropDownArrow(true);
+ validation.createErrorBox("输入错误", "请从下拉列表中选择区域名称");
+
+ // 添加数据验证到工作表
+ sheet.addValidationData(validation);
+ }
+
+
+}
diff --git a/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/RegionSheetWriteHandler.java b/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/RegionSheetWriteHandler.java
index 04584aa..37ad29b 100644
--- a/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/RegionSheetWriteHandler.java
+++ b/drone-service/drone-system/src/main/java/org/sxkj/system/util/excel/RegionSheetWriteHandler.java
@@ -25,9 +25,10 @@
* 这里需要根据实际情况从区域数据源获取
* </p>
*/
- private static final String[] REGION_NAMES = {
- "吉州区", "青原区", "吉安县", "吉水县", "峡江县", "新干县", "永丰县", "泰和县", "遂川县", "万安县"
- };
+
+ private static final String[] REGION_NAMES = { "市级", "区县级", "街道级" };
+ // 启用 禁用
+ private static final String[] ENABLE_DISABLE = { "启用", "禁用" };
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
@@ -41,7 +42,8 @@
// 为区域列添加下拉选择
// 假设区域列是第3列（索引为1），从第2行开始（索引为1）
- addRegionValidation(workbook, sheet, 1, 1, 1000);
+ addRegionValidation(workbook, sheet, 3, 1, 1000, REGION_NAMES);
+ addRegionValidation(workbook, sheet, 5, 1, 1000, ENABLE_DISABLE);
}
/**
@@ -53,12 +55,12 @@
* @param startRow 开始行索引
* @param endRow 结束行索引
*/
- private void addRegionValidation(Workbook workbook, Sheet sheet, int column, int startRow, int endRow) {
+ private void addRegionValidation(Workbook workbook, Sheet sheet, int column, int startRow, int endRow, String[] regionNames) {
// 创建数据验证助手
DataValidationHelper helper = sheet.getDataValidationHelper();
// 创建下拉列表约束
- DataValidationConstraint constraint = helper.createExplicitListConstraint(REGION_NAMES);
+ DataValidationConstraint constraint = helper.createExplicitListConstraint(regionNames);
// 创建数据验证区域
CellRangeAddressList addressList = new CellRangeAddressList(startRow, endRow, column, column);
@@ -75,4 +77,6 @@
// 添加数据验证到工作表
sheet.addValidationData(validation);
}
+
+
}
--
Gitblit v1.9.3