使用openpyxl插入列 [英] Insert column using openpyxl
问题描述
.EntireColumn.Insert
。有没有openpyxl的方法插入这样的列?
如果没有,任何建议写一个?
在openpyxl中没有找到任何类似 .EntireColumn.Insert
的内容。
首先想到的是通过在工作表上修改_cell来手动插入列。我不认为这是插入列的最佳方法,但它有效:
从openpyxl.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 =范围名称
#插入样本数据
为xrange中的col_idx(1,10):
col = get_column_letter(col_idx)
for for xrange(1,10):
ws.cell('%s%s'%(col,row))value ='%s%s'%(col,row)
#插入列在4和5之间
column_index = 5
new_cells = {}
ws.column_dimensions = {}
用于坐标,在ws._cells中的单元格。 iteritems():
column_letter,row = coordinate_from_string(coordinate)
column = column_index_from_string(column_letter)
#移动列
如果列> = column_index:
column + = 1
co lumn_letter = get_column_letter(column)
coordinate ='%s%s'%(column_letter,row)
#重要的是创建新的单元格对象
new_cells [coordinate] =单元格(ws,column_letter,row,cell.value)
ws._cells = new_cells
wb.save(filename = dest_filename)
我明白这个解决方案非常丑陋,但希望能帮助您以正确的方向思考。
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.
这篇关于使用openpyxl插入列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!