From cdcd2d540de3e6ba4c2e9e7c6a879416da78c17b Mon Sep 17 00:00:00 2001
From: Administrator <admin>
Date: Thu, 16 Dec 2021 10:53:51 +0800
Subject: [PATCH] 保安员导出过多导致报错问题处理
---
src/main/java/org/springblade/modules/system/mapper/UserMapper.xml | 7 +++
src/main/java/org/springblade/modules/training/controller/TrainingRegistrationController.java | 22 +++++------
src/main/java/org/springblade/modules/equipage/controller/CarController.java | 1
src/main/java/org/springblade/common/utils/FtpSyncUtil.java | 8 ++--
src/main/java/org/springblade/common/excel/RowWriteHandler.java | 53 +++++++++++++++++---------
5 files changed, 55 insertions(+), 36 deletions(-)
diff --git a/src/main/java/org/springblade/common/excel/RowWriteHandler.java b/src/main/java/org/springblade/common/excel/RowWriteHandler.java
index ae04aa7..38a7282 100644
--- a/src/main/java/org/springblade/common/excel/RowWriteHandler.java
+++ b/src/main/java/org/springblade/common/excel/RowWriteHandler.java
@@ -6,6 +6,7 @@
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import org.apache.poi.ss.usermodel.*;
+import org.springblade.core.tool.utils.Func;
import java.util.List;
@@ -14,31 +15,45 @@
* @author zhongrj
* @since 2021-9-26
*/
-public class RowWriteHandler implements CellWriteHandler {
+public class RowWriteHandler implements CellWriteHandler {
+ public static CellStyle cellStyle;
- @Override
- public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+ /**
+ * 设置全局变量，防止 cellStyle 创建过多报错 2021-12-16 zrj
+ * The maximum number of cell styles was exceeded. You can define up to 64000styles in a .xlsx workbook
+ * @param cellStyle
+ */
+ public static void setCellStyle(CellStyle cellStyle) {
+ RowWriteHandler.cellStyle = cellStyle;
+ }
+ @Override
+ public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
+
+ }
+
+ @Override
+ public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+ //设置单元格格式为文本
+ Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
+ //自定义样式不为空时,重复利用
+ if(Func.isEmpty(cellStyle)){
+ cellStyle = workbook.createCellStyle();
+ setCellStyle(cellStyle);
}
+ DataFormat dataFormat = workbook.createDataFormat();
+ cellStyle.setDataFormat(dataFormat.getFormat("@"));
+ cell.setCellStyle(cellStyle);
+ }
- @Override
- public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) {
- //设置单元格格式为文本
- Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
- CellStyle cellStyle = workbook.createCellStyle();
- DataFormat dataFormat = workbook.createDataFormat();
- cellStyle.setDataFormat(dataFormat.getFormat("@"));
- cell.setCellStyle(cellStyle);
- }
+ //@Override 加上会报错
+ public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
- //@Override 加上会报错
- public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) {
+ }
- }
+ @Override
+ public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
- @Override
- public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
-
- }
+ }
}
diff --git a/src/main/java/org/springblade/common/utils/FtpSyncUtil.java b/src/main/java/org/springblade/common/utils/FtpSyncUtil.java
index a8e9f61..2f588b1 100644
--- a/src/main/java/org/springblade/common/utils/FtpSyncUtil.java
+++ b/src/main/java/org/springblade/common/utils/FtpSyncUtil.java
@@ -15,12 +15,12 @@
* sql 处理
* @return
*/
- public static StringBuilder sqlHandler(StringBuilder builder,String sql){
- String[] split = builder.toString().split(";");
+ public static StringBuilder sqlHandler(StringBuilder builder,String sql,int length){
//拼接
builder = builder.append(sql).append(";");
+ String[] split = builder.toString().split(";");
//判断list.size 大小
- if (split.length==999){
+ if (split.length==length){
//执行
FtpUtil.sqlFileUpload(builder.toString());
//清空
@@ -30,7 +30,7 @@
}
/**
- * sql 处理
+ * sql 处理,执行
* @return
*/
public static void slqSync(StringBuilder builder){
diff --git a/src/main/java/org/springblade/modules/equipage/controller/CarController.java b/src/main/java/org/springblade/modules/equipage/controller/CarController.java
index 7735838..71f3295 100644
--- a/src/main/java/org/springblade/modules/equipage/controller/CarController.java
+++ b/src/main/java/org/springblade/modules/equipage/controller/CarController.java
@@ -180,6 +180,7 @@
response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
fileName = URLEncoder.encode("车辆导入数据模板"+ DateUtil.time(), Charsets.UTF_8.name());
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
+
//修改单元格格式为文本格式
EasyExcel.write(response.getOutputStream(), CarExcel.class).sheet("车辆数据表").registerWriteHandler(new RowWriteHandler()).doWrite(list);
} catch (Throwable var6) {
diff --git a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
index 220ebc5..9b39a2b 100644
--- a/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
+++ b/src/main/java/org/springblade/modules/system/mapper/UserMapper.xml
@@ -778,7 +778,12 @@
and bu.real_name like concat('%', #{user.realName},'%')
</if>
<if test="user.dispatch!=null and user.dispatch != ''">
- and bu.dispatch = #{user.dispatch}
+ <if test="user.dispatch == 0">
+ and bu.dispatch = #{user.dispatch}
+ </if>
+ <if test="user.dispatch == 1">
+ and bu.dispatch = #{user.dispatch}
+ </if>
</if>
<if test="user.userType!=null and user.userType != ''">
and bu.user_type = #{user.userType}
diff --git a/src/main/java/org/springblade/modules/training/controller/TrainingRegistrationController.java b/src/main/java/org/springblade/modules/training/controller/TrainingRegistrationController.java
index 599e01a..6780ebc 100644
--- a/src/main/java/org/springblade/modules/training/controller/TrainingRegistrationController.java
+++ b/src/main/java/org/springblade/modules/training/controller/TrainingRegistrationController.java
@@ -139,7 +139,7 @@
+ "," + "'" + trainingRegistration.getIsExam() + "'"
+ "," +"'" + trainingRegistration.getAuditStatus() + "'" + ");" +
"update blade_user set is_train = " + user1.getIsTrain() + " " +"where id = " + "'" + user1.getId() + "'";
- return FtpSyncUtil.sqlHandler(builder, sql);
+ return FtpSyncUtil.sqlHandler(builder, sql,500);
}
/**
@@ -196,13 +196,9 @@
if (status){
return R.data(200,"取消报名成功");
}
-
-
-
}else {
return R.data(201,"尚未报名");
}
-
}
return R.data(202,"取消报名失败");
}
@@ -302,7 +298,7 @@
AtomicInteger count = new AtomicInteger();
//创建年龄部分对象集合
List<String> errorList = new ArrayList<>();
- AtomicReference<StringBuilder> builder = new AtomicReference<>(new StringBuilder());
+// AtomicReference<StringBuilder> builder = new AtomicReference<>(new StringBuilder());
//遍历
list.forEach(userId ->{
User user = userService.getById(userId);
@@ -326,7 +322,8 @@
userService.updateById(user);
//数据同步
- builder.set(batchSaveTrainingRegistration(trainingRegistration1, user, builder.get()));
+ saveTrainingRegistration(trainingRegistration1,user);
+// builder.set(batchSaveTrainingRegistration(trainingRegistration1, user, builder.get()));
} else {
if (user.getIsTrain() != 1) {
TrainingRegistration trainingRegistration1 = new TrainingRegistration();
@@ -344,7 +341,8 @@
userService.updateById(user);
//数据同步
- builder.set(batchSaveTrainingRegistration(trainingRegistration1, user, builder.get()));
+ saveTrainingRegistration(trainingRegistration1,user);
+// builder.set(batchSaveTrainingRegistration(trainingRegistration1, user, builder.get()));
}
}
}else {
@@ -354,10 +352,10 @@
errorList.add(user.getCardid());
}
});
- //同步
- if (builder.get().length()>0) {
- FtpSyncUtil.slqSync(builder.get());
- }
+// //同步
+// if (builder.get().length()>0) {
+// FtpSyncUtil.slqSync(builder.get());
+// }
//如果存在年龄不符的，则抛出异常
if (count.get()>0){
String errorAccount = StringUtils.join(errorList, "\\\n");
--
Gitblit v1.9.3