考试审核查询修改，保安员导出模板修改单元格格式为文本格式

Administrator

2021-09-26 917cf37b0c0496b3fed8671837fc633d2c264977

考试审核查询修改，保安员导出模板修改单元格格式为文本格式

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

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

import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
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 java.util.List;

/**
 * 修改单元格格式为文本格式
 * @author zhongrj
 * @since 2021-9-26
 */
public class RowWriteHandler implements CellWriteHandler {


        @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();
            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 afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {

        }
}

 src/main/java/org/springblade/modules/exam/mapper/ExamPaperMapper.xml

@@ -110,7 +110,10 @@
            and exam_type = #{paper.examType}
        </if>
        <if test="paper.examName!=null and paper.examName!=''">
            and exam_name = #{paper.examName}
            and exam_name like concat('%',#{paper.examName},'%')
        </if>
        <if test="paper.auditStatus!=null and paper.auditStatus!=''">
            and audit_status = #{paper.auditStatus}
        </if>
        order by start_time desc
    </select>

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

@@ -17,6 +17,7 @@
package org.springblade.modules.system.controller;


import com.alibaba.excel.EasyExcel;
import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -28,9 +29,11 @@
import io.swagger.annotations.ApiParam;
import com.alibaba.fastjson.JSON;
import lombok.AllArgsConstructor;
import org.apache.commons.codec.Charsets;
import org.springblade.common.cache.DictCache;
import org.springblade.common.config.FtpConfig;
import org.springblade.common.enums.DictEnum;
import org.springblade.common.excel.RowWriteHandler;
import org.springblade.common.utils.arg;
import org.springblade.core.cache.utils.CacheUtil;
import org.springblade.core.excel.util.ExcelUtil;
@@ -71,6 +74,8 @@

import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

@@ -413,7 +418,7 @@
    @GetMapping("export-template-security")
    @ApiOperationSupport(order = 14)
    @ApiOperation(value = "导出模板")
    public void exportSecurity(HttpServletResponse response) {
    public void exportSecurity(HttpServletResponse response) throws IOException {
        List<UserExcel> list = new ArrayList<>();
        UserExcel userExcel = new UserExcel();
        userExcel.setDeptId("xxx保安公司");
@@ -449,7 +454,18 @@
        userExcel1.setHold("否");
        userExcel1.setSecuritynumber("");
        list.add(userExcel1);
        ExcelUtil.export(response, "保安员导入数据模板", "保安员数据表", list, UserExcel.class);
        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(), UserExcel.class).sheet("保安员数据表").registerWriteHandler(new RowWriteHandler()).doWrite(list);
        } catch (Throwable var6) {
            throw var6;
        }
//        ExcelUtil.export(response, "保安员导入数据模板", "保安员数据表", list, UserExcel.class);
    }