jxyh/src/main/java/fi/em/formPlugin/SalaryFileUploadEditPlugin.java

package fi.em.formPlugin;



import kd.bos.bill.AbstractBillPlugIn;
import kd.bos.cache.CacheFactory;
import kd.bos.cache.TempFileCache;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.exception.KDBizException;
import kd.bos.form.control.AttachmentPanel;
import kd.bos.form.control.Toolbar;
import kd.bos.form.control.events.UploadEvent;
import kd.bos.form.control.events.UploadListener;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.math.BigDecimal;
import java.util.*;

/**
 * @author cjz
 * @date 2024/8/20 16:18
 * @description:薪酬计提单上传附件，并将附件中的数据写入分录
 */
public class SalaryFileUploadEditPlugin extends AbstractBillPlugIn implements UploadListener {
    private static String bos_org = "bos_org"; //业务单元组织标识
    private static String bos_adminorg="bos_adminorg";//行政组织标识

    //给附件面板添加监听
    @Override
    public void registerListener(EventObject e) {

        super.registerListener(e);
        //按钮面板
        Toolbar toolbar = this.getControl("tbmain");
        toolbar.addUploadListener(this);
        //附件面板
        AttachmentPanel attachmentPanel = getControl("attachmentpanel");
        attachmentPanel.addUploadListener(this);
    }


    @Override
    public void afterUpload(UploadEvent evt) {
        //获取当前操作单据
        DynamicObject dynamicObject=this.getModel().getDataEntity(true);
        String nckd_entrytype=dynamicObject.getString("nckd_entrytype");
        //解析附件面板
        AttachmentPanel attachmentPanel=this.getControl("attachmentpanel");
        List<Map<String,Object>> attachmentData=attachmentPanel.getAttachmentData();
        //获取最新上传的文件路径
        String fileurl = (String) attachmentData.get(attachmentData.size()-1).get("url");
        //获取文件缓存
        TempFileCache cache = CacheFactory.getCommonCacheFactory().getTempFileCache();
        //从缓存中拿到输入流
        InputStream inputStream = cache.getInputStream(fileurl);
        String billtype="";
        //根据分录类型获取对应分录标识
        if ("A".equals(nckd_entrytype)) {
            billtype="nckd_staffentry";
            readExcelByRowOrCell(4,44,inputStream,billtype);
        }else if ("B".equals(nckd_entrytype)) {
            billtype="nckd_earlyretiredentry";
            readExcelByRowOrCell(4,34,inputStream,billtype);
        } else if ("C".equals(nckd_entrytype)) {
            billtype="nckd_retireentry";
            readExcelByRowOrCell(4,15,inputStream,billtype);
        } else if ("D".equals(nckd_entrytype)) {
            billtype="nckd_salaryentry";
            readExcelByRowOrCell(4,78,inputStream,billtype);
        } else if ("E".equals(nckd_entrytype)) {
            billtype="nckd_salaryentryre";
            readExcelByRowOrCell(4,41,inputStream,billtype);
        } else if (null==nckd_entrytype) {
            throw new KDBizException("请选择分录类型");
        }
    }


    /**
     * @line execel头行数
     * @column excel列数
     * @inputStream 文件流
     * @nckd_entrytype 分录类型
     * @description:读取上传的excel，并将数据写入对应分录中
     */
    public void readExcelByRowOrCell(int line,int column,InputStream inputStream,String billtype)
    {
        try {
            Workbook wb = WorkbookFactory.create(inputStream);
            Sheet filesheet=wb.getSheetAt(0);//默认取第一个工作表
            //表中存的数据
            List<Row> rowList=new ArrayList<>();
            //表头字段
            List<Row> headList=new ArrayList<>();
            //人力薪酬和薪酬明细等excel分开处理
            if (billtype.equals("nckd_salaryentry")) {
                headList.add(filesheet.getRow(line-3));
            }else {
                headList.add(filesheet.getRow(line-2));
            }
            //读取每行的数据存入list中
            for (int i=line;i<=filesheet.getLastRowNum();i++) {
                //获取每一行
                Row row = filesheet.getRow(i);
                if (null==row.getCell(1)) {
                    break;
                }
                //根据字段行循环，没有值的单元格则赋值，防止空指针
                for(int j=1;j<column;j++){
                    Cell cell=row.getCell(j);
                    if (null==cell) {
                        row.createCell(j);
                    }
                }
                rowList.add(row);
            }
            //excel数据校验
            dataVerify(headList,rowList,billtype);
            //分录赋值
            createNewRow(rowList,billtype);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }



    /**
     * @rowList 读取的excel数据
     * @nckd_entrytype 分录标识
     * @description:根据分录类型不同，赋值分录
     */
    public void createNewRow(List<Row> rowList,String nckd_entry){
        //薪酬明细（一般员工）分录字段标识
        List<String> nckd_generalemployees =Arrays.asList("nckd_se_institution","nckd_se_empname","nckd_se_empnumber"
                ,"nckd_se_idnumber","nckd_se_banknumber","nckd_se_unit","nckd_se_org","nckd_se_department","nckd_se_allbasicsry"
                ,"nckd_se_basicsry","nckd_se_acmsry","nckd_se_reacmsry","nckd_se_bsnrwd","nckd_se_otreward","nckd_se_cmcsbd"
                ,"nckd_se_otherpysry","nckd_se_clearrwd","nckd_se_awareward","nckd_se_otherdevreward","nckd_se_coldsbad"
                ,"nckd_se_warmsbad","nckd_se_nursabd","nckd_se_otherallowance","nckd_se_salarypayable","nckd_se_per_endins"
                ,"nckd_se_per_hopins","nckd_se_per_bhopins","nckd_se_per_ljins","nckd_se_per_hosins","nckd_se_per_cmymey"
                ,"nckd_se_unionmey","nckd_se_tax","nckd_se_otherreduce","nckd_se_desalary","nckd_se_netsalary","nckd_se_cpy_endins"
                ,"nckd_se_cpy_hopins","nckd_se_cpy_bhopins","nckd_se_cpy_ljins","nckd_se_cpy_birthins","nckd_se_cpy_dmgins","nckd_se_cpy_hosins"
                ,"nckd_se_cpy_cmymey");
        //薪酬明细（内退）分录字段标识
        List<String> nckd_earlyretired=Arrays.asList("nckd_ee_institution","nckd_ee_empname","nckd_ee_empnumber","nckd_ee_idnumber","nckd_ee_banknumber"
                ,"nckd_ee_unit","nckd_ee_org","nckd_ee_department","nckd_ee_allbasicsry","nckd_ee_allowance","nckd_ee_otherallowance","nckd_ee_per_endins"
                ,"nckd_ee_per_ljins","nckd_ee_per_hopins","nckd_ee_per_bhopins","nckd_ee_per_hosins","nckd_ee_per_cmymey","nckd_ee_unionmey"
                ,"nckd_ee_tax","nckd_ee_desalary","nckd_ee_netsalary","nckd_ee_otherreduce","nckd_ee_cpy_endins","nckd_ee_cpy_bhopins"
                ,"nckd_ee_cpy_ljins","nckd_ee_cpy_birthins","nckd_ee_cpy_dmgins","nckd_ee_cpy_cmymey","nckd_ee_cpy_hosins","nckd_ee_taxt"
                ,"nckd_ee_alltaxt","nckd_ee_charge","nckd_ee_finallsys");
        //薪酬明细（退休）分录字段标识
        List<String> nckd_retired=Arrays.asList("nckd_re_institution","nckd_re_empname","nckd_re_empnumber","nckd_re_idnumber","nckd_re_banknumber"
                ,"nckd_re_unit","nckd_re_org","nckd_re_department","nckd_re_livallowance","nckd_re_otherallowance","nckd_re_reward"
                ,"nckd_re_salarypayable","nckd_re_desalary","nckd_re_netsalary");
        //人力薪酬分录标识
        List<String> nckd_pay=Arrays.asList("nckd_sae_institution","nckd_sae_empname","nckd_sae_jobid","nckd_sae_idnumber","nckd_sae_banknumber","nckd_sae_company"
                ,"nckd_sae_institutions","nckd_sae_department","nckd_sae_levelsry","nckd_sae_dutiessry","nckd_sae_updutiessry","nckd_sae_workyearsry"
                ,"nckd_sae_depyear","nckd_sae_eduasry","nckd_sae_transry","nckd_sae_jobsry","nckd_sae_basicsry","nckd_sae_allbasicsry","nckd_sae_acmsry"
                ,"nckd_sae_mountsry","nckd_sae_reacmsry","nckd_sae_balyearsry","nckd_sae_blastyearsry","nckd_sae_allsry","nckd_sae_bsnrwd","nckd_sae_cpmrwd"
                ,"nckd_sae_psrwd","nckd_sae_comrwd","nckd_sae_cardrwd","nckd_sae_intwrd","nckd_sae_clearrwd","nckd_sae_allrwd","nckd_sae_retainsry"
                ,"nckd_sae_reward","nckd_sae_otreward","nckd_sae_cmcsbd","nckd_sae_trfsbd","nckd_sae_housesbd","nckd_sae_warmsbd","nckd_sae_edusbd"
                ,"nckd_sae_trafficsbd","nckd_sae_othersbd","nckd_sae_reduce","nckd_sae_othersry","nckd_sae_sickreduce","nckd_sae_sickhopreduce"
                ,"nckd_sae_eventreduce","nckd_sae_afwreduce","nckd_sae_latereduce","nckd_sae_cwasry","nckd_sae_cwareduce","nckd_sae_dgreduce"
                ,"nckd_sae_dcpreduce","nckd_sae_badreduce","nckd_sae_otherreduce","nckd_sae_allreduce","nckd_sae_lastsry","nckd_sae_allpay"
                ,"nckd_sae_per_endins","nckd_sae_per_ljins","nckd_sae_per_hopins","nckd_sae_per_bhopins","nckd_sae_per_hosins","nckd_sae_per_cmymey"
                ,"nckd_sae_per_ins","nckd_sae_unionmey","nckd_sae_tax","nckd_sae_allamount","nckd_sae_cash","nckd_sae_wages","nckd_sae_cpy_endins"
                ,"nckd_sae_cpy_hopins","nckd_sae_cpy_bhopins","nckd_sae_cpy_birthins","nckd_sae_cpy_ljins","nckd_sae_cpy_dmgins","nckd_sae_cpy_hosins"
                ,"nckd_sae_cpy_cmymey");

        //人力薪酬内退分录标识
        List<String> nckd_payretird=Arrays.asList("nckd_see_institution","nckd_see_empname","nckd_see_jobid","nckd_see_idnumber"
                ,"nckd_see_banknumber","nckd_see_company","nckd_see_institutions","nckd_see_department","nckd_see_allbasicsry"
                ,"nckd_see_allowance","nckd_see_otherallowanc","nckd_see_per_endins","nckd_see_per_ljins","nckd_see_per_hopins"
                ,"nckd_see_per_bhopins","nckd_see_per_hosins","nckd_see_per_cmymey","nckd_see_unionmey","nckd_see_tax"
                ,"nckd_see_desalary","nckd_see_netsalary","nckd_see_otherreduce","nckd_see_cpy_endins","nckd_see_cpy_bhopins"
                ,"nckd_see_cpy_ljins","nckd_see_cpy_birthins","nckd_see_cpy_dmgins","nckd_see_cpy_cmymey","nckd_see_cpy_hosins"
                ,"nckd_see_taxt","nckd_see_alltaxt","nckd_see_charge","nckd_see_finallsys","nckd_see_cpy_oldins","nckd_see_cpy_hop"
                ,"nckd_see_cpy_bhop","nckd_see_cpy_bir","nckd_see_cpy_ljob","nckd_see_cpy_dwork","nckd_see_cpy_hos","nckd_see_cpy_cmy");


        //将excel数据存入薪酬明细（一般员工）分录
        if(nckd_entry.equals("nckd_staffentry")) {
            //先清空分录
            this.getModel().deleteEntryData("nckd_staffentry");
            for (int i=0;i< rowList.size();i++) {
                //新增动态单据分录
                int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
                //发薪机构
                if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
                    //获取发薪机构
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_generalemployees.get(0),entrycostdept,rowIndex);
                }
                //员工名称
                if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
                    this.getModel().setValue(nckd_generalemployees.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
                }
                //员工工号
                if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
                    this.getModel().setValue(nckd_generalemployees.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
                }
                //证件号
                if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
                    this.getModel().setValue(nckd_generalemployees.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
                }
                //银行账号
                if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
                    this.getModel().setValue(nckd_generalemployees.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
                }
                //所在单位
                if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()){
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
                    //获取所在单位
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_generalemployees.get(5),entrycostdept,rowIndex);
                }
                //一级机构
                if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
                    //获取部门
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_generalemployees.get(6),entrycostdept,rowIndex);
                }
                //所在部门
                if (!getCellValue(rowList.get(i).getCell(8)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
                    //获取部门
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_generalemployees.get(7),entrycostdept,rowIndex);
                }
                //金额类型字段赋值
                for (int j=8;j<nckd_generalemployees.size();j++) {
                    Cell cell=rowList.get(i).getCell(j+1);
                    //空值则跳过
                    if (!Objects.equals(getCellValue(cell), "")) {
                        //金额字段转化为BigDecimal赋值进分录中
                        String string=getCellValue(cell);
                        BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
                        this.getModel().setValue(nckd_generalemployees.get(j),bigDecimal,rowIndex);
                    }
                }
                //刷新分录
                this.getView().updateView("nckd_staffentry");
            }
        }
        //薪酬明细（内退）分录
        if (nckd_entry.equals("nckd_earlyretiredentry")) {
            this.getModel().deleteEntryData("nckd_earlyretiredentry");
            for (int i=0;i<rowList.size();i++) {
                //新增动态单据分录
                int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
                //发薪机构
                if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
                    //获取发薪机构
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_earlyretired.get(0),entrycostdept,rowIndex);
                }
                //员工姓名
                if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
                    this.getModel().setValue(nckd_earlyretired.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
                }
                //员工工号
                if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
                    this.getModel().setValue(nckd_earlyretired.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
                }
                //证件号
                if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
                    this.getModel().setValue(nckd_earlyretired.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
                }

                //银行账号
                if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
                    this.getModel().setValue(nckd_earlyretired.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
                }
                //所在单位
                if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()){
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_earlyretired.get(5),entrycostdept,rowIndex);
                }
                //一级机构
                if (!getCellValue(rowList.get(i).getCell(7)).isEmpty())
                {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_earlyretired.get(6),entrycostdept,rowIndex);
                }
                //所在部门
                if (!getCellValue(rowList.get(i).getCell(8)).isEmpty())
                {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_earlyretired.get(7),entrycostdept,rowIndex);
                }
                //金额类型字段赋值
                for (int j=8;j<nckd_earlyretired.size();j++) {
                    Cell cell=rowList.get(i).getCell(j+1);
                    //空值则跳过
                    if (!Objects.equals(getCellValue(cell), "")) {
                        //金额字段转化为BigDecimal赋值进分录中
                        String string=getCellValue(cell);
                        BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
                        this.getModel().setValue(nckd_earlyretired.get(j),bigDecimal,rowIndex);
                    }
                }
            }
        }

        //薪酬明细（退休）分录
        if(nckd_entry.equals("nckd_retireentry")) {
            this.getModel().deleteEntryData("nckd_retireentry");
            for (int i=0;i<rowList.size();i++) {
                //新增动态单据分录
                int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
                //发薪机构
                if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
                    //获取发薪机构
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_retired.get(0),entrycostdept,rowIndex);
                }
                //员工姓名
                if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
                    this.getModel().setValue(nckd_retired.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
                }
                //员工工号
                if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
                    this.getModel().setValue(nckd_retired.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
                }
                //证件号
                if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
                    this.getModel().setValue(nckd_retired.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
                }
                //银行账号
                if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
                    this.getModel().setValue(nckd_retired.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
                }
                //所在单位
                if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_retired.get(5),entrycostdept,rowIndex);
                }
                //一级机构
                if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_retired.get(6),entrycostdept,rowIndex);
                }
                //所在部门
                if (!getCellValue(rowList.get(i).getCell(8)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_retired.get(7),entrycostdept,rowIndex);
                }
                //金额类型字段赋值
                for (int j=8;j<nckd_retired.size();j++) {
                    Cell cell=rowList.get(i).getCell(j+1);
                    //空值则跳过
                    if (!Objects.equals(getCellValue(cell), "")) {
                        //金额字段转化为BigDecimal赋值进分录中
                        String string=getCellValue(cell);
                        BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
                        this.getModel().setValue(nckd_retired.get(j),bigDecimal,rowIndex);
                    }
                }
            }
        }
        //人力薪酬分录
        if(nckd_entry.equals("nckd_salaryentry")) {
            this.getModel().deleteEntryData("nckd_salaryentry");
            for (int i=0;i<rowList.size();i++) {
                //新增动态单据分录
                int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
                //发薪机构
                if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
                    //获取发薪机构
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_pay.get(0),entrycostdept,rowIndex);
                }
                //姓名
                if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
                    this.getModel().setValue(nckd_pay.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
                }
                //工号
                if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
                    this.getModel().setValue(nckd_pay.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
                }
                //证件号
                if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
                    this.getModel().setValue(nckd_pay.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
                }
                //银行账号
                if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
                    this.getModel().setValue(nckd_pay.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
                }
                //所在单位
                if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_pay.get(5),entrycostdept,rowIndex);
                }
                //一级机构
                if (!getCellValue(rowList.get(i).getCell(7)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_pay.get(6),entrycostdept,rowIndex);
                }
                //所在部门
                if (!getCellValue(rowList.get(i).getCell(8)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_pay.get(7),entrycostdept,rowIndex);
                }
                //金额类型字段赋值
                for (int j=9;j<nckd_pay.size();j++) {
                    Cell cell=rowList.get(i).getCell(j+1);
                    //空值则跳过
                    if (!Objects.equals(getCellValue(cell), "")) {
                        //金额字段转化为BigDecimal赋值进分录中
                        String string=getCellValue(cell);
                        BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
                        this.getModel().setValue(nckd_pay.get(j),bigDecimal,rowIndex);
                    }
                }
            }

        }

        //人力薪酬内退分录
        if (nckd_entry.equals("nckd_salaryentryre")){
            this.getModel().deleteEntryData("nckd_salaryentryre");
            for (int i=0;i<rowList.size();i++) {
                //新增动态单据分录
                int rowIndex = this.getModel().createNewEntryRow(nckd_entry);
                //发薪机构
                if (!getCellValue(rowList.get(i).getCell(1)).isEmpty()) {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(1)));
                    //获取发薪机构
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_org,"id,entrycostdept",new QFilter[]{filter});
                    this.getModel().setValue(nckd_payretird.get(0),entrycostdept,rowIndex);
                }
                //员工姓名
                if (!getCellValue(rowList.get(i).getCell(2)).isEmpty()){
                    this.getModel().setValue(nckd_payretird.get(1),getCellValue(rowList.get(i).getCell(2)),rowIndex);
                }
                //员工工号
                if (!getCellValue(rowList.get(i).getCell(3)).isEmpty()){
                    this.getModel().setValue(nckd_payretird.get(2),getCellValue(rowList.get(i).getCell(3)),rowIndex);
                }
                //证件号
                if (!getCellValue(rowList.get(i).getCell(4)).isEmpty()){
                    this.getModel().setValue(nckd_payretird.get(3),getCellValue(rowList.get(i).getCell(4)),rowIndex);
                }
                //银行账号
                if (!getCellValue(rowList.get(i).getCell(5)).isEmpty()){
                    this.getModel().setValue(nckd_payretird.get(4),getCellValue(rowList.get(i).getCell(5)),rowIndex);
                }
                //所在单位
                if (!getCellValue(rowList.get(i).getCell(6)).isEmpty()){
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(6)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_payretird.get(5),entrycostdept,rowIndex);
                }
                //一级机构
                if (!getCellValue(rowList.get(i).getCell(7)).isEmpty())
                {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(7)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_payretird.get(6),entrycostdept,rowIndex);
                }
                //所在部门
                if (!getCellValue(rowList.get(i).getCell(8)).isEmpty())
                {
                    QFilter filter=new QFilter("number", QFilter.equals,getCellValue(rowList.get(i).getCell(8)));
                    DynamicObject entrycostdept= BusinessDataServiceHelper
                            .loadSingle(bos_adminorg,"id",new QFilter[]{filter});
                    this.getModel().setValue(nckd_payretird.get(7),entrycostdept,rowIndex);
                }
                //金额类型字段赋值
                for (int j=8;j<nckd_payretird.size();j++) {
                    Cell cell=rowList.get(i).getCell(j+1);
                    //空值则跳过
                    if (!Objects.equals(getCellValue(cell), "")) {
                        //金额字段转化为BigDecimal赋值进分录中
                        String string=getCellValue(cell);
                        BigDecimal bigDecimal= BigDecimal.valueOf(Double.parseDouble(string));
                        this.getModel().setValue(nckd_payretird.get(j),bigDecimal,rowIndex);
                    }
                }
            }
        }
    }




    /**
     * @headList 读取的excel表头数据
     * @rowList 读取excel的表数据
     * @billtype 分录标识
     * @description：校验excel数据是否合法，传入的excel是否为正确的格式
     */
    public void dataVerify(List<Row> headList,List<Row> rowList,String billtype) {
        //获取对应单据的字段名
        String flag;
        //薪酬明细（一般员工）
        if (billtype.equals("nckd_staffentry")) {
            //获取单据的字段名
             flag=headList.get(0).getCell(9).getStringCellValue();
            if (!flag.equals("基本工资")) {
                throw new KDBizException("上传的文件格式有误，请检查！");
            }
            for (int i=0;i<rowList.size();i++) {
                //校验金额字段
                for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
                    Cell cell=rowList.get(i).getCell(j);
                    //单元格不为空
                    if (!Objects.equals(getCellValue(cell), "")) {
                        if (cell.getCellType()!=CellType.NUMERIC){
                            //输入金额字段的类型不为数字则提示
                            throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
                        }
                    }
                }
            }
        }
        //薪酬明细（内退）
        if (billtype.equals("nckd_earlyretiredentry")) {
            flag=headList.get(0).getCell(9).getStringCellValue();
            if (!flag.equals("内退生活费")) {
                throw new KDBizException("上传的文件格式有误，请检查！");
            }
            for (int i=0;i<rowList.size();i++) {
                //校验金额字段
                for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
                    Cell cell=rowList.get(i).getCell(j);
                    //单元格不为空
                    if (!Objects.equals(getCellValue(cell), "")) {
                        if (cell.getCellType()!=CellType.NUMERIC){
                            //输入金额字段的类型不为数字则提示
                            throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
                        }
                    }
                }
            }
        }
        //薪酬明细（退休）
        if (billtype.equals("nckd_retireentry")) {
            flag=headList.get(0).getCell(9).getStringCellValue();
            if (!flag.equals("退休员工生活补贴")) {
                throw new KDBizException("上传的文件格式有误，请检查！");
            }
            for (int i=0;i<rowList.size();i++) {
                //校验金额字段
                for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
                    Cell cell=rowList.get(i).getCell(j);
                    //单元格不为空
                    if (!Objects.equals(getCellValue(cell), "")) {
                        if (cell.getCellType()!=CellType.NUMERIC){
                            //输入金额字段的类型不为数字则提示
                            throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
                        }
                    }
                }
            }
        }
        //人力薪酬
        if (billtype.equals("nckd_salaryentry")) {
            flag=headList.get(0).getCell(9).getStringCellValue();
            if (!flag.equals("级别工资")) {
                throw new KDBizException("上传的文件格式有误，请检查！");
            }
            for (int i=0;i<rowList.size();i++) {
                //校验金额字段
                for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
                    Cell cell=rowList.get(i).getCell(j);
                    //单元格不为空
                    if (!Objects.equals(getCellValue(cell), "")) {
                        if (cell.getCellType()!=CellType.NUMERIC){
                            //输入金额字段的类型不为数字则提示
                            throw new KDBizException("第"+(i+1)+"行第"+(j+1)+"列的数据:"+headList.get(i).getCell(j+1).getStringCellValue()+"字段有误，请检查！");
                        }
                    }
                }
            }
        }
        //人力薪酬（内退）
        if (billtype.equals("nckd_salaryentryre"))
        {
            flag=headList.get(0).getCell(9).getStringCellValue();
            if (!flag.equals("内退生活费")) {
                throw new KDBizException("上传的文件格式有误，请检查！");
            }
            for (int i=0;i<rowList.size();i++) {
                //校验金额字段
                for (int j=9;j<rowList.get(i).getLastCellNum();j++) {
                    Cell cell=rowList.get(i).getCell(j);
                    //单元格不为空
                    if (!Objects.equals(getCellValue(cell), "")) {
                        if (cell.getCellType()!=CellType.NUMERIC){
                            //输入金额字段的类型不为数字则提示
                            throw new KDBizException("第"+(i+1)+"行第"+j+"列的数据:"+headList.get(i).getCell(j).getStringCellValue()+"字段有误，请检查！");
                        }
                    }
                }
            }
        }
    }


    /**
     * 对Excel的各个单元格的格式进行判断并转换
     */
    public  String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        // 判断数据的类型
        switch (cell.getCellType()) {
            case NUMERIC:
                //读取数字将不必要的小数点去掉
                BigDecimal bigDecimal=new BigDecimal(Double.toString(cell.getNumericCellValue()));
                cellValue=bigDecimal.stripTrailingZeros().toPlainString();
                break;
            case STRING: // 字符串
                cellValue = String.valueOf(cell.getStringCellValue()).trim();
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "未知类型";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }
}