如何使用Pandas从Excel中仅读取可见的工作表? [英] How to read only visible sheets from Excel using Pandas?

问题描述

我必须获取一些随机的Excel工作表，以便从这些文件中仅读取可见工作表.

I have to get some random Excel sheets where I want to read only visible sheets from those files.

一次考虑一个文件，假设我有Mapping_Doc.xls，其中包含2个可见工作表和2个隐藏工作表.

Consider one file at a time, let's say I have Mapping_Doc.xls which contains 2-visible sheets and 2-hidden sheets.

由于工作表较少，因此我可以使用以下名称解析它们:

As the sheets are less here, I can parse them with names like this:

代码:

xls = pd.ExcelFile('D:\\ExcelRead\\Mapping_Doc.xls')
print xls.sheet_names
df1 = xls.parse('Sheet1') #visible sheet
df2 = xls.parse('Sheet2') #visible sheet

输出:

[u'sheet1',u'sheet2',u'sheet3',u'sheet4']

我怎么只得到可见的纸?

How can I get only the visible sheets?

推荐答案

Pandas内部使用xlrd库(请查看

Pandas uses the xlrd library internally (have a look at the excel.py source code if you're interested).

您可以通过访问每个工作表的visibility属性来确定可见性状态.根据 xlrd源代码中的注释，是可能的值:

You can determine the visibility status by accessing each sheet's visibility attribute. According to the comments in the xlrd source code, these are the possible values:

• 0 =可见
• 1 =隐藏(可由用户取消隐藏-格式->工作表->取消隐藏)
• 2 =非常隐藏"(只能由VBA宏取消隐藏).

下面是一个示例，该示例读取具有2个工作表的Excel文件，第一个工作表可见，第二个工作表隐藏:

Here's an example that reads an Excel file with 2 worksheets, the first one visible and the second one hidden:

import pandas as pd

xls = pd.ExcelFile('test.xlsx')

sheets = xls.book.sheets()

for sheet in sheets:
    print(sheet.name, sheet.visibility)

输出:

Sheet1 0
Sheet2 1

这篇关于如何使用Pandas从Excel中仅读取可见的工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！