发布时间:2020-12-22 09:26:19编辑:admin阅读(3735)
需求:使用pandas读取excel,并生成html文件
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,效果如下:
默认样式,可能不太好看,可以自定义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,效果如下:
上面只是读取了一个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文件
打开Sheet1.html,效果同上!
本文参考链接:
https://blog.csdn.net/wangxingfan316/article/details/79609711
https://blog.csdn.net/qq_38316655/article/details/104663077
47303
45507
36587
34101
28810
25424
24274
19469
18914
17483
5304°
5904°
5403°
5488°
6375°
5220°
5219°
5731°
5687°
6996°