js加载并显示excel文件

4/18/2021 Javascript

# js 加载并显示 excel 文件

养成先放效果在开讲的好习惯~

项目演示和代码地址:codesandbox/js-xlsx-demo (opens new window)

完全通过 JS 的能力加载了一份 excel 表格,虽然每个单元格的宽度高度不能 100%还原(也许是我没看完具体的文档)。不过前端可以通过样式,给每一个格子在另外定样式

# 今天主角 sheetjs (opens new window)

官网的 demo (opens new window)

官网的 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

react api 示例 (opens new window)

vue api 示例 (opens new window)

其实也就是 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)
}
1
2
3
4
5
6
7
8
9
10
  • 读取远程文件
    • 可以使用 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()
}
1
2
3
4
5
6
7
8
9
10
11
12
13

# workbook 对象解析

介绍几个重要的参数:

  • SheetNamesSheets

这是 excel 中的表名,Sheets 对象中记录的每个表名对应的数据

Sheets.Sheet1['!merges'] 记录着需要合并的单元格信息。如上图,一共有 6 块区域需要合并单元格

其中s代表开始的单元格信息,e 代表结束单元格信息。 c代表列(col),r代表行(row)

{
    "s": {
        "c": 0,
        "r": 0
    },
    "e": {
        "c": 8,
        "r": 0
    }
}
1
2
3
4
5
6
7
8
9
10

就拿这个来说,代表着从 第 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 单元格的样式/主题(如果适用)

说到样式主题,这里面还有 ThemesStyles 对象。这些具体就看一下文档了~

# 前端展示表格

先看一下插件提供了多少方法

对于操作表格的,我们都用 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> 代码,而不是 <table></table>,需要对表格进行一些定制时不太方便,所以具体还是要视情况来采用合适的工具类。 (最新的版本生成的 html 已经是合并后的单元格了)

# 使用 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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
  • 合并单元格

合并单元格思路也很简单

  1. 首先确定要合并的单元格的范围(上面有说到)
    1.1 确定范围的时候需要留意上面一步我们是否自己加了单元格进去

  2. 匹配对应单元格
    2.1 匹配时,如果行和列都是起点,那这个单元格就是将来要合并的单元格
    2.2 在非起点的单元格后面,都是要被合并的,简单来说就是要删除的单元格
    2.3 不能在循环中去操作表格,因为那样会引起单元格错乱,影响后面的循环

  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')
  })
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

# 导出 excel

既然有展示,就会有导出

excel 导出方案有很多,比如 js-export-excel (opens new window) 也是其中一种,不过今天就不介绍这个库先,感兴趣可以自己看下 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)
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

导出 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) {
  // 生成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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 普通表格导出

  • 最简单的 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, '工作表1')

downLoadFile(sheet2blob(wb), '导出.xlsx')
1
2
3
4
5
  • 二维数组导出 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')
1
2
3
4
5
6
7
8
9
10
11
  • 对象类型导出 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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

# 合并单元格的导出

需要注意的地方就是被合并的单元格要用 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')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 最后

总的使用下来,js-xlsx 提供的 API 还是很多的,而且最后也支持复杂表头的导出,还有很多好玩的功能,就自己研究了。

Last Updated: 5/9/2021, 10:45:03 PM