新增保安员证相关信息导出接口（包含图片，二维码）

Administrator

2021-10-29 f41f7a7efb363425a88e1a060f81f785ba2b57a8

新增保安员证相关信息导出接口（包含图片，二维码）

 src/main/java/org/springblade/common/excel/CustomCellWriteHeightConfig.java

New file
@@ -0,0 +1,49 @@
package org.springblade.common.excel;

import com.alibaba.excel.write.style.row.AbstractRowHeightStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.util.Iterator;

/**
 * 自适应行高
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }

        // 默认为 1行高度
        Integer maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            switch (cell.getCellTypeEnum()) {
                case STRING:
                    if (cell.getStringCellValue().indexOf("\n") != -1) {
                        int length = cell.getStringCellValue().split("\n").length;
                        maxHeight = Math.max(maxHeight, length);
                    }
                    break;
                default:
                    break;
            }
        }

        row.setHeight((short) (maxHeight * DEFAULT_HEIGHT));
    }
}

 src/main/java/org/springblade/common/excel/CustomCellWriteWeightConfig.java

New file
@@ -0,0 +1,83 @@
package org.springblade.common.excel;

import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 自适应行款
 * @author zhongrj
 * @since 2021-10-29
 */
public class CustomCellWriteWeightConfig extends AbstractColumnWidthStyleStrategy {
    private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap<>();
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 254) {
                    columnWidth = 254;
                }

                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    /**
     * 计算长度
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                            cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

 src/main/java/org/springblade/modules/exam/controller/ExamScoreController.java

@@ -28,6 +28,7 @@
import org.springblade.modules.exam.vo.UpdateParamVo;
import org.springblade.modules.system.entity.Role;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.service.IRoleService;
import org.springblade.modules.system.service.IUserService;
@@ -425,7 +426,6 @@
        //导出成绩集合数据
        ExcelUtil.export(response, "成绩数据", "成绩数据表", examScoreExcels, ExportExamScoreExcel.class);
    }


    /**
     * 考试成绩恢复状态重置（重考）

 src/main/java/org/springblade/modules/exam/mapper/ExamScoreMapper.java

@@ -6,6 +6,7 @@
import org.springblade.modules.exam.entity.ExamScore;
import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.system.vo.UserVO;
import org.springblade.modules.training.entity.TrainingRegistration;

import java.util.List;

 src/main/java/org/springblade/modules/exam/service/ExamScoreService.java

@@ -7,6 +7,7 @@
import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.exam.vo.UpdateParamVo;
import org.springblade.modules.system.excel.ExportSecurityPaperExcel;

import java.util.List;
import java.util.Map;
@@ -90,4 +91,7 @@
     * @return
     */
    Boolean refreshTrainExamScore();



}

 src/main/java/org/springblade/modules/system/controller/UserController.java

@@ -33,6 +33,8 @@
import org.springblade.common.cache.DictCache;
import org.springblade.common.config.FtpConfig;
import org.springblade.common.enums.DictEnum;
import org.springblade.common.excel.CustomCellWriteHeightConfig;
import org.springblade.common.excel.CustomCellWriteWeightConfig;
import org.springblade.common.excel.RowWriteHandler;
import org.springblade.common.utils.arg;
import org.springblade.core.cache.utils.CacheUtil;
@@ -50,6 +52,7 @@
import org.springblade.core.tool.support.Kv;
import org.springblade.core.tool.utils.*;
import org.springblade.modules.FTP.FtpUtil;
import org.springblade.modules.exam.excel.ExportExamScoreExcel;
import org.springblade.modules.experience.entity.Experience;
import org.springblade.modules.experience.service.IExperienceService;
import org.springblade.modules.jurisdiction.entity.Jurisdiction;
@@ -413,6 +416,34 @@
//    }

    /**
     * 导出保安员证信息
     */
    @GetMapping("export-security-paper")
    public void exportSecurityPaper(UserVO user, HttpServletResponse response) throws IOException {
        //按条件查询成绩数据
        List<ExportSecurityPaperExcel> excels = userService.exportSecurityPaperList(user);
        String fileName = null;
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding(org.apache.commons.codec.Charsets.UTF_8.name());
            fileName = URLEncoder.encode("保安员证数据", Charsets.UTF_8.name());
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            //修改单元格格式为文本格式
            EasyExcel.write(response.getOutputStream(), ExportSecurityPaperExcel.class)
                .sheet("保安员证数据表")
                //自定义行高
//                .registerWriteHandler(new CustomCellWriteHeightConfig())
                //自定义宽度
//                .registerWriteHandler(new CustomCellWriteWeightConfig())
                //写入
                .doWrite(excels);
        } catch (Throwable var6) {
            throw var6;
        }
//        ExcelUtil.export(response, "保安员证数据" + DateUtil.time(), "保安员证数据表", excels, ExportSecurityPaperExcel.class);
    }

    /**
     * 导出模板
     */
    @GetMapping("export-template")

 src/main/java/org/springblade/modules/system/excel/ExportSecurityPaperExcel.java

New file
@@ -0,0 +1,64 @@
/*
 *      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.springblade.modules.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 java.io.Serializable;
import java.net.URL;

/**
 * ExamPaymentExcel
 * @author zhongrj
 * @since 2021-10-29
 */
@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(125)
public class ExportSecurityPaperExcel implements Serializable {
    private static final long serialVersionUID = 1L;

    @ColumnWidth(15)
    @ExcelProperty("姓名")
    private String realName;

    @ColumnWidth(20)
    @ExcelProperty("身份证号码")
    private String idCardNo;

    @ColumnWidth(35)
    @ExcelProperty("所属公司")
    private String deptName;


    @ColumnWidth(20)
    @ExcelProperty("保安证编号")
    private String securityNumber;

    @ColumnWidth(20)
    @ExcelProperty("照片")
    private URL avatar;

    @ColumnWidth(20)
    @ExcelProperty("二维码图片")
    private byte[] qrCode;
}

 src/main/java/org/springblade/modules/system/mapper/UserMapper.java

@@ -206,4 +206,11 @@
     * @return
     */
    List<Map<String, Object>> getSecurityInfo(@Param("jurisdiction")String jurisdiction);

    /**
     * 导出保安员证信息
     * @param user
     * @return
     */
    List<UserVO> exportSecurityPaperList(@Param("user") UserVO user);
}

 src/main/java/org/springblade/modules/system/mapper/UserMapper.xml

@@ -575,4 +575,57 @@
        and bu.is_deleted = 0
        and br.role_alias = "保安"
    </select>


    <!--导出保安员证信息-->
    <select id="exportSecurityPaperList" resultType="org.springblade.modules.system.vo.UserVO">
        select
        bu.*,bd.dept_name deptName
        from
        blade_user bu
        left join
        blade_dept bd
        on
        bu.dept_id = bd.id
        left join
        blade_role br
        on
        br.id = bu.role_id
        where
        bu.is_deleted = 0
        and role_alias = '保安'
        <if test="user.realName!=null and user.realName != ''">
            and bu.real_name like concat(concat('%', #{user.realName}),'%')
        </if>
        <if test="user.userType!=null and user.userType != ''">
            and bu.user_type = #{user.userType}
        </if>
        <if test="user.nativeplace!=null and user.nativeplace != ''">
            and bu.nativeplace = #{user.nativeplace}
        </if>
        <if test="user.deptId!=null and user.deptId != ''">
            and bu.dept_id = #{user.deptId}
        </if>
        <if test="user.sex!=null and user.sex != ''">
            and bu.sex = #{user.sex}
        </if>
        <if test="user.dispatch!=null and user.dispatch != ''">
            <if test="user.dispatch==1">
                and (bu.dispatch = #{user.dispatch} or bu.dispatch is null)
            </if>
            <if test="user.dispatch==0">
                and bu.dispatch = #{user.dispatch}
            </if>
        </if>
        <if test="user.status!=null and user.status != ''">
            and bu.status = #{user.status}
        </if>
        <if test="user.jurisdiction!=null and user.jurisdiction != ''">
            and bu.jurisdiction = #{user.jurisdiction}
        </if>
        <if test="user.securitynumber!=null and user.securitynumber != ''">
            and bu.securitynumber = #{user.securitynumber}
        </if>
        ORDER BY bu.id
    </select>
</mapper>

 src/main/java/org/springblade/modules/system/service/IUserService.java

@@ -23,9 +23,11 @@
import org.springblade.core.mp.base.BaseService;
import org.springblade.core.mp.support.Query;
import org.springblade.modules.auth.enums.UserEnum;
import org.springblade.modules.exam.vo.ExamScoreVO;
import org.springblade.modules.system.entity.User;
import org.springblade.modules.system.entity.UserInfo;
import org.springblade.modules.system.entity.UserOauth;
import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.QrCodeExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.node.TreeNode;
@@ -323,4 +325,11 @@
     * @param deptId
     */
    void importQrCode(List<QrCodeExcel> data, Boolean isCovered, String deptId);

    /**
     * 导出保安员证信息
     * @param user
     * @return
     */
    List<ExportSecurityPaperExcel> exportSecurityPaperList(UserVO user);
}

 src/main/java/org/springblade/modules/system/service/impl/UserServiceImpl.java

@@ -44,6 +44,7 @@
import org.springblade.modules.information.entity.Information;
import org.springblade.modules.information.service.IInformationService;
import org.springblade.modules.system.entity.*;
import org.springblade.modules.system.excel.ExportSecurityPaperExcel;
import org.springblade.modules.system.excel.QrCodeExcel;
import org.springblade.modules.system.excel.UserExcel;
import org.springblade.modules.system.mapper.UserMapper;
@@ -51,6 +52,10 @@
import org.springblade.modules.system.service.*;
import org.springblade.modules.system.vo.UserVO;
import org.springblade.modules.system.wrapper.UserWrapper;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@@ -58,6 +63,8 @@
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
@@ -883,4 +890,57 @@
            }
        });
    }


    /**
     * 导出保安员证信息
     * @param user
     * @return
     */
    @Override
    public List<ExportSecurityPaperExcel> exportSecurityPaperList(UserVO user) {
        //查询数据
        List<UserVO> userVOList = baseMapper.exportSecurityPaperList(user);
        if (userVOList.size()>0) {
            //返回的集合数据
            List<ExportSecurityPaperExcel> list = new ArrayList<>();
            //遍历
            userVOList.forEach(userVO -> {
                ExportSecurityPaperExcel excel = new ExportSecurityPaperExcel();
                //数据转换封装
                excel.setRealName(userVO.getRealName());
                excel.setIdCardNo(userVO.getCardid());
                excel.setDeptName(userVO.getDeptName());
                //如果头像有
                if (null != userVO.getAvatar() && userVO.getAvatar() != "") {
                    try {
                        if (userVO.getAvatar().contains("http:")) {
                            excel.setAvatar(new URL(userVO.getAvatar()));
                        }
                    } catch (MalformedURLException e) {
                        e.printStackTrace();
                    }
                }
                //如果保安证编号不为空
                if (null != userVO.getSecuritynumber() && userVO.getSecuritynumber() != "") {
                    //去生成二维码
                    String url = "http://223.82.109.183:2080/securityInfo.html";
                    String encoded = null;
                    try {
                        encoded = URLEncoder.encode(userVO.getSecuritynumber(), "UTF-8");
                        String content = url + "?securityNumber=" + encoded;
                        URLEncoder.encode(content, "utf-8");
                        byte[] qrCodeImage = QRCodeUtil.getQRCodeImage(content, 350, 350);
                        //设置二维码
                        excel.setQrCode(qrCodeImage);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
                list.add(excel);
            });
            return list;
        }
        return null;
    }
}