From 9c2530bafbd5f502fd9bdc9abaa7c357d6f91e86 Mon Sep 17 00:00:00 2001
From: xiangpei <xiangpei@timesnew.cn>
Date: 星期二, 06 八月 2024 17:46:00 +0800
Subject: [PATCH] 点位导入导出
---
ycl-server/src/main/java/com/ycl/dataListener/CurrencyDataListener.java | 102 ++++++++++++++++++++
ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml | 18 +++
ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointSelectHandler.java | 44 ++++++++
ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java | 9 +
ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java | 1
ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java | 1
ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java | 32 ++++++
ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java | 17 +++
ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java | 48 +++++++++
ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java | 20 ++++
10 files changed, 290 insertions(+), 2 deletions(-)
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java
new file mode 100644
index 0000000..b384837
--- /dev/null
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointExport.java
@@ -0,0 +1,32 @@
+package com.ycl.platform.domain.excel;
+
+import com.alibaba.excel.annotation.ExcelProperty;
+import com.alibaba.excel.annotation.write.style.ColumnWidth;
+import lombok.Data;
+
+/**
+ * 鐐逛綅瀵煎叆瀵煎嚭
+ *
+ * @author锛歺p
+ * @date锛�2024/8/6 11:38
+ */
+@Data
+public class PointExport {
+
+ @ColumnWidth(50)
+ @ExcelProperty("鐐逛綅鍚嶇О")
+ private String pointName;
+
+ @ColumnWidth(30)
+ @ExcelProperty("鍥芥爣鐮�")
+ private String serialNumber;
+
+ @ColumnWidth(16)
+ @ExcelProperty("鐐逛綅IP")
+ private String pointIP;
+
+ @ColumnWidth(40)
+ @ExcelProperty("褰撳墠杩愮淮鍗曚綅")
+ private String unitName;
+
+}
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointSelectHandler.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointSelectHandler.java
new file mode 100644
index 0000000..d86e1f4
--- /dev/null
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/excel/PointSelectHandler.java
@@ -0,0 +1,44 @@
+package com.ycl.platform.domain.excel;
+
+import com.alibaba.excel.metadata.Head;
+import com.alibaba.excel.metadata.data.WriteCellData;
+import com.alibaba.excel.write.handler.CellWriteHandler;
+import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
+import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddressList;
+
+import java.util.List;
+
+/**
+ * 璁剧疆easy-excel涓嬫媺澶勭悊鍣�
+ *
+ * @author锛歺p
+ * @date锛�2024/3/15 17:27
+ */
+public class PointSelectHandler implements CellWriteHandler {
+
+ private List<String> unitNameList;
+
+ public PointSelectHandler(List unitNameList) {
+ this.unitNameList = unitNameList;
+ }
+
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
+ // 鑾峰彇sheet瀵硅薄
+ Sheet sheet = writeSheetHolder.getSheet();
+
+
+ // 鑾峰彇鏁版嵁鏍￠獙helper锛宔xcel鐨勪笅鎷夊氨鏄�氳繃鏁版嵁鏍￠獙璁剧疆
+ DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
+
+ // 璁剧疆涓嬫媺鑼冨洿锛岀涓�鍒楋紝20000琛屽唴
+ CellRangeAddressList questionTypeRange = new CellRangeAddressList(1, 20000, 4, 4);
+ // 娣诲姞鍗曚綅涓嬫媺
+ DataValidationConstraint questionConstraint = dataValidationHelper.createExplicitListConstraint((String[]) unitNameList.toArray());
+ DataValidation questionValidation = dataValidationHelper.createValidation(questionConstraint, questionTypeRange);
+ sheet.addValidationData(questionValidation);
+
+ }
+}
diff --git a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java
index a67e7dc..f3265dd 100644
--- a/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java
+++ b/ycl-pojo/src/main/java/com/ycl/platform/domain/vo/YwPointVO.java
@@ -17,7 +17,6 @@
* @since 2024-03-05
*/
@Data
-@Accessors(chain = true)
public class YwPointVO extends AbsVo {
/** 鐐逛綅鍚嶇О */
diff --git a/ycl-server/src/main/java/com/ycl/dataListener/CurrencyDataListener.java b/ycl-server/src/main/java/com/ycl/dataListener/CurrencyDataListener.java
new file mode 100644
index 0000000..50ef882
--- /dev/null
+++ b/ycl-server/src/main/java/com/ycl/dataListener/CurrencyDataListener.java
@@ -0,0 +1,102 @@
+package com.ycl.dataListener;
+
+import com.alibaba.excel.context.AnalysisContext;
+import com.alibaba.excel.metadata.data.ReadCellData;
+import com.alibaba.excel.read.listener.ReadListener;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
+
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
+import java.util.function.Consumer;
+
+/**
+ * 閫氱敤easyexcel鏁版嵁鐩戝惉鍣�
+ *
+ * @author锛歺p
+ * @date锛�2024/8/6 15:04
+ */
+public class CurrencyDataListener<T> implements ReadListener<T> {
+
+ private Consumer<List<T>> consumer;
+
+ /**
+ * 姣忛殧100鏉″瓨鍌ㄦ暟鎹簱锛岀劧鍚庢竻鐞唋ist 锛屾柟渚垮唴瀛樺洖鏀�
+ */
+ private static final int BATCH_COUNT = 100;
+
+ /**
+ * 缂撳瓨鐨勬暟鎹�
+ */
+ private List<T> cachedDataList = new ArrayList<>(BATCH_COUNT);
+
+ private final static Logger log = LoggerFactory.getLogger(CurrencyDataListener.class);
+
+ public CurrencyDataListener(Consumer<List<T>> consumer) {
+ this.consumer = consumer;
+ }
+
+ /**
+ * 璇诲彇鍑虹幇寮傚父澶勭悊
+ * @param e
+ * @param analysisContext
+ * @throws Exception
+ */
+ @Override
+ public void onException(Exception e, AnalysisContext analysisContext) throws Exception {
+
+ }
+
+ /**
+ * 澶勭悊琛ㄥご
+ * @param map
+ * @param analysisContext
+ */
+ @Override
+ public void invokeHead(Map<Integer, ReadCellData<?>> map, AnalysisContext analysisContext) {
+
+ }
+
+ /**
+ * 璇诲彇鏁版嵁,姣忎竴鏉℃暟鎹В鏋愰兘浼氭潵璋冪敤
+ * @param data
+ * @param analysisContext
+ */
+ @Override
+ public void invoke(T data, AnalysisContext analysisContext) {
+ cachedDataList.add(data);
+ // 杈惧埌BATCH_COUNT浜嗭紝闇�瑕佸幓瀛樺偍涓�娆℃暟鎹簱锛岄槻姝㈡暟鎹嚑涓囨潯鏁版嵁鍦ㄥ唴瀛橈紝瀹规槗OOM
+ if (cachedDataList.size() >= BATCH_COUNT) {
+ try {
+ saveData();
+ } catch(Exception e) {
+ // 杩欓噷闇�瑕佹崟鑾峰紓甯革紝鍚﹀垯list鏃犳硶娓呯┖锛屽鑷存瘡璇讳竴鏉℃暟鎹氨浼氭墽琛宻aveData鏂规硶
+ }
+ // 瀛樺偍瀹屾垚娓呯悊 list
+ cachedDataList = new ArrayList<>(BATCH_COUNT);
+ }
+ }
+
+ /**
+ * 璇诲彇瀹屾垚
+ * @param analysisContext
+ */
+ @Override
+ public void doAfterAllAnalysed(AnalysisContext analysisContext) {
+ saveData();
+ log.info("鎵�鏈夋暟鎹В鏋愬畬鎴愶紒");
+ }
+
+ @Override
+ public boolean hasNext(AnalysisContext analysisContext) {
+ return true;
+ }
+
+ private void saveData() {
+ log.info("{}鏉℃暟鎹紝寮�濮嬪瓨鍌ㄦ暟鎹簱锛�", cachedDataList.size());
+ consumer.accept(cachedDataList);
+ log.info("瀛樺偍鏁版嵁搴撴垚鍔燂紒");
+ }
+
+}
diff --git a/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java b/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java
index 1260ea4..0207ee6 100644
--- a/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java
+++ b/ycl-server/src/main/java/com/ycl/platform/controller/YwPointController.java
@@ -3,10 +3,13 @@
import com.ycl.platform.domain.form.BatchEditPointForm;
import com.ycl.system.domain.group.Update;
import com.ycl.system.domain.group.Add;
+import jakarta.servlet.http.HttpServletResponse;
import jakarta.validation.constraints.NotBlank;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.validation.annotation.Validated;
import lombok.RequiredArgsConstructor;
+
+import java.io.IOException;
import java.util.List;
import org.springframework.validation.annotation.Validated;
import jakarta.validation.constraints.NotEmpty;
@@ -18,6 +21,7 @@
import com.ycl.platform.domain.query.YwPointQuery;
import lombok.RequiredArgsConstructor;
import org.springframework.web.bind.annotation.*;
+import org.springframework.web.multipart.MultipartFile;
/**
* 杩愮淮鐐逛綅 鍓嶇鎺у埗鍣�
@@ -102,4 +106,17 @@
public Result select(@NotBlank(message = "璇疯緭鍏ョ偣浣�") String keyword) {
return ywPointService.select(keyword);
}
+
+ @GetMapping("/export")
+ @ApiOperation(value = "瀵煎嚭鏁版嵁", notes = "瀵煎嚭鏁版嵁")
+ public void export(YwPointQuery query, HttpServletResponse response) throws IOException {
+ ywPointService.export(query, response);
+ }
+
+ @PostMapping("/import/{unitId}")
+ @ApiOperation(value = "瀵煎叆鏁版嵁", notes = "瀵煎叆鏁版嵁")
+ public Result importData(MultipartFile file, @PathVariable("unitId") Integer unitId) throws IOException {
+ return ywPointService.importData(file, unitId);
+ }
+
}
diff --git a/ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java b/ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java
index 650ff2d..c6b12d9 100644
--- a/ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java
+++ b/ycl-server/src/main/java/com/ycl/platform/mapper/YwPointMapper.java
@@ -6,6 +6,7 @@
import com.ycl.platform.domain.entity.YwPoint;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.platform.domain.vo.YwPointVO;
+import com.ycl.platform.domain.excel.PointExport;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
@@ -40,4 +41,12 @@
* 閫氳繃鏍囩鏌ラ噸鐐圭偣浣嶆垨鐪佸巺鐐逛綅
*/
List<YwPoint> selectByTag(@Param("important")String important, @Param("province")String province);
+
+ /**
+ * 瀵煎嚭鏁版嵁
+ *
+ * @param query
+ * @return
+ */
+ List<PointExport> export(@Param("query") YwPointQuery query);
}
diff --git a/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java b/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java
index 62ec7f9..447932b 100644
--- a/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java
+++ b/ycl-server/src/main/java/com/ycl/platform/service/YwPointService.java
@@ -6,7 +6,10 @@
import com.ycl.platform.domain.form.YwPointForm;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.system.Result;
+import jakarta.servlet.http.HttpServletResponse;
+import org.springframework.web.multipart.MultipartFile;
+import java.io.IOException;
import java.util.List;
/**
@@ -101,4 +104,21 @@
* @return 鏁版嵁
*/
List<YwPoint> home();
+
+ /**
+ * 瀵煎嚭鏁版嵁
+ *
+ * @param query
+ * @param response
+ */
+ void export(YwPointQuery query, HttpServletResponse response) throws IOException;
+
+ /**
+ * 瀵煎叆鏁版嵁
+ *
+ * @param file
+ * @param unitId 杩愮淮鍗曚綅id
+ * @return
+ */
+ Result importData(MultipartFile file, Integer unitId) throws IOException;
}
diff --git a/ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java b/ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java
index 47706e3..415f927 100644
--- a/ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java
+++ b/ycl-server/src/main/java/com/ycl/platform/service/impl/WorkOrderServiceImpl.java
@@ -79,6 +79,7 @@
workOrderList.stream().filter(item -> {
return StringUtils.hasText(item.getSerialNumber()) && Objects.nonNull(item.getStatus()) && StringUtils.hasText(item.getErrorType());
});
+ // TODO 鑷姩涓嬪彂宸ュ崟
int real = workOrderList.size();
boolean result = this.saveBatch(workOrderList);
log.info("浼犲叆宸ュ崟鎬绘暟: {}锛屽疄闄呮坊鍔犲伐鍗曟暟锛歿}", total, real);
diff --git a/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java b/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java
index b8f6cfd..a379aa6 100644
--- a/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java
+++ b/ycl-server/src/main/java/com/ycl/platform/service/impl/YwPointServiceImpl.java
@@ -1,16 +1,21 @@
package com.ycl.platform.service.impl;
+import com.alibaba.excel.EasyExcel;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.baomidou.mybatisplus.extension.conditions.update.LambdaUpdateChainWrapper;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
+import com.ycl.dataListener.CurrencyDataListener;
import com.ycl.platform.base.BaseSelect;
import com.ycl.platform.domain.entity.YwPeople;
import com.ycl.platform.domain.entity.YwPoint;
+import com.ycl.platform.domain.entity.YwUnit;
import com.ycl.platform.domain.form.BatchEditPointForm;
import com.ycl.platform.domain.form.YwPointForm;
import com.ycl.platform.domain.query.YwPointQuery;
import com.ycl.platform.domain.vo.YwPointVO;
+import com.ycl.platform.domain.excel.PointExport;
+import com.ycl.platform.domain.excel.PointSelectHandler;
import com.ycl.platform.mapper.YwPeopleMapper;
import com.ycl.platform.mapper.YwPointMapper;
import com.ycl.platform.service.YwPointService;
@@ -20,16 +25,20 @@
import com.ycl.system.page.PageUtil;
import com.ycl.utils.DateUtils;
import com.ycl.utils.SecurityUtils;
+import jakarta.servlet.http.HttpServletResponse;
import lombok.RequiredArgsConstructor;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;
+import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
+import org.springframework.web.multipart.MultipartFile;
+import java.io.IOException;
import java.util.List;
-import java.util.Map;
import java.util.Objects;
+import java.util.function.Consumer;
import java.util.stream.Collectors;
/**
@@ -221,4 +230,41 @@
public List<YwPoint> home() {
return baseMapper.home();
}
+
+ @Override
+ public void export(YwPointQuery query, HttpServletResponse response) throws IOException {
+ // 瀵煎嚭鏁版嵁
+ List<PointExport> exportData = baseMapper.export(query);
+
+ EasyExcel.write(response.getOutputStream(), PointExport.class)
+ .sheet("鐐逛綅鏇存崲杩愮淮鍗曚綅")
+ .doWrite(exportData);
+ }
+
+ @Override
+ public Result importData(MultipartFile file, Integer unitId) throws IOException {
+ Consumer<List<PointExport>> consumer = (dataList) -> {
+ this.updatePoint(dataList, unitId);
+ };
+ EasyExcel.read(file.getInputStream(), PointExport.class , new CurrencyDataListener(consumer)).headRowNumber(1).doReadAll();
+ return Result.ok();
+ }
+
+ /**
+ * 淇敼鐐逛綅鐨勮繍缁村崟浣�
+ *
+ * @param dataList
+ * @param unitId
+ */
+ private void updatePoint(List<PointExport> dataList, Integer unitId) {
+ if (CollectionUtils.isEmpty(dataList)) {
+ throw new RuntimeException("瀵煎叆鏁版嵁涓嶈兘涓虹┖");
+ }
+ List<String> pointList = dataList.stream().map(PointExport::getSerialNumber).collect(Collectors.toList());
+ new LambdaUpdateChainWrapper<>(baseMapper)
+ .in(YwPoint::getSerialNumber, pointList)
+ .set(YwPoint::getUnitId, unitId)
+ .update();
+ }
+
}
diff --git a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml b/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml
index ead5205..1bbc828 100644
--- a/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml
+++ b/ycl-server/src/main/resources/mapper/zgyw/YwPointMapper.xml
@@ -74,4 +74,22 @@
</if>
</where>
</select>
+
+ <select id="export" resultType="com.ycl.platform.domain.excel.PointExport">
+ SELECT
+ m.name as pointName,
+ m.serial_number,
+ m.ip,
+ yu.unit_name
+ FROM
+ t_yw_point yp
+ INNER JOIN t_monitor m ON yp.serial_number = m.serial_number
+ INNER JOIN t_yw_unit yu ON yu.id = yp.unit_id
+ <where>
+ <if test="query.pointName != null and query.pointName != ''">
+ AND m.name like concat('%', #{query.pointName} ,'%')
+ </if>
+ </where>
+ </select>
+
</mapper>
--
Gitblit v1.8.0