js-xlsx相关教程:
纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例
纯前端利用 js-xlsx 实现 Excel 文件导入导出功能示例(2)
纯前端利用 js-xlsx(3) 之合并单元格
纯前端利用 js-xlsx 之单元格样式(4)
js-xlsx导出自定义合并列头实现思路
js-xlsx工具类库 xlsxUtils 使用示例
handsontable结合js-xlsx实现可编辑xlsx导入导出功能(参考)
js-xlsx异步文件流读取示例
两种使用原生js实现导出excel数据简单讲解
js-xlsx导入CSV文件中文乱码处理参考xlsxUtils
函数说明:
var xlsxUtils = {
Binary: {
fixdata(data) { //文件流转BinaryString
//......
},
s2ab(s) { //字符串转字符流
//......
}
},
_wb: null,//缓存导入时的Workbook
_rABS: false,//设置读取方式
/**
* @desc 导入根据文件
* @param {File} f 文件
* @param {Function} c 回调
* @return {Object} 回调值
*/
import(f, c) {
//......
},
/**
* @desc 根据表Sheet名获取数据
* @param {String} name
* @return {Object}
*/
getSheetByName(name) {//
//......
},
/**
* @desc 根据表Sheet索引获取数据
* @param {Number} index
* @return {Object}
*/
getSheetByIndex(index = 0) {
//......
},
/**
* @desc 导出
* @param {Array} data 数据{title1:dataList,title2:dataList....}
* @param {String} type
* @return {Blob}
*/
export(data, type) {
//......
},
/**
* 从数据数组或对象中根据key生成相同key值的对象
* @param {Object|Array} data
* @return {Object}
*/
readDataHead(data) {
//......
},
/**
* @desc 格式化数据为Sheet格式
* @param {Array} json 数据
* @param {Number} n
* @param {Number} r
* @param {Array} keyMap
*/
format2Sheet(json, n, r, keyMap) {
//......
},
/**
* @desc 格式化数据为Sheet格式
* @param {Array} sheetData
* @param {String} title
* @param {Object} wb
* @param {Object} ref
*/
format2WB(sheetData, title = "mySheet", wb,ref) {
//......
},
/**
* @desc 将xlsx Workbook 转为blob
* @param {Array} wb
* @param {String} type 类型
*/
format2Blob(wb, type) {
//......
},
/**
* @desc 匹配单元格对应的标识
* @param {Number} n
*/
getCharCol(n) {
//......
},
};
示例:
导入示例(在线示例):
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script>
<script src="./xlsx.utils.js"></script>
</head>
<body>
<input type="file" id="demo" />
<div id="loadInfo"></div>
<div>
<div>
<label>SheetNames</label>:
<span id="SheetNames"></span>
</div>
<div>
<label id="demoName"></label>(示例):
<span id="demoData"></span>
</div>
</div>
<script>
var demo = document.getElementById("demo");
demo.onchange = function () {
let f = this.files[0];
let loadInfo = document.getElementById("loadInfo");
let demoName = document.getElementById("demoName");
let demoData = document.getElementById("demoData");
loadInfo.innerHTML = "正在读取请骚等";
demoName.innerHTML = "";
demoData.innerHTML = "";
xlsxUtils.import(f, (w) => {
document.getElementById("SheetNames").innerHTML = JSON.stringify(w.SheetNames);
demoName.innerHTML = w.SheetNames[0];
demoData.innerHTML = JSON.stringify(xlsxUtils.getSheetByIndex(0));
loadInfo.innerHTML = "读取完成";
});
};
</script>
</body>
</html>
导出示例(在线示例)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>demo</title>
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script>
<script src="xlsx.utils.min.js"></script>
<script src="saveAs.min.js"> </script>
</head>
<body>
<script>
var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];
</script>
<button id="down">导出</button>
<script>
var down = document.getElementById("down");
down.onclick = function () {
data.unshift(xlsxUtils.readDataHead(data));
var blob = xlsxUtils.export({ "Sheet1": data, "Sheet2": [{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }] });
saveAs(URL.createObjectURL(blob), "aa.xlsx");
};
</script>
</body>
</html>
自定义起始列导出示例(在线示例)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>demo</title>
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script>
<script src="xlsx.utils.min.js"></script>
<script src="saveAs.min.js"> </script>
</head>
<body>
<script>
var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];
</script>
<button id="down">导出</button>
<script>
var down = document.getElementById("down");
down.onclick = function () {
data.unshift(xlsxUtils.readDataHead(data));
var tmpdata = xlsxUtils.format2Sheet(data, 2);
var tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet1");
tmpdata = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }], 4);
tmpWB = xlsxUtils.format2WB(tmpdata, "Sheet2", tmpWB);
var blob = xlsxUtils.format2Blob(tmpWB);
saveAs(URL.createObjectURL(blob), "aa.xlsx");
};
</script>
</body>
</html>
多数据合并导出到单表示例(在线示例)
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>demo</title>
<script src="http://oss.sheetjs.com/js-xlsx/xlsx.core.min.js"></script>
<script src="xlsx.utils.min.js"></script>
<script src="saveAs.min.js"> </script>
</head>
<body>
<script>
var data = [{ 主页: "111", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 主页: "433", 名称: "6800", 数量: "6800", 昵称: "广告主网" }, { 名称: "22", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 名称: "43", 商家: "6800", 数量: "6800", 昵称: "广告主网", }, { 店家: "43", 价格: "6800", 数量: "6800", 昵称: "广告主网", }];
</script>
<button id="down">导出</button>
<script>
var down = document.getElementById("down");
down.onclick = function () {
var d1 = xlsxUtils.format2Sheet([{ "a": "A", "b": "B" }, { "a": 1, "b": "2" }, { "a": 3, "b": 4 }, { "a": 5, "b": 6 }]);
var h=xlsxUtils.readDataHead(data);//读取data的列头数据
data.unshift(h);//将列头追加到最data的前
var em={};//用于换行
Object.keys(h).forEach((v)=>{
em[v]="";
});
for(var i=0;i<5;i++){//换5行
data.unshift(em);
}
var d2 = xlsxUtils.format2Sheet(data, 3);//3代表从D列开始
d1=Object.assign(d1,d2);
var tmpWB = xlsxUtils.format2WB(d1, "Sheet1");
var blob = xlsxUtils.format2Blob(tmpWB);
saveAs(URL.createObjectURL(blob), "aa.xlsx");
};
</script>
</body>
</html>
网友评论