StyleFrame.read_excel ignores merge cells

[nddeshmukh1610]

Hi Team,

trying to read excel having few merged cells, noticed StyleFrame.read_excel ignores merge cells

Python 3.8.3 StyleFrame 3.0.2 openpyxl 3.0.2 pandas 1.0.5

Any fix or workaround ?

Thanks, Nandlal

[DeepSpace2]

Hi. StyleFrame.read_excel uses pandas.read_excel, and they provide a very similar output when dealing with merged cells.

Consider the sheet:

Then reading with both pandas.read_excel and StyleFrame.read_excel we get a very similar output:

df = pd.read_excel('merged_cells_test.xlsx')
print(df)
sf = StyleFrame.read_excel('merged_cells_test.xlsx')
print(sf)

outputs

     a     b
0    1   6.0
1    2   7.0
2  3 8   NaN
3    4   9.0
4    5  10.0

     a     b
0    1   6.0
1    2   7.0
2  3 8   nan
3    4   9.0
4    5  10.0

If you notice any different behavior please add some more information, such as how the sheet you are trying to read looks like and what dataframe you get after reading.

[nddeshmukh1610]

Hi,

I appreciate your quick response.

I need to preserve the Style (cell color, format, merged cells, header format, etc.) when I'm merging two excels. I understood we can do it using StyleFrame.read_excel(filename,0, read_style=True)

but while reading excel using StyleFrame.read_excel(filename,0, read_style=True) we are not able to preserve the merged cells.

Sample input has below content

But StyleFrame.read_excel(filename,0, read_style=True) reading it as

Is there any way to read the excel with preserving merged cell ?

Thanks, Nandlal

[DeepSpace2]

As a temp workaround, I created a temp branch that will preserve merged cells data when using read_style=True then to_excel().save(), but it still does not allow to merge/unmerge arbitrary cells. It will only preserve merged cells from the file that is being read.

You can give it a try by pip installing from the branch directly:

pip install -U git+https://github.com/DeepSpace2/StyleFrame@preserving-merged-cells

[nddeshmukh1610]

Hi team,

thanks for your kind help, I have tested enhancement and it is working as expected.

regards, Nandlal