Nodejs中基于 SheetJS/js-xlsx 转 Excel
前言
在日常开发中,有时为了避免一些费时且无意义的操作时,我们可以借助一些第三方来达到目的。
比如,用原生 html 画 table 表格。我们就可以借助标题中的第三方库来实现。
什么是 Sheet.js
SheetJS是用于多种电子表格格式的解析器和编写器。通过官方规范、相关文档以及测试文件实现简洁的JS方法。SheetJS强调解析和编写的稳健,其跨格式的特点和统一的JS规范兼容,并且ES3/ES5浏览器向后兼容IE6。
目前这个是社区版,我们也提供了性能增强的专业版,专业版提供样式和专业支持的附加功能。
基本介绍
在使用这个第三方库之前,先来了解一些数据字段的概念
workbook
其实就是一个完整的 Excel 文档。
worksheet
是 Excel 文档中的表,可以有多个。
cell
是 表中的单元格。
在 sheet 中读单元格是通过索引下标的形式,像这样:sheet["A1"]
表示读取第一个单元格(下文用 A1 泛指这种表示方法)。这种方式保持了 excel 软件里面的用法,很好理解,但是不利于程序编码。通常处理二维数据,最容易想到的就是通过行列索引直接定位一个单元格,但是 sheet 并不支持这种方式,好在 xlsx 提供了几个函数,方便在行列索引和“A1”索引相互转换:
xlsx.utils.encode_row(row_index)
将基于 0 的索引转换为基于 1 的索引,之所以有这个方法是因为,程序里面下标都是从 0 开始计算,而 excel 软件却是从 1 开始计算,下同xlsx.utils.decode_row(row)
与上个方法相反,将基于 1 的索引转换为基于 0 的索引xlsx.utils.encode_col(col_index)
将基于 0 的索引转换为 ABCD 这种列;excel 表格软件中,列的表示是A, B, C, D,这个函数将 A, B, C, D 对应到 0, 1, 2, 3xlsx.utils.decode_col(col)
与上个方法相反,将 ABCD 这种列索引转换为基于 0 的索引xlsx.utils.encode_cell(object)
将一个行列对象转换为 A1 这种字符串xlsx.utils.decode_cell(addr)
与上个方法相反,将 A1 这种字符串转换为一个行列对象xlsx.utils.encode_range(obj)
将一个范围对象转换为 A1:G8 这种字符串xlsx.utils.decode_range(addr)
与上个方法相反,将 A1:G8 这种字符串转换为行列对象
举个栗子,假设要读第 8 行第 8 列这个单元格的数据,首先得到它的 A1 表示法:
// =========== 第一种方法 ==============
const row = xlsx.utils.encode_row(7);
//输出:'8'
const col = xlsx.utils.encode_col(7);
//输出:'H'
const addr = col + row;// => H8
// =========== 第二种方法 ==============
//注意这里的行列对象表示形式
const addr = xlsx.utils.encode_cell({r: 7, c: 7});
//输出:H8
//如果执行 xlsx.utils.decode_cell('H8'); 则
//输出:{r: 7, c: 7}
再举个栗子,假设已知需要获取 B4:D9 范围的数据,也就是 B, C, D 3 列和 4 5 6 7 8 9 6 行的交叉区域:
const range = xlsx.utils.decode_range('B4:D9')
// console.log(range)
{
s: { c: 1, r: 3 },
e: { c: 3, r: 8 }
}
SheetJS
转化 Excel 文档后的欢喜如下:
// ------ workbook
{
SheetNames: ['sheet1', 'sheet2'],
Sheets: {
// ------ worksheet
'sheet1': {
// -------- cell
'A1': { ... },
// cell
'A2': { ... },
...
},
// worksheet
'sheet2': {
// cell
'A1': { ... },
// cell
'A2': { ... },
...
}
}
}
且兼容性也能满足大部分浏览器。
基本用法
-
安装
npm install xlsx
-
引入 Excel 并且读取
import XLSX from 'xlsx'; const workbook = XLSX.readFile('testSheet.xlsx');
workbook
就是上述 Excel 经 Sheetjs 转化后的数据关系,为一个对象。至于如何读取的,可以看一下官方源码。js-xlsx github源码
-
获取 Excel 文档中的表
// 获取 Excel 中所有表名 const sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2'] // 根据表名获取对应某张表 const worksheet = workbook.Sheets[sheetNames[0]];
-
使用对应的工具函数(内置API)去得到想要的数据格式(json、html 等其他的),例
xlsl.utils.sheet_to_html(worksheet)
-
需注意,表中的某些字段代表了一些特殊含义。
Sheets: { 'sheet1': { '!ref': "A1:E5", // cell 'A1': { "t": "s", "v": "第一行", "h": "第一行", "w": "第一行" }, 'A2': { ... }, '!merges': [{ "s": { "c": 0, "r": 0 }, "e": { "c": 0, "r": 3 }], '!margins': {"left": 0.7,}, }, ... }, }
Cell 单元格中的
t、v、h
等字段,详细含义见 单元格字段含义{ "v": "单元格的原始值", "w": "单元格格式化之后的文本", "t": "数据类型", "f": "cell formula", "r": "富文本", "h": "富文本的HTML表示形式", "c": "注释", "z": "数字格式化模板", "l": "如果当前单元格内容是超链接,这里存储了超链接内容", "s": "单元格样式" }
"!ref"
:表格范围,上述代码片段表明范围为Excel表中的 A1-E5。"!merges"
:合并的单元格的信息,为一个数组。"s" => start "e" => end "c" => column "r" => row
DEMO 实战,
-
纯享版(使用官方工具函数,一步到位)
const xlsl = require('xlsx'); const path = require('path'); const fs = require('fs'); let workbook = xlsl.readFile('./testSheet.xlsx'); let html = ''; workbook.SheetNames.forEach(function (name, index) { let ws = workbook.Sheets[name]; let str = xlsl.utils.sheet_to_html(ws, {header:1,defval:''}); // let str = s(ws); // 只截取table的内容 let startNo = str.indexOf(`<table>`); let endNo = str.indexOf(`</table>`); str = str.substring(startNo, endNo + `</table>`.length); str = str.replace(/(\b(?:id|t|v)=".*?")/g, ''); str = str.replace('<table>', `<table border="1" style="border-collapse:collapse; width: 100%; border:1px solid #666666; margin-bottom:5px;font-size:14px;margin: 15px 0;">`); html += str; }) fs.writeFile(path.resolve(__dirname, './result.js'), JSON.stringify(workbook), function (err) { if (err) { return console.log(err); } }) fs.writeFile(path.resolve(__dirname, './output.html'), html, function (err) { if (err) { return console.log(err); } })
-
手动版
const xlsl = require('xlsx'); const path = require('path'); const fs = require('fs'); const workbook = xlsl.readFile('./testSheet.xlsx'); // 获取 Excel 中所有表名 const sheetNames = workbook.SheetNames; // 返回 ['sheet1', 'sheet2'] // 根据表名获取对应某张表 const worksheet = workbook.Sheets[sheetNames[0]]; // 拿到这张表中表格数据的范围, const range = xlsl.utils.decode_range(worksheet['!ref']); // console.log(worksheet['!ref']); // A1:E5 //保存数据范围数据 const row_start = range.s.r; // 表格范围,开始行的数据 const row_end = range.e.r; // 表格范围,结束行的数据 const col_start = range.s.c; // 表格范围,开始列的数据 const col_end = range.e.c; // 表格范围,结束行的数据 const tableMerge = worksheet['!merges'] || []; // 表格中进行单元格合并操作的数据 var oo = []; var preamble = "<tr>"; // 转 html 时进行拼接 // let rows = [], row_data, i, addr, cell; //按行对 sheet 内的数据循环 //首先读取当前对象内的所有行数据,从开始到结束 for(var R = row_start; R <= row_end; ++R) { // out.push(make_html_row(ws, r, R, o)); // 读取列数据,开始到结束 for(var C = col_start; C <= col_end; ++C) { var RS = 0, CS = 0; // 针对表中进行合并单元格操作的数据 for(var j = 0; j < tableMerge.length; ++j) { if(tableMerge[j].s.r > R || tableMerge[j].s.c > C) continue; if(tableMerge[j].e.r < R || tableMerge[j].e.c < C) continue; if(tableMerge[j].s.r < R || tableMerge[j].s.c < C) { RS = -1; break; } RS = tableMerge[j].e.r - tableMerge[j].s.r + 1; CS = tableMerge[j].e.c - tableMerge[j].s.c + 1; break; } if(RS < 0) continue; var coord = xlsl.utils.encode_cell({r:R,c:C}); var cell = worksheet[coord]; // console.log(cell); // { // t: 's', // v: '第一行', // r: '<t>第一行</t><rPh sb="0" eb="2"><t>di yi han</t></rPh><phoneticPr fontId="1" type="noConversion"/>', // h: '第一行', // w: '第一行' // } var sp = ({}); if(RS > 1) sp.rowspan = RS; if(CS > 1) sp.colspan = CS; sp.t = cell && cell.t || 'z'; sp.id = "sjs" + "-" + coord; if(sp.t != "z") { sp.v = cell.v; if(cell.z != null) sp.z = cell.z; } // 打印一下 sp 对象 // { rowspan: 4, t: 's', id: 'sjs-A1', v: '第一行' } // { t: 's', id: 'sjs-B1', v: '一' } // { rowspan: 2, colspan: 2, t: 's', id: 'sjs-C1', v: '第三列' } // { rowspan: 3, t: 's', id: 'sjs-E1', v: '第四列' } // { t: 's', id: 'sjs-B2', v: '二' } // { t: 's', id: 'sjs-B3', v: '三' } // { t: 's', id: 'sjs-C3', v: '三杠一' } // { t: 's', id: 'sjs-D3', v: '三杠二' } // { t: 'z', id: 'sjs-B4' } // { t: 's', id: 'sjs-C4', v: '四杠一' } // { t: 's', id: 'sjs-D4', v: '四杠二' } // { t: 's', id: 'sjs-E4', v: '???' } // { t: 's', id: 'sjs-A5', v: '第二行' } // { colspan: 4, t: 's', id: 'sjs-B5', v: '无敌' } // 这里就得到了我们所要的数据 // 最终将数据进行 <table> <tr> <td> 的结构进行拼接 // 官方源码奉上。 var w = (cell && cell.v != null) && (cell.h || escapehtml(cell.w || (xlsl.utils.format_cell(cell), cell.w) || "")) || ""; oo.push(writextag('td', w, sp)); } } var wtregex = /(^\s|\s$|\n)/; function keys(o) { var ks = Object.keys(o), o2 = []; for(var i = 0; i < ks.length; ++i) { if(Object.prototype.hasOwnProperty.call(o, ks[i])) o2.push(ks[i]); } return o2; } function escapehtml(text){ var s = text + ''; return s.replace(decregex, function(y) { return rencoding[y]; }).replace(/\n/g, "<br/>") .replace(htmlcharegex,function(s) { return "&#x" + ("000"+s.charCodeAt(0).toString(16)).slice(-4) + ";"; }); } function wxt_helper(h) { return keys(h).map(function(k) { return " " + k + '="' + h[k] + '"'; }).join(""); } function writextag(f,g,h) { return '<' + f + ((h != null) ? wxt_helper(h) : "") + ((g != null) ? (g.match(wtregex)?' xml:space="preserve"' : "") + '>' + g + '</' + f : "/") + '>'; } let str = preamble + oo.join("") + "</tr>"; str = str.replace(/(\b(?:id|t|v)=".*?")/g, ''); str = str.replace('<table>', `<table border="1" style="border-collapse:collapse; width: 100%; border:1px solid #666666; margin-bottom:5px;font-size:14px;margin: 15px 0;">`); // console.log(str) // <table border="1" // style="border-collapse:collapse; width: 100%; border:1px solid #666666; margin-bottom:5px;font-// // //size:14px;margin: 15px 0;"> // <tr> // <td rowspan="4">第一行</td> // <td>一</td> // <td rowspan="2" colspan="2">第三列</td> // <td rowspan="3">第四列</td> // </tr> // <tr> // <td>二</td> // </tr> // <tr> // <td>三</td> // <td>三杠一</td> // <td>三杠二</td> // </tr> // <tr> // <td></td> // <td>四杠一</td> // <td>四杠二</td> // <td>???</td> // </tr> // <tr> // <td>第二行</td> // <td colspan="4">无敌</td> // </tr> // </table>
end…