js 读取 Excel xlsx 的数据并放到 页面 jExce 插件中

<!--  JExcel js css    -->
<script th:src="@{/assets/jExcel/jexcel.js}"></script>
<script th:src="@{/assets/jExcel/jsuites.js}"></script>
<link rel="stylesheet" th:href="@{/assets/jExcel/jexcel.css}">
<link rel="stylesheet" th:href="@{/assets/jExcel/jsuites.css}">

<!-- SheetJS ， 是一个接卸Excel 文件的js库，用于解析 xlxs 文件内容-->

<script th:src="@{/assets/sheetjs/shim.min.js}"></script>
<script th:src="@{/assets/sheetjs/xlsx.full.min.js}"></script>

$("#" + define.id).jexcel({
                    tableHeight: '450px',
                tableWidth: $("#" + define.id).parent().width() + 'px',
                tableOverflow: true,
                columnDrag: false,
                columnResize: true,
                rowDrag: false,
                rowResize: true,
                allowInsertColumn: false,
                allowDeleteColumn: false,
                allowRenameColumn: false,
                defaultColWidth: 100,
                defaultColAlign: 'left',
                colHeaders: colHeaders: ['*所属项目编号', '*实验名称', '实验描述', '实验方案', '相关链接'],
                colWidths: [100, 100, 100, 100, 100, 150, 150, 100],
                columns: [
{type: 'text', name: 'project_no'},
                    {type: 'text', name: 'name'},
                    {type: 'text', name: 'description'},
                    {type: 'text', name: 'protocol'},
                    {type: 'text', name: 'xref'},
                    {
                        type: 'autocomplete', name: 'library_strategy',
                        source: ["WGA", "WGS", "WXS", "RNA-Seq", "miRNA-Seq", "WCS", "CLONE", "POOLCLONE", "AMPLICON", "CLONEEND", "FINISHING", "ChIP-Seq", "MNase-Seq", "DNase-Hypersensitivity", "Bisulfite-Seq", "Tn-Seq", "EST", "FL-cDNA", "CTS", "MRE-Seq", "MeDIP-Seq", "MBD-Seq", "Synthetic-Long-Read", "ATAC-seq", "ChIA-PET", "FAIRE-seq", "Hi-C", "ncRNA-Seq", "RAD-Seq", "RIP-Seq", "SELEX", "ssRNA-seq", "Targeted-Capture", "Tethered Chromatin Conformation Capture", "OTHER"]
                    },
                    {
                        type: 'autocomplete', name: 'library_selection',
                        source: ["RANDOM", "PCR", "RANDOM PCR", "RT-PCR", "HMPR", "MF", "CF-S", "CF-M", "CF-H", "CF-T", "MDA", "MSLL", "cDNA", "ChIP", "MNase", "DNAse", "Hybrid Selection", "Reduced Representation", "Restriction Digest", "5-methylcytidine antibody", "MBD2 protein methyl-CpG binding domain", "CAGE", "RACE", "size fractionation", "Padlock probes capture method", "other", "unspecified", "cDNA_oligo_dT", "cDNA_randomPriming", "Inverse rRNA", "Oligo-dT", "PolyA", "repeat fractionation"]
                    },
                    {type: 'autocomplete', name: 'library_layout', source: ["Paired", "Single"]},
                    {type: 'autocomplete', name: 'mate_pair', source: ["Y", "N"]},
],
                data: [[],[],[],[]]
            });

读取 Excel 数据

 

function readExcelData(_this) {
            let files = _this.files;
            if (files.length == 0) {
                console.info("未选择文件");
                return;
            }
            let file = files[0];
            if (!file.name.toLowerCase().endsWith(".xlsx")) {
                layer.msg("Excel 文件格式错误，当前支持 .xlsx");
                return;
            }

            let selectedType = $("input[name=eType]:checked").val();
            let selectedTypeId = $("input[name=eType]:checked").attr("excel");

            // 读取文件
            let loadingIndex;
            let reader = new FileReader();
            reader.onloadstart = function () {
                loadingIndex = layer.load();
            };
            reader.onload = function (e) {
                try {
                    let data = e.target.result;
                    let workbook = XLSX.read(data, {type: 'binary'});

                    let sheet = workbook.Sheets[selectedType];
                    if (sheet === undefined || sheet == null) {
                        layer.msg("文件读取失败：Excel 中未找到表格 “" + selectedType + "”", {time: 5 * 1000});
                        return;
                    }

                    // 表头处理
                    let headers = parseSheetHeader(sheet, selectedTypeId);

                    // 将结果数据设置给 Excel表格
                    let rows = XLSX.utils.sheet_to_json(sheet, {
                        header: headers,
                        range: 1,
                        defval: null
                    });
                    $("#" + selectedTypeId).jexcel("setData", rows);
                } catch (e) {
                    console.error(e);
                    layer.msg("文件读取失败：请检查您的文件格式", {time: 5 * 1000});
                } finally {
                    $(_this).val('');
                    layer.close(loadingIndex);
                }
            };
            reader.readAsBinaryString(file);
        }

// 获取第一行的title，并且根据jexcel的定义确定
        // https://github.com/SheetJS/sheetjs#general-structures
        function parseSheetHeader(sheet, id) {
//sheetRange 读到的的是sheet表格有多少行多少列的一个数组，里面有 s(表示start)，c(表示列)，e(表示结束值)，比如 sheetRange.s.c 为获取当前表格列从那个下标开始（一般从0开始），sheetRange.e.c 表示该表格最后一列的下标（即有多少列）
            let sheetRange = XLSX.utils.decode_range(sheet['!ref']);
            let headers = [];
            for (let sheetColumnIndex = sheetRange.s.c; sheetColumnIndex <= sheetRange.e.c; ++sheetColumnIndex) {
                let address = XLSX.utils.encode_cell({r: sheetRange.s.r, c: sheetColumnIndex});
                let cellInfo = sheet[address];
                if (!cellInfo) {
                    continue;
                }
                // 拿到名称，与 jExcel 表格定义比较，然后替换为 columns 里的定义名称
                let headName = $.trim(cellInfo.v);
                for (let jExcelDefineIndex = 0; jExcelDefineIndex < jExcelDefine.length; jExcelDefineIndex++) {
                    let colHeaders = jExcelDefine[jExcelDefineIndex].colHeaders;
                    let columns = jExcelDefine[jExcelDefineIndex].columns;
                    let currentIndex = colHeaders.indexOf(headName);
                    if (currentIndex == -1){
                        continue;
                    }
                    headName = columns[currentIndex].name;
                }
                headers.push(headName);
            }
            return headers;
        }