保安员导出过多导致报错问题处理

Administrator

2021-12-16 cdcd2d540de3e6ba4c2e9e7c6a879416da78c17b

保安员导出过多导致报错问题处理

 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) {

        }
    }
}

 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){

 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) {

 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}

 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");