英文:
It shows ValueError: Duplicate Position 0.0 when loading an Excel file (.xlsm)
问题
I want to loading an Excel file (.xlsm) which contains Excel Macro in header cell. <br>
它显示如下：<br>
A1 cell contains both text and Macro <br>
(Sorry for providing just a few information due to business confidential) <br>
实际上，我不知道特殊格式的单元格是否作为标题导致了这个问题：<br>
> ValueError: Duplicate Position 0.0
还是因为单元格设置了密码保护，所以我无法读取？
我也尝试了一些方法来修复它，但仍然无法读取文件，如下所示。
I use both pandas and openpyxl to read the xlsm file, show part of it below: <br>
df = pd.read_excel(filePath, sheet_name = 'sheet0', header = 0, index_col = None)
or
wb = openpyxl.load_workbook(filePath, data_only = True)
sh = wb['sheet0']
我只是使用了非常常见的语法，但无论我使用pandas还是openpyxl，都会显示相同的错误。
我还发现类似的问题，就像这个。它没有给我一个有效的解决方案。<br>
希望有人能帮助。谢谢！
英文:
I want to loading an Excel file (.xlsm) which contains Excel Macro in header cell. <br>
It shows like this: <br>
A1 cell contains both text and Macro <br>
(Sorry for providing just few information due to business confidential) <br>
Actually I don't know whether the special-formatted cell as a header lead to this issue: <br>
> ValueError: Duplicate Position 0.0
or because the cell has password to protect so that I can't read?
I also try some ways to fix it but still fail to read the file as below.
I use both pandas and openpyxl to read the xlsm file, show part of it below: <br>
df = pd.read_excel(filePath, sheet_name = 'sheet0', header = 0, index_col = None)
or
wb = openpyxl.load_workbook(filePath, data_only = True)
sh = wb['sheet0']
I just use very common syntax however either I use pandas or openpyxl it shows the same error.
And I also find similiar issue like this. It didn't give me an effective solution. <br>
Hope someone can help. Thanks!
答案1
得分: 0
我今天解决了这个问题。简单来说，我只需要文本'LVL'，并且不需要按钮功能。我尝试读取文件，但是出现了问题。我认为这是因为单元格既包含文本又包含按钮，导致了这种情况。
解决方案如下：
import pylightxl
wb = pylightxl.readxl(fn=filePath, ws='sheet_name')
data = []
for row in wb.ws('sheet_name').rows:
row_data = []
for cell in row:
if isinstance(cell, str):
# 如果单元格包含文本，则加入row_data。
row_data.append(cell)
elif isinstance(cell, int):
row_data.append(cell)
elif isinstance(cell, float):
row_data.append(cell)
data.append(row_data)
df = pd.DataFrame(data[1:], columns=data[0])
KeyCol_df = df[['KeyCol_A', 'KeyCol_B', 'KeyCol_C', 'KeyCol_D']]
主要思路是，我只读取我需要的信息，并将其附加到一个列表中（最终转化为DataFrame）。
我查看了这个网站，确定要使用pylightxl模块来读取xlsm文件。
也许我没有描述清楚我的问题，对此我感到抱歉。
感谢所有曾经查看过这个问题的人！
英文:
I fix the issue today. To say simply, I just need the text 'LVL' and drop the button function. And I tried to read the file but showed the issue. I think that it's because the cell contains both text and button leading to this situation. <br>
And the solution shows as below: <br>
import pylightxl
wb = pylightxl.readxl(fn = filePath, ws = 'sheet_name')
data = []
for row in wb.ws('sheet_name').rows:
row_data = []
for cell in row:
if isinstance(cell, str):
#Add to row_data if cell contains text.
row_data.append(cell)
elif isinstance(cell, int):
row_data.append(cell)
elif isinstance(cell, float):
row_data.append(cell)
data.append(row_data)
df = pd.DataFrame(data[1:], columns = data[0])
KeyCol_df = df[['KeyCol_A', 'KeyCol_B', 'KeyCol_C', 'KeyCol_D']]
The main idea is that I just read the information what I need and append to a list (finally to a dataframe). <br>
And I check this site to determine use pylightxl module to read xlsm file. <br>
Maybe I didn't describe my question clear, I apologize for this.<br>
Thanks anyone who had ever checked this question!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论