How To Read Merged Cells In Python Using Openpyxl?

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')

Solution 1:

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')

Solution 2:

There You go:

=^..^=

import openpyxl
from openpyxl import Workbook

# load data
raw_data = openpyxl.load_workbook('data.xlsx')
select_sheet = raw_data['Sheet1']


# collect data fromrows
valid_row = []
data = []
forrowin select_sheet.iter_rows(max_row=select_sheet.max_row, max_col=select_sheet.max_column):
    # get cell values
    row_data = [cell.value for cell inrow]

    # handle merged cells
    new_row_data = [0]*select_sheet.max_column
    if Nonein row_data:
        new_row_data[0] = valid_row[0]
        new_row_data[1] = valid_row[1]
        new_row_data[2] = row_data[2]
        new_row_data[3] = row_data[3]
        data.append(new_row_data)
    else:
        data.append(row_data)
    # storage valid row
    if Nonenotin row_data:
        valid_row = row_data


# save data
book = Workbook()
new_sheet = book.active

forrowin data:
    new_sheet.append(row)

book.save('new_data.xlsx')

Input:

01230B212801.21    C   212801.32    D   B60   601.43NoneNone601.54    E   212801.65NoneNone601.76NoneNone601.8

Output:

01230B212801.21  C  212801.32  D  B60  601.43  D  B60  601.54  E  212801.65  E  212601.76  E  212601.8