轉(zhuǎn)載自https://www.cnblogs.com/lxk233/p/10224164.html
第一種導(dǎo)出excel無需自己設(shè)置南蹂，直接根據(jù)json生成 （缺點(diǎn)：json數(shù)據(jù)全部展示松嘶，且只能按獲取數(shù)據(jù)的順序顯示）
//json數(shù)據(jù)轉(zhuǎn)excel
function JSONToExcelConvertor(JSONData, FileName) {
//先轉(zhuǎn)化json
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = '<table>';
var row = "<tr>";
//設(shè)置表頭
var keys = Object.keys(JSONData[0]);
keys.forEach(function (item) {
row += "<td>" + item + '</td>';
});
//換行
excel += row + "</tr>";
//設(shè)置數(shù)據(jù)
for (var i = 0; i < arrData.length; i++) {
var row = "<tr>";
for (var index in arrData[i]) {
console.log(arrData[i][index]);
//var value = arrData[i][index] === "." ? "" : arrData[i][index];
row += '<td>' + arrData[i][index] + '</td>';
}
excel += row + "</tr>";
}
excel += "</table>";
var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
excelFile += '; charset=UTF-8">';
excelFile += "<head>";
excelFile += "<!--[if gte mso 9]>";
excelFile += "<xml>";
excelFile += "<x:ExcelWorkbook>";
excelFile += "<x:ExcelWorksheets>";
excelFile += "<x:ExcelWorksheet>";
excelFile += "<x:Name>";
excelFile += "{worksheet}";
excelFile += "</x:Name>";
excelFile += "<x:WorksheetOptions>";
excelFile += "<x:DisplayGridlines/>";
excelFile += "</x:WorksheetOptions>";
excelFile += "</x:ExcelWorksheet>";
excelFile += "</x:ExcelWorksheets>";
excelFile += "</x:ExcelWorkbook>";
excelFile += "</xml>";
excelFile += "<![endif]-->";
excelFile += "</head>";
excelFile += "<body>";
excelFile += excel;
excelFile += "</body>";
excelFile += "</html>";
var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = FileName + ".xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
第二種json排序恍箭，可手動設(shè)置數(shù)據(jù)顯示(缺點(diǎn)：無法對列間距進(jìn)行設(shè)置，生成的excel會擠在一起)
//導(dǎo)出訪問路徑Excel
function exportPathMethod(data) {
//要導(dǎo)出的json數(shù)據(jù)
var jsonData = [];
for(var i=0; i<data.length ; i++){
jsonData.push({
index :i+1,
title: data[i].title,
url: data[i].url,
createTime :data[i].createTime
});
}
//列標(biāo)題佛玄，逗號隔開镇眷，每一個(gè)逗號就是隔開一個(gè)單元格
let str = `序號,標(biāo)題,地址,時(shí)間\n`; //增加\t為了不讓表格顯示科學(xué)計(jì)數(shù)法或者其他格式
for(let i = 0 ; i < jsonData.length ; i++ ){
for(let item in jsonData[i]){
str+=`${jsonData[i][item] + '\t'},`;
}
str+='\n'; }
//encodeURIComponent解決中文亂碼
let uri = 'data:text/csv;charset=utf-8,\ufeff' + encodeURIComponent(str);
//通過創(chuàng)建a標(biāo)簽實(shí)現(xiàn)
var link = document.createElement("a"); link.href = uri; //對下載的文件命名
link.download = "json數(shù)據(jù)表.xls";
document.body.appendChild(link);
link.click();
}
第三種方法 是為了解決返回的json數(shù)據(jù)中一些數(shù)據(jù)不想展示給用戶時(shí)采取的措施
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<script type="text/javascript" src="js/jquery-3.3.1.min.js" ></script>
<script type="text/javascript" src="js/JSONToExcelConvertor.js" ></script>
<script type="text/javascript"> $(document).ready(function(){
$('#wwo').click(function(){ //測試的json數(shù)據(jù)
var data3=[{"id":10000,"username":"user-0","sex":"女","city":"城市-0","sign":"簽名-0","experience":255,"logins":24},
{"id":10001,"username":"user-1","sex":"男","city":"城市-1","sign":"簽名-1","experience":884,"logins":58} ,
{"id":10002,"username":"user-2","sex":"女","city":"城市-2","sign":"簽名-2","experience":650,"logins":77}] //自定義標(biāo)題欄
var title=['用戶名','性別','城市','簽名','經(jīng)驗(yàn)'] //自定義過濾欄（不需要導(dǎo)出的行）
var filter=['id','logins'] //原始導(dǎo)出
JSONToExcelConvertor(data3,"report"); //自定義導(dǎo)出
//JSONToExcelConvertor(data3,"report",title,filter);
});
}); </script>
</head>
<body>
<input type="button" id="wwo" value="導(dǎo)出" />
</body>
</html> function JSONToExcelConvertor(JSONData, FileName,title,filter) {
if(!JSONData)
return;
//轉(zhuǎn)化json為object
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = "<table>";
//設(shè)置表頭
var row = "<tr>";
if(title)
{
//使用標(biāo)題項(xiàng)
for (var i in title) {
row += "<th align='center'>" + title[i] + '</th>';
}
}
else{
//不使用標(biāo)題項(xiàng)
for (var i in arrData[0]) {
row += "<th align='center'>" + i + '</th>';
}
}
excel += row + "</tr>";
//設(shè)置數(shù)據(jù)
for (var i = 0; i < arrData.length; i++) {
var row = "<tr>";
for (var index in arrData[i]) {
//判斷是否有過濾行
if(filter)
{
if(filter.indexOf(index)==-1) {
var value = arrData[i][index] == null ? "" : arrData[i][index];
row += '<td>' + value + '</td>';
}
}
else
{
var value = arrData[i][index] == null ? "" : arrData[i][index];
row += "<td align='center'>" + value + "</td>";
}
}
excel += row + "</tr>";
}
excel += "</table>";
var excelFile = "<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='http://www.w3.org/TR/REC-html40'>";
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">';
excelFile += '<meta http-equiv="content-type" content="application/vnd.ms-excel';
excelFile += '; charset=UTF-8">';
excelFile += "<head>";
excelFile += "<!--[if gte mso 9]>";
excelFile += "<xml>";
excelFile += "<x:ExcelWorkbook>";
excelFile += "<x:ExcelWorksheets>";
excelFile += "<x:ExcelWorksheet>";
excelFile += "<x:Name>";
excelFile += "{worksheet}";
excelFile += "</x:Name>";
excelFile += "<x:WorksheetOptions>";
excelFile += "<x:DisplayGridlines/>";
excelFile += "</x:WorksheetOptions>";
excelFile += "</x:ExcelWorksheet>";
excelFile += "</x:ExcelWorksheets>";
excelFile += "</x:ExcelWorkbook>";
excelFile += "</xml>";
excelFile += "<![endif]-->";
excelFile += "</head>";
excelFile += "<body>";
excelFile += excel;
excelFile += "</body>";
excelFile += "</html>";
var uri = 'data:application/vnd.ms-excel;charset=utf-8,' + encodeURIComponent(excelFile);
var link = document.createElement("a");
link.href = uri;
link.style = "visibility:hidden";
link.download = FileName + ".xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
再次簡化：終極簡化導(dǎo)出excel（一萬條數(shù)據(jù)可在10秒內(nèi)導(dǎo)出）
//json數(shù)據(jù)轉(zhuǎn)excel
function JSONToOrderExcelConvertor(JSONData) {
var str = '序號,訂單號,訂單時(shí)間,主要用途,客戶名稱,電話,產(chǎn)品型號,是否形成有效線索\n';
for(let i=0;i<JSONData.length;i++){
var result =''; if (JSONData[i].orderStatusc=='0'){ result="是";
} else {
result="否";
}
str += (i+1).toString()+','+JSONData[i].orderId+'\t'+','+formateOrderTime(JSONData[i].orderTime)+'\t'+','+JSONData[i].p1+'\t'+','+JSONData[i].userName+'\t'+','+JSONData[i].recMobile+'\t'+','+JSONData[i].productName+'\t'+','+result+'\t'+',\n' }
var blob = new Blob([str], {type: "text/plain;charset=utf-8"}); //解決中文亂碼問題
blob = new Blob([String.fromCharCode(0xFEFF), blob], {type: blob.type});
object_url = window.URL.createObjectURL(blob); var link = document.createElement("a"); link.href = object_url; link.download = "導(dǎo)出訂單.xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
第四種、使用插件導(dǎo)出js
引入js
<script src="https://cuikangjie.github.io/JsonExportExcel/dist/JsonExportExcel.min.js"></script>
function JsonToExcel(jsonData,fileName,sheetName,sheetHeader) {
var option = {};
option.fileName = fileName;
option.datas = [
{
sheetData : jsonData,
sheetName : sheetName,
sheetHeader : sheetHeader
}
];
var toExcel=new ExportJsonExcel(option);
toExcel.saveExcel();
}
由于使用nginx 翎嫡，數(shù)據(jù)量超過倆萬條時(shí)欠动，請求時(shí)間超出nginx要求的響應(yīng)時(shí)間就會報(bào)504 鏈接超時(shí)
2021/6/21更新
轉(zhuǎn)載：https://blog.csdn.net/qq_42344946/article/details/110072473#comments_17113007
使用插件
exceljs服務(wù)端導(dǎo)出表格，瀏覽器直接用不了惑申，nodejs的運(yùn)行環(huán)境和瀏覽器的不同
exceljs.min.js 下載地址：https://www.bootcdn.cn/exceljs/具伍，git的exceljs里面沒有這個(gè)文件。
代碼和nodejs版沒什么區(qū)別
<script src="https://cdn.bootcdn.net/ajax/libs/exceljs/4.2.0/exceljs.min.js"></script>
<script>
///
async function ddd() {
console.log(111)
const wb = new ExcelJS.Workbook();
const Sheet1 = wb.addWorksheet('Sheet1');
const Sheet2 = wb.addWorksheet('Sheet2');
const test = wb.addWorksheet('test');
//表2 性別
Sheet2.columns = [{
header: '性別',
key: 'sex',
width: 20
},
{
header: '性別值',
key: 'sexVal',
width: 20
},
];
const Sheet2_data = [{
sex: '女',
sexVal: '0',
}, {
sex: '男',
sexVal: '1',
}];
Sheet2.addRows(Sheet2_data);
// 添加性別管理器
const _data = Sheet2_data.reduce((p, c) => {
if (!p.has(c.sex)) {
p.set(c.sex, [c.sexVal]);
} else {
const arr = p.get(c.sex);
arr.push(c.sexVal);
}
return p;
}, new Map());
// console.log(_data)
const sexs = Array.from(_data.keys());
const sexVals = Array.from(_data.values());
test.addRows(sexVals);
test.eachRow(function(row, i) {
const sex = sexs[i - 1];
// console.log(sex, i);
row.eachCell(function(cell, colNumber) {
cell.addName(sex);
});
});
Sheet1.columns = [{
header: '編號',
key: 'no',
width: 20
}, // A1
{
header: '姓名',
key: 'name',
width: 20
}, // B2
{
header: '性別',
key: 'sex',
width: 20
}, // C3
{
header: '性別值',
key: 'sexVal',
width: 20
}, // D4
];
const Sheet1_data = [{
no: '1',
name: '小紅',
sex: '女',
sexVal: '0',
}]
Sheet1.addRows(Sheet1_data);
///
new Array(1000).fill(0).forEach((_, idx) => {
const row = idx + 2;
// 渲染部門下拉框
Sheet1.getCell(row, 3).dataValidation = {
type: 'list',
formulae: [`=Sheet2!$A$2:$A${Sheet2_data.length+1}`]
};
// 使用indirect函數(shù)添加引用, 渲染性別值
Sheet1.getCell(row, 4).dataValidation = {
type: 'list',
formulae: [`=INDIRECT(C${row})`]
};
});
// await wb.xlsx.writeFile('C:/Users/Administrator/Desktop/dd/模板列表.xlsx');
// await wb.xlsx.writeFile('./dd.xlsx');
const buffer = await wb.xlsx.writeBuffer();
var blob = new Blob([buffer], {
type: "application/octet-stream"
});
var url = blob, saveName = 'test.xlsx';
if (typeof url == 'object' && url instanceof Blob) {
url = URL.createObjectURL(url); // 創(chuàng)建blob地址
}
var aLink = document.createElement('a');
aLink.href = url;
aLink.download = saveName || ''; // HTML5新增的屬性圈驼，指定保存文件名人芽，可以不要后綴，注意绩脆，file:///模式下不會生效
var event;
if (window.MouseEvent) event = new MouseEvent('click');
else {
event = document.createEvent('MouseEvents');
event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0,
null);
}
aLink.dispatchEvent(event);
console.log(111)
return;
// return blob;
}
ddd();
<script>
2023/5/15更新
使用xlsx與xlsx-style萤厅，導(dǎo)出帶有樣式的表格
1、安裝文件
npm install xlsx
npm install xlsx-style
npm install后xlsx-style會報(bào)錯(cuò),只需在vue.config.js中加上
externals: {
'./cptable': 'var cptable'
}
image.png
PS：這個(gè)方法也用過靴迫，取出單獨(dú)引入后仍然報(bào)錯(cuò)惕味，遂進(jìn)行上面的操作（文件保持是單獨(dú)引入的）
image.png
2、引入xlsx
import * as XLSX from 'xlsx'
3玉锌、使用
downLoadFailList() {
// 自定義下載的header名挥，注意是數(shù)組中的數(shù)組哦
const Header = [["*題目類型","*題干","*分值","*正確答案","解析","選項(xiàng)A","選項(xiàng)B","選項(xiàng)C","選項(xiàng)D","選項(xiàng)E","選項(xiàng)F","選項(xiàng)G","選項(xiàng)H",
"選項(xiàng)I","選項(xiàng)J","選項(xiàng)K","選項(xiàng)L","選項(xiàng)M","選項(xiàng)N","選項(xiàng)O","選項(xiàng)P","選項(xiàng)Q","選項(xiàng)R",
"選項(xiàng)S","選項(xiàng)T","選項(xiàng)U","選項(xiàng)V","選項(xiàng)W","選項(xiàng)X","選項(xiàng)Y","選項(xiàng)Z"]];
// 需要導(dǎo)出的數(shù)據(jù)
var exportArr = [];
for (var o of this.failList) {
var item = {};
for (var j of Header[0]) {
if (o[j]) {
item[j] = o[j];
}else{
item[j] = "";
}
}
exportArr.push(item);
}
// 將JS數(shù)據(jù)數(shù)組轉(zhuǎn)換為工作表。
const headerWs = XLSX.utils.aoa_to_sheet([["導(dǎo)入失敗提示：格式錯(cuò)誤主守，無法導(dǎo)入禀倔，請仔細(xì)閱讀模板導(dǎo)入說明"]]); //固定的頭部提示
const ws = XLSX.utils.sheet_add_json(headerWs, exportArr, {skipHeader: false, origin: 'A2'});
ws["A1"].s = { font: { sz: 14, color: { rgb: "FFE10001" } } }; //設(shè)置單元格顏色
console.log(ws);
/* 新建空的工作表 */
const wb = XLSX.utils.book_new();
// 可以自定義下載之后的sheetname
XLSX.utils.book_append_sheet(wb, ws, '失敗記錄');
/* 生成xlsx文件 */
//這里要用XLSXStyle的write方法ｉ凇！＞群＠⒈！Ｐ取Ａ拧！Ｌ尉取Ｎ沸稀Ｒ蛋恰＜爝骸！３檀ⅰ２渑妗！章鲤！
var wbout = XLSXStyle.write(wb,{type: 'buffer'});
var blob = new Blob([wbout], {
type: 'application/octet-stream',
}); // 字符串轉(zhuǎn)ArrayBuffer
if ("download" in document.createElement("a")) {
// 非IE下載
const elink = document.createElement("a");
elink.download = "失敗記錄.xlsx";
elink.style.display = "none";
elink.href = URL.createObjectURL(blob);
document.body.appendChild(elink);
elink.click();
URL.revokeObjectURL(elink.href); // 釋放URL 對象
document.body.removeChild(elink);
} else {
// IE10+下載
navigator.msSaveBlob(blob, "失敗記錄.xlsx");
}
},
