使用Python将含图片、表头自动筛选的Excel转为HTML问题求助

完整实现方案 #

替换原有xlsx2html方案，采用openpyxl读取Excel内容自定义生成HTML，同时内嵌JS实现筛选功能，生成的单文件HTML可直接打开使用。

第一步：安装依赖 #

pip install openpyxl

第二步：转换代码 #

import openpyxl
import base64
from io import BytesIO

# 配置参数
INPUT_EXCEL = "in_put.xlsx"
OUTPUT_HTML = "out_put.html"

# 读取Excel
wb = openpyxl.load_workbook(INPUT_EXCEL, data_only=True)
ws = wb.active

# 收集所有图片的锚点位置
img_map = {}
for img in ws._images:
    # 获取图片所在的行、列索引（从0开始）
    row = img.anchor._from.row
    col = img.anchor._from.col
    # 图片转base64避免路径依赖
    img_buffer = BytesIO()
    img.ref.save(img_buffer, format=img.ref.format)
    img_base64 = base64.b64encode(img_buffer.getvalue()).decode()
    img_src = f"data:image/{img.ref.format.lower()};base64,{img_base64}"
    if (row, col) not in img_map:
        img_map[(row, col)] = []
    img_map[(row, col)].append(img_src)

# 生成HTML基础结构
html_content = """
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>Excel转换结果</title>
    <style>
        table {border-collapse: collapse; width: 100%; margin: 20px 0;}
        th, td {border: 1px solid #ddd; padding: 8px; text-align: left; position: relative;}
        th {background-color: #f2f2f2; cursor: pointer;}
        /* 图片对齐专用样式 */
        .cell-img {max-width: 100%; max-height: 120px; vertical-align: middle; display: block; margin: 0 auto;}
        /* 筛选菜单样式 */
        .filter-menu {display: none; position: absolute; top: 100%; left: 0; background: white; border: 1px solid #ddd; z-index: 100; min-width: 150px; max-height: 300px; overflow-y: auto; padding: 5px;}
        .filter-item {padding: 3px 5px; cursor: pointer;}
        .filter-item:hover {background-color: #f2f2f2;}
        .filter-active::after {content: " ▼"; font-size: 12px;}
    </style>
</head>
<body>
<table id="excel-table">
"""

# 生成表格行内容
for row_idx, row in enumerate(ws.iter_rows(values_only=True)):
    html_content += "<tr>"
    for col_idx, cell_val in enumerate(row):
        tag = "th" if row_idx == 0 else "td"
        cell_content = str(cell_val) if cell_val is not None else ""
        # 插入对应单元格的图片
        if (row_idx, col_idx) in img_map:
            for img_src in img_map[(row_idx, col_idx)]:
                cell_content += f'<img src="{img_src}" class="cell-img">'
        html_content += f"<{tag}>{cell_content}</{tag}>"
    html_content += "</tr>\n"

# 内嵌筛选逻辑JS
html_content += """
</table>
<script>
const table = document.getElementById('excel-table');
const headers = table.querySelectorAll('th');
const allRows = Array.from(table.querySelectorAll('tr:not(:first-child)'));

// 给每个表头绑定筛选功能
headers.forEach((header, colIdx) => {
    header.classList.add('filter-header');
    // 生成筛选菜单
    const menu = document.createElement('div');
    menu.className = 'filter-menu';
    
    // 去重获取该列所有可选值
    const values = [...new Set(allRows.map(row => row.children[colIdx].textContent.trim()))];
    // 加全选选项
    const allItem = document.createElement('div');
    allItem.className = 'filter-item';
    allItem.textContent = '全选';
    allItem.dataset.value = 'all';
    menu.appendChild(allItem);
    // 加单个值选项
    values.forEach(val => {
        const item = document.createElement('div');
        item.className = 'filter-item';
        item.textContent = val || '(空)';
        item.dataset.value = val;
        menu.appendChild(item);
    });
    header.appendChild(menu);
    
    // 点击表头切换菜单显示状态
    header.addEventListener('click', (e) => {
        e.stopPropagation();
        // 关闭其他已打开的菜单
        document.querySelectorAll('.filter-menu').forEach(m => {
            if(m !== menu) m.style.display = 'none';
        });
        menu.style.display = menu.style.display === 'block' ? 'none' : 'block';
        header.classList.toggle('filter-active', menu.style.display === 'block');
    });
    
    // 点击筛选项执行过滤
    menu.addEventListener('click', (e) => {
        e.stopPropagation();
        const selectVal = e.target.dataset.value;
        allRows.forEach(row => {
            const cellVal = row.children[colIdx].textContent.trim();
            row.style.display = (selectVal === 'all' || cellVal === selectVal) ? '' : 'none';
        });
        menu.style.display = 'none';
        header.classList.remove('filter-active');
    });
});

// 点击页面其他区域关闭所有筛选菜单
document.addEventListener('click', () => {
    document.querySelectorAll('.filter-menu').forEach(m => m.style.display = 'none');
    document.querySelectorAll('.filter-header').forEach(h => h.classList.remove('filter-active'));
});
</script>
</body>
</html>
"""

# 输出HTML文件
with open(OUTPUT_HTML, 'w', encoding='utf-8') as f:
    f.write(html_content)

功能说明 #

• 图片对齐：通过获取Excel图片的锚点位置，将图片插入对应单元格，搭配专用CSS样式实现图片在单元格内居中对齐，不会超出单元格范围
• 自动筛选：内嵌JS实现了和Excel逻辑一致的表头筛选功能，点击表头即可弹出筛选菜单，支持全选和单独筛选对应值
• 所有资源均内嵌到HTML文件中，不需要加载额外外部资源，可直接打开使用

内容的提问来源于stack exchange，提问作者Rogers