问题描述
我正在尝试从已合并_cells_range 的 excel 文件中读取数据...但输出不是我的目标.请帮帮我
I'm trying to read data from the excel file that has merged_cells_range... but the output is not my goal. Pls help me out
import openpyxl
wb = openpyxl.load_workbook('book1.xlsx')
sheet = wb.get_sheet_by_name('info')
all_data=[]
print(sheet.merged_cells.ranges)
for row_index in range(1,sheet.max_row+1):
row=[]
for col_index in range(1,sheet.max_column+1):
vals = sheet.cell(row_index,col_index).value
if vals =='':
for crange in sheet.merged_cells.ranges:
rlo,rhi,clo,chi = crange
if rlo<=row_index and row_index<rhi and clo<=col_index and col_index<chi:
vals = sheet.cell(rlo,clo).value
print(vals)
break
row.append(vals)
all_data.append(row)
print(all_data)
for row in all_data:
sheet.append(row)
wb.save('bbbb.xlsx')
我想得到输出:[['06B', '大宇 BC 212', 80, 1373], ['06C', '大宇 BC 212', 80, 1020], ['06D', 'Transinco B60KL', 60, 1061], ['06D', 'Transinco B60KL', 60, 19], ['06E', '大宇 BC 212', 80, 1020], ['06E', '大宇 BC 212', 60, 1061], ['06E','Daewoo BC 212', 60, 19]] 但结果是:
I desired to get output:[['06B', 'Daewoo BC 212', 80, 1373], ['06C', 'Daewoo BC 212', 80, 1020], ['06D', 'Transinco B60KL', 60, 1061], ['06D', 'Transinco B60KL', 60, 19], ['06E', 'Daewoo BC 212', 80, 1020], ['06E', 'Daewoo BC 212', 60, 1061], ['06E', 'Daewoo BC 212', 60, 19]] but results is:
[['06B', '大宇 BC 212', 80, 1373], ['06C', '大宇 BC 212', 80, 1020], ['06D', 'Transinco B60KL', 60, 1061], [None, None, 60, 19], ['06E', 'Daewoo BC 212', 80, 1020], [None, None, 60, 1061], [None, None, 60, 19]]
[['06B', 'Daewoo BC 212', 80, 1373], ['06C', 'Daewoo BC 212', 80, 1020], ['06D', 'Transinco B60KL', 60, 1061], [None, None, 60, 19], ['06E', 'Daewoo BC 212', 80, 1020], [None, None, 60, 1061], [None, None, 60, 19]]
推荐答案
我修改了我的代码,它的工作.
I amend my code and it's work.
import openpyxl
from openpyxl.utils import range_boundaries
wb = openpyxl.load_workbook('book1.xlsx')
sheet = wb.get_sheet_by_name('info')
all_data=[]
for row_index in range(1,sheet.max_row+1):
row=[]
for col_index in range(1,sheet.max_column+1):
vals = sheet.cell(row_index,col_index).value
if vals == None:
for crange in sheet.merged_cells:
clo,rlo,chi,rhi = crange.bounds
top_value = sheet.cell(rlo,clo).value
if rlo<=row_index and row_index<=rhi and clo<=col_index and col_index<=chi:
vals = top_value
print(vals)
break
row.append(vals)
all_data.append(row)
print(all_data)
for row in all_data:
sheet.append(row)
wb.save('bbbb.xlsx')
这篇关于如何使用openpyxl读取python中的合并单元格?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!