请问x-spreadsheet如何打开和导出excel文件？我想直接通过x-spreadsheet在线打开本地的excel文件，编辑好后又导出到本地，请问代码怎么写？
网友回复
思路就是
打开execl文件：用xlsx打开本地的execl文件转换成xspreadsheet可以理解的数据
保存下载excel文件：xspreadsheet将修改后的数据转换成xlsx理解的格式并触发下载
具体步骤：
先实例化一个grid
<script type="text/javascript" src="//repo.bfw.wiki/bfwrepo/js/xlsx.full.min.js"></script>
<script type="text/javascript" src="//repo.bfw.wiki/bfwrepo/js/xspreadsheet.js"></script>
<div id="gridctr"></div>
<script>
var grid = x_spreadsheet(document.getElementById("gridctr"));
</script>下面是SheetJS的数据转换成x-spreadsheet代码
function stox(wb) {
var out = [];
wb.SheetNames.forEach(function(name) {
var o = {name:name, rows:{}};
var ws = wb.Sheets[name];
var aoa = XLSX.utils.sheet_to_json(ws, {raw: false, header:1});
aoa.forEach(function(r, i) {
var cells = {};
r.forEach(function(c, j) { cells[j] = ({ text: c }); });
o.rows[i] = { cells: cells };
})
out.push(o);
});
return out;
}
加载数据grid.loadData(stox(workbook_object));
保存下载到本地
grid.getData() 返回所有数据可以转换回worksheet:
function xtos(sdata) {
var out = XLSX.utils.book_new();
sdata.forEach(function(xws) {
var aoa = [[]];
var rowobj = xws.rows;
for(var ri = 0; ri < rowobj.len; ++ri) {
var row = rowobj[ri];
if(!row) continue;
aoa[ri] = [];
Object.keys(row.cells).forEach(function(k) {
var idx = +k;
if(isNaN(idx)) return;
aoa[ri][idx] = row.cells[k].text;
});
}
var ws = XLSX.utils.aoa_to_sheet(aoa);
XLSX.utils.book_append_sheet(out, ws, xws.name);
});
return out;
}
转换完后下载到本地/* build workbook from the grid data */ var new_wb = xtos(xspr.getData()); /* generate download */ XLSX.writeFile(new_wb, "SheetJS.xlsx");
完整的代码如下：
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>BFW NEW PAGE</title>
<script id="bfwone" data="dep=jquery-3.2.1.min|xspreadsheet|xlsx.full.min&err=0" type="text/javascript" src="//repo.bfw.wiki/bfwrepo/js/bfwone.js"></script>
<script type="text/javascript">
var fllename = "bfw.xls";
function stox(wb) {
var out = [];
wb.SheetNames.forEach(function(name) {
var o = {
name: name, rows: {}};
var ws = wb.Sheets[name];
var aoa = XLSX.utils.sheet_to_json(ws, {
raw: false, header: 1
});
aoa.forEach(function(r, i) {
var cells = {};
r.forEach(function(c, j) {
cells[j] = ({
text: c
});
});
o.rows[i] = {
cells: cells
};
})
out.push(o);
});
return out;
}
function xtos(sdata) {
var out = XLSX.utils.book_new();
sdata.forEach(function(xws) {
var aoa = [[]];
var rowobj = xws.rows;
for (var ri = 0; ri < rowobj.len; ++ri) {
var row = rowobj[ri];
if (!row) continue;
aoa[ri] = [];
Object.keys(row.cells).forEach(function(k) {
var idx = +k;
if (isNaN(idx)) return;
aoa[ri][idx] = row.cells[k].text;
});
}
var ws = XLSX.utils.aoa_to_sheet(aoa);
XLSX.utils.book_append_sheet(out,
ws,
xws.name);
});
return out;
}
bready(function() {
use(["xspreadsheet-zh-cn",
"xspreadsheet"],
function() {
x_spreadsheet.locale('zh-cn');
var grid = x_spreadsheet('#xspreadsheet');
$(".open").click(function() {
$("#file").click();
})
$(".save").click(function() {
/* build workbook from the grid data */
var new_wb = xtos(grid.getData());
/* generate download */
XLSX.writeFile(new_wb, filename);
})
document.querySelector("#file").addEventListener("change", function () {
//获取到选中的文件
var file = document.querySelector("#file").files[0];
// var type = file.name.split('.');
// if (type[type.length - 1] !== 'xlsx' && type[type.length - 1] !== 'xls') {
// alert('只能选择excel文件导入');
// return false;
// }
filename = document.querySelector("#file").files[0].name;
const reader = new FileReader();
reader.readAsBinaryString(file);
reader.onload = (e) => {
const data = e.target.result;
const excel = XLS.read(data, {
type: 'binary'
});
/* load data */
grid.loadData(stox(excel));
}
});
});
});
</script>
<style>
.x-spreadsheet-toolbar {
margin-left: 100px;
}
.open {
padding: 12px 10px;
float: left;
margin-left: 6px;
border: 1px solid #e8e6e6;
outline: 0;
background: #fdfdfd;
border-top: 0;
margin-top: -6px;
}
.save {
padding: 12px 10px;
float: left;
margin-left: 6px;
border: 1px solid #e8e6e6;
outline: 0;
background: #fdfdfd;
border-top: 0;
margin-top: -6px;
}
</style>
</head>
<body>
<input type="file" id="file" accept="application/vnd.ms-excel" style="display:none;">
<div style="position: fixed; left: 0; top: 0px;z-index:12;">
<button class="open">打开</button>
<button class="save">保存</button>
</div>
<div id="xspreadsheet"></div>
</body>
</html>
by thinkfuture
我知道答案，我要回答
私活外包
最新提问
