使用Pandas或其他模块在Excel中读取没有隐藏列的Excel文件 [英] Reading Excel file without hidden columns in Python using Pandas or other modules

问题描述

任何人都可以告诉我如何使用Pandas或任何其他模块在不隐藏Python列的情况下读取Excel文件吗?

Can anyone tell me how to read the Excel file without hidden columns in Python with Pandas or any other modules?

例如，当我尝试使用Pandas读取excel文件时

When I try to read excel file using Pandas, for example:

file_np = pd.read_excel(f_name)

数据帧file_np始终包含所有列.从这个数据框中，我不知道如何确定Excel文件中隐藏了哪一列. 谢谢！

the dataframe file_np always contain all the column. From this dataframe, I do not know how to identify which column was hidden in the Excel file. Thank you!

推荐答案

我不认为pandas是开箱即用的.

I don't think pandas does it out of the box.

输入

不幸的是，您将不得不做一些多余的阅读(两次). openpyxl做您想做的-

You will have to unfortunately do some redundant reading (twice). openpyxl does what you want -

import openpyxl
import pandas as pd

loc = 'sample.xlsx'
wb = openpyxl.load_workbook(loc)
ws = wb.get_sheet_by_name('Sheet1')

hidden_cols = []
for colLetter,colDimension in ws.column_dimensions.items():
    if colDimension.hidden == True:
        hidden_cols.append(colLetter)

df = pd.read_excel(loc)
unhidden = list( set(df.columns) - set(hidden_cols) )
df = df[unhidden]
print(df)

输出

    C   A
0   1   7
1   9   7
2   5  10
3   7   7
4   4   8
5   4   6
6   9   9
7  10   3
8   1   2

说明

首先使用openpyxl-

loc = 'C:/Users/FGB3140/Desktop/sample.xlsx'
wb = openpyxl.load_workbook(loc)
ws = wb.get_sheet_by_name('Sheet1')

在单元格中搜索隐藏属性(这是捕获隐藏列的位置)

Searching for hidden property in cells (this is where the hidden columns are captured)

hidden_cols = []
for colLetter,colDimension in ws.column_dimensions.items():
    if colDimension.hidden == True:
        hidden_cols.append(colLetter)

使用熊猫读取同一文件-df = pd.read_excel(loc)

Read the same file using pandas - df = pd.read_excel(loc)

通过从其余部分中减去隐藏的列来查找未隐藏的列-

Find the unhidden columns by subtracting the hidden ones from the rest -

unhidden = list( set(df.columns) - set(hidden_cols) )

最后，过滤掉未隐藏的列-

Finally, filter out the unhidden columns -

df = df[unhidden]

PS

我知道我可以做colDimension.hidden == False或简单的if not colDimension.hidden-这里的目标是捕获隐藏的列，然后进行相应的过滤.希望这会有所帮助！

I know I could have done colDimension.hidden == False or simple if not colDimension.hidden - The goal here is to capture the hidden columns and then do the filtering accordingly. Hope this helps!

这篇关于使用Pandas或其他模块在Excel中读取没有隐藏列的Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！