转载自https://www.cnblogs.com/lxk233/p/10224164.html
第一种导出excel无需自己设置，直接根据json生成 （缺点：json数据全部展示，且只能按获取数据的顺序显示）
//json数据转excel
function JSONToExcelConvertor(JSONData, FileName) {
//先转化json
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = '<table>';
var row = "<tr>";
//设置表头
var keys = Object.keys(JSONData[0]);
keys.forEach(function (item) {
row += "<td>" + item + '</td>';
});
//换行
excel += row + "</tr>";
//设置数据
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排序，可手动设置数据显示(缺点：无法对列间距进行设置，生成的excel会挤在一起)
//导出访问路径Excel
function exportPathMethod(data) {
//要导出的json数据
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
});
}
//列标题，逗号隔开，每一个逗号就是隔开一个单元格
let str = `序号,标题,地址,时间\n`; //增加\t为了不让表格显示科学计数法或者其他格式
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);
//通过创建a标签实现
var link = document.createElement("a"); link.href = uri; //对下载的文件命名
link.download = "json数据表.xls";
document.body.appendChild(link);
link.click();
}
第三种方法 是为了解决返回的json数据中一些数据不想展示给用户时采取的措施
<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数据
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}] //自定义标题栏
var title=['用户名','性别','城市','签名','经验'] //自定义过滤栏（不需要导出的行）
var filter=['id','logins'] //原始导出
JSONToExcelConvertor(data3,"report"); //自定义导出
//JSONToExcelConvertor(data3,"report",title,filter);
});
}); </script>
</head>
<body>
<input type="button" id="wwo" value="导出" />
</body>
</html> function JSONToExcelConvertor(JSONData, FileName,title,filter) {
if(!JSONData)
return;
//转化json为object
var arrData = typeof JSONData != 'object' ? JSON.parse(JSONData) : JSONData;
var excel = "<table>";
//设置表头
var row = "<tr>";
if(title)
{
//使用标题项
for (var i in title) {
row += "<th align='center'>" + title[i] + '</th>';
}
}
else{
//不使用标题项
for (var i in arrData[0]) {
row += "<th align='center'>" + i + '</th>';
}
}
excel += row + "</tr>";
//设置数据
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);
}
再次简化：终极简化导出excel（一万条数据可在10秒内导出）
//json数据转excel
function JSONToOrderExcelConvertor(JSONData) {
var str = '序号,订单号,订单时间,主要用途,客户名称,电话,产品型号,是否形成有效线索\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 = "导出订单.xls";
document.body.appendChild(link);
link.click();
document.body.removeChild(link);
}
第四种、使用插件导出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 ，数据量超过俩万条时，请求时间超出nginx要求的响应时间就会报504 链接超时
2021/6/21更新
转载：https://blog.csdn.net/qq_42344946/article/details/110072473#comments_17113007
使用插件
exceljs服务端导出表格，浏览器直接用不了，nodejs的运行环境和浏览器的不同
exceljs.min.js 下载地址：https://www.bootcdn.cn/exceljs/，git的exceljs里面没有这个文件。
代码和nodejs版没什么区别
<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函数添加引用, 渲染性别值
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); // 创建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，导出带有样式的表格
1、安装文件
npm install xlsx
npm install xlsx-style
npm install后xlsx-style会报错,只需在vue.config.js中加上
externals: {
'./cptable': 'var cptable'
}
image.png
PS：这个方法也用过，取出单独引入后仍然报错，遂进行上面的操作（文件保持是单独引入的）
image.png
2、引入xlsx
import * as XLSX from 'xlsx'
3、使用
downLoadFailList() {
// 自定义下载的header，注意是数组中的数组哦
const Header = [["*题目类型","*题干","*分值","*正确答案","解析","选项A","选项B","选项C","选项D","选项E","选项F","选项G","选项H",
"选项I","选项J","选项K","选项L","选项M","选项N","选项O","选项P","选项Q","选项R",
"选项S","选项T","选项U","选项V","选项W","选项X","选项Y","选项Z"]];
// 需要导出的数据
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数据数组转换为工作表。
const headerWs = XLSX.utils.aoa_to_sheet([["导入失败提示：格式错误，无法导入，请仔细阅读模板导入说明"]]); //固定的头部提示
const ws = XLSX.utils.sheet_add_json(headerWs, exportArr, {skipHeader: false, origin: 'A2'});
ws["A1"].s = { font: { sz: 14, color: { rgb: "FFE10001" } } }; //设置单元格颜色
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',
}); // 字符串转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");
}
},
