js 加载并显示 excel 文件
养成先放效果在开讲的好习惯~
项目演示和代码地址:codesandbox/js-xlsx-demo
完全通过 JS 的能力加载了一份 excel 表格,虽然每个单元格的宽度高度不能 100%还原(也许是我没看完具体的文档)。不过前端可以通过样式,给每一个格子在另外定样式
今天主角 sheetjs
官网的 demo 并没有合并单元格,不过他可以像 excel 一样选中多个单元格,感兴趣的可以去研究一下
兼容性:IE8 (那就是全世界浏览器都支持了)
引入
CDN | URL |
---|---|
unpkg | https://unpkg.com/xlsx/ |
jsDelivr | https://jsdelivr.com/package/npm/xlsx |
CDNjs | http://cdnjs.com/libraries/xlsx |
packd | https://bundle.run/xlsx@latest?name=XLSX |
或者 npm install xlsx
其实也就是import XLSX from 'xlsx'
后面都医药了
本地的 demo 我就用个 cdn 就行了。或者直接下载 js 文件
使用
读取 excel 主要是通过 XLSX.read(data, {type: type})
方法来实现,返回一个叫 WorkBook
的对象,type 主要取值如下:
type 值 | 描述 |
---|---|
base64 | 以 base64 方式读取 |
binary | BinaryString 格式(byte n is data.charCodeAt(n)) |
string | UTF8 编码的字符串 |
buffer | nodejs Buffer |
array | Uint8Array,8 位无符号数组 |
file | 文件的路径(仅 nodejs 下支持) |
- 本地文件读取
// 读取本地excel文件
function readWorkbookFromLocalFile(file, callback) {
var reader = new FileReader()
reader.onload = function(e) {
var data = e.target.result
var workbook = XLSX.read(data, { type: 'binary' })
if (callback) callback(workbook)
}
reader.readAsBinaryString(file)
}
- 读取远程文件
- 可以使用 axios 等发起请求,只要接口返回的是二进制流即可
- 文件的网络地址必须和 url 同域,否则会报错
function readWorkbookFromRemoteFile(url, callback) {
var xhr = new XMLHttpRequest()
xhr.open('get', url, true)
xhr.responseType = 'arraybuffer'
xhr.onload = function(e) {
if (xhr.status == 200) {
var data = new Uint8Array(xhr.response)
var workbook = XLSX.read(data, { type: 'array' })
if (callback) callback(workbook)
}
}
xhr.send()
}
workbook 对象解析
介绍几个重要的参数:
-
SheetNames
和Sheets
这是 excel 中的表名,Sheets 对象中记录的每个表名对应的数据
Sheets.Sheet1['!merges']
记录着需要合并的单元格信息。如上图,一共有 6 块区域需要合并单元格
其中s
代表开始的单元格信息,e
代表结束单元格信息。c
代表列(col),r
代表行(row)
{
"s": {
"c": 0,
"r": 0
},
"e": {
"c": 8,
"r": 0
}
}
就拿这个来说,代表着从 第 0 行(s.r
)的第 0 个(s.c
)单元格 开始
合并到 第 0 行(e.r
)的第 8 个(e.c
)单元格
即:A1-I1 单元格
Sheets 往下的 A2,B2
等等就是每个单元格的信息了
其中字段和信息如下:
key | 描述 |
---|---|
v | 原始值(有关更多信息,请参见“数据类型”部分) |
w | 格式化文本(如果适用) |
t | 类型:b 布尔值,e 错误,n 数字,d 日期,s 文本,z 存根 |
f | 单元格公式编码为 A1 样式的字符串(如果适用) |
F | 如果公式是数组公式,则包含数组的范围(如果适用) |
r | 富文本编码(如果适用) |
h | 富文本格式的 HTML 呈现(如果适用) |
c | 与单元格相关的评论 |
z | 与单元格关联的数字格式字符串(如果要求) |
l | 单元超链接对象(.Target 包含链接,.Tooltip 是工具提示) |
s | 单元格的样式/主题(如果适用) |
说到样式主题,这里面还有 Themes
和Styles
对象。这些具体就看一下文档了~
前端展示表格
先看一下插件提供了多少方法
对于操作表格的,我们都用 sheet_
开头的 API
API | 功能 |
---|---|
XLSX.utils.sheet_to_csv |
【常用】生成 CSV 格式 |
XLSX.utils.sheet_to_html |
【常用】生成 HTML 格式 |
XLSX.utils.sheet_to_txt | 生成纯文本格式 |
XLSX.utils.sheet_to_json | 输出 JSON 格式 |
常用的主要是 sheet_to_csv
或者 sheet_to_html
,转 csv 的话会忽略格式、单元格合并等信息,所以复杂表格可能不适用。转 html 的话会保留单元格合并,但是生成的是 (最新的版本生成的 html 已经是合并后的单元格了)<html></html>
代码,而不是 <table></table>
,需要对表格进行一些定制时不太方便,所以具体还是要视情况来采用合适的工具类。
使用 CVS,并且实现合并单元格
看了上面的!merges
解析。我们可以根据给出的表格信息,自己处理合并单元格
- 前端展示 CVS 数据
// 将csv转换成表格
function csv2table(csv, showTab) {
var html = '<table>'
var rows = csv.split('\n')
rows.pop() // 最后一行没用的
rows.forEach(function(row, idx) {
var columns = row.split(',')
if (showTab) {
columns.unshift(idx + 1) // 添加行索引
if (idx == 0) {
// 添加列索引
html += '<tr>'
for (var i = 0; i < columns.length; i++) {
html += '<th>' + (i == 0 ? '' : String.fromCharCode(65 + i - 1)) + '</th>'
}
html += '</tr>'
}
}
html += '<tr>'
columns.forEach(function(column) {
html += '<td>' + column + '</td>'
})
html += '</tr>'
})
html += '</table>'
return html
}
- 合并单元格
合并单元格思路也很简单
-
首先确定要合并的单元格的范围(上面有说到)
1.1 确定范围的时候需要留意上面一步我们是否自己加了单元格进去 -
匹配对应单元格
2.1 匹配时,如果行和列都是起点,那这个单元格就是将来要合并的单元格
2.2 在非起点的单元格后面,都是要被合并的,简单来说就是要删除的单元格
2.3 不能在循环中去操作表格,因为那样会引起单元格错乱,影响后面的循环 -
根据上面打的标识,该删的删,该合并的合并
下面的代码中有个 #result tr
是因为我本地调试代码中我是把我的表格放到了 id="result"
里面,这一点注意区分和修改。
function mergeTable(workbook, hasTab = false) {
let SheetNames = workbook.SheetNames[0]
let mergeInfo = workbook.Sheets[SheetNames]['!merges']
console.log(mergeInfo)
let result = document.getElementById('result')
// 是否显示了tab
let baiseAdd = hasTab ? 1 : 0
mergeInfo.forEach(item => {
let start_r = item.s.r + baiseAdd
let end_r = item.e.r + baiseAdd
let start_c = item.s.c + baiseAdd
let end_c = item.e.c + baiseAdd
for (let i = start_r; i <= end_r; i++) {
let row = document.querySelectorAll('#result tr')[i]
for (let child = start_c; child <= end_c; child++) {
if (child === start_c && i === start_r) {
// 循环到就是第一个单元格,以这个单元格为开始进行合并
row.children[child].classList.add('will_span')
row.children[child].setAttribute('row', end_r - start_r + 1)
row.children[child].setAttribute('col', end_c - start_c + 1)
} else {
// 只做标记,不在这里删除
row.children[child].classList.add('remove')
}
}
}
})
// 移除对应的td
document.querySelectorAll('.remove').forEach(item => {
item.parentNode.removeChild(item)
})
// 为大的td设置跨单元格合并
document.querySelectorAll('.will_span').forEach(item => {
item.classList.remove('will_span')
item.rowSpan = item.getAttribute('row')
item.colSpan = item.getAttribute('col')
})
}
导出 excel
既然有展示,就会有导出
excel 导出方案有很多,比如 js-export-excel 也是其中一种,不过今天就不介绍这个库先,感兴趣可以自己看下 npm 上的文档。
既然导出,那就有下载方法,可以使用 download.js
,我这里就手写一个简单的下载方法
/**
* 通用的打开下载对话框方法,没有测试过具体兼容性
* @param url 下载地址,也可以是一个blob对象,必选
* @param saveName 保存文件名,可选
*/
function downLoadFile(url, saveName) {
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url) // 创建blob地址
}
var aLink = document.createElement('a')
aLink.href = url
aLink.download = saveName || '' // HTML5新增的属性,指定保存文件名,可以不要后缀,注意,file:///模式下不会生效
var event
if (window.MouseEvent) event = new MouseEvent('click')
else {
event = document.createEvent('MouseEvents')
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null)
}
aLink.dispatchEvent(event)
}
导出 excel 的话就得把我们的数据转化为插件认识的数据。官方提供的 API(均在 XLSX.utils 里面):
导出的 excel 表格肯定要有工作表
book
。相关的 api 就 3 个
api | 描述 |
---|---|
book_new | 创建一个新的工作表 |
book_append_sheet | 工作表中添加 sheet 单元格数据 |
book_set_sheet_visibility | 设置工作表是否可见 |
创建单元格数据(sheet) API 如下
api | 描述 |
---|---|
aoa_to_sheet | 将一个二维数组转成 sheet,会自动处理 number、string、boolean、date 等类型数据 |
table_to_sheet | 将一个 table dom 直接转成 sheet,会自动识别 colspan 和 rowspan 并将其转成对应的单元格合并 |
json_to_sheet | 将一个由对象组成的数组转成 sheet |
把工作表和数据结合配置 使用
XLSX.write
生成对应的数据
光写入数据还不够,既然要导出文件,那肯定也要把 workbook 重新转成二进制流
function sheet2blob(workbook, wopts) {
// if (!wopts) {
// }
// 生成excel的配置项参考
var defaultWopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table,官方解释是,如果开启生成速度会下降,但在低版本IOS设备上有更好的兼容性
type: 'binary'
}
wopts = Object.assgin({}, defaultWopts, wopts)
var wbout = XLSX.write(workbook, wopts)
var blob = new Blob([s2ab(wbout)], { type: 'application/octet-stream' })
return blob
}
// 字符串转ArrayBuffer
function s2ab(s) {
var buf = new ArrayBuffer(s.length)
var view = new Uint8Array(buf)
for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
return buf
}
普通表格导出
- 最简单的 table_to_sheet
var sheet = XLSX.utils.table_to_sheet($('table')[0])
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, sheet1, '收件人列表')
downLoadFile(sheet2blob(wb), '导出.xlsx')
- 二维数组导出 aoa_to_sheet
var aoa = [
['姓名', '性别', '年龄', '注册时间'],
['张三', '男', 18, new Date()],
['李四', '女', 22, new Date()]
]
var sheet = XLSX.utils.aoa_to_sheet(aoa)
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, sheet, 'Sheet1')
downLoadFile(sheet2blob(wb), '导出.xlsx')
- 对象类型导出 table_to_sheet
var arr = [
{ name: '张三', sex: '男', age: 18, register: new Date() },
{ name: '李四', sex: '女', age: 22, register: new Date() }
]
// 键名就是表头名称,值就是对应列的值
var sheetData = arr.map(item => {
return {
姓名: item.name,
性别: item.sex,
年龄: item.age,
注册时间: item.register
}
})
var sheet = XLSX.utils.json_to_sheet(sheetData)
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, sheet, 'Sheet1')
downLoadFile(sheet2blob(wb), '导出.xlsx')
合并单元格的导出
需要注意的地方就是被合并的单元格要用 null
预留出位置
var aoa = [
['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
['姓名', '性别', '年龄', '注册时间'],
['张三', '男', 18, new Date()],
['李四', '女', 22, new Date()]
]
var sheet = XLSX.utils.aoa_to_sheet(aoa)
sheet['!merges'] = [
// 设置A1-C1的单元格合并
{ s: { r: 0, c: 0 }, e: { r: 0, c: 2 } }
]
const wb = XLSX.utils.book_new()
XLSX.utils.book_append_sheet(wb, sheet, 'Sheet1')
downLoadFile(sheet2blob(wb), '单元格合并示例.xlsx')
最后
总的使用下来,js-xlsx 提供的 API 还是很多的,而且最后也支持复杂表头的导出,还有很多好玩的功能,就自己研究了。