【PythonでExcel操作】Openpyxlで「列のコピー」と「コピーしたセルを挿入」する

はじめに

今まで手作業で編集していたエクセルファイルを自動で読み書きできると、いろいろな業務効率化が期待できると思います。
ここでは、Openpyxlを使って、エクセルの「列のコピー」を考えます。

• エクセルを手動で「列のコピー」し、「コピーしたセルを挿入」した時と同じような結果をめざします。
• 例）D列をコピーし、F列とG列の間に挿入した結果
  • 合計セルの計算式(=SUM(セル範囲))も更新され、挿入した列の分まで自動計算されます
    
    

単純にOpenpyxlの列の挿入と値コピーした場合

import openpyxl
File = "in.xlsx"

src = 4    #4列目(条件B列)	  をコピー
dest = 6   #6列目(条件C列の右)に挿入

wb = openpyxl.load_workbook(File)
ws = wb.active

#列を挿入
ws.insert_cols(dest)
for i in range(1, ws.max_column+1):
	#セルの値をコピー
	ws.cell(row=i, column=dest).value = ws.cell(row=i, column=src).value

wb.save("out.xlsx")

• 問題点
  • セルの属性がコピーされないため、文字が小さかったりセルの色・罫線がついてなく見栄えがわるい
  • 列が増えても合計列のセルに書かれてある[=SUM(セルの範囲)] 関数のセルの範囲が変わらない
  • F8 の値が、=SUM(D3:D7) となっていてF列の合計になっていない

改良

セルの書式すべてをコピーする

	ws.cell(row=i, column=dest)._style = copy(ws.cell(row=i, column=src)._style)

セルが数式の場合、数式の列文字を更新する

1. はじめに、D列の合計"=SUM(D3:D7)" の文字列を、F列にコピーしたので、"=SUM(F3:F7)"　の文字列に変換します。
   　4番目のD列の”D”という文字を、6番目のF列の”F”という文字に置換しています。

2. 次に、3行目の合計 "=SUM(C3:F3)"　の文字列を、1列ふえたので、"=SUM(C3:G3)"　の文字列に変換します。
   　数式のあらわれたセルの一つ左のF列の”F”という文字を、コピーした列の”G”という文字に置換しています。

	#セルの値を取得
	value = ws.cell(row=i, column=src).value
	#セルの値が数式の場合
	if str(value)[0] == "=":
		#コピー元と同じ列が数式に使われているとき、コピー先の列に変更
		value = value.replace(utils.cell.get_column_letter(src),utils.cell.get_column_letter(dest))
		#コピー元の左の列を使われているとき、コピー先の左の列に変更
		value = value.replace(utils.cell.get_column_letter(src-1),utils.cell.get_column_letter(dest-1))

挿入した列移行の数式を正しく更新する

ws.insert_cols(dest) は使わずに、コピー挿入したい場所より先の列を、うしろから1列づつ右に1つ移動していく。
そのために、1列コピー処理を関数化する。

def CopyCol(src,dest):
	for i in range(1, ws.max_row+1):
	    # 1つづつセルのコピーする処理

#コピー挿入したい場所よりうしろの列を、最後の列から1列づつ右に1つ移動していく
for i in range(ws.max_column + 1):
	x = ws.max_column + 1 - i
	CopyCol(x,x+1)
	if x == dest:
		break
#最後に本当にコピーしたい列をコピー
CopyCol(src,dest)

まとめ

import openpyxl
from openpyxl import utils
from copy import copy
File = "in.xlsx"

src = 4    #4列目(条件B列)	  をコピー
dest = 6   #6列目(条件C列の右)に挿入
wb = openpyxl.load_workbook(File)
ws = wb.active

def CopyCol(src,dest):
	for i in range(1, ws.max_row+1):
		value = ws.cell(row=i, column=src).value
		#セルの値が数式の場合
		if str(value)[0] == "=":
			#コピー元と同じ列が数式に使われているとき、コピー先の列に変更
			value = value.replace(utils.cell.get_column_letter(src),utils.cell.get_column_letter(dest))
			#コピー元の左の列を使われているとき、コピー先の左の列に変更
			value = value.replace(utils.cell.get_column_letter(src-1),utils.cell.get_column_letter(dest-1))
		#セルの値をコピー
		ws.cell(row=i, column=dest).value = value
		#セルのスタイルをコピー
		ws.cell(row=i, column=dest)._style = copy(ws.cell(row=i, column=src)._style)


for i in range(ws.max_column):
    x = ws.max_column - i
    CopyCol(x,x+1)
    if x == dest:
        break
CopyCol(src,dest)

wb.save("out.xlsx")

D列がF列にコピー挿入できました。H列の数式や8行目の数式も正しく更新されました。
今回はこれでおわりですが、ただG列の幅が大きいままです。
実は、コピー前の列の幅情報が正しい値がとれず、最終的な列の幅の調整がうまくいっていません。
やり方がわかる方は教えて頂きたいです。

さいごに

次回は、コピー元の列、コピー先の列も検索し、すべてのシートに対して処理を実行してみたいと思います。