1.导出
<script src="scripts/jquery-1.7.2.min.js"></script> <script src="scripts/rusty1s_table2excel.js"></script>//网上找
html
<body> <div> <button type="button" id="btnExport">导出Excel</button> </div> <div id="myDiv"> <table id="tableExcel" width="70%" border="1" cellspacing="0" cellpadding="0"> <tr> <td colspan="5" align="center">html 表格导出道Excel</td> </tr> <tr> <td>列标题1</td> <td>列标题2</td> <td>类标题3</td> <td>列标题4</td> <td>列标题5</td> </tr> </table> </div> </body>
js
<script language="JavaScript" type="text/javascript"> $("#btnExport").click(function () { exportExcel("tableExcel", "aaa"); }); function addhtml() { var html = ""; for (var i = 0; i <10; i++) { html += "<tr><td>AAAAAAA</td><td>BBBBBBBBBB</td><td>CCCCCCCCCCCCC</td><td>DDDDDDDDDDD</td><td>EEEEEEEEEEE</td></tr>"; } $("#tableExcel").append(html); } addhtml(); function exportExcel(tableid, filename) { var table2excel = new Table2Excel({ defaultFileName: filename }); table2excel.export(document.getElementById(tableid)); } </script>
2.导入
<script src="scripts/jquery-1.7.2.min.js"></script> <script src="scripts/SheetJS_js-xlsx/xlsx.core.min.js.js"></script>
//https://github.com/SheetJS/sheetjs/tree/master/dist
html
<body>
<input type="file" id="file" style="display:none;" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet">
<a href="javascript:selectFile()">加载本地excel文件</a>
<div id="result" contenteditable=""></div>
</body>
js
<script type="text/javascript">
function selectFile() {
document.getElementById('file').click();
}
$(function () {
//改变事件
document.getElementById('file').addEventListener('change', function (e) {
var files = e.target.files;
if (files.length == 0) return;
var f = files[0];
if (!/.xlsx$/g.test(f.name)) {
alert('仅支持读取xlsx格式！');
return;
}
readWorkbookFromLocalFile(f, function (workbook) {
readWorkbook(workbook);
});
});
loadRemoteFile('./sample/test.xlsx');
});
function readWorkbook(workbook) {
var sheetNames = workbook.SheetNames; // 工作表名称集合
var worksheet = workbook.Sheets[sheetNames[0]]; // 这里我们只读取第一张sheet
var csv = XLSX.utils.sheet_to_csv(worksheet);
document.getElementById('result').innerHTML = csv2table(csv);
}
// 将csv转换成表格
function csv2table(csv) {
var html = '<table>';
var rows = csv.split('
');
rows.pop(); // 最后一行没用的
//得到数据，这里可以转为json
//处理数据
rows.forEach(function (row, idx) {
var columns = row.split(',');
columns.unshift(idx + 1); // 添加行索引
if (idx == 0) { // 添加列索引
html += '<tr>';
for (var i = 0; i < columns.length; i++) {
html += '<th>' + (i == 0 ? '' : String.fromCharCode(65 + i - 1)) + '</th>';
}
html += '</tr>';
}
html += '<tr>';
columns.forEach(function (column) {
html += '<td>' + column + '</td>';
});
html += '</tr>';
});
html += '</table>';
return html;
}
//读取本地excel文件
function readWorkbookFromLocalFile(file, callback) {
var reader = new FileReader();
reader.onload = function (e) {
var data = e.target.result;
var workbook = XLSX.read(data, { type: 'binary' });
if (callback) callback(workbook);
};
reader.readAsBinaryString(file);
}
function loadRemoteFile(url) {
readWorkbookFromRemoteFile(url, function (workbook) {
readWorkbook(workbook);
});
}
</script>