Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”.
A journey of a thousand miles begins with single step
background
In our company’s projects, the most commonly used method is to find out the data according to the conditions, and then use the el-table to display the data. Since the data is displayed in the table, it is inevitable to export the data. Although generally in the company, the export data is processed by the back-end and returned to the front-end address for download, but due to the high cost of the back-end staff of our company, this work is completed by the front-end. After searching on the Internet, I found that Export2Excel export is very simple, so I started to use it. This is now the most used feature in our project.
The installation
-S===--save-D===--save-dev
# use npm
npm install file-saver -S
npm install xlsx -S
npm install script-loader -D
# use yarn
yarn add file-saver xlsx
yarn add file-saver script-loader --dev
Copy the codeThe introduction ofExport2Excelfile
Download the Export2Excel. Js file from the Internet and place it in the appropriate place in your project.
Code address: gitee.com/yunxii/vue-…
The code is as follows:
/* eslint-disable */
require('script-loader! file-saver')
import XLSX from 'xlsx'
function generateArray(table) {
var out = []
var rows = table.querySelectorAll('tr')
var ranges = []
for (var R = 0; R < rows.length; ++R) {
var outRow = []
var row = rows[R]
var columns = row.querySelectorAll('td')
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C]
var colspan = cell.getAttribute('colspan')
var rowspan = cell.getAttribute('rowspan')
var cellValue = cell.innerText
if(cellValue ! = =' ' && cellValue == +cellValue) cellValue = +cellValue
//Skip ranges
ranges.forEach(function(range) {
if (
R >= range.s.r &&
R <= range.e.r &&
outRow.length >= range.s.c &&
outRow.length <= range.e.c
) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null)}})//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1
colspan = colspan || 1
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1.c: outRow.length + colspan - 1}})}//Handle ValueoutRow.push(cellValue ! = =' ' ? cellValue : null)
//Handle Colspan
if (colspan) for (var k = 0; k < colspan - 1; ++k) outRow.push(null)
}
out.push(outRow)
}
return [out, ranges]
}
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 function export_table_to_excel(id) {
var theTable = document.getElementById(id)
var oo = generateArray(theTable)
var ranges = oo[1]
/* original data */
var data = oo[0]
var ws_name = 'SheetJS'
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data)
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['! merges'] = ranges
/* add worksheet to workbook */
wb.SheetNames.push(ws_name)
wb.Sheets[ws_name] = ws
var wbout = XLSX.write(wb, {
bookType: 'xlsx'.bookSST: false.type: 'binary'
})
saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
}),
'test.xlsx')}export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
excelWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
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(),
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) {
/* Sets the maximum width of each column of the worksheet */
const colWidth = data.map(row= >
row.map(val= > {
/* Check whether the value is null/undefined*/
if (val == null) {
return {
wch: 10}}else if (val.toString().charCodeAt(0) > 255) {
/* Check whether it is Chinese */
return {
wch: val.toString().length * 2}}else {
return {
wch: val.toString().length
}
}
})
)
/* starts with the first behavior */
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
}
if (excelWidth) {
/* Sets the maximum width of each column of the worksheet */
const colWidth = data.map(row= >
row.map(val= > {
/* Check whether the value is null/undefined*/
if (val == null) {
return {
wch: 10}}else if (val.toString().charCodeAt(0) > 255) {
/* Check whether it is Chinese */
return {
wch: 20}}else if (val.toString().indexOf("<operation")! = = -1) {
/* Check whether it is packet data */
return {
wch: 30}}else {
return {
wch: val.toString().length
}
}
})
)
/* starts with the first behavior */
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
}
/* add worksheet to workbook */
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}`)}Copy the codeuse
Create a VUE file in your project, configure the route for testing, and use the elementUI component library in your project
In the template
<el-button type="primary" icon="el-icon-download" @click="onExcelDown">Copy the codeWrite exported methods in methods
async onExcelDown() {
this.$message({ type: 'warning'.message: 'Data reading, please wait.' })
this.loading = true
// Get the back-end interface data here
await this.getExportData()
this.loading = false
const filename = 'Report data'
const autoWidth = true
const bookType = 'xlsx'
const header = [ 'Point in time requirements'.'Report Name'.'Demand room'.'maintenance'.'BOSS']
const headValue = [ 'ask'.'reportName'.'xqs'.'whs'.'boss''] const formatJson = (headValue, jsonData) => { return jsonData.map((v) => headValue.map((j) => v[j])) } this.$confirm('Determine what to export', 'prompt', { cancelButtonClass: 'btn-custom-cancel', confirmButtonText: 'determine', cancelButtonText: 'cancel', type: 'warning'}).then(() => {// import local Export2Excel. Js import('@/vendor/Export2Excel.js'). Then (moudle => {moudle. Export_json_to_excel ({header, // this. formatJson(headValue, this.exportData), filename, autoWidth, bookType }) }) }).catch(() => { this.$message({ type: 'info', message: 'Canceled export'})})},Copy the codeResults the following
Case code
Export2Excel use code:
Gitee.com/yunxii/vue-…