excel_0">1、前端excel文件生成
安装插件
npm install file-saver --save
如使用TS开发，可安装file-saver的TypeScript类型定义
npm install @types/file-saver --save-dev
下载文件流
import { saveAs } from 'file-saver'
/**
*
* @param {*} fileStream // 文件流
* @param {*} saveFileName // 文件下载名称
* @returns
*/
export const downloadFile = (fileStream, saveFileName = '.xlsx') => {
return new Promise((resolve, reject) => {
const blob = new Blob([fileStream])
// 兼容IE
if (navigator.msSaveBlob) {
navigator.msSaveBlob(blob, saveFileName)
} else {
const url = window.URL.createObjectURL(blob)
saveAs(url, saveFileName + '.xlsx')
}
resolve()
})
}
/**
* @description 方法调用
*/
downloadFile(fileStream, saveFileName).then(res => {
console.log('下载成功！')
})
本地文件下载
import { saveAs } from 'file-saver'
/**
*
* @param {*} localFileUrl 本地地址
* @param {*} saveFileName 文件名称
* @returns
*/
export const downloadFile = (localFileUrl, saveFileName) => {
return new Promise((resolve, reject) => {
axios({
url: localFileUrl, // 文件地址
method: 'get',
responseType: 'blob'
})
.then(res => {
const blob = new Blob([res.data])
if (navigator.msSaveBlob) {
navigator.msSaveBlob(blob, saveFileName)
} else {
const url = window.URL.createObjectURL(blob)
saveAs(url, saveFileName)
}
resolve()
})
.catch(err => {
if (err.message === 'Request failed with status code 404') {
// 未找到相关文件
} else {
// 下载失败
}
reject(err)
})
})
}
/**
* @description 方法调用
*/
downloadFile(localFileUrl, saveFileName).then(res => {
console.log('下载成功！')
})
文件下载（列宽自适应）
import fs from 'file-saver'
import XLSX from 'xlsx'
export function xlsxGenerate(json, fields, filename = '.xlsx') {
json.forEach(item => {
for (let i in item) {
if (fields.hasOwnProperty(i)) {
item[fields[i]] = item[i]
}
delete item[i]
}
})
let sheetName = filename
let wb = XLSX.utils.book_new()
let ws = XLSX.utils.json_to_sheet(json, {
header: Object.values(fields)
})
const defaultCellStyle = {
font: {
name: 'Verdana',
sz: 16,
color: 'FF00FF88'
},
alignment: {
horizontal: 'center',
vertical: 'center'
},
fill: {
fgColor: {
rgb: 'FFFFAA00'
}
}
}
let headsArr = Object.values(fields)
const headsWidth = headsArr.map(value => {
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
return parseFloat(value.toString().length * 2.1)
} else {
return parseFloat(value.toString().length * 1.1)
}
})
// console.log("所有表头的宽度：", headsWidth);
// 2.所有表体值的宽度
json.unshift(fields)
const rowsWidth = json.map(item => {
const maxValue = Object.values(item).map((value, index) => {
let valueWidth
if (/.*[\u4e00-\u9fa5]+.*$/.test(value)) {
valueWidth = parseFloat(value.toString().length * 2.1)
} else {
valueWidth = parseFloat(value.toString().length * 1.1)
}
return Math.max(valueWidth, headsWidth[index])
})
return maxValue
})
// console.log("每行数据对比出的最大宽度：", rowsWidth)
// 3.对比每列最大值
let aotuWidth = []
rowsWidth.map((row, index) => {
let maxWidth = []
row.map((value, i) => {
if (index === 0) {
maxWidth.push({
wch: value
})
} else {
maxWidth.push({
wch: Math.max(value, aotuWidth[i].wch)
})
}
})
aotuWidth = maxWidth
})
// ws["!rows"] // 杭高
ws['!cols'] = aotuWidth // 列宽
wb.SheetNames.push(sheetName)
wb.Sheets[sheetName] = ws
let wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary',
cellStyles: true,
defaultCellStyle: defaultCellStyle,
showGridLines: false
}
let wbout = XLSX.write(wb, wopts)
let blob = new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
})
fs.saveAs(blob, filename + '.xlsx')
}
/**
* 数据下载
* @param {*} header 表头
* @param {*} tableData 数据
*/
dataDownload() {
let headerArr = ['Name', 'Age', 'Address']
let header = {
title0: 'Name',
title1: 'Age',
title2: 'Address'
}
let tableData = [
{
Name: '喜洋洋',
Age: '108',
Address: '青青草原-羊村-2幢'
},
{
Name: '美羊羊',
Age: '108',
Address: '青青草原-羊村-1幢'
}
]
let tableDataList = []
tableData.forEach((item, index) => {
let obj = {}
for (let i = 0; i < headerArr.length; i++) {
if (i === 0) {
obj['title0'] = item.Name
} else if (i === 1) {
obj['title1'] = item.Age
} else if (i === 2) {
obj['title2'] = item.Address
}
}
tableDataList.push(obj)
})
xlsxGenerate(tableDataList, header, '羊村登记表')
}
表格显示，每列列宽自适应
xlsx文件链接数据导出
import fs from 'file-saver'
import XLSX from 'xlsx'
export 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 tableData = new Uint8Array(xhr.response)
var workbook = XLSX.read(tableData, { type: 'array' }) // 该方法接收一个二进制的数据,和数据的类型,返回解析完成后的对象
if (callback) callback(workbook)
}
}
xhr.send()
}
export function readWorkbook(workbook) {
let sheetNames = workbook.SheetNames // 工作表名称集合
let worksheet = workbook.Sheets[sheetNames[0]] // 这里我们只读取第一张sheet
let csv = XLSX.utils.sheet_to_csv(worksheet)
return csv2table(csv)
}
// 将csv转换成简单的表格，会忽略单元格合并，在第一行和第一列追加类似excel的索引
export function csv2table(csv) {
let arrs = []
let rows = csv.split('\n')
let first_rows = rows.shift().split(',') // 第一行没用的
rows.pop() // 最后一行没用的
rows.forEach(function(row, idx) {
let columns = row.split(',')
let obj = {}
columns.forEach(function(col, idx) {
obj[first_rows[idx]] = col
})
arrs.push(obj)
})
return { first_rows, arrs }
}
方法调用
let urlNew = url.replace(/http:/, 'https:') // url xlsx文件链接,转https
readWorkbookFromRemoteFile(
urlNew,
workbook => {
let param = readWorkbook(workbook)
param.arrs // 表格数据
param.first_rows // 表头
}
)
