vue导出excel(多个sheet表)

曾经终败给现在 2022-11-28 10:36 1119阅读 0赞

（一）直接调用

（二）、封装导出函数调用

最近做项目中有一个导出excel表格的需求

具体思路是：后端返回给我json数据，前端根据数据和具体的几项字段去导出excel表格，还有导出多个sheet，多页表格到一个excel表里面，具体思路 根据Export2Excel插件，并修改插件Export2Excel完成导出多页(多个sheet)的excel

第一步：安装插件 依赖

npm install file-saver --save  
npm install xlsx --save  
npm install script-loader --save-dev  
第二步、下载两个所需要的js文件Blob.js和 Export2Excel.js。

下载地址 微信扫描二维码下载

第三步、src目录下新建vendor文件夹，将Blob.js和 Export2Excel.js放进去。

（一）直接调用  
第一步，在.vue中调用

//引入字段过滤器时间过滤  
import \{ parseTime \} from “@/utils/setMethods”

handleDownload () \{  
var excelDatas = \[  
\{  
tHeader: \[“Id”, “Title”, “Author”, “Readings”, “Date”\], // sheet表一头部  
filterVal: \[“id”, “title”, “author”, “pageviews”, “display\_time”\], // 表一的数据字段  
tableDatas: this.list, // 表一的整体json数据  
sheetName: “sheet1”// 表一的sheet名字  
\},  
\{  
tHeader: \[“序号”, “标题”, “作者”, “服务”\],  
filterVal: \[“id”, “title”, “author”, “reviewer”\],  
tableDatas: this.list,  
sheetName: “sheet2”  
\},  
\{  
tHeader: \[“序号”, “名字”, “描述”\],  
filterVal: \[“userId”, “userName”, “userDescription”\],  
tableDatas: this.rolesList,  
sheetName: “sheet5555”  
\}  
\]

this.json2excel(excelDatas, "数据报表", true, "xlsx")
    },
    // tableJson 导出数据 ; filenames导出表的名字; autowidth表格宽度自动 true or false; bookTypes xlsx & csv & txt
    json2excel (tableJson, filenames, autowidth, bookTypes) {
      var that = this

//这个是引用插件  
import("@/vendor/Export2Excel").then(excel => \{  
var tHeader = \[\]  
var dataArr = \[\]  
var sheetnames = \[\]  
for (var i in tableJson) \{  
tHeader.push(tableJson\[i\].tHeader)  
dataArr.push(that.formatJson(tableJson\[i\].filterVal, tableJson\[i\].tableDatas))  
sheetnames.push(tableJson\[i\].sheetName)  
\}  
excel.export\_json\_to\_excel(\{  
header: tHeader,  
data: dataArr,  
sheetname: sheetnames,  
filename: filenames,  
autoWidth: autowidth,  
bookType: bookTypes  
\})  
\})  
\},  
// 数据过滤，时间过滤  
formatJson (filterVal, jsonData) \{  
return jsonData.map(v => filterVal.map(j => \{  
if (j === “timestamp”) \{  
return parseTime(v\[j\])  
\} else \{  
return v\[j\]  
\}  
\}))  
\}  
第二步：修改插件Export2Excel导出多个sheet表格，导出多页

修改过的 Export2Excel.js 主要修改 export\_json\_to\_excel 函数

//Export2Excel.js

/\* 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 Value
      outRow.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,  
sheetname,  
filename,  
merges = \[\],  
autoWidth = true,  
bookType = “xlsx”  
\} = \{\}) \{  
/\* original data \*/  
filename = filename || “excel-list”  
data = \[…data\]

for (var i = 0; i < header.length; i++) \{  
data\[i\].unshift(header\[i\])  
\}

// data.unshift(header)

for (let i = multiHeader.length - 1; i > -1; i–) \{  
data.unshift(multiHeader\[i\])  
\}

var ws\_name = sheetname  
var wb = new Workbook(),  
ws = \[\]  
for (var j = 0; j < header.length; j++) \{  
ws.push(sheet\_from\_array\_of\_arrays(data\[j\]))  
\}

if (merges.length > 0) \{  
if (!ws\["!merges"\]) ws\["!merges"\] = \[\]  
merges.forEach(item => \{  
ws\["!merges"\].push(XLSX.utils.decode\_range(item))  
\})  
\}  
// console.log(“width”, autoWidth)  
if (autoWidth) \{  
/*设置worksheet每列的最大宽度*/  
var colWidth = \[\]  
for (var k = 0; k < header.length; k++) \{  
colWidth.push(  
data\[k\].map(row =>  
row.map(val => \{  
/*先判断是否为null/undefined*/  
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 = []
    for (var k = 0; k < colWidth.length; k++) {
      result[k] = colWidth[k][0]
      for (let i = 1; i < colWidth[k].length; i++) {
        for (let j = 0; j < colWidth[k][i].length; j++) {
          if (result[k][j]["wch"] < colWidth[k][i][j]["wch"]) {
            result[k][j]["wch"] = colWidth[k][i][j]["wch"]
          }
        }
      }
    }
    // 分别给sheet表设置宽度
    for (var l = 0; l < result.length; l++) {
      ws[l]["!cols"] = result[l]
    }

/\* add worksheet to workbook \*/  
for (var k = 0; k < header.length; k++) \{  
wb.SheetNames.push(ws\_name\[k\])  
wb.Sheets\[ws\_name\[k\]\] = ws\[k\]  
\}

var wbout = XLSX.write(wb, \{  
bookType: bookType,  
bookSST: false,  
type: “binary”  
\})  
saveAs(  
new Blob(\[s2ab(wbout)\], \{  
type: “application/octet-stream”  
\}),  
`${filename}.${bookType}`  
)  
\}  
Export2Excel.js 原文件

// Export2Excel.js  
/\* 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 Value
      outRow.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,  
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) \{  
/*设置worksheet每列的最大宽度*/  
const colWidth = data.map(row => row.map(val => \{  
/*先判断是否为null/undefined*/  
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;  
\}

/\* 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}`);  
\}

（二）、封装导出函数调用  
在src中新建utils文件夹 ——> 新建文件setMethods.js

setMethods.js

/\*\*

*  Parse the time to string
 *  @param \{(Object|string|number)\} time
 *  @param \{string\} cFormat
 *  @returns \{string\}  
    \*/  
    export function parseTime (time, cFormat) \{  
    if (arguments.length === 0) \{  
    return null  
    \}  
    const format = cFormat || “\{y\}-\{m\}-\{d\} \{h\}:\{i\}:\{s\}”  
    let date  
    if (typeof time === “object”) \{  
    date = time  
    \} else \{  
    if (typeof time === “string” && /1\+$/.test(time)) \{  
    time = parseInt(time)  
    \}  
    if (typeof time === “number” && time.toString().length === 10) \{  
    time = time \* 1000  
    \}  
    date = new Date(time)  
    \}  
    const formatObj = \{  
    y: date.getFullYear(),  
    m: date.getMonth() + 1,  
    d: date.getDate(),  
    h: date.getHours(),  
    i: date.getMinutes(),  
    s: date.getSeconds(),  
    a: date.getDay()  
    \}  
    // eslint-disable-next-line  
    const time\_str = format.replace(/\{(y|m|d|h|i|s|a)+\}/g, (result, key) => \{  
    let value = formatObj\[key\]  
    // Note: getDay() returns 0 on Sunday  
    if (key === “a”) \{  
    return \[“日”, “一”, “二”, “三”, “四”, “五”, “六”\]\[value\]  
    \}  
    if (result.length > 0 && value < 10) \{  
    value = “0” + value  
    \}  
    return value || 0  
    \})  
    // eslint-disable-next-line  
    return time\_str  
    \}

/\*\*

*  Parse the json to excel
 *  tableJson 导出数据 ; filenames导出表的名字; autowidth表格宽度自动 true or false; bookTypes xlsx & csv & txt
 *  @param \{(Object)\} tableJson
 *  @param \{string\} filenames
 *  @param \{boolean\} autowidth
 *  @param \{string\} bookTypes  
    \*/  
    export function json2excel (tableJson, filenames, autowidth, bookTypes) \{  
    import("@/vendor/Export2Excel").then(excel => \{  
    var tHeader = \[\]  
    var dataArr = \[\]  
    var sheetnames = \[\]  
    for (var i in tableJson) \{  
    tHeader.push(tableJson\[i\].tHeader)  
    dataArr.push(formatJson(tableJson\[i\].filterVal, tableJson\[i\].tableDatas))  
    sheetnames.push(tableJson\[i\].sheetName)  
    \}  
    excel.export\_json\_to\_excel(\{  
    header: tHeader,  
    data: dataArr,  
    sheetname: sheetnames,  
    filename: filenames,  
    autoWidth: autowidth,  
    bookType: bookTypes  
    \})  
    \})  
    \}  
    // 数据过滤，时间过滤  
    function formatJson (filterVal, jsonData) \{  
    return jsonData.map(v =>  
    filterVal.map(j => \{  
    if (j === “timestamp”) \{  
    return parseTime(v\[j\])  
    \} else \{  
    return v\[j\]  
    \}  
    \})  
    )  
    \}  
    在需要使用到的 .vue文件组件中调用上面封装的函数

//exportExcel.vue

亲测有效！！ 欢迎留言交流

--------------------

1.  0-9 ↩︎