Html页面Table表格导出Excel文件
引用 xlsx.full.min.js 文件
导出
<!DOCTYPE html>
<html>
<head><meta charset="utf-8" /><title></title><script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.3/jquery.min.js"></script><script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script><!--<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>--><script type="text/javascript">$(function () {$('#btnTableToExcel').click(function () {var wb = XLSX.utils.table_to_book(document.getElementById('gridTable'), { sheet: "Sheet1" });XLSX.writeFile(wb, 'Table表格数据.xlsx');});$('#btnJsonToExcel').click(function () {var test_data = [{"id": "1","name": "张三",},{"id": "2","name": "李四",}]exportXlsx(test_data);});function exportXlsx(data) {const book = XLSX.utils.book_new();const sheet = XLSX.utils.json_to_sheet(data);XLSX.utils.book_append_sheet(book, sheet);XLSX.writeFile(book, 'Json表格数据.xlsx');}///读取table内容$('#btnRead').click(function () {$('#gridTable tr').each(function (rowIndex) {var data = {};$(this).find('td').each(function (columnIndex) {/*if (rowIndex > 0 && columnIndex == 2) {*/if (rowIndex > 0 && columnIndex > 1) {var inputValue = $('tr:eq(' + rowIndex + ') td:eq(' + columnIndex + ') input').val();data['column' + columnIndex] = inputValue;}else {data['column' + columnIndex] = $(this).text();}});console.log(data);});});});</script>
</head>
<body><input type="button" id="btnTableToExcel" value="Table导出Excel" /><input type="button" id="btnJsonToExcel" value="Json导出Excel" /><input type="button" id="btnRead" value="读取" /><br /><table id="gridTable" border="1"><tr><td>序号</td><td>姓名</td><td>年龄</td><td>电话</td></tr><tr><td>1</td><td>张三</td><td><input type="text" value="10" /></td><td><input type="text" value="13012345601" /></td></tr><tr><td>2</td><td>李四</td><td><input type="text" value="11" /></td><td><input type="text" value="13012345602" /></td></tr><tr><td>3</td><td>王五</td><td><input type="text" value="12" /></td><td><input type="text" value="13012345603" /></td></tr></table>
</body>
</html>*、导入
<!DOCTYPE html>
<html>
<head><meta charset="utf-8" /><title></title><script src="https://cdn.bootcdn.net/ajax/libs/jquery/3.6.3/jquery.min.js"></script><script src="https://cdn.bootcdn.net/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script><!--<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.16.9/xlsx.full.min.js"></script>--><script type="text/javascript">$(function () {$('#fileInput').on('change', function (e) {var file = e.target.files[0]; // 获取选中的文件var reader = new FileReader();reader.onload = function (e) {var data = new Uint8Array(e.target.result); // 读取文件内容为 Uint8Arrayvar workbook = XLSX.read(data, { type: 'array' }); // 解析文件内容// 获取第一个工作表的名字var firstSheetName = workbook.SheetNames[0];var worksheet = workbook.Sheets[firstSheetName];方式1：header: 1//var jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 1 }); // 转换为 二维数组 数据//console.log(JSON.stringify(jsonData))输出数据到页面//$('#output').empty(); // 清空输出区域//jsonData.forEach(function (row) {// $('#output').append('<p>' + row.join(', ') + '</p>'); // 将每行数据作为段落添加到页面中//});// 方式2：header: 2var jsonData = XLSX.utils.sheet_to_json(worksheet, { header: 0 }); // 转换为 JSON 数据；0、2好像都是转换为 JSON 数据console.log("Ln 34 Table:" + JSON.stringify(jsonData));var rowCount = $('#gridTable').get(0).rows.length - 1;console.log("Ln 36 Table:" + rowCount + ",Json:" + jsonData.length);var msg = "数据不匹配，请重新下载上传数据";var isContinue = true;if (rowCount != jsonData.length) {isContinue = false;alert(msg);return false;}var codes = [];if (isContinue) {console.log("Ln 192 table共 " + rowCount + " 数据,共 ：Excel共" + jsonData.length + " 数据");const isRate = /(^[1-9]\d*(\.\d{1,2})?$)|(^0(\.\d{1,2})?$)/;$(jsonData).each(function (index, item) {var number = index + 1;codes.push($.trim(item.险种组合编码));var itemString = JSON.stringify(item).replace("手续费率%", "CounterRate").replace("劳务费率%", "ServiceRate").replace("政府补助费率%", "AgentRate").replace("服务费率%", "CoverRate")console.log("Ln 209 " + itemString);var newItem = jQuery.parseJSON(itemString);console.log("Ln 211 " + newItem.CounterRate);if (isRate.test(newItem.CounterRate) == false) {isContinue = false;alert("第 " + number + " 行，手续费率% 数据格式错误");return false;}if (isRate.test(newItem.ServiceRate) == false) {isContinue = false;alert("第 " + number + " 行，劳务费率% 数据格式错误");return false;}if (isRate.test(newItem.AgentRate) == false) {isContinue = false;alert("第 " + number + " 行，政府补助费率% 数据格式错误");return false;}if (isRate.test(newItem.CoverRate) == false) {isContinue = false;alert("第 " + number + " 行，服务费率% 数据格式错误");return false;}});if (isContinue) {//var codes = jsonData.map(function (x) {// return $.trim(x.险种组合编码)//});console.log("Ln 196 Excel 原始数据：" + codes);var uniqueExcelCodes = codes.filter(function (item, index, self) {return index === self.findIndex(function (t) {return t === $.trim(item);});});console.log("Ln 202 Excel 去重后数据：" + uniqueExcelCodes);codes.sort(function (a, b) {return $.trim(a) - $.trim(b);});console.log("Ln 206 Excel 去重、排序后数据：" + codes);var tableCodes = [];$('#gridTable tr').each(function (rowIndex) {if (rowIndex > 0) {var code = $('tr:eq(' + rowIndex + ') td:eq(2)').text();console.log("Ln 211：第 " + rowIndex + " 行，编码：" + code);tableCodes.push($.trim(code));}});console.log("Ln 215 Table 原始数据：" + tableCodes);var uniqueTableCodes = tableCodes.filter(function (item, index, self) {return index === self.findIndex(function (t) {return t === $.trim(item);});});console.log("Ln 221 Table 去重后数据：" + uniqueTableCodes);tableCodes.sort(function (a, b) {return $.trim(a) - $.trim(b);});console.log("Ln 225 Table 去重、排序后数据：" + tableCodes);if (uniqueTableCodes.length != uniqueExcelCodes.length) {isContinue = false;alert(msg);return false;}else {for (var i = 0; i < uniqueTableCodes.length; i++) {if ($.trim(uniqueTableCodes[i]) !== $.trim(uniqueExcelCodes[i])) {var number = i + 1;isContinue = false;console.log("Ln 237 Table编码：" + uniqueTableCodes[i] + " Excel编码：" + uniqueExcelCodes[i]);alert("第 " + number + " 行，险种组合编码 " + uniqueExcelCodes[i] + " <br>" + msg);break;}}}console.log("Ln 241 Table 验证完毕");if (isContinue) {console.log("Ln 243 Table 准备赋值");$('#gridTable tr').each(function (rowIndex) {if (rowIndex > 0) {$(this).find('td').each(function (columnIndex) {if (rowIndex > 0 && columnIndex > 1 && columnIndex <= 7) {var columnName = "";if (rowIndex > 0 && columnIndex > 3 && columnIndex <= 7) {var code = $('tr:eq(' + rowIndex + ') td:eq(2)').text();console.log("Ln 251：第 " + rowIndex + " 行，编码：" + code);var objItem = jsonData.filter(function (x) {return x.险种组合编码 == code;});if (objItem == '' || objItem == undefined || objItem == null) {alert(code + " " + msg);return false;}var item = objItem[0];var itemString = JSON.stringify(item).replace("手续费率%", "CounterRate").replace("劳务费率%", "ServiceRate").replace("政府补助费率%", "AgentRate").replace("服务费率%", "CoverRate")item = jQuery.parseJSON(itemString);console.log("Ln 267：" + JSON.stringify(item));console.log(item);if (columnIndex == 4) {columnName = '手续费率%';$('tr:eq(' + rowIndex + ') td:eq(4) input').val(item.CounterRate);}else if (columnIndex == 5) {columnName = '劳务费率%';$('tr:eq(' + rowIndex + ') td:eq(5) input').val(item.ServiceRate);}else if (columnIndex == 6) {columnName = '政府补助费率%';$('tr:eq(' + rowIndex + ') td:eq(6) input').val(item.AgentRate);}else if (columnIndex == 7) {columnName = '服务费率%';$('tr:eq(' + rowIndex + ') td:eq(7) input').val(item.CoverRate);}}}});}});}}}};reader.readAsArrayBuffer(file); // 以 ArrayBuffer 形式读取文件内容$('#fileInput').prop("disabled", true);$('#fileInput').val(''); // 清空值$('#fileInput').prop('disabled', false);});});function importPolicy() {$('#fileInput').click();}</script>
</head>
<body><input type="button" id="btnRead" value="导入Excel" onclick="importPolicy()" /><input type="file" id="fileInput" style="display:none;" /><br /><table id="gridTable" border="1"><tr><td>序号</td><td>姓名</td><td>编号</td><td>电话</td><td>手续费率%</td><td>劳务费率%</td><td>政府补助费率%</td><td>服务费率%</td></tr><tr><td>1</td><td>张三</td><td>2859</td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td></tr><!--<tr><td>2</td><td>李四</td><td>2858</td><td><input type="text" value="13012345602" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td></tr><tr><td>3</td><td>王五</td><td>2587</td><td><input type="text" value="13012345603" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td><td><input type="text" value="13012345601" /></td></tr>--></table>
</body>
</html>*
*
*