本文接上一篇文章：《Web优雅的实现大数据量Excel导出》
类似的思路也可以实现Excel导入：
实现思路
1、 浏览器读取上传的文件
2、 使用js-xlsx库读取文件
3、 每次ajax向后端发送若干行的数据，直到发送完所有行
4、 导入完成
本文省略php,web服务器等环境搭建步骤。看懂接下来代码实现，需要有一些js,html,PHP,ajax,ES6:promise,async,await,HTML5:FileReader的一些相关知识。
js-xlsx库的安装，引入参照上篇文章。
1、创建html页面元素
index.html
<body>
<!-- 进度显示-->
<span>导入进度:</span><span id="progressText">0%</span>
<br>
<!-- 选择文件按钮-->
<input type="file" id="fileBtn" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" value="选择文件">
<!-- 文件读取标识-->
<span id="readIndicator" style="display:none">文件读取中.....</span>
<br>
<!-- 开始导入按钮-->
<input type="button" id="startBtn" value="开始导入" disabled>
</body>
浏览器显示:
image.png
2、添加js代码，绑定按钮点击事件
//监听上传文件change事件
document.getElementById('fileBtn').addEventListener('change', function () {
//进度指示清零
document.getElementById("progressText").innerHTML = "0%";
//获取文件内容
let file = this.files[0];
//判断文件名
if(!file.name.includes('.xlsx')){
alert('只支持xlsx文件');
return;
}
//读取文件内容
let fileReader = new FileReader();
//读取完成回调
fileReader.addEventListener('load', function(e){
//读取上传的xlsx文件内容
let workbook = XLSX.read(e.target.result, {type: 'binary'});
//隐藏文件读取中标识
document.getElementById('readIndicator').style = "display:none";
//准备导入
importMgr = new ImportMgr(workbook);
document.getElementById('startBtn').disabled = false;
});
fileReader.readAsBinaryString(file);
//展示文件读取中标识
document.getElementById('readIndicator').style = "display:inline";
}, false);
//点击开始导入按钮开始导入
document.getElementById('startBtn').addEventListener('click', function(){
importMgr.begin();
});
ImportMgr类见下章。
这里的js-xlsx库的一个用法解释:
XLSX.read(binaryString, {type: 'binary'});
第一个参数： 输入的数据
第二个参数：{type: 'binary'}代表接收数据的格式是二进制字符串，fileReader.readAsBinaryString(file);刚好满足这个格式，其他用法可以参考官网
返回:一个工作簿类对象
3、ImportMgr导入逻辑封装类
//导入管理类对象
let importMgr = null;
//导入管理类
class ImportMgr{
//当前已经导入到第几行, 从1开始，跳过第一行标题
rowIndex = 1;
//总共有几行
rowsTotal = 0;
//列数组, ['A', 'B', 'C'......]
cols = [];
//每次ajax请求导入多少行
rowsPerRequest = 50;
//excel工作簿实例
workbook=null;
//excel sheet实例
worksheet=null;
//初始化
constructor(workbook){
this.workbook = workbook;
this.worksheet = this.workbook.Sheets[this.workbook.SheetNames[0]];
this.getTotal();
}
//开始导入
async begin(){
while(this.rowIndex <= this.rowsTotal){
//计算本次请求结束行
let rowEnd;
if(this.rowIndex + this.rowsPerRequest <= this.rowsTotal){
rowEnd = this.rowIndex + this.rowsPerRequest;
}else{
rowEnd = this.rowsTotal;
}
//异步导入
await this.importOnce(this.rowIndex+1, rowEnd);
//更新行index
this.rowIndex += this.rowsPerRequest;
//更新进度指示
this.updateProgress(rowEnd, this.rowsTotal);
}
document.getElementById('fileBtn').value = null;
alert("导入完成!");
}
//发起一次导入请求，rowStart:从指定行开始 rowEnd:从指定行结束
importOnce(rowStart, rowEnd){
// console.log(rowStart, rowEnd);
//生成多行数据
let dataArr = [];
for(let i = rowStart; i<= rowEnd; i++){
//当前行数据
let rowDataArr = [];
//当前行cell地址
let cellAddrArr = this.getRowAddressArr(i);
//获取当前行数据
for(let cellAddr of cellAddrArr){
// console.log(cellAddr);
rowDataArr.push(this.worksheet[cellAddr].v);
}
dataArr.push(rowDataArr);
}
// console.log(dataArr);
return new Promise((resolve, reject)=>{
//发起ajax请求
let xhr = new XMLHttpRequest();
xhr.open('POST', 'store.php');
xhr.setRequestHeader("content-type", "application/json" );
xhr.responseType = "json";
xhr.onload = function(){
console.log(xhr.response);
resolve();
};
xhr.send(JSON.stringify(dataArr));
});
}
//获取行数，和列数
getTotal(){
//获取cell范围
let refArr = this.worksheet['!ref'].split(':');
let end = refArr[1];
//生成列[A, B, C, .....]数组, A的unicode:65
for(let i=65; i<=end.charCodeAt(0); i++){
this.cols.push(String.fromCharCode(i));
}
//获取行数, 把类似"F123"的cell地址，提取出123
let startIndexDigital = end.search(/\d+/);
this.rowsTotal = parseInt(end.substring(startIndexDigital));
}
//根据列数组生成一行完整的cell地址
getRowAddressArr(rowNum){
let cols = [];
for(let col of this.cols){
cols.push(col + rowNum);
}
return cols;
}
/**更新进度显示
*
* @param now 当前已更新行数
* @param total 总行数
*/
updateProgress(now, total){
//计算百分比
let percent = (now/total * 100).toFixed(2);
//更新dom
document.getElementById("progressText").innerHTML = percent + "%";
}
}
解读：
begin()方法使用async, await关键字使得异步代码更清晰, 方法中await调用importOnce(）异步操作
importOnce()是向服务器发起一次导入请求，使用了Promise
this.workbook.SheetNames[0]：获取工作簿中第一个标签页名
this.workbook.Sheets['标签页名']: 获取标签页sheet对象
this.worksheet['!ref']标签页对象中'!ref'属性中存储了excel单元格有内容范围 格式如：A1:B2代表了2*2大小的范围
image.png
cell = this.worksheet['XX']标签页对象中字母数字属性代表了单元格，如:worksheet['A13']获取了第十三行，A列的单元格对象
cell.v单元格对象，cell的v属性就是单元格内容,其他属性有需要可参考官方文档
4、后端store.php 代码:
<?php
//设置返回json头
header('Content-type: application/json');
//打开一个文件，模拟数据库
$file = fopen('import.txt', 'a');
//获取json数据
$json = file_get_contents("php://input");
$data = json_decode($json, true);
//处理
foreach($data as $row){
insertData($row, $file);
}
fclose($file);
echo json_encode(['success'=>true]);
//插入数据到数据库,这里为了简化，写入文件
function insertData(array $dataArr, $file):bool {
$str = implode(',', $dataArr);
fwrite($file, $str."\n");
return true;
}
用文件简化存储数据库流程
完整的前端代码:
index.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>demo-xlsx-import</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="file" id="fileBtn" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" value="选择文件">
<!-- 文件读取标识-->
<span id="readIndicator" style="display:none">文件读取中.....</span>
<br>
<!-- 开始导入按钮-->
<input type="button" id="startBtn" value="开始导入" disabled>
</body>
<script>
//监听上传文件change事件
document.getElementById('fileBtn').addEventListener('change', function () {
//进度指示清零
document.getElementById("progressText").innerHTML = "0%";
//获取文件内容
let file = this.files[0];
//判断文件名
if(!file.name.includes('.xlsx')){
alert('只支持xlsx文件');
return;
}
//读取文件内容
let fileReader = new FileReader();
//读取完成回调
fileReader.addEventListener('load', function(e){
//读取上传的xlsx文件内容
let workbook = XLSX.read(e.target.result, {type: 'binary'});
//隐藏文件读取中标识
document.getElementById('readIndicator').style = "display:none";
//准备导入
importMgr = new ImportMgr(workbook);
document.getElementById('startBtn').disabled = false;
});
fileReader.readAsBinaryString(file);
//展示文件读取中标识
document.getElementById('readIndicator').style = "display:inline";
}, false);
//点击开始导入按钮开始导入
document.getElementById('startBtn').addEventListener('click', function(){
importMgr.begin();
});
//导入管理类对象
let importMgr = null;
//导入管理类
class ImportMgr{
//当前已经导入到第几行, 从1开始，跳过第一行标题
rowIndex = 1;
//总共有几行
rowsTotal = 0;
//列数组, ['A', 'B', 'C'......]
cols = [];
//每次ajax请求导入多少行
rowsPerRequest = 50;
//excel工作簿实例
workbook=null;
//excel sheet实例
worksheet=null;
//初始化
constructor(workbook){
this.workbook = workbook;
this.worksheet = this.workbook.Sheets[this.workbook.SheetNames[0]];
this.getTotal();
}
//开始导入
async begin(){
while(this.rowIndex <= this.rowsTotal){
//计算本次请求结束行
let rowEnd;
if(this.rowIndex + this.rowsPerRequest <= this.rowsTotal){
rowEnd = this.rowIndex + this.rowsPerRequest;
}else{
rowEnd = this.rowsTotal;
}
//异步导入
await this.importOnce(this.rowIndex+1, rowEnd);
//更新行index
this.rowIndex += this.rowsPerRequest;
//更新进度指示
this.updateProgress(rowEnd, this.rowsTotal);
}
document.getElementById('fileBtn').value = null;
alert("导入完成!");
}
//发起一次导入请求，rowStart:从指定行开始 rowEnd:从指定行结束
importOnce(rowStart, rowEnd){
// console.log(rowStart, rowEnd);
//生成多行数据
let dataArr = [];
for(let i = rowStart; i<= rowEnd; i++){
//当前行数据
let rowDataArr = [];
//当前行cell地址
let cellAddrArr = this.getRowAddressArr(i);
//获取当前行数据
for(let cellAddr of cellAddrArr){
// console.log(cellAddr);
rowDataArr.push(this.worksheet[cellAddr].v);
}
dataArr.push(rowDataArr);
}
// console.log(dataArr);
return new Promise((resolve, reject)=>{
//发起ajax请求
let xhr = new XMLHttpRequest();
xhr.open('POST', 'store.php');
xhr.setRequestHeader("content-type", "application/json" );
xhr.responseType = "json";
xhr.onload = function(){
console.log(xhr.response);
resolve();
};
xhr.send(JSON.stringify(dataArr));
});
}
//获取行数，和列数
getTotal(){
//获取cell范围
let refArr = this.worksheet['!ref'].split(':');
let end = refArr[1];
//生成列[A, B, C, .....]数组, A的unicode:65
for(let i=65; i<=end.charCodeAt(0); i++){
this.cols.push(String.fromCharCode(i));
}
//获取行数, 把类似"F123"的cell地址，提取出123
let startIndexDigital = end.search(/\d+/);
this.rowsTotal = parseInt(end.substring(startIndexDigital));
}
//根据列数组生成一行完整的cell地址
getRowAddressArr(rowNum){
let cols = [];
for(let col of this.cols){
cols.push(col + rowNum);
}
return cols;
}
/**更新进度显示
*
* @param now 当前已更新行数
* @param total 总行数
*/
updateProgress(now, total){
//计算百分比
let percent = (now/total * 100).toFixed(2);
//更新dom
document.getElementById("progressText").innerHTML = percent + "%";
}
}
</script>
</html>
总结:
经测试效果不错，导入20w条数据浏览器占用900M内存
image.png
image.png
结束语：
对比服务端解析excel文件的方案，我觉得有以下一些优点:
- 性能更好，导入速度快
- 有进度指示器，对用户友好
- 分批生成，不会触发请求超时
- 不占用服务器资源，尤其是内存
- 和后端更新记录的逻辑基本一致，后端几乎不用增加功能直接用
缺点：前端逻辑比较复杂
注意：
- 本文为原创，代码可以随意使用，无版权，转载请注明原地址
- 本文代码不能直接用在生产环境，只用作流程演示，如需使用需要修改增加安全性，数据字段校验，异常处理等等。
- 能生成excel表格最大行数只和客户端配置有关
- excel所有单元格都设置成文本格式，可以避免一些坑，比如excel的时间
- 本文前端代码在
Chrome 77内核的浏览器下运行正常，其他浏览器兼容性本文不做探讨
