CODEWITHSUNDEEP
pythonexcelopenpyxl
vy32
vy32

Reputation: 29655

Is it possible to merge cells with an openpyxl write-only worksheet?

I am creating worksheets with about 100,000 rows and openpyxl's writing operation is quite slow. It would be useful to get a row object and to fill it in, but I can't find an API for that. The documentation on optimization mentions write-only mode. My problem with this mode is that it doesn't obviously support merged cells, because merging cells seems to be an operation that is done on a spreadsheet, not on a row that is appended.

from openpyxl import Workbook
from openpyxl.cell import WriteOnlyCell
from openpyxl.comments import Comment
from openpyxl.styles import Font
wb = Workbook(write_only = True)
ws = wb.create_sheet()
cell = WriteOnlyCell(ws, value="hello world")
cell.font = Font(name='Courier', size=36)
cell.comment = Comment(text="A comment", author="Author's Name")
ws.append([cell, 3.14, "foo","bar",None])
ws.append(["merged cells"])
ws.merge_cells(start_row=2,end_row=2,start_column=1,end_column=5)
wb.save('write_only_file.xlsx')

% python cm.py
Traceback (most recent call last):
  File "cm.py", line 12, in <module>
    ws.merge_cells(start_row=2,end_row=2,start_column=1,end_column=5)
AttributeError: 'WriteOnlyWorksheet' object has no attribute 'merge_cells'

Is there any way to support merging cells? Failing that, what's a faster way to write cells than to get each cell with ws.cell() and manually set it?

Upvotes: 3

Views: 3412

Answers (2)

mavroprovato
mavroprovato

Reputation: 8362

Messing around with the merged_cells attribute of the worksheet worked for me. Here is an example:

from openpyxl.workbook import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Test")

ws.append(['A', '', '', '', '', 'B', '', '', '', ''])

for _ in range(100):
    ws.append(['%d' % i for i in range(10)])

ws.merged_cells.ranges.add("A1:E1")
ws.merged_cells.ranges.add("F1:J1")

wb.save("Test.xlsx")

Upvotes: 5

Charlie Clark
Charlie Clark

Reputation: 19507

No, it is not possible in write-only mode to specify if cells are merged.

The information regarding merged cells is at the end of the worksheet and the API currently doesn't support this functionality, although it would probably be possible to add the code to do this.

Upvotes: 0

Related Questions