indexupload.vue

<template>
    <div class="app-container">
        <el-form  ref="queryForm" size="small" :inline="true" >
            <el-form-item label="行" prop="row">
                <el-input
                    oninput="value=value.replace(/[^\d]/g,'')"
                    v-model="row"
                    placeholder="请输入开始行"
                    clearable
                    @keyup.enter.native="handleEnter"
                    maxlength="8"
                />
            </el-form-item>
            <!--            <el-form-item>
                            <el-checkbox v-model="zero">是否比较0或空</el-checkbox>
                        </el-form-item>-->
            <el-form-item>
                <el-checkbox v-model="numm">是否只比较数值</el-checkbox>
            </el-form-item>
        </el-form>
        <el-row :gutter="10" class="mb8">
            <el-col :span="1.5">
                <el-upload
                    type="file"
                    name="file"
                    ref="upload"
                    :before-upload="handleFileChange"
                    action=''
                    accept='.xlsx'
                    :limit="1"
                    :file-list="fileList"
                    :show-file-list=false
                >
                    <el-button  plain size="mini" icon="el-icon-download" type="primary"  >导入初始文件</el-button>
                </el-upload>
            </el-col>
            <el-col :span="1.5">
                <el-upload
                    type="file"
                    name="file"
                    ref="upload"
                    :before-upload="handleFileChange1"
                    action=''
                    accept='.xlsx'
                    :limit="1"
                    :file-list="fileList"
                    :disabled="disableNextButton"
                    :show-file-list=false
                >
                    <el-button  plain size="mini" icon="el-icon-download" type="primary"  :disabled="disableNextButton"  >导入对比文件</el-button>
                </el-upload>
            </el-col>
            <el-col :span="1.5">
                <el-button
                    type="warning"
                    plain
                    icon="el-icon-upload2"
                    size="mini"
                    @click="handleExport"
                >导出</el-button>
            </el-col>
            <el-col :span="1.5">
                <el-button icon="el-icon-s-opportunity" size="mini" @click="addToMap">生成</el-button>
            </el-col>
            <el-button icon="el-icon-refresh" size="mini" @click="resetQuery" class="right-float">重置</el-button>
        </el-row>
        <!-- luckysheet容器 -->
        <div
            id="luckysheet"
            style="margin: 0px; padding: 0px; position: absolute; width: 100%;left: 0px; top: 105px; height: 75vh; z-index: 0"
        ></div>

    </div>
</template>

<style>
.right-float {
    float: right;
}
</style>
<script>

/*import luckysheet from 'luckysheet'*/
import LuckyExcel from 'luckyexcel'
//导入库export.js 这个文件是es6的，不能在普通的HTML文件直接引入js文件（虽然都是js文件，但是有区别，具体请百度es6与es5）！需要把es6转es5才可以直接引入使用！
import { exportExcel } from '../../../../public/exportExcel';
import {vueScript} from "@/utils/generator/html";


var fileName="新建XLSX工作表"; //定义表名
var data1;
var name1;
var map=new Map();
var maper=new Map();
export default {
    name: "Mymodule",
    data() {
        return {
            row:1,
            numm:false,
            //弹出页面的表名
            // 是否显示弹出层
            fileList:[],
            disableNextButton: true,
            // 查询参数
            queryParams: {
                status:0
            },
        };
    },
    created() {

    },
    mounted() {

        this.init();
        fileName="新建XLSX工作表";
    },
    methods:{

        /** 回车事件和保存提交绑定 */
        handleEnter(event) {
            if (event.keyCode === 13) {
                event.preventDefault(); // 阻止默认的回车事件
                // 触发确定操作
                this.handleFileChange1();
            }
        },
        /** 重置按钮操作 */
        resetQuery() {
            //刷新下拉选框内容
            fileName="新建XLSX工作表";
            this.disableNextButton='';
            this.row=1;
            //刷新luckysheet表格
            this.init();
            map.clear();
            maper.clear();
            //this.zero=false;
            this.numm=false;
        },
        /** Luckyexcel文档 */
        init() {
            let options = {
                container: 'luckysheet', //luckysheet为容器id
                title:'',
                lang:'zh',
                showinfobar:false,
                data:[
                    {
                        "name": "sheet1", //工作表名称
                        "color": "", //工作表颜色
                        "index": 0, //工作表索引
                        "status": 1, //激活状态
                        "order": 0, //工作表的下标
                        "hide": 0,//是否隐藏
                        "row": 50, //行数
                        "column": 50, //列数
                        "defaultRowHeight": 19, //自定义行高
                        "defaultColWidth": 73, //自定义列宽
                        "celldata": [


                        ], //初始化使用的单元格数据
                        "config": {
                            "merge": {
                            }, //合并单元格
                            "rowlen":{}, //表格行高
                            "columnlen":{}, //表格列宽
                            "rowhidden":{}, //隐藏行
                            "colhidden":{}, //隐藏列
                            "borderInfo":{

                            }, //边框
                            "authority":{}, //工作表保护
                        },
                    },
                    /*{
                      "name": "Sheet2",
                      "color": "",
                      "index": 1,
                      "status": 0,
                      "order": 1,
                      "celldata": [],
                      "config": {}
                    },
                    {
                      "name": "Sheet3",
                      "color": "",
                      "index": 2,
                      "status": 0,
                      "order": 2,
                      "celldata": [],
                      "config": {},
                    }*/
                ]

            }
            luckysheet.create(options);

        },
        /** 导入事件*/
        handleFileChange(evt) {
            this.disableNextButton=false;
            let name = evt.name
            let suffixArr = name.split('.'),
                suffix = suffixArr[suffixArr.length - 1]
            if (suffix !== 'xlsx') {
                this.$message({
                    message: "上传格式不正确，请上传xlsx格式文件",
                    type: "warning"});
                return
            }
            LuckyExcel.transformExcelToLucky(
                evt,
                (exportJson)=> {
                    if (exportJson.sheets === null || exportJson.sheets.length === 0) {
                        this.$message({
                            message: "导入失败，请检查上传的文件是否正确",
                            type: "warning"
                        });
                        return
                    }
                    luckysheet.destroy()
                    luckysheet.create({
                        container: 'luckysheet', //luckysheet is the container id
                        title: exportJson.info.name,
                        lang: 'zh', // 设定表格语言
                        showinfobar: false,
                        data: exportJson.sheets,
                        userInfo: exportJson.info.name.creator
                    })
                    data1=exportJson.sheets[0];
                    name1=exportJson.sheets[0].name;
                    let suffixArr = exportJson.info.name.split('.');
                    fileName = suffixArr[0];
                }
            )

        },
        /** 生成标签*/
        addToMap(){
            map.forEach((value,key)=>{
                for(let i=0;i<value.length;i++){
                    let row=value[i][0]-1,cell=this.stringTonum(value[i][1])-1;
                    //let row=value[i][0]-1,cell=value[i][1]-1;
                    let sd=luckysheet.getCellValue(row,cell,{type:"bg"});
                    //用批注进行比较的时候把下面的!==判断去掉
                    //let sd=luckysheet.getCellValue(row,cell,{type:"ps"});
                    if(sd===null || sd!=="#e85f61"){
                        luckysheet.setCellValue(row, cell, {bg: "#e85f61"})
                        /*  luckysheet.setCellValue(row, cell, {
                               ps: { //批注
                                   "left": 92, //批注框左边距
                                   "top": 10, //批注框上边距
                                   "width": 91, //批注框宽度
                                   "height": 48, //批注框高度
                                   "value": key+"表有误", //批准内容
                                   "isshow": false //批注框为显示状态
                               }
                           })*/
                        maper.set("("+(row+1)+","+value[i][1]+")",1);
                    }else{
                        //let sd=luckysheet.getCellValue(row,cell,{type:"ps"}).value;
                        /*luckysheet.setCellValue(row, cell, {
                            ps: { //批注
                                "left": 92, //批注框左边距
                                "top": 10, //批注框上边距
                                "width": 91, //批注框宽度
                                "height": 48, //批注框高度
                                "value": sd+'\n；'+key+"表有误", //批准内容
                                "isshow": false //批注框为显示状态
                            }
                        })*/
                        maper.set("("+(row+1)+","+value[i][1]+")",maper.get("("+(row+1)+","+value[i][1]+")")+1);
                    }
                }

            })
            let row;
            let num=luckysheet.getSheet().data.length;
            let data=luckysheet.getSheet().data;
            for(let i=0;i<num;i++){
                if(data[i][0]===null){
                    row=i;
                    break;
                }
            }
            //生成多少条数据添加多少行
            let asd=maper.size+map.size;
            if(row+asd>=num){
                for (let i=0;i<=row+asd-num;i++){
                    luckysheet.insertRow(row + 1);
                }
                luckysheet.insertRow(row + 1);
            }
            row=row+1;
            let k=0;
            for (let [key,value] of map.entries()) {

                let a="("+value.join("),(")+")";
                luckysheet.setCellValue(row+k,0,key);
                luckysheet.setCellValue(row+k,1 ,a);
                k++
            }
            let froms = Array.from(maper);
            froms.sort((a, b) => {
                return b[1] - a[1];
            });
            for (let i=0;i<froms.length;i++){
                luckysheet.setCellValue(row+k,0,froms[i][0]);
                luckysheet.setCellValue(row+k,1 ,froms[i][1]);
                k++;
            }
        },
        handleFileChange1(evt) {
            if (this.row==="") {
                this.$message({
                    message: "请先输入导入开始的行",
                    type: "warning"});
                return false
            }
            let name = evt.name
            let suffixArr = name.split('.'),
                suffix = suffixArr[suffixArr.length - 1];
            name=suffixArr[0]
            if (suffix !== 'xlsx') {
                this.$message({
                    message: "上传格式不正确，请上传xlsx格式文件",
                    type: "warning"});
                return
            }
            LuckyExcel.transformExcelToLucky(
                evt,
                (exportJson) =>{
                    if (exportJson.sheets === null || exportJson.sheets.length === 0) {
                        this.$message({
                            message: "导入失败，请检查上传的文件是否正确",
                            type: "warning"
                        });
                        return
                    }
                    let ddd=data1;
                    let as=0;
                    let d1=window.luckysheet.transToData(ddd.celldata);
                    let d2=window.luckysheet.transToData(exportJson.sheets[0].celldata);
                    let r1=d1.length,r2=d2.length,c1=d1[0].length,c2=d2[0].length;
                    let row=(r1<=r2)?r1:r2;
                    let cell=(c1<=c2)?c1:c2;
                    let array=[];
                    let r=this.row-1;
                    try{
                        for(let i=r;i<row;i++) {
                            for (let j = 0; j <cell; j++) {
                                //第一个表的单元格为null直接跳出
                                if(d1[i][j]==null ){
                                    continue
                                }
                                //第二个表的单元格为null直接跳出
                                if(d2[i][j]==null ){
                                    continue
                                }
                                //第一个表内数据为0 且 第二个表只有样式内部为空
                                if(d2[i][j].v==0 && d1[i][j].v==undefined ){
                                    continue
                                }
                                //第一个表内数据为0 且 第二个表只有样式内部为空
                                if( d1[i][j].v==0 && d2[i][j].v==undefined ){
                                    continue
                                }
                                // 第一个表内数据为string型 且 第二个表只有样式内部为空
                                /*if((typeof(d1[i][j].v)=="string" && d2[i][j].v==undefined)){
                                    continue
                                }*/
                                //第一个表为合并单元格 且第二个内没有值
                                if(d1[i][j].mc!=undefined &&d2[i][j].v==undefined){
                                    continue
                                }
                                //第二个表为合并单元格 且第一个内没有值
                                if(d2[i][j].mc!=undefined &&d1[i][j].v==undefined){
                                    continue
                                }
                                if((isNaN(d1[i][j].v) && d2[i][j].v==undefined)){

                                }
                                // 第二个表内数据为string型 且 第一个表只有样式内部为空
                                if(isNaN(d2[i][j].v)&& d1[i][j].v==undefined){

                                }
                                // 没有选只比较数值 且 第一个表内数据为string型 且 第二个表内数据位string型
                                if(this.numm!=false &&(isNaN(d1[i][j].v)&&isNaN(d2[i][j].v))){
                                    continue;
                                }
                                // 两个表的值都是0
                                if(d1[i][j].v==0 && d2[i][j].v==0 ){
                                    continue
                                }
                                // 两个表都是只有样式没有数据
                                if(d1[i][j].v==undefined && d2[i][j].v==undefined ){

                                }else if (d1[i][j].v==undefined || d2[i][j].v==undefined){
                                    luckysheet.setCellValue(i, j, {bg: "#e85f5f"})
                                    as++
                                    let s = this.numToString(j+1);
                                    array.push([i+1,s]);
                                    //array.push([i+1,j+1]);
                                }else {
                                    if (d1[i][j].v!= d2[i][j].v) {
                                        luckysheet.setCellValue(i, j, {bg: "#e85f5f"})
                                        //查看是否有标签
                                        /*luckysheet.setCellValue(i, j, {
                                                ps: { //批注
                                                    "left": 92, //批注框左边距
                                                    "top": 10, //批注框上边距暖色

                                                    "width": 91, //批注框宽度
                                                    "height": 48, //批注框高度
                                                    "value": this.dep+"表有误", //批准内容
                                                    "isshow": false //批注框为显示状态
                                                }
                                            })*/
                                        as++
                                        let s = this.numToString(j+1);
                                        array.push([i+1,s]);
                                        //array.push([i+1,j+1]);
                                    }
                                }
                            }
                        }
                        map.set(name1,array);
                        if(as>0){
                            this.$message({
                                message: "导入完成,本次导入共发现"+as+"条不同数据",
                                type: "warning"});
                        }else{
                            this.$message({
                                message: "导入完成,本次导入未发现不同数据",
                                type: "success"});
                        }
                    }catch (err){
                        this.$message({
                            message: "导入失败,导入文件与对比文件有所不匹",
                            type: "error"});
                    }

                })
        },
        /** 导出设置 */
        handleExport: debounce(function() {
            // 处理点击事件
            exportExcel(luckysheet.getSheet(),fileName+"(对比后数据)")
        }, 500), // 设置延迟时间，单位为毫秒
        numToString(numm){
            let char=[];
            char.length = 0;
            let numToStringAction = function(nnum){
                let num = nnum - 1;
                let a = parseInt(num / 26);
                let b = num % 26;
                char.push(String.fromCharCode(64 + parseInt(b+1)));
                if(a>0){
                    numToStringAction(a);
                }
            }
            numToStringAction(numm);
            return char.reverse().join("");
        },
        stringTonum(a) {
            var str = a.toLowerCase().split("");
            var num = 0;
            var al = str.length;
            var getCharNumber = function (charx) {
                return charx.charCodeAt() - 96;
            };
            var numout = 0;
            var charnum = 0;
            for (var i = 0; i < al; i++) {
                charnum = getCharNumber(str[i]);
                numout += charnum * Math.pow(26, al - i - 1);
            }
            ;
            return numout;
        },
    }
// 配置项111
};
// debounce函数定义
function debounce(func, delay) {
    let timer;
    return function () {
        const context = this;
        const args = arguments;
        clearTimeout(timer);
        timer = setTimeout(function () {
            func.apply(context, args);
        }, delay);
    };
}
</script>