注意看注释
1、首先 安装两个包
npm i -- save xlsx@0.17 .0
npm i -- save file- saver@2.0 .5
2、核心js 建立文件 Export2Excel.js (名字随便用的地方引入就行)
import { saveAs } from 'file-saver'
import * as XLSX from 'xlsx'
function datenum ( v, date1904 ) {
if ( date1904) v += 1462
var epoch = Date. parse ( v)
return ( epoch - new Date ( Date. UTC ( 1899 , 11 , 30 ) ) ) / ( 24 * 60 * 60 * 1000 )
}
function sheet_from_array_of_arrays ( data, opts ) {
var ws = { }
var range = {
s : {
c : 10000000 ,
r : 10000000
} ,
e : {
c : 0 ,
r : 0
}
}
for ( var R = 0 ; R != data. length; ++ R ) {
for ( var C = 0 ; C != data[ R ] . length; ++ C ) {
if ( range. s. r > R ) range. s. r = R
if ( range. s. c > C ) range. s. c = C
if ( range. e. r < R ) range. e. r = R
if ( range. e. c < C ) range. e. c = C
var cell = {
v : data[ R ] [ C ]
}
if ( cell. v == null ) continue
var cell_ref = XLSX . utils. encode_cell ( {
c : C ,
r : R
} )
if ( typeof cell. v === 'number' ) cell. t = 'n'
else if ( typeof cell. v === 'boolean' ) cell. t = 'b'
else if ( cell. v instanceof Date ) {
cell. t = 'n'
cell. z = XLSX . SSF . _table[ 14 ]
cell. v = datenum ( cell. v)
} else cell. t = 's'
ws[ cell_ref] = cell
}
}
if ( range. s. c < 10000000 ) ws[ '!ref' ] = XLSX . utils. encode_range ( range)
return ws
}
function Workbook ( ) {
if ( ! ( this instanceof Workbook ) ) return new Workbook ( )
this . SheetNames = [ ]
this . Sheets = { }
}
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
}
export const export_json_to_excel = ( {
multiHeader = [ ] ,
header,
data,
filename,
merges = [ ] ,
autoWidth = true ,
bookType = 'xlsx'
} = { } ) => {
filename = filename || 'excel-list'
data = [ ... data]
data. unshift ( header)
for ( let i = multiHeader. length - 1 ; i > - 1 ; i-- ) {
data. unshift ( multiHeader[ i] )
}
var ws_name = 'SheetJS'
var wb = new Workbook ( )
var ws = sheet_from_array_of_arrays ( data)
if ( merges. length > 0 ) {
if ( ! ws[ '!merges' ] ) ws[ '!merges' ] = [ ]
merges. forEach ( ( item ) => {
ws[ '!merges' ] . push ( XLSX . utils. decode_range ( item) )
} )
}
if ( autoWidth) {
const colWidth = data. map ( ( row ) =>
row. map ( ( val ) => {
if ( val == null ) {
return {
wch : 10
}
} else if ( val. toString ( ) . charCodeAt ( 0 ) > 255 ) {
return {
wch : val. toString ( ) . length * 2
}
} else {
return {
wch : val. toString ( ) . length
}
}
} )
)
let result = colWidth[ 0 ]
for ( let i = 1 ; i < colWidth. length; i++ ) {
for ( let j = 0 ; j < colWidth[ i] . length; j++ ) {
if ( result[ j] [ 'wch' ] < colWidth[ i] [ j] [ 'wch' ] ) {
result[ j] [ 'wch' ] = colWidth[ i] [ j] [ 'wch' ]
}
}
}
ws[ '!cols' ] = result
}
wb. SheetNames. push ( ws_name)
wb. Sheets[ ws_name] = ws
var wbout = XLSX . write ( wb, {
bookType : bookType,
bookSST : false ,
type : 'binary'
} )
saveAs (
new Blob ( [ s2ab ( wbout) ] , {
type : 'application/octet-stream'
} ) ,
` ${ filename} . ${ bookType} `
)
}
3、使用 (以vue3为例子)
const onConfirm = async ( ) => {
const allUser = ( await getUserAllList ( ) ) . list
const excel = await import ( '@/utils/Export2Excel' )
const data = formatJson ( USER_RELATIONS , allUser)
excel. export_json_to_excel ( {
header : Object. keys ( USER_RELATIONS ) ,
data,
filename : excelName. value || exportDefaultName,
autoWidth : true ,
bookType : 'xlsx'
} )
}
const formatJson = ( headers, rows ) => {
return rows. map ( item => {
return Object. keys ( headers) . map ( key => {
if ( headers[ key] === 'openTime' ) {
return dateFormat ( item[ headers[ key] ] )
}
if ( headers[ key] === 'role' ) {
const roles = item[ headers[ key] ]
return JSON . stringify ( roles. map ( role => role. title) )
}
return item[ headers[ key] ]
} )
} )
}
用到的一些公共函数
import dayjs from 'dayjs'
export const dateFormat = ( val, format = 'YYYY-MM-DD' ) => {
if ( isNaN ( val) ) {
return val
}
val = parseInt ( val)
return dayjs ( val) . format ( format)
}