大文件导出到excel

package cn.bosc.pdmp.ebss.batch.job;


import cn.bosc.pdmp.ebss.batch.constant.Constants;
import cn.bosc.pdmp.ebss.batch.util.BeanContext;
import cn.bosc.pdmp.ebss.batch.util.DateUtils;
import cn.bosc.pdmp.ebss.batch.util.SendFileUtils;
import cn.bosc.pdmp.ebss.major.entity.CommDictEntity;
import cn.bosc.pdmp.ebss.major.entity.DtBankNumber;
import cn.bosc.pdmp.ebss.major.entity.DtCapsCity;
import cn.bosc.pdmp.ebss.major.entity.DtCapsProvince;
import cn.bosc.pdmp.ebss.major.mapper.CommDictEntityMapper;
import cn.bosc.pdmp.ebss.major.mapper.DtBankNumberMapper;
import cn.bosc.pdmp.ebss.major.mapper.DtCapsCityMapper;
import cn.bosc.pdmp.ebss.major.mapper.DtCapsProvinceMapper;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.LambdaUpdateWrapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.batch.core.StepContribution;
import org.springframework.batch.core.scope.context.ChunkContext;
import org.springframework.batch.core.step.tasklet.Tasklet;
import org.springframework.batch.repeat.RepeatStatus;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.time.LocalDate;
import java.util.*;


/**
 * 行名行号文件导出
 */
@Slf4j
public class BankExportTasklet implements Tasklet {
    private final DtBankNumberMapper dtBankNumberMapper = BeanContext.getBean(DtBankNumberMapper.class);
    private final DtCapsCityMapper dtCapsCityMapper = BeanContext.getBean(DtCapsCityMapper.class);
    private final DtCapsProvinceMapper dtCapsProvinceMapper = BeanContext.getBean(DtCapsProvinceMapper.class);
    private final CommDictEntityMapper commDictEntityMapper = BeanContext.getBean(CommDictEntityMapper.class);

    @Override
    public RepeatStatus execute(StepContribution stepContribution, ChunkContext chunkContext) throws Exception {
        String logPre = "BankExportTasklet | execute | {}";
        log.info(logPre, "START");
        File dirName = new File(Constants.BANK_FILE_PATH);
        if (!dirName.exists()) {
            dirName.mkdir();
        }

        // 1、创建excel文件
        String excelFilePath = Constants.BANK_FILE_PATH + "行名行号表_" + DateUtils.getCurrTime() + Constants.SPOT + Constants.EXPORT_TYPE;
        new File(excelFilePath);
        // 2、excel文件初始化
        SXSSFWorkbook workbook = new SXSSFWorkbook(100);
        Sheet sheet = initFile(excelFilePath, workbook);

        // 3、数据写入
        int total = dtBankNumberMapper.selectCount(new LambdaQueryWrapper<>());
        log.info(logPre, "行名行号数据总量：" + total);
        // 若未查到数据直接返回
        if (total == 0) return RepeatStatus.FINISHED;
        int rowIndex = 1;
        for (int i = 0; i < total; ) {
            log.info(logPre, "行名行号数据查询偏移量：" + i);
            // 分批查询行名行号表数据
            LambdaQueryWrapper<DtBankNumber> wrapper = new LambdaQueryWrapper<>();
            wrapper.last("limit " + i + "," + Constants.BATCH_SIZE);
            List<DtBankNumber> dtBankNumbers = dtBankNumberMapper.selectList(wrapper);

            // 数据封装
            List<List<String>> bankInfos = getBankInfos(dtBankNumbers);

            // 写入数据到excel
            for (List<String> rowData : bankInfos) {
                Row row = sheet.createRow(rowIndex++);
                for (int j = 0; j < rowData.size(); j++) {
                    Cell cell = row.createCell(j);
                    cell.setCellValue(rowData.get(j));
                }
            }
            bankInfos = null;
            i = i + Constants.BATCH_SIZE;
        }

        // 4、释放资源
        try (FileOutputStream out = new FileOutputStream(excelFilePath)) {
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        // 5、上传影像平台，获取文件地址
        String fileUrl = uploadFile(excelFilePath);
        // 6、保存文件地址到字典表
        updateFileUrl(fileUrl);
        log.info(logPre, "更新行名行号表文件地址：" + fileUrl);
        // 7、删除当天之前的文件
        deleteFile(logPre);

        log.info(logPre, "END");
        return RepeatStatus.FINISHED;
    }

    /**
     * excel文件初始化
     */
    private Sheet initFile(String excelFilePath, SXSSFWorkbook workbook) {
        Sheet sheet = workbook.createSheet("sheet1");
        // 设置列宽
        sheet.setColumnWidth(0, 20 * 256);
        sheet.setColumnWidth(1, 40 * 256);
        sheet.setColumnWidth(2, 20 * 256);

// 第一行表头
        Row firstRow = sheet.createRow(0);
        List<String> cellTitle = Arrays.asList("银行全称", "银行行号", "开户行所在地");
        for (int i = 0; i < cellTitle.size(); i++) {
            Cell cell = firstRow.createCell(i);
            cell.setCellValue(cellTitle.get(i));
        }
        return sheet;
    }

    /**
     * 删除当天之前的文件
     *
     * @param logPre
     */
    private void deleteFile(String logPre) {
        log.info(logPre, "开始删除历史文件");
        Date today = DateUtils.localDate2Date(LocalDate.now());
        File directory = new File(Constants.BANK_FILE_PATH);
        File[] files = directory.listFiles();
        for (File file : files) {
            if (file.lastModified() < today.getTime()) {
                boolean delete = file.delete();
                if (delete) {
                    log.info(logPre, "文件已被删除" + file.getName());
                } else {
                    log.info(logPre, "文件删除失败" + file.getName());
                }
            }
        }
        log.info(logPre, "结束删除历史文件");
    }

    /**
     * 保存文件地址到字典表
     *
     * @param fileUrl
     */
    private void updateFileUrl(String fileUrl) {
        LambdaUpdateWrapper<CommDictEntity> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.set(CommDictEntity::getDictValue, fileUrl)
                .eq(CommDictEntity::getDictTypeId, Constants.FILE_URL)
                .eq(CommDictEntity::getDictId, Constants.BANK_FILE_URL);
        commDictEntityMapper.update(null, updateWrapper);
    }

    /**
     * 上传影像平台
     *
     * @param excelFilePath
     * @return
     */
    private String uploadFile(String excelFilePath) {
        List<File> files = new ArrayList<>();
        File file = new File(excelFilePath);
        files.add(file);
        try {
            List<String> urls = SendFileUtils.uploadFile(files);
            return splitUrl(urls.get(0));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 地址分割
     *
     * @param pdfUrl
     * @return
     */
    private String splitUrl(String pdfUrl) {
        if (pdfUrl.startsWith("/")) {
            return pdfUrl;
        }
        String[] split = pdfUrl.split("/");
        StringBuilder sb = new StringBuilder("/");
        for (int i = 5; i < split.length; i++) {
            if (i == split.length - 1) {
                String endStr = split[i];
                String replace = endStr.replace("?au=0672", "");
                sb.append(replace);
            } else {
                sb.append(split[i]).append("/");
            }
        }
        return sb.toString();
    }

    /**
     * 数据封装
     *
     * @param dtBankNumbers
     * @return
     */
    private List<List<String>> getBankInfos(List<DtBankNumber> dtBankNumbers) {
        List<List<String>> bankInfos = new ArrayList<>();
        for (DtBankNumber dtBankNumber : dtBankNumbers) {
            List<String> rowData = new ArrayList<>();
            rowData.add(dtBankNumber.getBankNo());
            rowData.add(dtBankNumber.getBankName());

            // 查询对应市
            DtCapsCity city = dtCapsCityMapper.selectOne(new LambdaQueryWrapper<DtCapsCity>().eq(DtCapsCity::getCityId, dtBankNumber.getCityCode()));
            if (Objects.nonNull(city)) {
                // 查询对应省
                DtCapsProvince province = dtCapsProvinceMapper.selectOne(new LambdaQueryWrapper<DtCapsProvince>().eq(DtCapsProvince::getProvId, city.getProvId()));
                if (Objects.nonNull(province)) {
                    rowData.add(province.getProvName() + city.getCityName());
                }
            }
            bankInfos.add(rowData);
        }
        return bankInfos;
    }

}