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.support.ExcelTypeEnum;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
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.stereotype.Component;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
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;
    }

    /**
     * 不创建对象的写
     */
    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.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);
        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.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 = saveaIns(inputStream);

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



        List<InvAccountExcel> list = new ArrayList<>();
        // 获取工作表并填充
        for (InvAccountExcel excel : invAccountExcel) {
            ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
            // 写入文件
            Date date = new Date();
            String file = date.getTime() + ".xlsx";
            String fileName = uploadFilePath + file;
            excel.setExcelApplyTime(new SimpleDateFormat("yyyy-MM-dd").format(excel.getApplyTime()));
            String payMethod = excel.getPayMethod();
            String[] split = payMethod.split(",");
            StringBuilder a = new StringBuilder();
            for (int i = 0; i < split.length; i++) {
                a.append(InvPayMethodEnum.getNameByCode(split[i]));
                if (i < split.length - 1) {
                    a.append(",");
                }
            }

            StringBuilder stringBuffer = new StringBuilder();

            Map<String, List<InvAccountProfit>> collect = invAccountProfit.stream().collect(
                    Collectors.groupingBy(
                            InvAccountProfit::getProfitEng
                    ));

            for (Map.Entry<String, Object> entry : excel.getOutProfit().entrySet()) {
                try {
                    double value = Double.parseDouble(entry.getValue().toString());
                    if (value != 0) {
                        stringBuffer.append(collect.get(entry.getKey()).get(0).getProfitName()).append("    ").append(value);
                    }
                } catch (NumberFormatException e) {
                    // 处理转换异常
                    e.printStackTrace();
                }
            }

            stringBuffer.append("→");

            for (Map.Entry<String, Object> entry : excel.getEntProfit().entrySet()) {
                try {
                    double value = Double.parseDouble(entry.getValue().toString());
                    if (value != 0) {
                        stringBuffer.append(collect.get(entry.getKey()).get(0).getProfitName()).append("    ").append(value);
                    }
                } catch (NumberFormatException e) {
                    // 处理转换异常
                    e.printStackTrace();
                }
            }

            excel.setPlate(stringBuffer.toString());


            excel.setPayMethod(a.toString());
            // 生成工作簿对象
            ExcelWriterBuilder workBookWriter = EasyExcel.write(fileName, InvAccountExcel.class)
                    .withTemplate(byteArrayInputStream);

            workBookWriter.sheet().doFill(excel);
            excel.setUplodePath(file);
            excel.setPayMethod(payMethod);
            list.add(excel);
            byteArrayInputStream.close();

        }

        insAccountExcelService.updateBatchById(list);

        File[] srcFiles = new File[list.size()];
        for (int i = 0; i < list.size(); i++) {
            srcFiles[i] = new File(uploadFilePath + list.get(i).getUplodePath());
        }
        String a = LocalDate.now() + ".zip";
        File zipFile = new File(uploadFilePath + a);
        // 调用压缩方法
        zipFiles(srcFiles, zipFile);

        return a;
    }


    //保存流对象（输入流在第二次使用的时候会失效）
    public byte[] saveaIns(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;
    }

}