java-insagent-pc/src/main/java/com/ydtech/modules/inv/utils/EasyExcelUtils.java

package com.ydtech.modules.inv.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alipay.api.internal.util.file.IOUtils;
import com.ydtech.constants.enums.inv.InvPayMethodEnum;
import com.ydtech.modules.inv.model.*;
import com.ydtech.modules.inv.model.excel.InvSettlementSourceExcel;
import com.ydtech.modules.inv.model.excel.InvSourceExcel;
import com.ydtech.modules.inv.service.InvAccountExcelService;
import com.ydtech.modules.inv.utils.excel.InvSettlementSourceDataListener;
import com.ydtech.modules.inv.utils.excel.InvSourceDataListener;
import lombok.SneakyThrows;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;
import java.util.stream.Collectors;

import static com.ydtech.modules.inv.utils.ZipMultiFile.zipFiles;


/**
 * @author jianlong
 * @date 2023-12-29 16:46
 */
@Component
public class EasyExcelUtils {

    @Autowired
    private InvAccountExcelService insAccountExcelService;

    //通用写入
    public static String downExcel(String uploadFilePath, Class clz, List<?> list) {
        String file = System.currentTimeMillis() + ".xlsx";
        String fileName = uploadFilePath + file;
        EasyExcel.write(fileName, clz).sheet("1").doWrite(list);
        return file;
    }

    /**
     * @param uploadFilePath 路劲
     * @param fileName       文件名称
     * @param sheetName      sheet 名称
     * @param clz            写入类型
     * @param list           写入数据类型
     * @return 文件路劲
     */
    public static String downExcel(String uploadFilePath, String fileName, String sheetName, Class<?> clz, List<?> list) {
        String file = fileName + System.currentTimeMillis() + ".xlsx";
        String filePath = uploadFilePath + file;
        EasyExcel.write(filePath, clz).sheet(sheetName).doWrite(list);
        return filePath;
    }

    /**
     * 不创建对象的写
     */
    public static String noModelWrite(String uploadFilePath, List<InvAccountProfit> invAccountProfitList, List<InvAccount> invAccounts, List<InvAccountOp> invAccountOpList) {
        // 写法1
        String file = System.currentTimeMillis() + ".xlsx";
        String fileName = uploadFilePath + file;
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName).head(head(invAccountProfitList)).sheet("1").doWrite(dataList(invAccounts, invAccountOpList, invAccountProfitList));
        return file;
    }

    private static List<List<String>> head(List<InvAccountProfit> invAccountProfitList) {
        List<List<String>> list = new ArrayList<>(4 + invAccountProfitList.size());
        List<String> head = ListUtils.newArrayList();
        head.add("账户性质");
        List<String> head1 = ListUtils.newArrayList();
        head1.add("户名");
        List<String> head2 = ListUtils.newArrayList();
        head2.add("开户银行");
        List<String> head3 = ListUtils.newArrayList();
        head3.add("银行卡余额");
        list.add(0, head);
        list.add(1, head1);
        list.add(2, head2);
        list.add(3, head3);
        invAccountProfitList.forEach(invAccountProfit -> {
            List<String> head0 = ListUtils.newArrayList();
            head0.add(invAccountProfit.getProfitName());
            list.add(head0);
        });
        return list;
    }


    private static List<List<Object>> dataList(List<InvAccount> invAccounts, List<InvAccountOp> invAccountOpList, List<InvAccountProfit> invAccountProfitList) {
        List<List<Object>> list = ListUtils.newArrayList();
        invAccounts.forEach(invAccount -> {

            invAccount.getInvAccountCardList().forEach(invAccountCard -> {
                List<Object> data = ListUtils.newArrayList();
                if (invAccountCard.getBankCardNum() != null) {
                    data.add(invAccount.getOuterFlag().equals("0") ? "内部账户" : "外部账户");
                    data.add(invAccount.getAccountName());
                    data.add(invAccountCard.getBankName());
                    data.add(invAccountCard.getBalance());

                    invAccountProfitList.forEach(invAccountProfitList1 -> {
                        String a = "0";
                        for (InvAccountOp invAccountProfit : invAccountOpList) {
                            if (invAccountProfitList1.getProfitEng().equals(invAccountProfit.getProfit()) && invAccountCard.getBankCardNum().equals(invAccountProfit.getCardNum())) {
                                a = new BigDecimal(a).add(new BigDecimal(invAccountProfit.getMoney())).toString();
                            }
                        }
                        data.add(a);
                    });
                    list.add(data);
                }
            });

        });
        return list;
    }


    /**
     * 不创建对象的写
     */
    public static String noModelOperateWrite(String uploadFilePath, List<InvAccountProfit> invAccountProfitList, List<InvAccountOperate> invAccountOperates) {
        // 写法1
        String file = System.currentTimeMillis() + ".xlsx";
        String fileName = uploadFilePath + file;
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName).head(operateHead(invAccountProfitList)).sheet("1").doWrite(operatedataList(invAccountOperates, invAccountProfitList));
        return file;
    }

    private static List<List<String>> operateHead(List<InvAccountProfit> invAccountProfitList) {
        List<List<String>> list = new ArrayList<>(7 + invAccountProfitList.size());
        List<String> head = ListUtils.newArrayList();
        head.add("申请时间");
        List<String> head1 = ListUtils.newArrayList();
        head1.add("交易时间");
        List<String> head2 = ListUtils.newArrayList();
        head2.add("部门");
        List<String> head3 = ListUtils.newArrayList();
        head3.add("支出方");
        List<String> head4 = ListUtils.newArrayList();
        head4.add("收入方");
        List<String> head5 = ListUtils.newArrayList();
        head5.add("支出项目");
        List<String> head6 = ListUtils.newArrayList();
        head6.add("分项");
        List<String> head7 = ListUtils.newArrayList();
        head7.add("支出金额");
        List<String> head8 = ListUtils.newArrayList();
        head8.add("转账类型");
        List<String> head9 = ListUtils.newArrayList();
        head9.add("备注");

        list.add(0, head);
        list.add(1, head1);
        list.add(2, head2);
        list.add(3, head3);
        list.add(4, head4);
        list.add(5, head5);
        list.add(6, head6);
        list.add(7, head7);
        list.add(8, head8);
        list.add(9, head9);
        invAccountProfitList.forEach(invAccountProfit -> {
            List<String> head0 = ListUtils.newArrayList();
            head0.add(invAccountProfit.getProfitName());
            list.add(head0);
        });

        return list;
    }


    private static List<List<Object>> operatedataList(List<InvAccountOperate> invAccountOperates, List<InvAccountProfit> invAccountProfitList) {
        List<List<Object>> list = ListUtils.newArrayList();
        invAccountOperates.forEach(invAccountOperate -> {
            List<Object> data = ListUtils.newArrayList();
            data.add(invAccountOperate.getApplyTime());
            data.add(invAccountOperate.getCreateTime());
            data.add(invAccountOperate.getApplySector());
            data.add(invAccountOperate.getFundFlow().substring(0, invAccountOperate.getFundFlow().indexOf("→")));
            data.add(invAccountOperate.getFundFlow().substring(invAccountOperate.getFundFlow().indexOf("→") + 1));
            data.add(invAccountOperate.getFundUse());
            data.add(invAccountOperate.getFundFen());
            data.add(invAccountOperate.getApplyAmount());
            data.add(invAccountOperate.getRevenueOutlay().equals("0") ? "收入" : "支出");
            data.add(invAccountOperate.getRemark());
            invAccountProfitList.forEach(invAccountProfit -> {
                if (invAccountOperate.getOutProfit() != null) {
                    for (Map.Entry<String, Object> entry : invAccountOperate.getOutProfit().entrySet()) {
                        if (entry.getKey().equals(invAccountProfit.getProfitEng())) {
                            data.add(entry.getValue());
                        }
                    }
                }
                if (invAccountOperate.getEntProfit() != null) {
                    for (Map.Entry<String, Object> entry : invAccountOperate.getEntProfit().entrySet()) {
                        if (entry.getKey().equals(invAccountProfit.getProfitEng())) {
                            data.add(entry.getValue());
                        }
                    }
                }
            });
            list.add(data);
        });
        return list;
    }

    public static String downExcel(String uploadFilePath, String fileName, Class clz, List<?> list) {

        String file = fileName + System.currentTimeMillis() + ".xlsx";
        String filePath = uploadFilePath + file;
        EasyExcel.write(filePath, clz).sheet("1").doWrite(list);
        return file;
    }

    public static List<InvSourceExcel> readInvSourceExcel(String uploadFilePath, Class clz) {

        // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
//        List<InvSourceExcel> list = new ArrayList<>();
        List<InvSourceExcel> list = EasyExcel.read(uploadFilePath, clz, new InvSourceDataListener())
                .excelType(ExcelTypeEnum.XLSX)
                .sheet()
                .sheetName("来源表")
                .doReadSync();
        return list;
    }

    public static List<InvSettlementSourceExcel> readInvSettlementSourceExcelExcel(String uploadFilePath, Class clz) {

        // 这里默认每次会读取100条数据 然后返回过来 直接调用使用数据就行
        List<InvSettlementSourceExcel> list = EasyExcel.read(uploadFilePath, clz, new InvSettlementSourceDataListener())
                .sheet()
                .sheetName("结算来源表")
                .doReadSync();
        return list;
    }


    /**
     * 导出资金申请模板
     */
    @SneakyThrows
    public String downAccountExcel(String uploadFilePath, List<InvAccountExcel> invAccountExcel, List<InvAccountProfit> invAccountProfit) {

        // 加载模板
        InputStream inputStream = getClass().getResourceAsStream("/template/资金申请单模板.xlsx");

        byte[] bytes = saveIns(inputStream);

        if (inputStream != null) {
            inputStream.close();
        }

// 初始化一个空的列表用于存储已更新的对象
        List<InvAccountExcel> updatedList = new ArrayList<>();

        String fileName = "综合资金申请单" + System.currentTimeMillis() + ".xlsx";
        String filePath = uploadFilePath + fileName;
        Map<String, String> placeholders = new HashMap<>();

        for (int i = 0; i < invAccountExcel.size(); i++) {
            InvAccountExcel excel = invAccountExcel.get(i);

            // 格式化申请时间
            excel.setExcelApplyTime(new SimpleDateFormat("yyyy-MM-dd").format(excel.getApplyTime()));

            // 处理支付方式
            String payMethod = excel.getPayMethod();
            String[] split = payMethod.split(",");
            StringBuilder payMethodStr = new StringBuilder();
            for (int j = 0; j < split.length; j++) {
                payMethodStr.append(InvPayMethodEnum.getNameByCode(split[j]));
                if (j < split.length - 1) {
                    payMethodStr.append(",");
                }
            }
            excel.setPayMethod(payMethodStr.toString());

            // 处理利润分配
            StringBuilder plateStr = new StringBuilder();
            Map<String, List<InvAccountProfit>> collect = invAccountProfit.stream()
                    .collect(Collectors.groupingBy(InvAccountProfit::getProfitEng));

            // 处理输出利润
            for (Map.Entry<String, Object> outEntry : excel.getOutProfit().entrySet()) {
                try {
                    double value = Double.parseDouble(outEntry.getValue().toString());
                    if (value != 0) {
                        plateStr.append(collect.get(outEntry.getKey()).get(0).getProfitName()).append("    ").append(value);
                    }
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                }
            }

            plateStr.append("→");

            // 处理进入利润
            for (Map.Entry<String, Object> entEntry : excel.getEntProfit().entrySet()) {
                try {
                    double value = Double.parseDouble(entEntry.getValue().toString());
                    if (value != 0) {
                        plateStr.append(collect.get(entEntry.getKey()).get(0).getProfitName()).append("    ").append(value);
                    }
                } catch (NumberFormatException e) {
                    e.printStackTrace();
                }
            }

            excel.setPlate(plateStr.toString());

            // 准备占位符
            placeholders.put("company" + i, excel.getCompany());
            placeholders.put("excelAccountOperateId" + i, String.valueOf(excel.getExcelAccountOperateId()));
            placeholders.put("applicant" + i, excel.getApplicant());
            placeholders.put("applySector" + i, excel.getApplySector());
            placeholders.put("excelApplyTime" + i, excel.getExcelApplyTime());
            placeholders.put("collectionMessage" + i, excel.getCollectionMessage());
            placeholders.put("fundFlow" + i, excel.getFundFlow());
            placeholders.put("plate" + i, excel.getPlate());
            placeholders.put("fundUse" + i, excel.getFundUse());
            placeholders.put("remark" + i, excel.getRemark());
            placeholders.put("applyAmount" + i, String.valueOf(excel.getApplyAmount()));
            placeholders.put("bigApplyAmount" + i, excel.getBigApplyAmount());
            placeholders.put("payMethod" + i, excel.getPayMethod());

            // 设置上传路径和支付方式等信息
            excel.setUplodePath(fileName);
            excel.setPayMethod(payMethod); // 保持原始支付方式不变

            // 添加到已更新的列表中
            updatedList.add(excel);
        }

        // 从bytes数组创建新的输入流
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
        EasyExcel.write(filePath).withTemplate(byteArrayInputStream).sheet().doFill(placeholders);

        // 更新数据库中的记录
        insAccountExcelService.updateBatchById(updatedList);

        return fileName;
    }


    //保存流对象（输入流在第二次使用的时候会失效）
    public byte[] saveIns(InputStream ins) {
        byte[] buf = null;
        try {
            if (ins != null) {
                buf = org.apache.commons.io.IOUtils.toByteArray(ins);//ins为InputStream流
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return buf;
    }

}