CODEWITHSUNDEEP
pythonexcelopenpyxl
Shawn
Shawn

Reputation: 353

Insert column using openpyxl

I'm working on a script that modifies an existing excel document and I need to have the ability to insert a column between two other columns like the VBA macro command .EntireColumn.Insert.

Is there any method with openpyxl to insert a column like this?
If not, any advice on writing one?

Upvotes: 16

Views: 40606

Answers (2)

Naghmeh
Naghmeh

Reputation: 201

Here is an example of a much much faster way:

import openpyxl

wb = openpyxl.load_workbook(filename)
sheet = wb.worksheets[0]
# this statement inserts a column before column 2
sheet.insert_cols(2)
wb.save("filename.xlsx")

Upvotes: 20

alecxe
alecxe

Reputation: 474191

Haven't found anything like .EntireColumn.Insert in openpyxl.

First thought coming into my mind is to insert column manually by modifying _cells on a worksheet. I don't think it's the best way to insert column but it works:

from openpyxl.workbook import Workbook
from openpyxl.cell import get_column_letter, Cell, column_index_from_string, coordinate_from_string

wb = Workbook()
dest_filename = r'empty_book.xlsx'
ws = wb.worksheets[0]
ws.title = "range names"

# inserting sample data
for col_idx in xrange(1, 10):
    col = get_column_letter(col_idx)
    for row in xrange(1, 10):
        ws.cell('%s%s' % (col, row)).value = '%s%s' % (col, row)

# inserting column between 4 and 5
column_index = 5
new_cells = {}
ws.column_dimensions = {}
for coordinate, cell in ws._cells.iteritems():
    column_letter, row = coordinate_from_string(coordinate)
    column = column_index_from_string(column_letter)

    # shifting columns
    if column >= column_index:
        column += 1

    column_letter = get_column_letter(column)
    coordinate = '%s%s' % (column_letter, row)

    # it's important to create new Cell object
    new_cells[coordinate] = Cell(ws, column_letter, row, cell.value)

ws._cells = new_cells
wb.save(filename=dest_filename)

I understand that this solution is very ugly but I hope it'll help you to think in a right direction.

Upvotes: 8

Related Questions