[Python] Merged cells return NaN in load_workbook openpyxl

I need to read an excel file using load_workbook from openpyxl. Some cells are merged in this file. This will return NaN values to some of the merged cells. I would like to have all merged cells the same value of the file. Here I tried to create a reproducible example from this simple excel file:



Code:

import pandas as pd
import numpy as np
from openpyxl import load_workbook

workbook = load_workbook("example.xlsx", data_only=True)
sheet = workbook.active

# Create dataframe
max_row = sheet.max_row
max_col = sheet.max_column
df = pd.DataFrame(np.nan, index=range(1, max_row + 1), columns=range(1, max_col + 1))

# Iterate through all cells in the sheet
for row in sheet.iter_rows():
for cell in row:
df.at[cell.row, cell.column] = cell.value

print(df)


Output:

1 2 3 4 5
1 merged NaN long_merged NaN NaN
2 1 4.0 A NaN NaN
3 2 5.0 B NaN NaN
4 3 NaN None NaN NaN


As you can see it returns some NaN to the merged cells. My expected output should be like this:

1 2 3 4 5
1 merged merged long_merged long_merged long_merged
2 1 4.0 A NaN NaN
3 2 5.0 B NaN NaN
4 3 NaN None NaN NaN


So I was wondering how we can return the same value to all cells of the merged cell as in the expected output?

Continue reading...