sheetjs前段操作excel,点击合并拆分表格增删行列功能实现

         sheetjs的js-xlsx插件是专门为web前段处理excel表格开发的一款开源插件，插件的github地址是https://github.com/sheetjs/js-xlsx官网地址http://sheetjs.com/,它提供了excel表格的导入导出功能，为前段显示修改excel表格提供了可能，但是毕竟只是一个插件，无法实现excel的全部功能，比如表格的合并拆分这些基础的功能，这些都需要开发者自己来实现，本文将介绍如何用纯js实现excel表格的合并拆分表格以及动态插入删除行列。

0，基础知识

          js-xlsx导入excel表格的时候会生成一个sheet对象，这个对象包含了一个表格的具体数据，包括范围，合并的单元格，各单元格数据，基本样式等等，如下图：

其中"!ref"表示显示的范围，从A1到J35。"!merges"表示那些合并的单元格项，具体数据格式如下：

其中s表示开始单元格的坐标，c是列坐标，r是行坐标，都是从0开始，e表示结束单元格的坐标，我们通过修改这些数据完成单元格的合并与拆分。

var row_count = 0;
var colums_count = 0;
function refreshFromSheet() {var html = XLSX.utils.sheet_to_html(worksheet);document.getElementById('result').innerHTML = html;var ref = worksheet["!ref"];var s_e = ref.split(":");row_count = getRowFromId(s_e[1])+1;colums_count = getIndexFromId(s_e[1])+1;$("table tr:first-child td:first-child").css("font-size", 25);$("table tr td").css("text-align", "center");}
function readWorkbook(workbook) {var sheetNames = workbook.SheetNames; // 工作表名称集合worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheetrefreshFromSheet();
｝

1，合并单元格

         我们要实现的是鼠标点击两个单元格，就合并两个单元格间的所有表格为一个，我们记录下两次点击选择的表格id，转为坐标，然后加入到shheet对象里，最后重新渲染下界面：

    var selected_split_grid_id = "";//选中的要拆分的单元格idvar selected_grid_id = "";var selected_merge_grid_s = {};//要合并的开始单元格var selected_merge_grid_e = {};//要合并的结束单元格var selected_merge_grid_sid = "";//要合并的开始单元格idvar selectedType = -1;// 0是选中工艺状态用于拆分，// 1是参数行选中状态用于编辑// 2是合并模式// 3是插入行// 4是插入列// 5是插入列// 6是插入列+
function mergeGrid() {if (selectedType == 2) {resetType()} else {selectedType = 2;$("#label").text("合并模式");}clearType();}
function resetType() {selectedType = -1;$("#label").text("编辑模式");}
function clearType() {if (selected_merge_grid_sid)$("#" + selected_merge_grid_sid).css("background", "#EEEEEE");if (grid_selected) {$("#" + selected_grid_id).css("background", "#eeeeee");grid_selected = false}selected_merge_grid_s = {};selected_merge_grid_e = {};}
var letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z','AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];function getIndexFromId(str) {var reg = /([A-Z]+)/g;var result = reg.exec(str);return letters.indexOf(result[0]);}function getRowFromId(str) {var reg = /(\d+)/g;var result = reg.exec(str);return parseInt(result[0]) - 1;}
$("#result").on("tap", "table tbody tr td", function () {if (selectedType == 2) {//点击选择合并单元格var colums = getIndexFromId($(this).attr("id"));var row = getRowFromId($(this).attr("id"));if (JSON.stringify(selected_merge_grid_s) == "{}") {//选择合并的开始selected_merge_grid_sid = $(this).attr("id");selected_merge_grid_s.r = row;selected_merge_grid_s.c = colums;$(this).css("background", "#007aff");} else {//选择合并的结尾然后合并selected_merge_grid_e.r = row;selected_merge_grid_e.c = colums;if (selected_merge_grid_s.r == selected_merge_grid_e.r && selected_merge_grid_s.c == selected_merge_grid_e.c) {//如果第二次选择的单元格和第一次相同则取消第一次的选择selected_merge_grid_s = {};selected_merge_grid_e = {};$("#" + selected_merge_grid_sid).css("background", "#EEEEEE");} else// if (selected_merge_grid_s.r == selected_merge_grid_e.r || selected_merge_grid_s.c == selected_merge_grid_e.c){//如果后点在前点店面则交换两点位置信息if (selected_merge_grid_s.r >= selected_merge_grid_e.r && selected_merge_grid_s.c >= selected_merge_grid_e.c) {var tmpc = selected_merge_grid_s.c;selected_merge_grid_s.c = selected_merge_grid_e.c;selected_merge_grid_e.c = tmpc;var tmpr = selected_merge_grid_s.r;selected_merge_grid_s.r = selected_merge_grid_e.r;selected_merge_grid_e.r = tmpr;}var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);if (!sheet["!merges"]) sheet["!merges"] = [];var merges = sheet["!merges"];var merged = false;//考虑到第一次选择的单元格可能是已合并的单元格，遍历已合并列表把新合并数据替换进去if (merges) {for (var i = 0; i < merges.length; i++) {var merge_s = merges[i].s;if (merge_s.c == selected_merge_grid_s.c && merge_s.r == selected_merge_grid_s.r) {merges[i].e = selected_merge_grid_e;merged = true;}if (merge_s.c == selected_merge_grid_e.c && merge_s.r == selected_merge_grid_e.r) {merges[i].s = selected_merge_grid_s;merged = true;}}} else {merges = [];}//如果不是已合并单元格，就直接push进去if (!merged) {sheet["!merges"].push({"s": selected_merge_grid_s,"e": selected_merge_grid_e});}worksheet = sheet;refreshFromSheet();//刷新界面selected_merge_grid_s = {};selected_merge_grid_e = {};}}}
})

2，拆分单元格

       拆分单元格也是同理，根据点击选择的id，转为坐标然后遍历"!merges"数组，删除匹配的项目就完成已合并表格的删除

function splitGrid() {if (selectedType == 0) {resetType()} else {selectedType = 0;clearType();$("#label").text("拆分模式");}}
$("#result").on("tap", "table tbody tr td", function () {if (selectedType == 0) {//点击选择拆分单元格if (selected_split_grid_id)$("#" + selected_split_grid_id).css("background", "#eeeeee");selected_split_grid_id = "";var colums = getIndexFromId($(this).attr("id"));var row = getRowFromId($(this).attr("id"));var merges = worksheet["!merges"];if (!merges) {return;}var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);var changed = false;for (var j = 0; j < merges.length; j++) {var srcmerge = sheet["!merges"][j];if (srcmerge.s.c == colums && srcmerge.s.r == row) {sheet["!merges"].splice(j, 1);changed = true;break;}}if (changed) {worksheet = sheet;refreshFromSheet();resetType();}}
})

3，插入删除行和列

        首先扩展航和列，直接操作"!ref"字段，然后把所选中行列之后的数据全部后移

function insertRow() {if (selectedType == 3) {resetType()} else {selectedType = 3;$("#label").text("插入行模式，点击要插入的行");}}function insertRowReal(row) {var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);sheet["!ref"] = "A1:" + letters[colums_count-1] + String(row_count + 1);for (var i = row_count; i >= row + 1; i--) {for (var j = 0; j <= colums_count-1; j++) {sheet[letters[j] + (i + 1)] = sheet[letters[j] + (i)];}}var merges = sheet["!merges"];var tomerge = [];if (merges) {for (var i = 0; i < merges.length; i++) {var s = merges[i].s;var e = merges[i].e;if (s.r == e.r && s.r == row) {tomerge.push({s: {c: s.c, r: s.r}, e: {c: e.c, r: e.r}});e.r = e.r + 1;s.r = s.r + 1;} else {if (e.r >= row) {e.r = e.r + 1;}if (s.r > row) {s.r = s.r + 1;}}}for (var i = 0; i < tomerge.length; i++) {merges.push(tomerge[i]);}}worksheet = sheet;refreshFromSheet();resetType();generatexuhao();}function deleteRowReal(row) {var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);sheet["!ref"] = "A1:" + letters[colums_count-1] + String(row_count - 1);for (var i = row + 1; i <= row_count; i++) {for (var j = 0; j <= colums_count-1; j++) {sheet[letters[j] + (i)] = sheet[letters[j] + (i + 1)];}}var merges = sheet["!merges"];var tomerge = [];if (merges) {for (var i = 0; i < merges.length; i++) {var s = merges[i].s;var e = merges[i].e;if (s.r == e.r && s.r == row) {tomerge.push(i)} else {if (e.r >= row) {e.r = e.r - 1;}if (s.r > row) {s.r = s.r - 1;}}}for (var i = 0; i < tomerge.length; i++) {sheet["!merges"].splice(tomerge[i], 1);}}worksheet = sheet;refreshFromSheet();resetType();generatexuhao();}
function insertColums() {if (selectedType == 4) {resetType()} else {selectedType = 4;$("#label").text("插入列模式，点击要插入的列");}}function deleteRow() {if (selectedType == 5) {resetType()} else {selectedType = 5;$("#label").text("删除行模式，点击要删除的行");}}function deleteColums() {if (selectedType == 6) {resetType()} else {selectedType = 6;$("#label").text("删除列模式，点击要删除的列");}}function insertColumsReal(colums) {var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);sheet["!ref"] = "A1:" + letters[colums_count ] + String(row_count);for (var i = colums_count ; i >= colums; i--) {for (var j = 0; j <= row_count; j++) {sheet[letters[i + 1] + (j)] = sheet[letters[i] + (j)];}}var merges = sheet["!merges"];var tomerge = [];if (merges) {for (var i = 0; i < merges.length; i++) {var s = merges[i].s;var e = merges[i].e;if (s.c == e.c && s.c == colums) {tomerge.push({s: {c: s.c, r: s.r}, e: {c: e.c, r: e.r}});e.c = e.c + 1;s.c = s.c + 1;} else {if (e.c >= colums) {e.c = e.c + 1;}if (s.c > colums) {s.c = s.c + 1;}}}for (var i = 0; i < tomerge.length; i++) {merges.push(tomerge[i]);}}worksheet = sheet;refreshFromSheet();resetType();}function deleteColumsReal(colums) {var table = document.getElementsByTagName("table")[0];var sheet = XLSX.utils.table_to_sheet(table);sheet["!ref"] = "A1:" + letters[colums_count - 2] + String(row_count);for (var i = colums; i < colums_count-1; i++) {for (var j = 0; j <= row_count; j++) {sheet[letters[i] + (j)] = sheet[letters[i + 1] + (j)];}}var merges = sheet["!merges"];var tomerge = [];if (merges) {for (var i = 0; i < merges.length; i++) {var s = merges[i].s;var e = merges[i].e;if (s.c == e.c && s.c == colums) {tomerge.push(i);} else {if (e.c >= colums) {e.c = e.c - 1;}if (s.c > colums) {s.c = s.c - 1;}}}for (var i = 0; i < tomerge.length; i++) {sheet["!merges"].splice(tomerge[i], 1);}}worksheet = sheet;refreshFromSheet();resetType();}$("#result").on("tap", "table tbody tr td", function () {
if (selectedType == 3) {var row = getRowFromId($(this).attr("id"));insertRowReal(row);} else if (selectedType == 4) {var colums = getIndexFromId($(this).attr("id"));insertColumsReal(colums);} else if (selectedType == 5) {var row = getRowFromId($(this).attr("id"));deleteRowReal(row);} else if (selectedType == 6) {var colums = getIndexFromId($(this).attr("id"));deleteColumsReal(colums);} 
})

excel导入到web前端然后导出到后台的话会丢失大量样式，我们可以在后端操作excel(java有相关类库)，把前段返回的excel表格里的数据填入原excel文件里，这样即保留原文件的样式又获得了前端返回的数据。

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！