2023-11-07 最近更新
相关的视频教程 https://www.bilibili.com/video/BV1uK4y1Z7uw?share_source=copy_web
导入
<!doctype html>
<html>
<head>
<meta charset="UTF-8">
<title>上传文件</title>
<script type="text/javascript" src='./xlsx.core.min.js'></script>
<link href="./bootstrap.min.css" rel="stylesheet" />
<style>
.lqwvje-btn {
display: inline-block;
height: 38px;
line-height: 38px;
padding: 0 18px;
background-color: #009688;
color: #fff;
white-space: nowrap;
text-align: center;
font-size: 14px;
border: none;
border-radius: 2px;
cursor: pointer;
}
</style>
</head>
<body>
<label id="realBtn">
<input type="file" id="testFile" style="display:none">
上传文件
</label>
<script type="text/javascript">
window.onload = function () {
document.getElementById("testFile").addEventListener("click", function () {
const dt = new DataTransfer();
const input = document.getElementById('testFile');
input.files = dt.files;//清空input file
});
//原创来自 www.luofenming.com
//首先监听input框的变动，选中一个新的文件会触发change事件
document.querySelector("#testFile").addEventListener("change", function () {
//获取到选中的文件
var file = document.querySelector("#testFile").files[0];
if (file.size > 1024 * 1024) {
alert('当前文件大小:' + Math.floor(file.size / 1024) + 'KB,上传文件不能大于1024KB');
return false;
}
var type = file.name.split('.');
if (type[type.length - 1] !== 'xlsx' && type[type.length - 1] !== 'xls') {
alert('只能选择excel文件导入');
return false;
}
const reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = function (e) {
const data = e.target.result;
const zzexcel = window.XLS.read(data, {
type: 'binary'
});
const result = [];
for (let i = 0; i < zzexcel.SheetNames.length; i++) {
const newData = window.XLS.utils.sheet_to_json(zzexcel.Sheets[zzexcel.SheetNames[i]]);
result.push(...newData)
}
console.log(JSON.stringify(result));
//for (var i = 0; i < result.length; i++) {
// console.log(JSON.stringify(result[i]));
//}
//这里可以写执行 post发送到服务端
}
});
}
</script>
</body>
</html>导出
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>js导出excel</title>
<script type="text/javascript" src="./xlsx.core.min.js"></script>
</head>
<body>
<button onclick="downloadExl(jsono)">导出</button>
<script>
var jsono = [{
"姓名": "张三",
"年龄": "30",
"性别": "男"
}, {
"姓名": "李四",
"年龄": "40",
"性别": "女"
}, {
"姓名": "王五",
"年龄": "50",
"性别": "男"
}];
var tmpDown; //导出的二进制对象
function downloadExl(json, type) {
//根据json数据，获取excel的第一行(例如:姓名、年龄、性别)存至map
var tmpdata = json[0];
json.unshift({});
var keyMap = []; //获取keys
for (var k in tmpdata) {
keyMap.push(k);
json[0][k] = k;
}
var tmpdata = [];
json.map((v, i) => keyMap.map((k, j) => Object.assign({}, {
v: v[k],
position: (j > 25 ? getCharCol(j) : String.fromCharCode(65 + j)) + (i + 1)
}))).reduce((prev, next) => prev.concat(next)).forEach((v, i) => tmpdata[v.position] = {
v: v.v
});
//设置区域,比如表格从A1到D10
var outputPos = Object.keys(tmpdata);
var tmpWB = {
SheetNames: [], //保存的表标题
Sheets: { }
};
var sheetName = '保存的sheet名字';
tmpWB.SheetNames.push(sheetName);
tmpWB.Sheets[sheetName] = Object.assign({},
tmpdata, //内容
{ '!ref': outputPos[0] + ':' + outputPos[outputPos.length - 1] }//设置填充区域
);
//创建二进制对象写入转换好的字节流
tmpDown = new Blob([s2ab(XLSX.write(tmpWB,
{ bookType: (type == undefined ? 'xlsx' : type), bookSST: false, type: 'binary' }//这里的数据是用来定义导出的格式类型
))], {
type: ""
});
var href = URL.createObjectURL(tmpDown); //创建对象超链接
var aLink = document.createElement('a');
aLink.href = href;//绑定a标签
aLink.download = '我是导出来的测试文件.xlsx';
aLink.click();//模拟点击实现下载
setTimeout(function () { //延时释放
URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
//字符串转字符流
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;
}
//将指定的自然数转换为26进制表示。映射关系：[0-25] -> [A-Z]。
function getCharCol(n) {
let temCol = '',
s = '',
m = 0
while (n > 0) {
m = n % 26 + 1
s = String.fromCharCode(m + 64) + s
n = (n - m) / 26
}
return s
}
</script>
</body>
</html>导出带单元格合并的excel
<!DOCTYPE html>
<html lang="zh-cn">
<head>
<meta charset="UTF-8">
<title>JS读取和导出excel示例</title>
<script type="text/javascript" src="./xlsx.core.min.js"></script>
</head>
<body>
<input type="button" value="导出带单元格合并的excel" onclick="exportSpecialExcel()" />
<script type="text/javascript">
// 将一个sheet转成最终的excel文件的blob对象，然后利用URL.createObjectURL下载
function sheet2blob(sheet, sheetName) {
sheetName = sheetName || 'sheet1';
var workbook = {
SheetNames: [sheetName],
Sheets: {}
};
workbook.Sheets[sheetName] = sheet;
// 生成excel的配置项
var wopts = {
bookType: 'xlsx', // 要生成的文件类型
bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
type: 'binary'
};
var wbout = XLSX.write(workbook, wopts);
var blob = new Blob([s2ab(wbout)], { type: "application/octet-stream" });
// 字符串转ArrayBuffer
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;
}
return blob;
}
function exportSpecialExcel() {
var aoa = [
['主要信息', null, null, '其它信息'], // 特别注意合并的地方后面预留2个null
['姓名', '性别', '年龄', '注册时间'],
['张三', '男', 18, new Date()],
['李四', '女', 22, new Date()]
];
var sheet = XLSX.utils.aoa_to_sheet(aoa);
sheet['!merges'] = [
// 设置A1-C1的单元格合并
{ s: { r: 0, c: 0 }, e: { r: 0, c: 2 } }
];
var tmpDown = sheet2blob(sheet, '我是导出带单元格合并的excel')
var href = URL.createObjectURL(tmpDown); //创建对象超链接
var aLink = document.createElement('a');
aLink.href = href;//绑定a标签
aLink.download = '我是导出带单元格合并的excel.xlsx';
aLink.click();//模拟点击实现下载
setTimeout(function () { //延时释放
URL.revokeObjectURL(tmpDown); //用URL.revokeObjectURL()来释放这个object URL
}, 100);
}
</script>
</body>
</html>xlsx.core.min.js 链接: https://pan.baidu.com/s/1KMv0jWUrDqux8Rumlz3OXQ 提取码: r1np