jx-xlsx和handsontable显示excel表格中的合并单元格
1、首先,使用js-xlsx读取xlsx文件:
const XLSX = require('xlsx');
var workbook = XLSX.readFile(files[0]);
2、将workbook转化为二重数组在hansontable显示
var sheetArr = XLSX.utils.sheet_to_json(workbook.Sheets[workbook.SheetNames[0]], {header:1});
var options = {
rowHeaders: true,
colHeaders: true,
manualColumnResize: true,
manualRowResize: true,
}
var container = document.getElementById('xlsxTable');
var hot = new Handsontable(container,options);
hot.loadData(sheetArr)
3、将js-xlsx读取的合并单元格数据结构转化为hansontable所需要的格式
function getHotMergedCells (workbook){
var sheets = workbook.Sheets;
var result = {};
for(var name in sheets){
var sheet = sheets[name];
var rawArr = sheet['!merges'];
result[name] = [];
for(var i in rawArr){
var obj = rawArr[i];
var colspan = obj.e.c - obj.s.c + 1;
var rowspan = obj.e.r - obj.s.r + 1;
var handledObj = {
row : obj.s.r,
col : obj.s.c,
rowspan : rowspan,
colspan : colspan
}
result[name].push(handledObj);
}
}
return result;
}
var hotMergedCells =getHotMergedCells(workbook);
options.mergeCells = hotMergedCells[workbook.SheetNames[0]];
hot.updateSettings(options);
效果如下: