js 上传Excel并获取数据及图片

@extends('layout')

@section('content')
    <div class="layuimini-container">
        {{-- <div class="layui-upload-drag" id="test10">
            <i class="layui-icon"></i>
            <p>点击上传，或将文件拖拽到此处</p>
            <div class="layui-hide" id="uploadDemoView">
                <hr>
                <img src="" alt="上传成功后渲染" style="max-width: 196px">
            </div>
        </div> --}}
        <div class="layuimini-main">
            <script type="text/html" id="toolbarDemo">
                <div class="layui-btn-container">
                    <button type="button" class="layui-btn" id="excel_upload"> <i class="layui-icon">&#xe67c;</i>上传文件 </button>
                    <button class="layui-btn layui-btn-normal layui-btn-sm data-add-btn" lay-event="add"> 确认导入 </button>
                </div>
            </script>

            @verbatim
                <table class="layui-hide" id="currentTableId" lay-filter="currentTableFilter"></table>

                <script type="text/html" id="currentTableBar">
                    <a class="layui-btn layui-btn-xs layui-btn-danger data-count-delete" lay-event="delete">删除</a>
                </script>
                <template id="text">
                    <input type="text" value="{{ d . name }}" style="width:100%">
                </template>
            @endverbatim
        </div>
    </div>
@endsection

@section('footer')
    <script src="{{ URL::asset('layuimini/lib/xlsx/xlsx.full.min.js') }}" charset="utf-8"></script>
    <script src="{{ URL::asset('layuimini/lib/xlsx/jszip.min.js') }}" charset="utf-8"></script>
    <script src="{{ URL::asset('layuimini/lib/xlsx/xml2json.min.js') }}" charset="utf-8"></script>
    <script>
        layui.use(['form', 'table', 'upload', 'upload'], function() {
            var $ = layui.jquery,
                form = layui.form,
                upload = layui.upload,
                table = layui.table;

            // 监听搜索操作
            form.on('submit(data-search-btn)', function(data) {


                //执行搜索重载
                table.reload('currentTableId', {
                    page: {
                        curr: 1
                    },
                    where: {
                        searchParams: data.field
                    }
                }, 'data');

                return false;
            });

            //表单渲染
            function currentTableLoad(xlsx_list = []) {
                table.render({
                    elem: '#currentTableId',
                    toolbar: '#toolbarDemo',
                    data: xlsx_list,
                    defaultToolbar: ['filter', 'exports', 'print', {
                        title: '提示',
                        layEvent: 'LAYTABLE_TIPS',
                        icon: 'layui-icon-tips'
                    }],
                    cols: [
                        [{
                                type: "checkbox",
                                LAY_CHECKED: true,
                                width: 50
                            },
                            {
                                field: 'name',
                                event: 'setData_name',
                                width: 80,
                                title: '名称',
                            },
                            {
                                field: 'company',
                                event: 'setData_company',
                                width: 130,
                                title: '公司名称',
                                sort: true
                            },
                            {
                                field: 'mobile',
                                event: 'setData_mobile',
                                width: 130,
                                title: '手机号',
                                sort: true
                            },
                            {
                                field: 'type',
                                event: 'setData_type',
                                width: 80,
                                title: '类型',
                                sort: true
                            },
                            {
                                field: 'address',
                                event: 'setData_address',
                                width: 80,
                                title: '地址',
                            },
                            {
                                field: 'resource',
                                event: 'setData_resource',
                                width: 150,
                                title: '简介',
                            },
                            {
                                field: 'business',
                                event: 'setData_business',
                                width: 150,
                                title: '主打优势',
                            },
                            {
                                field: 'logo',
                                event: 'setData_logo',
                                width: 150,
                                title: '图片',
                                templet: function(d) {
                                    return `<img src="https://img-blog.csdnimg.cn/2022010613063850398.png"token interpolation">${d.logo}" alt="头像">`
                                }
                            },
                            {
                                title: '操作',
                                minWidth: 130,
                                toolbar: '#currentTableBar',
                                align: "center",
                                fixed: 'right'
                            }
                        ]
                    ],
                    limits: [10, 15, 20, 25, 50, 100],
                    limit: 15,
                    page: true,
                    text: {
                        none: '请上传Excel'
                    },
                    skin: 'line'
                });
                /**
                 * toolbar监听事件
                 */
                table.on('toolbar(currentTableFilter)', function(obj) {
                    var checkStatus = table.checkStatus('currentTableId');
                    var data = checkStatus.data;

                    switch (obj.event) {
                        case 'add': // 导入数据
                            if (data.length > 0) {
                                request({
                                    type: "POST",
                                    url: '/admin/UserInfo/excel',
                                    data: {
                                        data
                                    },
                                    success(res) {
                                        if (res.code == 0) {
                                            layer.msg('提交成功', {
                                                time: 1000
                                            }, quitOpen);
                                        }
                                    }
                                });
                            } else {
                                layer.msg('请选择要导入的数据');
                            }
                            break;

                        case 'delete': // 监听删除操作
                            var ids = [];
                            if (data.length > 0) {
                                layer.confirm('真的删除行么', function(index) {
                                    $.each(data, function(k, v) {
                                        ids.push(v.id);
                                    })
                                    request({
                                        type: "DELETE",
                                        url: '/admin/UserInfo/' + ids,
                                        success(res) {
                                            if (res.code == 0) {
                                                layer.close(index);
                                                table.reload('currentTableId');
                                            }
                                        }
                                    });
                                });


                            } else {
                                layer.alert('请选择数据');
                            }
                            break;

                    }
                });
                /**
                 * 侧边菜单
                 */
                table.on('tool(currentTableFilter)', function(obj) {
                    var data = obj.data;
                    switch (obj.event) {
                        case 'edit':
                            var index = layer.open({
                                title: '编辑用户',
                                type: 2,
                                shade: 0.2,
                                maxmin: true,
                                shadeClose: true,
                                area: ['80%', '90%'],
                                content: `/admin/excel/edit/`,
                            });
                            break;
                        case 'delete':
                            layer.confirm('真的删除行么', function(index) {
                                request({
                                    type: "DELETE",
                                    url: '/admin/UserInfo/' + data.id,
                                    success(res) {
                                        if (res.code == 0) {
                                            obj.del();
                                            layer.close(index);
                                        }
                                    }
                                });
                            });
                            break;
                        default:
                            if (obj.event.search(/^setData_.+$/) > -1) {
                                let name = obj.event.replace('setData_', '');
                                layer.prompt({
                                    formType: 2,
                                    title: '修改 用户 [' + data.name + '] 的数据',
                                    value: data[name]
                                }, function(value, index) {
                                    layer.close(index);
                                    //这里一般是发送修改的Ajax请求

                                    //同步更新表格和缓存对应的值
                                    obj.update({
                                        [name]: value
                                    });
                                });
                            }

                            break;

                    }

                    return false;
                });

                layer.quitOpen = function() {
                    table.reload('currentTableId');
                }
            }
            currentTableLoad();

            function LoadxmlToJson(xml) {
                var str = $(xml).find("etc:cellImages").html();
                json_obj = $xml.xml2json("<etc:cellImages>" + str + "</etc:cellImages>");
                return json_obj;
            }
            //上传文件
            upload.render({
                elem: '#excel_upload',
                auto: false,
                accept: 'file',
                exts: 'xls|xlt|xlsx',
                choose: function(obj) {
                    obj.preview(function(index, file, result) {

                        var reader = new FileReader();
                        reader.onload = function(e) {
                            var data = new Uint8Array(e.target.result);
                            var workbook = XLSX.read(data, {
                                type: 'array'
                            });


                            var xlsx_arr = XLSX.utils.sheet_to_json(workbook.Sheets.Sheet1);
                            var xlsx_list = [];
                            $.each(xlsx_arr, function(k, v) {
                                if (k > 0) {
                                    xlsx_list.push({
                                        type: '品牌商',
                                        name: v.__EMPTY,
                                        address: v.__EMPTY_1,
                                        resource: v.__EMPTY_2,
                                        mobile: v.__EMPTY_3,
                                        wx: v.__EMPTY_4,
                                        dy_url: v.__EMPTY_5,
                                        link: v.__EMPTY_6,
                                        business: v.__EMPTY_7,
                                        logo: v.__EMPTY_8,
                                    })
                                }
                            })

                            //需要图片的列表 异步修改图片
                            var set_img = {};
                            $.each(xlsx_list, function(k, v) {
                                let logo = v.logo.split('\"')
                                if (logo.length == 3) {
                                    if (set_img[logo[1]]) {
                                        set_img[logo[1]].push(k)
                                    } else {
                                        set_img[logo[1]] = [k];
                                    }
                                }
                            })

                            //读取文件中的图片
                            var zip = new JSZip();
                            var x2js = new X2JS();
                            zip.loadAsync(data).then(function(res) {

                                var excel_img = [];
                                res.files['xl/cellimages.xml'].async('string').then(function(xml) {
                                    let temp = x2js.xml_str2json(xml);
                                    $.each(temp.cellImages.cellImage, function(k, v) {
                                        Object.assign(excel_img, {
                                            [v.pic.blipFill.blip['_r:embed']]: {
                                                id: v.pic.blipFill.blip['_r:embed'],
                                                name: v.pic.nvPicPr.cNvPr['_name'],
                                            }
                                        })
                                    })
                                    res.files['xl/_rels/cellimages.xml.rels'].async('string').then(function(xml) {
                                        let temp = x2js.xml_str2json(xml);
                                        $.each(temp.Relationships.Relationship, function(k, v) {
                                            //读取到文件 显示拼接 data:image/png;base64,
                                            excel_img[v._Id].base64 = base64
                                            //存入数据
                                            res.files['xl/' + v._Target].async('base64').then(function(base64) {
                                                excel_img[v._Id].base64 = base64
                                                if (set_img[excel_img[v._Id].name]) {
                                                    $.each(set_img[excel_img[v._Id].name], function(k, v) {
                                                        xlsx_list[v].logo = base64;
                                                        currentTableLoad(xlsx_list);
                                                    })
                                                }
                                            })
                                        })

                                    })

                                })
                            })

                            currentTableLoad(xlsx_list);
                        };
                        reader.readAsArrayBuffer(file);

                    });
                }
            });
        });
    </script>
@endsection