pandas实现excel转为html格式并设置css样式

小咪咪 2022-12-28 09:01 213阅读 0赞

# 一、概述 #

需求：使用pandas读取excel，并生成html文件

![ae963e7bdbcb01aecaf7dd678c59fe83.png][]

# 二、演示 #

import pandas as pd
    import codecs
    xd = pd.ExcelFile('456.xlsx')
    df = xd.parse()
    with codecs.open('1.html','w','utf-8') as html_file:
        html_file.write(df.to_html(header = True,index = False))

执行程序，使用浏览器打开1.html，效果如下：

![3f47e7258d1ea6879b437600fcc4af71.png][]

默认样式，可能不太好看，可以自定义css

新建文件df\_style.css，内容如下：

/* includes alternating gray and white with on-hover color */
    .mystyle {
        font-size: 11pt;
        font-family: Arial;
        border-collapse: collapse;
        border: 1px solid silver;
    }
    .mystyle td, th {
        padding: 5px;
    }
    .mystyle tr:nth-child(even) {
        background: #E0E0E0;
    }
    .mystyle tr:hover {
        background: silver;
        cursor: pointer;
    }

应用css文件

import pandas as pd
    import codecs
    pd.set_option('display.width', 1000)
    pd.set_option('colheader_justify', 'center')
    xd = pd.ExcelFile('456.xlsx')
    df = xd.parse()
    # with codecs.open('1.html','w','utf-8') as html_file:
    #     html_file.write(df.to_html(header = True,index = False))
    
    pd.set_option('colheader_justify', 'center')   # FOR TABLE <th>
    html_string = '''
    <html>
      <head><title>HTML Pandas Dataframe with CSS</title></head>
      <link rel="stylesheet" type="text/css" href="df_style.css"/>
      <body>
        {table}
      </body>
    </html>.
    '''
    # OUTPUT AN HTML FILE
    with open('myhtml.html',encoding='utf-8',mode='w') as f:
        f.write(html_string.format(table=df.to_html(classes='mystyle')))

执行程序，使用浏览器打开myhtml.html，效果如下：

![ac6e8d0dc592641cce92a183187da348.gif][]

上面只是读取了一个sheet，如果要读取多个sheet呢？

修改一下代码

import os
    import pandas as pd
    import codecs
    pd.set_option('display.width', 1000)
    pd.set_option('colheader_justify', 'center')
    excel_file = '456.xlsx'
    xd = pd.ExcelFile(excel_file)
    
    # 遍历每一个sheet
    for i in xd.sheet_names:
        # print(i,type(i))
        # 读取指定sheet
        df = xd.parse(sheet_name=i)
    
        pd.set_option('colheader_justify', 'center')   # FOR TABLE <th>
        html_string = '''
        <html>
          <head><title>HTML Pandas Dataframe with CSS</title></head>
          <link rel="stylesheet" type="text/css" href="df_style.css"/>
          <body>
            {table}
          </body>
        </html>.
        '''
    
        # OUTPUT AN HTML FILE
        # 创建excel文件夹，用来存放sheet文件
        sheet_dir = excel_file.split('.')[0]
        # print("sheet_dir",sheet_dir)
        if not os.path.exists(sheet_dir):
            os.mkdir(sheet_dir)
    
        # 写入sheet文件
        sheet_file = os.path.join(sheet_dir, i + '.html')
    
        with open(sheet_file,encoding='utf-8',mode='w') as f:
            f.write(html_string.format(table=df.to_html(classes='mystyle')))

执行代码，它会创建和excel文件同名的目录，进入目录，会有3个html文件

![4383330b9ca82ed7aa69fdd6d723f9bd.png][]

打开Sheet1.html，效果同上！

本文参考链接：

[https://blog.csdn.net/wangxingfan316/article/details/79609711][https_blog.csdn.net_wangxingfan316_article_details_79609711]

[https://blog.csdn.net/qq\_38316655/article/details/104663077][https_blog.csdn.net_qq_38316655_article_details_104663077]

[ae963e7bdbcb01aecaf7dd678c59fe83.png]: /images/20221120/6259ad27f28f4359ae523c461024d3b0.png
[3f47e7258d1ea6879b437600fcc4af71.png]: /images/20221120/0c7d595a68ef4f81abc1c5f8ab0404be.png
[ac6e8d0dc592641cce92a183187da348.gif]: /images/20221120/e65a88c7b0784aac97a6cfe8ade9f0da.png
[4383330b9ca82ed7aa69fdd6d723f9bd.png]: https://img-blog.csdnimg.cn/img_convert/4383330b9ca82ed7aa69fdd6d723f9bd.png
[https_blog.csdn.net_wangxingfan316_article_details_79609711]: https://blog.csdn.net/wangxingfan316/article/details/79609711
[https_blog.csdn.net_qq_38316655_article_details_104663077]: https://blog.csdn.net/qq_38316655/article/details/104663077