前端Excel表格导入导出，包括合并单元格，表格自定义样式等
表格数据导入
读取导入Excel表格数据这里采用的是 xlsx 插件
npm i xlsx
读取excel需要通过 XLSX.read(data, {type: type}) 方法来实现，返回一个叫WorkBook的对象，type主要取值如下：
- base64: 以base64方式读取；
- binary: BinaryString格式(byte n is data.charCodeAt(n))
- string: UTF8编码的字符串；
- buffer: nodejs Buffer；
- array: Uint8Array，8位无符号数组；
- file: 文件的路径（仅nodejs下支持）；
同时需要用到插件自身的工具类XLSX.utils来对worksheet进行解析
- XLSX.utils.sheet_to_csv：生成CSV格式
- XLSX.utils.sheet_to_txt：生成纯文本格式
- XLSX.utils.sheet_to_html：生成HTML格式
- XLSX.utils.sheet_to_json：输出JSON格式
下面用一个 vue 的组件来演示一下
<template>
<div class="read_excel_file">
<slot></slot>
<input
class="file-input"
ref="readexcel_input"
type="file"
:accept="SheetJSFT"
@change="change"
/>
</div>
</template>
<script>
import XLSX from 'xlsx'
export default {
data() {
return {
SheetJSFT: '.xlsx',
}
},
mounted() {
// 绑定插槽点击触发导入文件
if (this.$slots && this.$slots.default && this.$slots.default.length > 0) {
this.$slots.default[0].elm.addEventListener(
'click',
this.openExcel.bind(this),
)
}
},
methods: {
// 点击打开导入excel
openExcel() {
let uploadBtn = this.$refs['readexcel_input']
uploadBtn.click()
},
// 文件导入时
change(evt) {
const files = evt.target.files
if (!/\.xlsx$/.test(files[0].name)) {
this.$emit('validate', false)
this.$emit('file-change', {
name: files[0].name,
})
console.error('请选择xlsx格式文件')
return false
} else {
this.$emit('validate', true)
}
if (files && files[0]) this.file(files[0])
},
// 数据读取
file(file) {
const reader = new FileReader()
reader.onload = e => {
const bstr = e.target.result
const wb = XLSX.read(bstr, { type: 'binary' }) // 这里使用type为binary
const wsname = wb.SheetNames[0]
const ws = wb.Sheets[wsname]
const data = XLSX.utils.sheet_to_json(ws, {
header: 1,
// blankrows: false,
}) // 读取json格式
this.formatData(data, file.name, ws['!merges'])
}
reader.readAsBinaryString(file)
},
// 数据格式化
formatData(list, name, merges) {
let arr = []
for (let i = 1; i < list.length; i++) {
if (list[i].length > 0) {
let obj = {}
list[i].map((v, j) => {
obj[list[0][j]] = v
})
arr.push(obj)
}
}
this.$emit('file-change', {
header: list[0],
body: arr,
name: name,
merges: merges,
})
// 必须清空input的value属性，不然第二次选择同样的文件，不会触发change事件。
this.$refs['readexcel_input'].value = ''
},
},
}
</script>
<style lang="scss" scoped>
.read_excel_file {
display: inline-block;
.file-input {
width: 0;
height: 0;
}
}
</style>
组件的具体使用如下
<template>
<div class="cs">
<ReadExcel @file-change="excelFileChange">
<div class="import-button">导入</div>
</ReadExcel>
</div>
</template>
<script>
import ReadExcel from './components/ReadExcel.vue'
export default {
components: {
ReadExcel,
},
methods: {
excelFileChange(data) {
let { merges, body, name, header } = data
console.log(merges, body, name, header)
},
},
}
</script>
接下来，我们尝试导入以下的表格
导出的数据（merges, body, name, header）如下
如此，便拿到了表格中的数据
导出excel表格
这里展示两种导出表格的方法
1、xlsx插件导出
这里还是用到xlsx插件
这里直接做一个小demo展示
function exportExcel(header, body, merges, wscols, wsrows, fileName) {
body.unshift(header)
const ws = XLSX.utils.aoa_to_sheet(body)
const wb = XLSX.utils.book_new()
ws['!cols'] = wscols
ws['!rows'] = wsrows
ws['!merges'] = merges
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1')
// 生成excel
XLSX.writeFile(wb, `${fileName}.xlsx`)
}
// 设置表格头部
let header = [
'*手机号码',
'*订单编号',
'*商品编号',
'*数量',
'*订单支付类型',
'*订单总金额¥',
'*订单实付金额¥',
]
// 设置表格数据
let body = [
['18600000002', 'svp000002', 'sp002', '1', '微信支付', '1100', '1100'],
[undefined, undefined, 'sp003', '2', undefined, undefined, undefined],
[undefined, undefined, 'sp004', '1', undefined, undefined, undefined],
]
// 设置合并单元格
let merges = [
{ e: { c: 0, r: 3 }, s: { c: 0, r: 1 } },
{ e: { c: 1, r: 3 }, s: { c: 1, r: 1 } },
{ e: { c: 4, r: 3 }, s: { c: 4, r: 1 } },
{ e: { c: 5, r: 3 }, s: { c: 5, r: 1 } },
{ e: { c: 6, r: 3 }, s: { c: 6, r: 1 } },
]
// 指定每一列的宽度
let wscols = [
{ wch: 20 },
{ wch: 20 },
{ wch: 30 },
{ wch: 30 },
{ wch: 30 },
{ wch: 30 },
{ wch: 30 },
]
// 指定每一行的高度
let wsrows = [{ hpx: 20 }]
exportExcel(header, body, merges, wscols, wsrows, '生成excel文件')
导出结果如下
导出是导出成功，但是只有光秃秃的数据
那么这里如果要设置单元格的样式该怎么做呢？
如果单纯使用xlsx插件是无法设置单元格的样式的，似乎有个xlsx的pro专业版可以做到，但是是收费的; 也有使用免费的xlsx-style实现设置样式
npm i xlsx-style
用xlsx-style确实可以实现设置样式，但是像我们一开始导入的excel文件中的表头中（如下图），有一个单元格中存在两种不同颜色的文本的情况，
这种情况下，笔者用xlsx-style也实现不了，所以这里不详诉xlsx-style的使用方法，我们试下第二种导出excel的方法
2、使用html table标签导出excel
这里是用table标签直接生成excel文件
直接上代码
function tableHtmlCompute(str) {
return (
"<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'><head><!--[if gte mso 9]><xml>" +
'<x:ExcelWorkbook>' +
'<x:ExcelWorksheets>' +
'<x:ExcelWorksheet>' +
'<x:WorksheetOptions><x:Print><x:ValidPrinterInfo /></x:Print></x:WorksheetOptions>' +
'</x:ExcelWorksheet>' +
'</x:ExcelWorksheets>' +
'</x:ExcelWorkbook></xml><![endif]--> ' +
'<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"></head><body>' +
'<table border="1" cellspacing="1" cellpadding="1">' +
'<tr>' + // 这里是表头
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>手机号码</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>订单编号</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>商品编号</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>数量</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>订单支付类型</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>订单总金额¥</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">*</span><span>订单实付金额¥</span></td>' +
'</tr>' +
'<tr>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span><br style="mso-data-placement:same-cell"/><span>1、学员购买商品收货填写的手机号</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span><br style="mso-data-placement:same-cell"/><span>1、填写导入的订单在售卖平台的订单编号</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span><br style="mso-data-placement:same-cell"/><span>1、上架商品时候填写的“售卖平台商品编号”</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span><br style="mso-data-placement:same-cell"/><span>1、购买商品的总数量</span><br style="mso-data-placement:same-cell"/><span>2、填写的数量>=1，且为整数。</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填+单选</span><br style="mso-data-placement:same-cell"/><span>1、选项</span><br style="mso-data-placement:same-cell"/><span>微信支付</span><br style="mso-data-placement:same-cell"/><span>支付宝支付</span><br style="mso-data-placement:same-cell"/><span>网银支付</span><br style="mso-data-placement:same-cell"/><span>pos机支付</span><br style="mso-data-placement:same-cell"/><span>银行汇款</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span></td>' +
'<td style="background:rgb(217,225,242);mso-number-format:@"><span style="color: rgb(255,0,0)">必填项</span></td>' +
'</tr>' +
str +
'</table></body></html>'
)
}
function excelExport(str) {
let html = tableHtmlCompute(str)
let blob = new Blob([html], {
type: 'text/plain;charset=utf-8',
})
//解决中文乱码问题
blob = new Blob([String.fromCharCode(0xfeff), blob], {
type: blob.type,
})
let a = document.createElement('a')
a.style.display = 'none'
// 利用URL.createObjectURL()方法为 a 元素生成 blob URL
a.href = URL.createObjectURL(blob)
// 设置文件名
a.download = 'excel名字.xlsx'
document.body.appendChild(a)
a.click()
document.body.removeChild(a)
}
let body = [
{
mobile: '18600000002',
orderNumber: 'svp000002',
products: [
{
productNumber: 'sp002',
quantity: 1,
},
{
productNumber: 'sp003',
quantity: 2,
},
{
productNumber: 'sp004',
quantity: 1,
},
],
paymentType: '微信支付',
total: 1100,
paymenteds: 1100,
},
]
let header = [
'mobile',
'orderNumber',
'productNumber',
'quantity',
'paymentType',
'total',
'paymenteds',
]
let productsHeader = ['productNumber', 'quantity']
let html = ''
body.forEach(e => {
if (e.products && e.products.length) {
e.products.forEach((item, i) => {
let str = '<tr>'
if (i) {
productsHeader.forEach(key => {
str += `<td style="mso-number-format:\\@"><span>${
item[key] ? item[key] : ''
}</span></td>`
})
str += '</tr>'
} else {
// i===0；每一条数据的头部
header.forEach(key => {
if (productsHeader.includes(key)) {
str += `<td style="mso-number-format:\\@"><span>${
item[key] ? item[key] : ''
}</span></td>`
} else {
let val = e[key] ? e[key] : ''
str += `<td style="mso-number-format:\\@" rowspan=${e.products.length}><span>${val}</span></td>`
}
})
str += '</tr>'
}
html += str
})
}
})
excelExport(html)
导出结果如下
这里有以下几个注意点
1、上面有展示了如何在同一单元格内换行，如果需要在同一单元格内换行，需要用到以下代码
<br style="mso-data-placement:same-cell"/>
如果单纯只写br标签，不加后面的style="mso-data-placement:same-cell", 则它会出现两个单元格合并在一起的情况
2、style="mso-number-format:\@" 这个样式可以让单元格的格式被识别为文本，避免不必要的格式自动转换
