Pandas完美读取html格式的Excel所有隐藏数据

大家好，我是小小明。

你是否有遇到这样的情况在一些网站导出Excel文件后，用pandas却无法直接以Excel方式读取。

本文就将遇到的这种情况，带你去完整的解析读取。

问题分析

有一个文件用Excel软件打开可以看到如下数据：

但尝试用pandas直接读取：

import pandas as pd df = pd.read_excel("明细费用表1.xlsx") df

1

2

3

4

结果报出：

ValueError: File is not a recognized excel file

1

这时我们可以考虑一下，这个问题有没有可能时间是csv等文本格式，于是用文本解析器打开看看：

原来这是一个html文档，那么我们就可以以纯html的方式读取它了：

但是可以很明显的看到pandas的网页读取，有大量数据并没有读取到。

这时候我们可以考虑使用pywin32转换格式，也可以通过网页解析直接提取需求的数据。

网页结构分析

首先分析一下主要的结构。

首先是表头：

很明显Excel表中的隐藏列就是受display:none的CSS样式控制。

再看看数据行：

可以看到整数类型的数据都存在于属性x:num中，而不是内部的文本节点中。

下面我们使用xpath来解析数据：

解析数据

经时间测试发现，带有x:的命名空间下的数据，几乎无法通过正常的方法解析获取，或者说非常麻烦。所以我们一次性去掉所有的x:前缀后，再读取数据并加载：

import pandas as pd from lxml import etree with open("明细费用表1.xlsx", encoding="u8") as f: html = etree.HTML(f.read().replace("x:", ""))

1

2

3

4

5

最终我编写的解析代码如下：

header = None data = [] for tr in html.xpath("//table/tr"): row = [] for td in tr.xpath("./td"): num = td.xpath("./@num") if num and num[0]: row.append(float(num[0])) else: row.append("".join(td.xpath(".//text()"))) if len(row) < 4: continue if header is None: header = row else: data.append(row) df = pd.DataFrame(data, columns=header) df

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

可以看到这下子，数据就全部都读取出来了。

解析带有命名空间xml的标准方法

前面对于xmlns:x="urn:schemas-microsoft-com:office:excel"的命名空间，我没有找到好的方法解析，只能直接替换原始文本删除。当对于正常的带有命名空间的数据xpath还是有标准方法解析的。

比如对于如下xml：

from lxml import etree xml = etree.parse("drawing1.xml") print(etree.tostring(xml, pretty_print=True).decode("utf-8"))

1

2

3

4

我们希望取出其中的a:blip节点下的r:embed属性：

namespaces = {"r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships", "a": "http://schemas.openxmlformats.org/drawingml/2006/main"} for e in xml.xpath("//a:blip", namespaces=namespaces): print(etree.tostring(e).decode("utf-8")) print(e.xpath("./@r:embed", namespaces=namespaces)[0])

1

2

3

4

5

rId1 rId2 rId3

1

2

3

4

5

6

可以看到对应的值都顺利获取到。

HTML

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。