在做系统后台的时候，一般Excel表导出的方案是在服务器生成Excel表，返回给浏览器Excel文件地址，触发下载。当要导出的Excel行数达到数万行时，占用大量的服务器CPU或内存不说，还容易请求超时，还要改nginx配置。我就想，能否在前端浏览器创建Excel表呢？之后找到一个合适的js库可以实现这个功能：js-xlsx。
官方github地址：https://github.com/SheetJS/js-xlsx
实现思路
1、 浏览器创建xlsx
2、 每次ajax向后端请求若干行的数据，直到请求完成
3、 触发下载
本文省略php,web服务器等环境搭建步骤。看懂接下来代码实现，需要有一些js,html,PHP,ajax的一些相关知识。
1、js-xlsx库安装使用
本文使用npm安装js-xlsx,如果不用npm，可以直接去https://github.com/SheetJS/js-xlsx/releases下载压缩包(很卡,我的网下不动)。
创建项目目录后，执行以下命令，下载js-xlsx包：
npm init
npm install xlsx
创建index.html在header中引入
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>demo</title>
<script src="node_modules/xlsx/jszip.js"></script>
<script src="node_modules/xlsx/xlsx.js"></script>
</head>
2、创建html页面元素
index.html
<body>
<body>
<!-- 进度显示-->
<span>导出进度:</span><span id="progressText">0%</span>
<br>
<!-- 开始导出按钮-->
<input type="button" id="startBtn" value="开始导出">
</body>
</body>
浏览器显示：
image.png
3、添加js代码，初始化excel工作簿全局变量/excel sheet全局变量，绑定按钮点击事件
<script>
//每次请求多少行数据
const ROWS_PER_REQ = 1000;
//总行数
let totalRows = 0;
//excel工作簿全局变量
let workBook = null;
//excel sheet全局变量
let workSheet = null;
//开始导出按钮绑定事件
document.getElementById("startBtn").addEventListener('click',()=>{
//初始化
initWorkBook();
console.log("导出开始!");
getTotal();
});
function initWorkBook(){
//创建excel工作簿对象
workBook = XLSX.utils.book_new();
//创建excel第一行，头部
let headArr = [
['第一列', '第二列', '第三列', '第四列', '第五列', '时间'],
];
//根据头部数组创建excel sheet
workSheet = XLSX.utils.aoa_to_sheet(headArr);
//把excel sheet添加到工作簿
XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
}
getTotal()函数见下章。
这里的js-xlsx库的几个用法解释:
XLSX.utils.book_new();创建并返回一个excel工作簿对象，可以理解为excel文件
XLSX.utils.aoa_to_sheet(headArr);接受一个二维数组作为参数，创建一个excel sheet对象(一个excel标签页)
XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
把excel sheet对象添加到工作簿对象,并命名为'sheet1'
4、发送Ajax请求从服务器获取数据总行数
index.html:
//发送Ajax请求从服务器获取数据总行数
function getTotal() {
let xhr = new XMLHttpRequest();
xhr.open('GET', 'get_data.php?getTotal=true');
xhr.responseType = "json";
xhr.onload = function(){
//接收到总行数
totalRows = xhr.response.total;
console.log("总行数:",totalRows);
//开始接收数据
getRowsFromServer(0, ROWS_PER_REQ);
};
xhr.send();
}
获取到总行数后，保存到全局变量totalRows, getRowsFromServer函数见下章
后端get_data.php:
<?php
//设置返回json头
header('Content-type: application/json');
//总行数
const TOTAL_ROW = 50000;
//获取总行数请求
if(isset($_GET['getTotal'])){
//此处一般情况是从数据库获取总行数，这里为了简化直接返回
$json = json_encode([
'total'=>TOTAL_ROW,
]);
echo $json;
exit;
}
5、使用递归依次获取数据
index.html
/**发送Ajax请求分批从服务器拿数据
*
* @param offset 从第几行数据开始
* @param limit 每次取出多少行
*/
function getRowsFromServer(offset, limit) {
let xhr = new XMLHttpRequest();
//拼接get字段
xhr.open('GET', `get_data.php?offset=${offset}&limit=${limit}`);
xhr.responseType = "json";
//接收到数据
xhr.onload = function(){
/*格式:
* [
* [第一列数据，第二列数据，第三列数据,.....],
* [第一列数据，第二列数据，第三列数据,.....],
* .......
* ]
* */
//写入到excel sheet
XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1});
//写入excel后释放内存
xhr.response = null;
let hasGetNum = offset + limit; //当前已取到的行数
console.log(`已写入${hasGetNum}行`);
//更新进度显示
updateProgress(hasGetNum, totalRows);
//如果没取完,递归获取下一批数据
if(hasGetNum < totalRows){
//剩余没传行数
let rest = totalRows - hasGetNum;
//计算下一批数据的数量
let nextLimit = rest > ROWS_PER_REQ ? ROWS_PER_REQ : rest;
//获取下一批数据
getRowsFromServer(hasGetNum, nextLimit);
}else{
//收取完成，下载excel
XLSX.writeFile(workBook, 'export.xlsx');
//总行数制0
totalRows = 0;
}
};
xhr.send();
}
/**更新进度显示
*
* @param now 当前已更新行数
* @param total 总行数
*/
function updateProgress(now, total){
//计算百分比
let percent = (now/total * 100).toFixed(2);
//更新dom
document.getElementById("progressText").innerHTML = percent;
}
每次获取完数据使用updateProgress更新进度
这里多了几个js-xlsx库的用法:
XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1})
作用：把数据添加到excel sheet
第一个参数：要添加的excel sheet 实例
第二个参数：数据二维数组(本例是从服务器返回)
第三个参数：从哪里插入数据，{origin:-1}代表从最后一行的下一行开始新增数据，其他用法本例用不到，想进一步了解可以参考官网
XLSX.writeFile(workBook, 'export.xlsx')
作用：生成一个excel文件，并触发浏览器下载
第一个参数：excel工作簿实例
第二个参数：下载的文件名，文件格式会根据文件后缀自动识别，本例是xlsx， 其他格式本例用不到，想进一步了解可以参考官网
后端发送数据的逻辑get_data.php:
//获取数据请求
$offset = intval($_GET['offset']);
$limit = intval($_GET['limit']);
//返回
echo json_encode(getData($offset, $limit));
//从数据库获取数据
function getData($offset, $limit)
{
//此处一般情况是从数据库获取数据，这里为了简化直接生成数据
$data = [];
for ($i = 0; $i < $limit; $i++) {
$lineNum = $offset + $i + 1; //行号
$row = ["第${lineNum}行第1列数据", "第${lineNum}行第2列数据", "第${lineNum}行第3列数据", "第${lineNum}行第4列数据", "第${lineNum}行第5列数据", date('Y-m-d H:i:s')];
$data[] = $row;
}
usleep(300000);//模拟耗时操作,暂停300ms
return $data;
}
本demo完整的前后端代码：
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>demo</title>
<script src="node_modules/xlsx/jszip.js"></script>
<script src="node_modules/xlsx/xlsx.js"></script>
</head>
<body>
<!-- 进度显示-->
<span>导出进度:</span><span id="progressText">0%</span>
<br>
<!-- 开始导出按钮-->
<input type="button" id="startBtn" value="开始导出">
</body>
<script>
//每次请求多少行数据
const ROWS_PER_REQ = 1000;
//总行数
let totalRows = 0;
//excel工作簿全局变量
let workBook = null;
//excel sheet全局变量
let workSheet = null;
//开始导出按钮绑定事件
document.getElementById("startBtn").addEventListener('click',()=>{
//初始化
initWorkBook();
console.log("导出开始!");
getTotal();
});
function initWorkBook(){
//创建excel工作簿对象
workBook = XLSX.utils.book_new();
//创建excel第一行，头部
let headArr = [
['第一列', '第二列', '第三列', '第四列', '第五列', '时间'],
];
//根据头部数组创建excel sheet
workSheet = XLSX.utils.aoa_to_sheet(headArr);
//把excel sheet添加到工作簿
XLSX.utils.book_append_sheet(workBook, workSheet, 'sheet1');
}
//发送Ajax请求从服务器获取数据总行数
function getTotal() {
let xhr = new XMLHttpRequest();
xhr.open('GET', 'get_data.php?getTotal=true');
xhr.responseType = "json";
xhr.onload = function(){
//接收到总行数
totalRows = xhr.response.total;
console.log("总行数:",totalRows);
//开始接收数据
getRowsFromServer(0, ROWS_PER_REQ);
};
xhr.send();
}
/**发送Ajax请求分批从服务器拿数据
*
* @param offset 从第几行数据开始
* @param limit 每次取出多少行
*/
function getRowsFromServer(offset, limit) {
let xhr = new XMLHttpRequest();
//拼接get字段
xhr.open('GET', `get_data.php?offset=${offset}&limit=${limit}`);
xhr.responseType = "json";
//接收到数据
xhr.onload = function(){
/*格式:
* [
* [第一列数据，第二列数据，第三列数据,.....],
* [第一列数据，第二列数据，第三列数据,.....],
* .......
* ]
* */
//写入到excel sheet
XLSX.utils.sheet_add_aoa(workSheet, xhr.response ,{origin:-1});
//写入excel后释放内存
xhr.response = null;
let hasGetNum = offset + limit; //当前已取到的行数
console.log(`已写入${hasGetNum}行`);
//更新进度显示
updateProgress(hasGetNum, totalRows);
//如果没取完,递归获取下一批数据
if(hasGetNum < totalRows){
//剩余没传行数
let rest = totalRows - hasGetNum;
//计算下一批数据的数量
let nextLimit = rest > ROWS_PER_REQ ? ROWS_PER_REQ : rest;
//获取下一批数据
getRowsFromServer(hasGetNum, nextLimit);
}else{
//收取完成，下载excel
XLSX.writeFile(workBook, 'export.xlsx');
//总行数制0
totalRows = 0;
}
};
xhr.send();
}
/**更新进度显示
*
* @param now 当前已更新行数
* @param total 总行数
*/
function updateProgress(now, total){
//计算百分比
let percent = (now/total * 100).toFixed(2);
//更新dom
document.getElementById("progressText").innerHTML = percent;
}
</script>
</html>
get_data.php
<?php
//设置返回json头
header('Content-type: application/json');
//总行数
const TOTAL_ROW = 50000;
//获取总行数请求
if(isset($_GET['getTotal'])){
//此处一般情况是从数据库获取总行数，这里为了简化直接返回
$json = json_encode([
'total'=>TOTAL_ROW,
]);
echo $json;
exit;
}
//获取数据请求
$offset = intval($_GET['offset']);
$limit = intval($_GET['limit']);
//返回
echo json_encode(getData($offset, $limit));
//从数据库获取数据
function getData($offset, $limit)
{
//此处一般情况是从数据库获取数据，这里为了简化直接生成数据
$data = [];
for ($i = 0; $i < $limit; $i++) {
$lineNum = $offset + $i + 1; //行号
$row = ["第${lineNum}行第1列数据", "第${lineNum}行第2列数据", "第${lineNum}行第3列数据", "第${lineNum}行第4列数据", "第${lineNum}行第5列数据", date('Y-m-d H:i:s')];
$data[] = $row;
}
usleep(300000);//模拟耗时操作,暂停300ms
return $data;
}
简单性能测试一下：
5w行数据，占用内存200MB，导出无压力
xlsx50w.PNG
20w行数据，占用内存600MB，导出无压力
xlsx20w.PNG
image.png
100w行数据，js报错，内存不够
结束语：
对比服务端生成excel的方案，我觉得有以下一些优点:
- 性能更好，导出速度快
- 有进度指示器，对用户友好
- 分批生成，不会触发请求超时
- 服务器压力大大降低
- 和后端查询列表页面的逻辑基本一致，后端几乎不用增加功能直接用
缺点：前端逻辑比较复杂
注意：
- 本文为原创，代码可以随意使用，无版权，转载请注明原地址
- 本文代码不能直接用在生产环境，只用作流程演示，如需使用需要修改增加安全性等等。
- 如果使用es6的
async，await语法，数据接收的代码比本例中的递归更简洁 - 能生成excel表格最大行数只和客户端配置有关
- js-xlsx还要很多用法，如调整单元格格式等，更多的用法可以参考官网
- 本文前端代码在
Chrome 77内核的浏览器下运行正常，其他浏览器兼容性本文不做探讨
