Skip to content
Advertisement

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?

Advertisement

Answer

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.append("A1:E1")
ws.merged_cells.ranges.append("F1:J1")

wb.save("Test.xlsx")
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement