Merging over already merged cells results in damaged file format

[hansu]

Current behavior

I think if one try to merge cells with already merges cells it should either result in an error or the merged range should be expanded. But I got no errors while creating, but this on opening:

Expected behavior

Raise an error, but not currupt the file.

Sample code to reproduce

import xlsxwriter

# Create a workbook and add a worksheet.
workbook = xlsxwriter.Workbook('test.xlsx')
worksheet = workbook.add_worksheet()

# Add a bold format to use to highlight cells.
bold = workbook.add_format({'bold': True})

# # Write some data headers.
# worksheet.write('A1', 'Item', bold)
# worksheet.write('B1', 'Cost', bold)

# worksheet.write_row(1, 0, ['hello','this','is','a','row'], heading)

worksheet.merge_range(1, 0, 2, 0, 'Merged Cells')
worksheet.merge_range(2, 0, 3, 0, 'Merged Cells')
worksheet.merge_range(3, 0, 4, 0, 'Merged Cells')

workbook.close()

Environment

- XlsxWriter version: 2.0
- Python version: 2.7
- Excel version: 365
- OS: Windows 10

Any other information

No response

OpenOffice and LibreOffice users

• [X] I have tested the output file with Excel.

[jmcnamara]

That is a known issue.

In the past I have avoided fixing it for technical reasons but it is one of the few ways to create an invalid file with xlsxwriter (along with overlapping tables) and that irks me a bit.

I'll look into changing the behaviour so it raises an error or warning.

[hansu]

Thanks!

[rocknet]

@jmcnamara Is there any workaround? It's a bummer not to be able to merge cells.

I'm using xslxwriter with pandas. I write the df starting on row 3 (index 2), after that I merge B1:C1 and write a header and get this error, making the merge inoperable (I can't tell my users to click yes and then close the repairs dialog every time).

I thought I might be able to workaround it by doing the merge first and then writing the df, but the order doesn't matter.

Is there any case where merging cells works without error, and would that help with a workaround?

[bipeenmishra-dev]

@jmcnamara I am facing same issue in excel

[bipeenmishra-dev]

I have written som code in python using xlswriter library while opening output in excel its showing excel found unreadable content warning is there any way to solve it.

[jmcnamara]

@rocknet To be clear, the intention of the "fix" here will be to raise an exception when the user tries to write overlapping merge ranges. There won't, and probably can't, be a fix to automatically merge the overlapping merged ranges. I'll clarify that above.

[jmcnamara]

@bipeenmishra-dev There are a number of reasons that you can get the "excel found unreadable content" warning. The 2 most common ones are overlapping merge ranges or overlapping tables. If you are using merge ranges or tables then you should check that first. If you still have an error after that then submit a bug report.

[bipeenmishra-dev]

yes i am merging serval ranges of excel

[bipeenmishra-dev]

And I am running on multiple file its giving error while running on single its not giving error

[rocknet]

@rocknet To be clear, the intention of the "fix" here will be to raise an exception when the user tries to write overlapping merge ranges. There won't, and probably can't, be a fix to automatically merge the overlapping merged ranges. I'll clarify that above.

My bad, this was a logic error on my part. I was trying to re-run the merge_range function on the same exact cells three times, rather than moving my starting row upon iteration. I thought surely I wasn't overlapping, but it turns out I was. Thanks.

[jmcnamara]

I've pushed a fix for this to main. XlsxWriter will now raise an exception if a merge or table range overlaps a previous merge or table range.

For example when running the sample code above you would now get this exception:

Traceback (most recent call last):
  File "/path/scaffold/gh848.py", line 18, in <module>
    worksheet.merge_range(2, 0, 3, 0, 'Merged Cells')
  File "/path/xlsxwriter/worksheet.py", line 121, in cell_wrapper
    return method(self, *args, **kwargs)
  File "/path/XlsxWriter/xlsxwriter/worksheet.py", line 1980, in merge_range
    raise OverlappingRange(
xlsxwriter.exceptions.OverlappingRange: Merge range 'A3:A4' overlaps previous merge range 'A2:A3'.

[jmcnamara]

Fixed in version 3.0.5.

[hansu]

Thanks!

[pranay-agarwal01]

How can we merge cells after adding a table in worksheet now?

[jmcnamara]

How can we merge cells after adding a table in worksheet now?

@pranay-agarwal01 This bug report is closed. If this is a new question or issue then please open a new question or issue.