openpyxl 列 插入_使用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?

解决方案

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.