lims系统，收付财务管理-支出成本管理-支出成本调整-导入表格数据功能实现

最新推荐文章于 2024-07-11 17:24:34 发布

Cyril.XF.Wang

最新推荐文章于 2024-07-11 17:24:34 发布

阅读量220

点赞数

文章标签： vue java excel

本文链接：https://blog.csdn.net/So_Hollow/article/details/134248000

版权

demo 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

页面增加导入数据按钮，新增点击导入按钮后弹出的页面组件

index.html

<section>
    <gv-data-table ref="table" :table="table" @on-reset="onReset" @on-list-btn="onListBtn"  >
        <template v-slot:search="scope">
            <div class="gv-row">
                <gv-form-item key-name="关键字" prop="keyWord" :colspan="5">
                    <el-input :placeholder="'请输入报价单号/项目编号/客户名称/销售/计费单号' | translate" v-model="scope.search.keyWord"
                              clearable></el-input>
                </gv-form-item>
            </div>
            <div class="gv-row">
                <gv-form-item key-name="custVoDepartment" prop="department" :colspan="5">
                    <el-cascader filterable @change="departmentChange" v-model="department.value"
                                 :options="department.data"
                                 :props="department.defaultProps"
                                 clearable></el-cascader>
                </gv-form-item>
                <!-- <gv-form-item key-name="部门" prop="salesDepartmentName" :colspan="5">
                    <el-input v-model="scope.search.salesDepartmentName" clearable></el-input>
                </gv-form-item> -->
            </div>
            <div class="gv-row">
                <gv-form-item key-name="坏帐标志" prop="isDadDebts">
                    <gv-select code-type="IsDadDebts" options-set="2" v-model="scope.search.isDadDebts">
                    </gv-select>
                </gv-form-item>
            </div>
        </template>
        <!-- 为了保持样式一致 ，在main.js里面配置 -->
<!--        <template v-slot:toolbar="scope">-->
<!--            <div class="pull-right">-->
<!--                <el-button class="gv-btn gv-btn-primary"  @click="onHandleSite()">-->
<!--                    {{ '测试单查询' | translate }}-->
<!--                </el-button>-->
<!--            </div>-->
<!--         </template>-->

        <!--新增-导入表格数据按钮-开始-->
        <template v-slot:toolbar="scope">
            <div class="pull-left">
                <el-button class="gv-btn gv-btn-primary" @click="openImportDialogPage()">
                    {{ '导入表格数据' | translate }}
                </el-button>
            </div>
        </template>
        <!--新增-导入表格数据按钮-结束-->

    </gv-data-table>
    <!--支出成本-->
    <transition name="dialog-fade">
        <test-item-price-revise-app-adj-pay v-if="isShowPay" @onClose="onCallPay" :testitemNo="testitemNo"
                                            :detailId="detailId" :quotationNo="quotationNo"
                                            :testitemId = "testitemId"
                                            :msTestItemOutlayRecordVoList="msTestItemOutlayRecordVoList"
                                            :toRmbPrice="toRmbPrice"
                                            :rmbActualAgentPrice="rmbActualAgentPrice"
                                            :isAgent="isAgent"
                                            :agentSupplierCode="agentSupplierCode"
                                            :agentSupplierName="agentSupplierName"/>
    </transition>

    <!--新增-导入表格数据按钮-开始-->
    <transition name="dialog-fade">
        <import-dialog-page-of-excel
                v-if="isShowImportDialogPage"
                @closeImportDialogPage="closeImportDialogPage"/>
    </transition>
    <!--新增-导入表格数据按钮-结束-->
</section>

index.js

/**
 * 支出金额调整
 */
define(function (require) {
    var TestItemPriceReviseAppAdjPay = require('./components/testItemPriceReviseAppAdjPay');
    // 导入表格数据
    var ImportDialogPageOfExcel = require('./components/importDialogPageOfExcel');
    return Vue.gvUtil.Page({
        template: require('./index.html'),
        name: 'testItemOutlayRecordApp',
        components: {
            TestItemPriceReviseAppAdjPay: TestItemPriceReviseAppAdjPay,
            // 导入表格数据
            ImportDialogPageOfExcel: ImportDialogPageOfExcel,
        },
        params: function () { // 双向绑定状态数据
            return {
                isShowPay: false,
                msTestItemOutlayRecordVoList: [],
                testitemNo: '',
                detailId: '',
                testitemId:'',
                toRmbPrice: '',
                rmbActualAgentPrice: '',
                isAgent: '',
                agentSupplierCode: '',
                agentSupplierName: '',
                // 导入表格数据，是否显示下载导入模板窗口
                isShowImportDialogPage: false,
            }
        },
        datas: function () { // 双向绑定页面显示数据
            return {
                table: {
                    basic: {
                        api: "testORList", //分页列表请求api
                        vo: "testItemOutlayReviseList", //分页列表返回的vo
                        context: "core", //分页列表请求上下文
                        singleElection: false, //是否支持单选  获取选中数据 this.$refs.table.getSelectData()
                        multipleElection: false, //是否支持多选  获取选中数据 this.$refs.table.getSelectData()
                        expand: false, // 是否使用展开行
                        autoSearch: true,
                        isQuotationSearchShow: true
                    },
                    search: { //查询域元数据
                        keyWord: '', // 关键字
                        salesDepartmentCode: '',
                        department: '',
                        isDadDebts: '' ,// 报价单状态
                        isShowSiteTest: '0'
                    },
                    fields: [{ //配置最后列按钮
                        prop: null,
                        labelKey: 'gTitleOperation',
                        width: '100px',
                        btns: [{
                            btnKey: '支出调整', //国际化key
                            flag: '1',
                            type: 'btn'
                        }]
                    }, {
                        prop: 'quotationNo',
                        width: '130px',
                        labelKey: '报价单号'
                    }, {
                        prop: 'testitemNo',
                        width: '140px',
                        labelKey: '项目编号'
                    }, {
                        prop: 'isMain',
                        width: '120px',
                        labelKey: '是否主项目'
                    }, {
                        prop: 'starName',
                        width: '220px',
                        labelKey: '客户名称'
                    }, {
                        prop: 'salesName',
                        width: '100px',
                        labelKey: '销售',
                    }, {
                        prop: 'salesDepartmentName',
                        width: '130px',
                        labelKey: '部门',
                        showOverflowTooltip: true,
                    }, {
                        prop: 'quotationStatus',
                        width: '110px',
                        labelKey: '报价单状态',
                        format: {
                            codeType: 'quotationStatus',
                            type: 'ggcode' //num ggcode date
                        }
                    }, {
                        prop: 'orgName',
                        width: '130px',
                        labelKey: '承接实验室',
                        showOverflowTooltip: true,
                    }, {
                        prop: 'status',
                        width: '120px',
                        labelKey: '项目状态',
                        format: {
                            codeType: 'LabProjectStatus',
                            type: 'ggcode' //num ggcode date
                        }
                    }, {
                        prop: 'incomeConfirmStatus',
                        width: '150px',
                        labelKey: '是否收入确认',
                        format: {
                            codeType: 'IncomeConfirmStatus',
                            type: 'ggcode' //num ggcode date
                        }
                    }, {
                        prop: 'isDadDebts',
                        width: '120px',
                        labelKey: '坏帐标记',
                        format: {
                            codeType: 'IsDadDebts',
                            type: 'ggcode' //num ggcode date
                        }
                    }, {
                        prop: 'toRmbPrice',
                        labelKey: '项目金额（RMB）',
                        width: '150px',
                        align: 'right',
                        format: {
                            type: 'num', //num ggcode date
                        }
                    }, {
                        prop: 'rmbActualAgentPrice',
                        labelKey: '外包金额（RMB）',
                        width: '150px',
                        align: 'right',
                        format: {
                            type: 'num', //num ggcode date
                        }
                    }, {
                        prop: 'outlayPriceTotal',
                        labelKey: '支出成本（RMB）',
                        width: '150px',
                        align: 'right',
                        format: {
                            type: 'num', //num ggcode date
                        }
                    }, {
                        prop: 'netPrice',
                        labelKey: '净额（RMB）',
                        width: '150px',
                        align: 'right',
                        format: {
                            type: 'num', //num ggcode date
                        }
                    }, {
                        prop: 'logUpdateTime',
                        labelKey: '最新修改金额时间',
                        width: '150px',
                        align: 'right'
                    }]
                },
                department: {
                    data: [{
                        label: '暂无数据',
                        value: ''
                    }],
                    defaultProps: {
                        multiple: false,
                        expandTrigger: 'hover',
                        children: 'children',
                        label: 'label',
                        value: 'id'
                    },
                    value: []
                }
            }
        },
        events: {
            // 清除查询条件
            onReset: function () {
                this.table.search.salesDepartmentCode = '';
                this.department.value = [];
            },
            onListBtn: function (row, type) {
                switch (type) {
                    case '1':
                        this.onOpenPay(row);
                        break;
                }
            },
            // 打开支出
            onOpenPay: function (row) {
                this.isShowPay = true;
                this.msTestItemOutlayRecordVoList = row.msTestItemOutlayRecordVoList;
                this.detailId = row.detailId;
                this.testitemId = row.testitemId ;
                this.testitemNo = row.testitemNo;
                this.quotationNo = row.quotationNo;
                this.toRmbPrice = row.toRmbPrice;
                this.rmbActualAgentPrice = row.rmbActualAgentPrice;
                this.isAgent = row.isAgent;
                this.agentSupplierCode = row.agentSupplierCode;
                this.agentSupplierName = row.agentSupplierName;
            },
            // 关闭支出
            onCallPay: function (flag) {
                this.isShowPay = false;
                if (flag) {
                    this.searchLists();
                }
                this.detailId = null;
                this.testitemId = '' ;
                this.msTestItemOutlayRecordVoList = [];
                this.testitemNo = '';
            },
        },
        methods: {
            initPage: function () {
                this.requestDepartment();
            },
            departmentChange: function (data) {
                if (data instanceof Array && data.length > 0) {
                    this.table.search.salesDepartmentCode = data[data.length - 1];
                } else {
                    this.table.search.salesDepartmentCode = ''
                }
            },
            // 获取部门
            requestDepartment: function () {
                const _this = this;
                let data = {
                    orgCate: '1',
                    fdOrgType: '2'
                };
                let url = Vue.gvUtil.getUrl({
                    apiName: 'orgSearchTree',
                    contextName: 'auth'
                });
                Vue.gvUtil.http.post(url, data).then(function (res) {
                    if (res.resCode === '0000') {
                        _this.setDepartment([res.resData.navMenusData])
                    }
                });
            },
            // 设置级联数据子级
            setDepartment: function (data) {
                for (let i = 0; i < data.length; i++) {
                    if (data[i].children instanceof Array && data[i].children.length > 0) {
                        this.setDepartment(data[i].children);
                    } else {
                        data[i].children = undefined
                    }
                }
                this.department.data = data;
            },
            // 导入表格数据弹出框
            openImportDialogPage: function () {
                // 延时打开窗口
                setTimeout(() => {
                    this.isShowImportDialogPage = true;
                }, 500)
            },
            // 关闭导入表格数据弹出框
            closeImportDialogPage: function (param) {
                this.isShowImportDialogPage = false;
                if (param == 'close') {
                    window.location.reload();
                }
            },
        }
    });
});

index.config.js

(function () {
    return {
        api: {
            // 查询列表
            'testORList': '/msTestitemDetails/find_testItem_outlay_revise_list',
            // 新增支出
            'testPRGetPriceRecordAdd': '/msTestitemOutlayRecord/add',
            // 获取部门数据orgCode: 1 部门数据
            'orgSearchTree': '/org/element/findTreeByCateid',
            // 支出成本调整-导入表格数据
            'importCapexDataOfExcel': '/msTestitemOutlayRecord/importCapexDataOfExcel',
            // 支出成本调整-下载导入表格数据的模板
            'exportCapexDataTemplate': '/msTestitemOutlayRecord/exportCapexDataTemplate',
        },
        router: []
    }
})();

components组件文件夹

importDialogPageOfExcel.html

<!--
支出成本调整，导入表格数据
路径：收付财务管理-支出成本管理-支出成本调整-导入表格数据
-->
<section>
    <el-dialog
            title="导入表格数据"
            :visible.sync="isShowUpload"
            width="40%" custom-class="gv-dialog-form"
            :close-on-click-modal="false" style="min-height: 500px;"
            :before-close="onClose">
        <gv-form ref="uploadForm" :model="uploadForm">
            <gv-panel sub-title="gRequiresTitle">
                <div class="gv-row">
                    <gv-form-item key-name="模板下载" :colspan="5">
                        <el-button @click="downLoad()" type="primary">
                            {{'gBtnDownLoad' | translate}}<i class="el-icon-upload el-icon--right"></i>
                        </el-button>
                    </gv-form-item>
                </div>
                <div class="gv-row">
                    <gv-form-item key-name="Excel导入" prop="file" :colspan="5">
                        <el-input v-model="uploadForm.fileSize" v-show="false"></el-input>
                        <el-upload class="upload-demo"
                                   :auto-upload="false"
                                   ref="upFile"
                                   action="upFileUrl"
                                   :multiple="false"
                                   :limit="1"
                                   :on-remove="handleRemove"
                                   :on-change="getFile"
                                   :file-list="uploadFormFileList"
                                   :on-exceed="onExceed">
                            <el-button size="small" type="primary">{{'gChangeUpload' | translate}}</el-button>
                            <div slot="tip" class="el-upload__tip">{{'temporaryPriceVoOnlyUploadExcelFile' |
                                translate}}
                            </div>
                        </el-upload>
                    </gv-form-item>
                </div>
            </gv-panel>
        </gv-form>
        <el-row class="toolbar-btn txt-center">
            <el-button class="gv-btn gv-btn-primary" type="primary" @click.stop="onuUploadSubmit()">
                {{ '确认' | translate }}
            </el-button>
            <el-button class="gv-btn gv-btn-white" @click.stop="onClose">
                {{ 'gBtnClose' | translate('Close') }}
            </el-button>
        </el-row>
    </el-dialog>
</section>

importDialogPageOfExcel.js

/**
 支出成本调整，导入表格数据
 路径：收付财务管理-支出成本管理-支出成本调整-导入表格数据
 */
define(function (require) {
    return Vue.gvUtil.Page({
        template: require('./importDialogPageOfExcel.html'),
        name: 'importDialogPageOfExcelApp',
        props: { // 路由跳转传的参数，必须显式维护在此
            quotationInfo: {
                type: Object,
                requires: true,
            }
        },
        datas: function () { // 双向绑定页面显示数据
            return {
                isShowUpload: true,
                uploadForm: {
                    file: [], // 文件
                },
                uploadFormFileList: []
            }
        },
        events: {
            // 关闭模板上传窗口
            onCloseUpload: function (param) {
                if (this.uploadFormFileList.length > 0) {
                    this.uploadFormFileList = [];
                }
                this.$emit('closeImportDialogPage', param);
            },
            // 关闭模板上传窗口
            onClose: function () {
                if (this.uploadFormFileList.length > 0) {
                    this.uploadFormFileList = [];
                }
                this.$emit('closeImportDialogPage', '');
            },
            // 获取文件
            getFile: function (File, uploadFormFileList) {
                if (this.uploadFormFileList.length > 0) {
                    this.handleRemove(File, this.uploadFormFileList);
                }
                let file = File.raw;
                let fileinfo = file.name.split('.');
                let type = fileinfo[fileinfo.length - 1];
                if (!(file.type === 'application/vnd.ms-excel' || type === 'xlsx' || type === 'xls')) {
                    Vue.gvUtil.alert({
                        msg: Vue.gvUtil.getInzTranslate('请上传Excel文件'),
                    }).then(function () {
                    })
                    this.uploadFormFileList = [];
                    return;
                }
                this.uploadFormFileList = uploadFormFileList;
            },
            // 删除文件
            handleRemove: function (File, uploadFormFileList) {
                this.uploadFormFileList = uploadFormFileList;
            },
            // 替换文件
            onExceed: function (File, uploadFormFileList) {
                this.uploadFormFileList = [{
                    raw: File[0],
                    name: File[0].name
                }];
                // 校验替换的文件是否仍然是excel文件
                this.checkUploadFlie(File, this.uploadFormFileList);
            },
            // 校验替换的文件是否仍然是excel文件
            checkUploadFlie: function (File, uploadFormFileList) {
                if (this.uploadFormFileList.length > 0) {
                    this.handleRemove(File, this.uploadFormFileList);
                }
                let file = uploadFormFileList[0].raw;
                let fileinfo = file.name.split('.');
                let type = fileinfo[fileinfo.length - 1];
                if (!(file.type === 'application/vnd.ms-excel' || type === 'xlsx' || type === 'xls')) {
                    Vue.gvUtil.alert({
                        msg: Vue.gvUtil.getInzTranslate('请上传Excel文件'),
                    }).then(function () {
                    })
                    this.uploadFormFileList = [];
                    return;
                }
                this.uploadFormFileList = uploadFormFileList;
            },
        },
        methods: {
            initPage: function () {

            },
            // 确认上传
            onuUploadSubmit: function () {
                const _this = this;
                _this.$refs.uploadForm.validate(function (valid) {
                    if (valid) {
                        // 校验通过  需要判断是否存在文件
                        if (_this.uploadFormFileList.length > 0) {
                            let formData = new FormData();
                            formData.append('file', _this.uploadFormFileList[0].raw);
                            // 新增
                            var url = Vue.gvUtil.getUrl({
                                apiName: 'importCapexDataOfExcel',
                                contextName: 'core',
                            })
                            Vue.gvUtil.http.post(url, formData).then(function (res) {
                                _this.appTempUploadSuccessSubmit(res);
                            })
                        } else {
                            Vue.gvUtil.message(Vue.gvUtil.getInzTranslate('校验不通过,请上传Excel文件！'))
                        }
                    }
                })
            },
            // 上传文件成功提示
            appTempUploadSuccessSubmit: function (res) {
                const _this = this;
                if (res.resCode === '0000') {
                    if (res.resData === "导入成功") {
                        Vue.gvUtil.message(res.resData, 3000, 'success');
                        // 关闭当前弹窗 且清除文件数据
                        _this.onCloseUpload('close');
                        // 刷新当前页面
                        // window.location.reload();
                    } else {
                        _this.$alert(res.resData, '', {
                            dangerouslyUseHTMLString: true
                        });
                        // Vue.gvUtil.message(res.resData, 1500, 'error');
                    }
                }
            },
            // 下载模板
            downLoad: function () {
                var url = Vue.gvUtil.getUrl({
                    apiName: 'exportCapexDataTemplate',
                    contextName: 'core',
                    serachParms: {
                        templateId: 'exportCapexDataTemplate',
                    }
                });
                url = url;
                var a = document.createElement("a");
                a.href = url;
                $("body").append(a); // 修复firefox中无法触发click
                a.click();
                $(a).remove();
            },
        },
    });
});

新增点击导入按钮后，处理“支出成本调整Excel导入表格数据”的后台方法

Controller

 /***
     * @description: 收付财务管理-支出成本管理-支出成本调整-导入表格数据
     * @param request
     * @param file
     * @return com.platform.gis.core.model.BaseResponse<java.lang.String>
     * @date: 2023/09/05
     */
    @ApiOperation("支出成本调整Excel导入表格数据")
    @RequestMapping(value = {"/importCapexDataOfExcel"}, method = {RequestMethod.POST})
    public BaseResponse<String> importCapexDataOfExcel(HttpServletRequest request, @RequestParam("file") MultipartFile file) {
        //声明返回结果变量
        BaseResponse<String> result = null;
        try {
            result = msTestitemOutlayRecordService.importCapexDataOfExcel(request, file);
        } catch (Exception e) {
            LOG.info("core_api_importCapexDataOfExcel导入失败", e);
            result = new BaseResponse<>(ResCodeConstant.RESCODE_SUCCESS, TemplateParam.IMPORT_FAILED);
        }
        return result;
    }

    /**
     * @param request
     * @param response
     * @param templateId
     * @return java.lang.Object
     * @description: 收付财务管理-支出成本管理-支出成本调整-导入表格数据-模板下载
     * @date: 2023/09/11
     */
    @ApiOperation("支出成本调整Excel模板导出")
    @GetMapping("/exportCapexDataTemplate")
    public Object exportCapexDataTemplate(HttpServletRequest request, HttpServletResponse response, @RequestParam String templateId) {
        HttpServletResponse httpServletResponse = null;
        // 获取模板参数
        if (StringUtils.isEmpty(templateId)) {
            return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, TemplateParam.NO_RELATED_TEMPLATE_FOUND);
        }
        try {
            httpServletResponse = msTestitemOutlayRecordService.exportCapexDataTemplate(request, response, templateId);
        } catch (Exception e) {
            LOG.error("core_api_exportCapexDataTemplate下载模板失败", e);
            return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, TemplateParam.FAILED_TO_DOWNLOAD_TEMPLATE);
        }
        return httpServletResponse;
    }

service

  /**
     * @param request
     * @param file
     * @return com.platform.gis.core.model.BaseResponse<java.lang.String>
     * @description: 支出成本调整Excel导入表格数据
     * @date: 2023/09/05
     */
    BaseResponse<String> importCapexDataOfExcel(HttpServletRequest request, MultipartFile file);

    /**
     * @param request
     * @param response
     * @param templateId
     * @return javax.servlet.http.HttpServletResponse
     * @description: 支出成本调整Excel模板导出
     * @date: 2023/09/11
     */
    HttpServletResponse exportCapexDataTemplate(HttpServletRequest request, HttpServletResponse response, String templateId);

serviceimpl

 /*添加注入*/
    @Autowired
    private MsApplicationMainService msApplicationMainService;

    /*添加导入Excel的字段说明*/
    public static final String TESTITEM_NO = "项目编号";
    public static final String QUOTATION_NO = "报价单号";
    public static final String OUTLAY_PRICE = "支出金额";
    public static final String OUTLAY_TYPE = "支出类型";
    public static final String REMARK = "支出原因";
    public static final String CREATETIME = "支出时间";
    /*支出成本调整模板下载地址*/
    public static final String OUTLAYRECORD_TEMPLATE_DOWN_PATH = "files" + File.separator + "template" + File.separator + "outlayRecord" + File.separator;
    /*支出成本调整模名称*/
    public static final String OUTLAYRECORD_FORM = "testItemOutlayRecord";
    /*支出成本调整模中文名称*/
    public static final String OUTLAYRECORD_NAME = "支出成本调整模板";
    /*支出成本调整模文件后缀名*/
    public static final String XLS = ".xls";

 /***
     * @description: 根据传递的参数查询支出成本调整的显示数据
     * @param request
     * @param msTestitemOutlayReviseReqVo
     * @return java.util.Map<java.lang.String, java.lang.Object>
     * @date: 2023/09/07
     */
    public Map<String, Object> findTestItemOutlayReviseList(HttpServletRequest request, @RequestBody @ApiParam("入参对象") MsTestitemOutlayReviseReqVo msTestitemOutlayReviseReqVo, String testitemNo) {
        Map<String, Object> map = new HashMap<String, Object>(4);
        // 获取用户信息
        String fdId = (String) this.redisTemplate.opsForHash().get(request.getHeader(CommonConstant.TP_SESSION_KEY), BaseConstant.REDIS_LOGIN_USER_FDID_KEY);
        msTestitemOutlayReviseReqVo.setFdId(fdId);
        // 手动放入前端传递的固定参数
        msTestitemOutlayReviseReqVo.setIsShowSiteTest("0");
        // 设置“项目编号”查询条件，获取“项目明细ID”、“外包供应商代码”、“实验室项目ID”
        if (!"".equals(testitemNo) || testitemNo != "") {
            msTestitemOutlayReviseReqVo.setTestitemNo(testitemNo);
        }
        // 用户权限设置
        if ("0".equals(msTestitemOutlayReviseReqVo.getIsShowSiteTest())) {
            msTestitemOutlayReviseReqVo.setUserPerInfo(msApplicationMainService.createUserPermissionsVo2(1, msTestitemOutlayReviseReqVo.getFdId(), UserPermissionsType.JURISDICTION, UserPermissionsVo.Joiner.AND, "mb.sales_code"));
        } else {
            msTestitemOutlayReviseReqVo.setUserPerInfo(msApplicationMainService.createUserPermissionsVo2(1, msTestitemOutlayReviseReqVo.getFdId(), UserPermissionsType.JURISDICTION, UserPermissionsVo.Joiner.AND, "mstm.sales_code"));
        }
        // 调用查询方法
        map.put("testItemOutlayReviseList", msTestitemDetailsService.findTestItemNoList(msTestitemOutlayReviseReqVo));
        return map;
    }

    /***
     * @description: 成本调整Excel导入数据方法
     * @param request
     * @param file
     * @return com.platform.gis.core.model.BaseResponse<java.lang.String>
     * @date: 2023/09/07
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public BaseResponse<String> importCapexDataOfExcel(HttpServletRequest request, MultipartFile file) {
        // 查询出当前人成本调整显示的数据
        Map<String, Object> listOutlayMap = this.findTestItemOutlayReviseList(request, new MsTestitemOutlayReviseReqVo(), "");
        // 保存查询出的所有项目编号的集合
        List<String> testitemNoList = new ArrayList<String>();
        // 成本调整集合
        List<MsTestitemPriceReviseVo> priceReviseList = new ArrayList<MsTestitemPriceReviseVo>();
        // 保存查询出来的所有项目编号，与导入的项目编号进行对比
        if (!listOutlayMap.isEmpty()) {
            priceReviseList = (List<MsTestitemPriceReviseVo>) listOutlayMap.get("testItemOutlayReviseList");
            if (priceReviseList.size() != 0) {
                for (int i = 0; i < priceReviseList.size(); i++) {
                    // 只放入项目编号
                    testitemNoList.add(priceReviseList.get(i).getTestitemNo());
                }
            }
        }
        // 定义返回结果
        String result = TemplateParam.IMPORT_SUCCEEDED;
        // 定义校验返回的数据
        StringBuffer excelResult = new StringBuffer();
        // 表格格式验证是否通过
        Boolean isGoOn = true;
        // 项目编号
        String testitemNo = "";
        // 项目编号是否重复存在
        Boolean isRepeatNo = false;
        // 处理单元格数据变量
        String cellValue = "";
        // 是否执行查询其他字段的操作
        Boolean isToQuery = true;
        // 项目编号是否异常
        Boolean isRowTestitemNo = false;
        // 单元格提示信息
        String messCell = "";
        // 支出金额校验正则表达式
        String outlayPriceReg = "^[0-9]+(.[0-9]+)?$";
        try {
            // 解析Excel文件
            Workbook workbook = this.getWorkbook(file.getInputStream(), file.getResource().getFilename());
            if (null == workbook) {
                result = "创建Excel为空，导入失败。";
                return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, result);
            }
            // 工作表
            Sheet sheet = null;
            // 行数
            Row row = null;
            // 列数
            Cell cell = null;
            // 所有行集合
            List<Map<String, String>> allRowList = new ArrayList<Map<String, String>>();
            // 空行汇总数
            int isEmptyRow = 0;
            // 获取excel中所有的sheet
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                // 取某个sheet
                sheet = workbook.getSheetAt(i);
                // 当前为空则跳过
                if (sheet == null) {
                    continue;
                }
                // 获取表格中每一行的数据，在传入的excel中第一行是标题，所以数据从第二行开始获取
                // 遍历当前sheet中的所有行
                for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
                    // 行数
                    int rowNum = j + 1;
                    // 重置标记
                    isGoOn = true;
                    isRepeatNo = false;
                    isToQuery = true;
                    isRowTestitemNo = false;
                    // 获取一行
                    row = sheet.getRow(j);
                    // 去空行
                    if (row == null) {
                        // 空行数累加
                        isEmptyRow++;
                        continue;
                    }
                    // 获取到当前行的每一个单元格，如果当前行一整行都为空，则空行行数增加1，如果一整行为空超过5行，则不往下读取数据
                    if (("".equals(getValue2Format(row.getCell(0))) || getValue2Format(row.getCell(0)) == "") &&
                            ("".equals(getValue2Format(row.getCell(1))) || getValue2Format(row.getCell(1)) == "") &&
                            ("".equals(getValue2Format(row.getCell(2))) || getValue2Format(row.getCell(2)) == "") &&
                            ("".equals(getValue2Format(row.getCell(3))) || getValue2Format(row.getCell(3)) == "") &&
                            ("".equals(getValue2Format(row.getCell(4))) || getValue2Format(row.getCell(4)) == "") &&
                            ("".equals(getValue2Format(row.getCell(5))) || getValue2Format(row.getCell(5)) == "") &&
                            ("".equals(getValue2Format(row.getCell(6))) || getValue2Format(row.getCell(6)) == "") &&
                            ("".equals(getValue2Format(row.getCell(7))) || getValue2Format(row.getCell(7)) == "") &&
                            ("".equals(getValue2Format(row.getCell(8))) || getValue2Format(row.getCell(8)) == "") &&
                            ("".equals(getValue2Format(row.getCell(9))) || getValue2Format(row.getCell(9)) == "") &&
                            ("".equals(getValue2Format(row.getCell(10))) || getValue2Format(row.getCell(10)) == "")
                    ) {
                        // 空行数累加
                        isEmptyRow++;
                        continue;
                    }
                    if (isEmptyRow > 0 && isEmptyRow <= 3) {
                        // 如果空行数量存在，且在三行以下，则视为表格异常
                        return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, "表格中存在空行，导入失败。");
                    } else if (isEmptyRow >= 4 && isEmptyRow <= 7) {
                        // 如果在三行或三行以上，且在七行以内，则视为结束
                        break;
                    } else if (isEmptyRow > 7) {
                        // 如果空行数量在七行以上的，也视为结束
                        break;
                    }
                    // 每一行的数据保存集合
                    Map<String, String> cellMap = new HashMap<>();
                    // 遍历当前行所有的列
                    for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
                        if (k >= 11) {
                            // 防止读取列数错误，如果读取到超过模板中的列数，则跳出
                            break;
                        }
                        // 修改单元格的提示信息
                        if (k == 0) {
                            // 项目编号
                            messCell = TESTITEM_NO;
                        } else if (k == 1) {
                            // 报价单号
                            messCell = QUOTATION_NO;
                        } else if (k == 6) {
                            // 支出金额
                            messCell = OUTLAY_PRICE;
                        } else if (k == 7) {
                            // 支出类型
                            messCell = OUTLAY_TYPE;
                        } else if (k == 8) {
                            // 支出原因
                            messCell = REMARK;
                        } else if (k == 9) {
                            // 支出时间
                            messCell = CREATETIME;
                        }
                        cell = row.getCell(k);
                        // 单元格内容
                        cellValue = getValue2Format(cell);
                        // 提示单元格为空
                        if (k == 0 || k == 1 || k == 6 || k == 7 || k == 8 || k == 9) {
                            if ("".equals(cellValue) || cellValue == "") {
                                excelResult.append("第[" + rowNum + "]行，[" + messCell + "]为空，请确认导入的文件。<br/>");
                                isGoOn = false;
                            }
                        }
                        if (isGoOn) {// 表格数据不为空，则验证每一项数据
                            // 第一个单元格放入的是项目编号
                            if (k == 0) {
                                // 判断在当前表格中是否存在重复项目编号
                                if (allRowList.size() != 0) {
                                    // 如果当前取到的是项目编号，并且已经添加了行数据
                                    for (int z = 0; z < allRowList.size(); z++) {
                                        // 取出已保存数据中的项目编号与当前的项目编号进行比较
                                        testitemNo = allRowList.get(z).get("testitemNo");
                                        if (StringUtils.isNotEmpty(testitemNo)) {
                                            if (cellValue.equals(testitemNo) || cellValue == testitemNo) {
                                                excelResult.append("[项目编号：" + cellValue + "]在表格中重复存在，请确认导入的文件。<br/>");
                                                isRepeatNo = true;
                                                isToQuery = false;
                                                isRowTestitemNo = true;
                                                break;
                                            }
                                        } else {
                                            excelResult.append("已导入数据项目编号存在异常。<br/>");
                                        }
                                    }
                                }
                                // 在当前表格中不存在重复项目编号，则查询当前人导入人的权限是否能显示这些项目编号
                                if (!isRepeatNo) {
                                    if (!testitemNoList.contains(cellValue)) {
                                        // 当前表格中的项目编号，不存在查询出来的集合中，则没有权限
                                        excelResult.append("[项目编号：" + cellValue + "]查询不到，请确认导入的文件。<br/>");
                                        isToQuery = false;
                                        isRowTestitemNo = true;
                                    } else {
                                        // 放入项目编号
                                        cellMap.put("testitemNo", cellValue);
                                        // 赋值
                                        testitemNo = cellValue;
                                        isRowTestitemNo = false;
                                    }
                                }
                                if (isRowTestitemNo) {
                                    // 当前行项目编号异常，则获取下一行
                                    break;
                                }
                            } else {
                                // 放入报价单号
                                if (k == 1) {
                                    cellMap.put("quotationNO", cellValue);
                                } else if (k == 6) {// 校验支出金额
                                    if (!cellValue.matches(outlayPriceReg)) {
                                        excelResult.append("第[" + rowNum + "]行，[" + messCell + "]异常，请确认导入的文件。<br/>");
                                        isToQuery = false;
                                    } else {
                                        cellMap.put("outlayPrice", cellValue);
                                    }
                                } else if (k == 7) {// 支出类型列举 "差旅费_1","运费_2","外包调整_3","样品费_4","工装_5","汇兑损失_6","收入调整_7","其他_9","专用耗材_11"
                                    if ("差旅费".equals(cellValue) || cellValue == "差旅费") {
                                        cellMap.put("outlayType", "1");
                                    } else if ("运费".equals(cellValue) || cellValue == "运费") {
                                        cellMap.put("outlayType", "2");
                                    } else if ("外包调整".equals(cellValue) || cellValue == "外包调整") {
                                        cellMap.put("outlayType", "3");
                                    } else if ("样品费".equals(cellValue) || cellValue == "样品费") {
                                        cellMap.put("outlayType", "4");
                                    } else if ("工装".equals(cellValue) || cellValue == "工装") {
                                        cellMap.put("outlayType", "5");
                                    } else if ("汇兑损失".equals(cellValue) || cellValue == "汇兑损失") {
                                        cellMap.put("outlayType", "6");
                                    } else if ("收入调整".equals(cellValue) || cellValue == "收入调整") {
                                        cellMap.put("outlayType", "7");
                                    } else if ("其他".equals(cellValue) || cellValue == "其他") {
                                        cellMap.put("outlayType", "9");
                                    } else if ("专用耗材".equals(cellValue) || cellValue == "专用耗材") {
                                        cellMap.put("outlayType", "11");
                                    } else {
                                        excelResult.append("第[" + rowNum + "]行，[" + messCell + "]异常，请确认导入的文件。<br/>");
                                        isToQuery = false;
                                    }
                                } else if (k == 8) {// 支出原因 添加 自动导入标识
                                    cellValue = "[自动导入]" + cellValue;
                                    cellMap.put("remark", cellValue);
                                } else if (k == 9) {// 支出时间是否格式匹配
                                    DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                                    try {
                                        LocalDateTime.parse(cellValue, dtf);
                                        cellMap.put("CreateTime", cellValue);
                                    } catch (Exception e) {
                                        excelResult.append("第[" + rowNum + "]行，[" + messCell + "]异常，请确认导入的文件。<br/>");
                                        isToQuery = false;
                                    }
                                } else if (k == 10 && isToQuery) {// 根据项目编号，查询得到项目明细ID、外包供应商代码、实验室项目ID信息
                                    // 读取到最后一行，才进行查询
                                    Map<String, Object> testItemMap = this.findTestItemOutlayReviseList(request, new MsTestitemOutlayReviseReqVo(), testitemNo);
                                    if (!testItemMap.isEmpty()) {
                                        List<MsTestitemPriceReviseVo> priceReviseList2 = (List<MsTestitemPriceReviseVo>) testItemMap.get("testItemOutlayReviseList");
                                        if (priceReviseList2.size() != 0) {
                                            // 项目明细ID
                                            cellMap.put("detailId", priceReviseList2.get(0).getDetailId());
                                            //外包供应商代码
                                            cellMap.put("agentSupplierCode", priceReviseList2.get(0).getAgentSupplierCode());
                                            //实验室项目ID
                                            cellMap.put("testitemId", priceReviseList2.get(0).getTestitemId());
                                        }
                                    } else {
                                        excelResult.append("[项目编号：" + testitemNo + "]查询不到相对应的数据，请确认导入的文件。<br/>");
                                    }
                                }
                            }
                        }
                    }
                    // 放入所有行集合
                    if (!cellMap.isEmpty()) {
                        allRowList.add(cellMap);
                    }
                }
            }
            // Excel文件检验有空数据，返回校验结果
            if (excelResult.toString().length() > 0) {
                // 防止返回信息太多页面显示太多，只显示部分
                String excelResultStr = "";
                String resultStr[] = excelResult.toString().split("<br/>");
                for (int i = 0; i < resultStr.length; i++) {
                    if (i < 20) {
                        excelResultStr += resultStr[i] + "<br/>";
                    }
                }
                return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, excelResultStr);
            }
            // 没有报错信息则进行插入操作
            if (allRowList.size() != 0) {
                for (int i = 0; i < allRowList.size(); i++) {
                    try {
                        MsTestitemOutlayRecordVo msTestitemOutlayRecordVo = new MsTestitemOutlayRecordVo();
                        // 项目编号
                        msTestitemOutlayRecordVo.setTestitemNo(allRowList.get(i).get("testitemNo"));
                        // 报价单号
                        msTestitemOutlayRecordVo.setQuotationNo(allRowList.get(i).get("quotationNO"));
                        // 支出金额
                        msTestitemOutlayRecordVo.setOutlayPrice(new BigDecimal(allRowList.get(i).get("outlayPrice")));
                        // 支出类型
                        msTestitemOutlayRecordVo.setOutlayType(allRowList.get(i).get("outlayType"));
                        // 支出原因
                        msTestitemOutlayRecordVo.setRemark(allRowList.get(i).get("remark"));
                        // 支出时间
                        msTestitemOutlayRecordVo.setCreateTime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(allRowList.get(i).get("CreateTime")));
                        // 项目明细ID
                        msTestitemOutlayRecordVo.setDetailId(allRowList.get(i).get("detailId"));
                        // 外包供应商代码
                        msTestitemOutlayRecordVo.setAgentSupplierCode(allRowList.get(i).get("agentSupplierCode"));
                        // 实验室项目ID
                        msTestitemOutlayRecordVo.setTestitemId(allRowList.get(i).get("testitemId"));
                        // 调用add方法
                        this.add(request, msTestitemOutlayRecordVo);
                    } catch (Exception e) {
                        LOG.error(e.getMessage(), e);
                        return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, "支出成本调整保存失败");
                    }

                }
            } else {
                result = "获取导入的数据为空，导入失败。";
                return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, result);
            }
        } catch (IOException e) {
            e.printStackTrace();
            return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, "导入报错，请联系管理员。");
        } catch (Exception e) {
            e.printStackTrace();
            return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, "导入报错，请联系管理员。");
        }
        return new BaseResponse(ResCodeConstant.RESCODE_SUCCESS, result);
    }

    /***
     * @description: 根据文件后缀，自适应上传文件的版本
     * @param inputStream
     * @param fileName
     * @return org.apache.poi.ss.usermodel.Workbook
     * @date: 2023/09/06
     */
    public Workbook getWorkbook(InputStream inputStream, String fileName) throws Exception {
        Workbook wb = null;
        // 判断是否是excel2007格式
        boolean isE2007 = false;
        String xlsx = TemplateParam.NO_POINT_XLSX;
        if (fileName.endsWith(xlsx)) {
            isE2007 = true;
        }
        try {
            // 根据文件格式(2003或者2007)来初始化
            if (isE2007) {
                // xlsx
                wb = new XSSFWorkbook(inputStream);// 2007
            } else {
                // xls
                wb = new HSSFWorkbook(inputStream);// 2003
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("importCapexDataOfExcel_解析的文件格式有误");
        }
        return wb;
    }

    /**
     * @param cell
     * @return java.lang.String
     * @description: 对表格中数值进行格式化
     * @date: 2023/09/07
     */
    public String getValue2Format(Cell cell) {
        String value = "";
        if (null == cell) {
            return value;
        }
        switch (cell.getCellType()) {
            //数值型
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 如果是date类型则 ，获取该cell的date值
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    SimpleDateFormat format = new SimpleDateFormat();
                    value = format.format(date);
                } else {
                    // 纯数字
                    BigDecimal big = new BigDecimal(cell.getNumericCellValue());
                    value = big.toString();
                }
                break;
            //字符串类型
            case STRING:
                value = cell.getStringCellValue().trim();
                break;
            // 公式类型
            case FORMULA:
                //读公式计算值
                value = String.valueOf(cell.getNumericCellValue());
                if (value.equals("NaN")) {// 如果获取的数据值为非法值,则转换为获取字符串
                    value = cell.getStringCellValue();
                }
                break;
            // 布尔类型
            case BOOLEAN:
                value = " " + cell.getBooleanCellValue();
                break;
            default:
                value = cell.getStringCellValue().trim();
        }
        if ("null".endsWith(value.trim())) {
            value = "";
        }
        return value;
    }

    /***
     * @description: 支出成本调整Excel模板导出
     * @param request
     * @param response
     * @param templateId
     * @return javax.servlet.http.HttpServletResponse
     * @date: 2023/09/12
     */
    @Override
    @Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
    public HttpServletResponse exportCapexDataTemplate(HttpServletRequest request, HttpServletResponse response, String templateId) {
        InputStream inputStream = null;
        try {
            // 去除水印
            new MsBusinessMainApi().getLicense();
            // 获取模板地址
            String templatePath = OUTLAYRECORD_TEMPLATE_DOWN_PATH + OUTLAYRECORD_FORM + XLS;
            // 模板中文名称
            String templateName = OUTLAYRECORD_NAME + XLS;
            // 获取excel对象，读取模板数据
            ClassPathResource classPathResource = new ClassPathResource(templatePath);
            inputStream = classPathResource.getInputStream();
            com.aspose.cells.Workbook workbook = new com.aspose.cells.Workbook(inputStream);
            inputStream.close();
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称(设置下载文件的默认名称)
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(templateName.getBytes("GBK"), "iso8859-1"));
            // 写到文件中
            workbook.save(response.getOutputStream(), workbook.getFileFormat());
        } catch (IOException e) {
            LOG.error("获取不到文件流", e);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (inputStream != null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return null;
    }