excel2html 把表格转化为html

原创已于 2022-02-09 15:15:03 修改 · 1.1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#python

于 2022-02-09 09:36:27 首次发布

基础技术专栏收录该内容

39 篇文章

订阅专栏

该博客介绍了一个Python函数，能够将.xls格式的表格文件转换成HTML字符串。函数考虑了单元格的合并，并提供了两种输出模式：包含文字信息和仅结构信息。在主函数中，它读取Excel文件，遍历单元格，处理合并的单元格，并生成相应的HTML表格。最后，将HTML字符串写入文件或直接打印。

部署运行你感兴趣的模型镜像

import os
from xlrd import open_workbook,formatting


def Excel2Html(filename, with_text=True):
    """
    该函数把.xls的表格文件转化为html字符串
    参数:
    with_text为True:输出表格结构和文字信息
    with_text为False:只输出表格结构信息
    """
    filepath = filename
    # wb = open_workbook(filepath,formatting_info=True)
    wb = open_workbook(filepath,formatting_info=False)
    sheet = wb.sheet_by_index(0)
    if with_text:
        html = '<table class="previewtable" border="1" cellpadding="0" cellspacing="0">'
    else:
        html = '<table>'

    mergedcells={}
    mergedsapn={}
    mergedcellvalue={}
    for crange in sheet.merged_cells:
        rlo, rhi, clo, chi = crange
        for rowx in range(rlo, rhi):
            for colx in range(clo, chi):
                mergedcells[(rowx,colx)]=False
                value = str(sheet.cell_value(rowx,colx))
                if value.strip() != '':
                    mergedcellvalue[(rlo,clo)]=value

        mergedcells[(rlo,clo)]=True
        mergedsapn[(rlo,clo)]=(rhi-rlo, chi-clo)
        mergedsapn[(rlo,clo)]=(rhi-rlo, chi-clo)

    for row in range(sheet.nrows):
        html=html+'<tr>'
        for col in range(sheet.ncols):
            if (row,col) in mergedcells:
                if mergedcells[(row,col)]==True:
                    rspan,cspan = mergedsapn[(row,col)]
                    value = ''
                    if (row,col) in mergedcellvalue:
                        value = mergedcellvalue[(row,col)]
                    if with_text:
                        html=html+'<td rowspan=%s colspan=%s>%s</td>'  % (rspan, cspan, value)
                    else:
                        html=html+'<td rowspan=%s colspan=%s></td>'  % (rspan, cspan)
            else:
                value =sheet.cell_value(row,col)
                if with_text:
                    html=html+'<td>' + str(value) + '</td>'
                else:
                    html=html+'<td>' + '</td>'
        html=html+'</tr>'
    html=html+'</table>'
    return html.replace(" rowspan=1", "").replace(" colspan=1", "")


if __name__ == "__main__":
    import codecs
    html_string = Excel2Html("test.xlsx", True)
    print(html_string)
    with codecs.open("dada.html", "w") as html_file:
        html_file.write(html_string)