网站查询excel内容
- 修改代码以查询类似12位数编码名称的表格数据，并显示查询结果。
- 增加自动补全功能
完整代码
<!DOCTYPE html>
<html lang="zh">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>编码名称查询</title>
<script src="https://cdn.jsdelivr.net/npm/xlsx/dist/xlsx.full.min.js"></script>
<style>
body {
font-family: Arial, sans-serif;
padding: 20px;
margin: 0;
box-sizing: border-box;
}
input, button {
margin: 10px 0;
display: block;
width: 100%;
max-width: 300px;
padding: 10px;
box-sizing: border-box;
}
.autocomplete-suggestions {
border: 1px solid #ccc;
border-top: none;
max-height: 150px;
overflow-y: auto;
position: absolute;
z-index: 9999;
background: white;
width: calc(100% - 22px);
}
.autocomplete-suggestion {
padding: 10px;
cursor: pointer;
}
.autocomplete-suggestion:hover {
background-color: #e0e0e0;
}
.history {
margin-top: 20px;
}
.history-item {
margin-bottom: 5px;
display: flex;
justify-content: space-between;
align-items: center;
}
.delete-btn {
margin-left: 10px;
cursor: pointer;
color: red;
}
@media (min-width: 600px) {
input, button {
width: auto;
display: inline-block;
}
button {
margin-left: 10px;
}
}
@media (min-width: 800px) {
.history-item {
justify-content: flex-start;
}
.delete-btn {
margin-left: auto;
}
}
</style>
</head>
<body>
<h1>编码名称查询</h1>
<input type="text" id="codeInput" placeholder="输入12位编码" maxlength="12" />
<div id="autocomplete-list" class="autocomplete-suggestions"></div>
<button onclick="findName()">查询名称</button>
<br>
<h3 id="result"></h3>
<div class="history">
<h3>最近查询</h3>
<button onclick="deleteAllHistory()">删除所有记录</button>
<div id="historyList"></div>
</div>
<script>
let data = {};
let history = [];
// 假设'查询表.xlsx'在同一目录下
function readExcelFile() {
fetch('查询表.xlsx')
.then(res => res.arrayBuffer())
.then(ab => {
const workbook = XLSX.read(ab, { type: 'array' });
const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
const json = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
json.forEach(row => {
const code = row[0].toString();
const name = row[1];
if (code && name && code.length === 12) {
data[code] = name;
}
});
})
.catch(error => console.error('Error reading the Excel file:', error));
}
function findName() {
const inputCode = document.getElementById('codeInput').value.trim();
const name = data[inputCode];
const result = name ? `名称: ${name}` : '未找到对应的名称';
document.getElementById('result').innerText = result;
// 清空输入框内容
document.getElementById('codeInput').value = '';
clearAutocomplete();
// 更新查询历史
if (inputCode) {
if (history.length === 10) {
history.shift(); // 删除最旧的一条记录
}
history.push({ code: inputCode, name: result });
updateHistoryList();
}
}
function updateHistoryList() {
const historyList = document.getElementById('historyList');
historyList.innerHTML = '';
history.forEach((item, index) => {
const div = document.createElement('div');
div.className = 'history-item';
div.innerHTML = `${index + 1}. 编码: ${item.code}, ${item.name} <span class="delete-btn" onclick="deleteHistoryItem(${index})">删除</span>`;
historyList.appendChild(div);
});
}
function deleteHistoryItem(index) {
history.splice(index, 1);
updateHistoryList();
}
function deleteAllHistory() {
history = [];
updateHistoryList();
}
function autocomplete(input, array) {
input.addEventListener('input', function() {
const value = this.value;
clearAutocomplete();
if (!value) return false;
const autocompleteDiv = document.getElementById('autocomplete-list');
Object.keys(array).forEach(key => {
if (key.includes(value)) {
const itemDiv = document.createElement('div');
itemDiv.classList.add('autocomplete-suggestion');
itemDiv.innerText = key;
itemDiv.addEventListener('click', function() {
input.value = key;
clearAutocomplete();
});
autocompleteDiv.appendChild(itemDiv);
}
});
});
}
function clearAutocomplete() {
const autocompleteDiv = document.getElementById('autocomplete-list');
while (autocompleteDiv.firstChild) {
autocompleteDiv.removeChild(autocompleteDiv.firstChild);
}
}
// 读取Excel文件
readExcelFile();
// 添加回车键查询功能
document.getElementById('codeInput').addEventListener('keypress', function(event) {
if (event.key === 'Enter') {
findName();
}
});
// 初始化自动补全功能
autocomplete(document.getElementById('codeInput'), data);
</script>
</body>
</html>
代码说明：
- HTML结构: 添加了一个用于显示自动补全建议的
div元素。
- 样式: 添加了自动补全建议的样式。
- JavaScript:
- 自动补全功能：
autocomplete函数根据用户输入实时显示与输入值匹配的12位编码。
- 清除自动补全建议：
clearAutocomplete函数用于清除当前显示的自动补全建议。