openpyxl調査
to_obaraセルのコピーについて
ググると色々と出てくるけど、もっと簡単にできないの？という疑問
"A1:E6" -> "H5"にコピーとかできると楽ちん
忘れていたので追記、どのバージョンについて言及しているのかがわからないですね。
$ sw_vers
ProductName: macOS
ProductVersion: 11.2.1
BuildVersion: 20D74
$ pyenv versions
system
3.8.0
* 3.9.0 (set by /Users/obara/dev/test/python/openpyxl/.python-version)
$ pip list | grep openpyxl
openpyxl 3.0.6
とりあえず、調査
「python openpyxl cell range copy」でググる
いかん、古い情報なので、日付を一年以内の記事にする
to_obaraとあるシートの範囲を指定してセル情報を得る方法はいくつかあるが、sheet["A1:E6"]と取れるのは直感的で良き
マニュアルを見ても出ていないのだが、ソースを見るとdef __getitem__(self, key):で実装されている。
マニュアルを読んでもイマイチわからないが、ソースを見ると、何をしたいのかが分かるだけでなく、勉強になるpythonの使い方という点でも非常に参考になる。
to_obaraとりあえず、sheet["A1:E6"]で得られたセルの内容を表示するコードを書いてみた。
Worksheet.append メソッドを参考にしています。
import openpyxl
from inspect import isgenerator
def print_cells(cells):
if (isinstance(cells, (list, tuple, range))
or isgenerator(cells)):
for idx, content in enumerate(cells):
if (isinstance(content, (list, tuple, range))
or isgenerator(content)):
print(idx, end=":")
print_cells(content)
else:
# print(type(content)) # <class 'openpyxl.cell.cell.Cell'>
# print(content)
#print(f"{content.value}({content.coordinate})", end=",")
if isinstance(content, (openpyxl.cell.Cell)):
print(f"{content.value}({content.col_idx})", end=",")
print("")
使い方
if __name__ == "__main__":
wb = openpyxl.load_workbook("test.xlsx")
ws = wb[wb.sheetnames[0]]
cells = ws["A1:E6"]
print_cells(cells)
前後になったけれど、cells = ws["A1:E6"]はどうなっているかを確認してみた。
cells = ws["A1:E6"]
print(type(cells))
print(len(cells))
print(type(cells[0]))
print(len(cells[0]))
print(type(cells[0][0]))
結果は以下の通り。
<class 'tuple'>
6
<class 'tuple'>
5
<class 'openpyxl.cell.cell.Cell'>
行ごとのtupleが6つあり、列ごとのtupleが5つ、その中にCellが入っている
to_obaraセルの内容（値）をコピーする関数
import openpyxl
from inspect import isgenerator
def copy_cells(
cells: tuple,
wsheet: openpyxl.worksheet.worksheet.Worksheet,
coord_str: str="A1"
) -> None:
"""セルの内容をコピーする
cellsに含まれるCellをwsheetのcoord_[str|pair]で示される位置にコピーする
Args:
cells (in): コピーするCellの内容を全て含む
wsheet (in): コピー先の表を指定する
coord_str(in, optional): コピーする表の起点となるセル（座標）情報(ex "A1", default: "A1")
Return:
None
"""
str_col, row = openpyxl.utils.cell.coordinate_from_string(coord_str)
column = openpyxl.utils.cell.column_index_from_string(str_col)
coord_pair = (row, column)
def _copy_cells(
cells: tuple,
wsheet: openpyxl.worksheet.worksheet.Worksheet,
coord_pair: tuple) -> None:
row, column = coord_pair
if (isinstance(cells, (list, tuple, range))
or isgenerator(cells)):
for idx, content in enumerate(cells):
if (isinstance(content, (list, tuple, range))
or isgenerator(content)):
coord_pair = (row, column)
_copy_cells(content, wsheet=wsheet, coord_pair=coord_pair)
row+=1
else:
wsheet.cell(row, column, value=content.value)
column+=1
_copy_cells(cells, wsheet, coord_pair)
openpyxlのソースを参考にしたのでゴテゴテしているけれど、入力が限定されているのであれば、もっとシンプルに記述できます。
cellsがWorksheetの範囲で指定した戻りという制約付きという条件であれば以下の通り。
import openpyxl
def simple_copy_cells(
cells: tuple,
wsheet: openpyxl.worksheet.worksheet.Worksheet,
coord_str: str="A1"
) -> None:
str_col, row = openpyxl.utils.cell.coordinate_from_string(coord_str)
col = openpyxl.utils.cell.column_index_from_string(str_col)
for irow, cells_row in enumerate(cells):
for icol, cell in enumerate(cells_row):
wsheet.cell(row+irow, col+icol, value=cell.value)
セルの属性も色々ある。単に値のコピーだけなら簡単（上述の通り）だが、それ以外の属性は色々と癖がありそう。
セル単体で良いものと、ワークシートと連携が必要なもの、ワークブックとの連携が必要なものなどありそう。
とりあえず、どんな属性があるかは、ソースを見ると良いかもしれない。マニュアルだと、どれが属性本体で、どれが参照しているだけなのかもよくわからない。
まず、値を設定する場合、以下valueメソッドsetterが呼ばれる。
@value.setter
def value(self, value):
"""Set the value and infer type and display options."""
self._bind_value(value)
コメントにある通り、値だけでなく、タイプや表示オプションをinfer（推測する）とあります。
_bind_valueを読んでみると、値の型が_TYPES = {int:'n', float:'n', str:'s', bool:'b'}かそうでない場合は、get_type(t, value)で調べられる。get_typeも中々興味深い実装です。
基本的には、あらかじめ想定したタイプをisinstanceを利用してチェックするものです。想定したタイプは次の3つNUMERIC_TYPES ,STRING_TYPES ,TIME_TYPES となります。で、isinstanceは複数の型をリストで指定できるので、それぞれさらにリストで定義されています。
NUMERIC_TYPESは、compat.numbersで定義されていて、基本はint, float, Decimalの3つですが、numpyがimportできる場合、numpyで定義されている型も登録されています。
STRING_TYPESはstr,byteの2つ、TIME_TYPESは、datetime.datetime, datetime.date, datetime.time, datetime.timedeltaの4つとなっています。さらにそれ以外の場合は、_TYPESに登録される実装です。
で型の推測を終えると、型がdの場合(前述のTIME_TYPES)、Cellのメンバであるnumber_formatに以下の4つのどれか(TIME_TYPESに合わせたモノ)が設定される
datetime.datetime:numbers.FORMAT_DATE_DATETIME,
datetime.date:numbers.FORMAT_DATE_YYYYMMDD2,
datetime.time:numbers.FORMAT_DATE_TIME6,
datetime.timedelta:numbers.FORMAT_DATE_TIMEDELTA,
次に文字列の場合は文字列長、エンコード、不正文字が含まれていないかをチェックし、エラーの場合はdata_typeに'e'を設定、先頭が'='の場合'f'を設定するという流れ。
従って、単に値をコピーしたつもりでも、これらの処理は行われているのですね。
to_obaraさて、属性という観点で見ると、Cell独自のもの（メンバ変数）は以下の2つです。
self._hyperlink = None
self._comment = None
ただ、Cell自体はStyleableObjectの派生クラスですので、StyleableObjectのメンバも確認する必要があります。
それが以下のものとなります。
font = StyleDescriptor('_fonts', "fontId")
fill = StyleDescriptor('_fills', "fillId")
border = StyleDescriptor('_borders', "borderId")
number_format = NumberFormatDescriptor()
protection = StyleDescriptor('_protections', "protectionId")
alignment = StyleDescriptor('_alignments', "alignmentId")
style = NamedStyleDescriptor()
quotePrefix = StyleArrayDescriptor('quotePrefix')
pivotButton = StyleArrayDescriptor('pivotButton')
それと、コンストラクタが以下のような実装になっています。
self.parent = sheet
if style_array is not None:
style_array = StyleArray(style_array)
self._style = style_array
これだけ見ても、実はよくわかりません。ポイントは、xxxDescriptorの実装にあるからです。
to_obaraxxxDescriptorは上述した通り、4種類ある。
StyleDescriptor,NumberFormatDescriptor,NamedStyleDescriptor,StyleArrayDescriptorで、いずれもobjectからの派生、ソースはこちら
to_obaraStyleDescriptorについて前述の通り、font = StyleDescriptor('_fonts', "fontId")といった使い方、定義をしている。それがどのように実装されているのかを追ってみる。
まずコンストラクタは、以下の通り。
def __init__(self, collection, key):
self.collection = collection
self.key = key
fontについては、"fontId"がkeyとなり、"_fonts"がcollectionとしてメンバ変数に設定されている。
そして、本クラスは、2つのメンバ関数が実装されていて、Descriptorという名前がついているだけに、__set__と__get__です。各属性にアクセスされるとこれらのメソッドが呼び出されることになっています。
Descriptorについては別にスクラップしておこう。
to_obaraさて、ここでコピーの話から、なぜここまで深掘りするのかにつながる点でもある（と思っています）__set__を見てみましょう。
def __set__(self, instance, value):
coll = getattr(instance.parent.parent, self.collection)
if not getattr(instance, "_style"):
instance._style = StyleArray()
setattr(instance._style, self.key, coll.add(value))
ざっとみると、(1)StyleableObjectの_styleという属性を参照し、無ければ初期化しておく。その後、(2)StyleableObjectの親の親の属性を参照、(3)その属性に値をaddして(4)その結果をStyleableObject._style.{self.key}に設定するという流れになっています。
この中でのポイントは(2)で親の親の属性を参照していて、さらに(3)で変更を加えるということです。つまりCellの属性を変更しようとすると、なぜかWorkbookの属性を変更していたということになるわけです。まぁ、Excelってそんな作りなのでしょうがないということでもありますが、注意しなければいけないのは、Cellの属性をWorkbookを跨いでコピーする場合となります。
順に見ていきます。
(1)はStyleableObjectのコンストラクタで_styleに設定しているので、この条件に当てはまることはないです。ただ、_styleはStyleArrayになるという点を認識しておけば良いかと思います。
(2)を確認します。CellとWorksheet、さらにWorksheetとWorkbookはそれぞれ子親関係になっていて、parentで参照できるようになっています。
そして、2行目のgetattr(instance.parent.parent, self.collection)は親の親なので、Workbookの属性を参照しようとしているわけです。この時、self.collectionには、_fontsのような文字列が入っているので、Workbookの属性をみてみましょう。
def _setup_styles(self):
"""Bootstrap styles"""
self._fonts = IndexedList()
self._fonts.add(DEFAULT_FONT)
さらにIndexedListというクラスが現れてきました。collにこのオブジェクトが設定されます。
(3)coll.add(value)で上記IndexedListに値を追加しています。IndexedList自体はドキュメントを見ておけば良いと思います。基本listと考えておけば良いです。ただ、addメソッドは通常のlistのappendとは異なり、valueがすでにあれば登録せず、無ければ登録し、そのindexを返却するという物です。
(4)では、例えばfontの場合StyleableObject._style.{"fontId"}にWorkbookの_fontsリストのindexが格納されます。
これが__set__の内容となります。つまり、Cellの属性fontはWorkbookの_fontsリストを参照しそのindexのリストが含まれるということになります。
to_obara次は__get__を見てみます。ただ、__set__を把握していれば、理解はしやすいでしょう。
def __get__(self, instance, cls):
coll = getattr(instance.parent.parent, self.collection)
if not getattr(instance, "_style"):
instance._style = StyleArray()
idx = getattr(instance._style, self.key)
return StyleProxy(coll[idx])
基本は入れたところから取り出すだけなのですが、こちらStyleProxy(coll[idx])が違うところでしょう。
StyleProxyのドキュメントをみると、Proxy formatting objects so that they cannot be altered（変更できないようにフォーマットオブジェクトをプロキシする：Google翻訳）となっています。
まず、coll[idx]自体は、Workbookの_fonts（例）リストのidx番目のfontオブジェクトをStyleProxyに入れて返却されている。
その返却されたオブジェクト自体（例：font_obj）は、fo=font_objといったコピーはできないようにしたく、ただし、普通にfontオブジェクトの属性には透過的にアクセスすることができるようです。
to_obaraそれで、コピー部分はどうなのかということですが、今までみてきた内容から、StyleableObjectに関わる属性は、_styleにIDが格納されているので、以下のようにすれば良いです。
import openpyxl
def copy_styles_of_cell(
src: openpyxl.cell.cell,
dst: openpyxl.cell.cell) -> None:
"""セル単体の属性をコピーする,
Args:
src (openpyxl.cell.cell): [description]
dst (openpyxl.cell.cell): [description]
"""
dst.style = copy(src.style)
dst._style = copy(src._style)
if isinstance(src, (openpyxl.cell.Cell)):
dst.hyperlink = src._hyperlink
dst.comment = src.comment
def copy_cells(
cells: tuple,
wsheet: openpyxl.worksheet.worksheet.Worksheet,
coord_str: str="A1"
) -> None:
str_col, row = openpyxl.utils.cell.coordinate_from_string(coord_str)
col = openpyxl.utils.cell.column_index_from_string(str_col)
for irow, cells_row in enumerate(cells):
for icol, cell in enumerate(cells_row):
dcell = wsheet.cell(row+irow, col+icol, value=cell.value)
copy_styles_of_cell(cell, dcell)
ただし、Workbookが同じ場合でないと例外が発生して正常に動作しないです。
to_obara新しいWorkbookにコピーする場合は、以下のように元の情報もコピーしてあげる必要があります。
def copy_styles_of_workbook(
src: openpyxl.workbook.workbook.Workbook,
dst: openpyxl.workbook.workbook.Workbook
)->None:
"""Workbookにあるスタイル（本体）をコピーする。上書きするので既存のWorkbookへのコピーは問題が発生します。
Args:
src (in): ソース（コピー元）のWorkbook
dst (out): ディスティネーション（コピー先）のWorkbook
"""
dst._number_formats = src._number_formats
dst._fonts = src._fonts
dst._fills = src._fills
#ic(f"{src.loaded_theme}")
dst.loaded_theme = src.loaded_theme
dst._borders = src._borders
dst._colors = src._colors
dst._alignments = src._alignments
dst._named_styles = src._named_styles
def copy_cells(
cells: tuple,
wsheet: openpyxl.worksheet.worksheet.Worksheet,
coord_str: str="A1"
) -> None:
# コピー対象とするセルからWorkbook/Worksheetを取得する
src = cells[0][0]
src_wb = src.parent.parent
src_ws = src.parent
dst_wb = wsheet.parent
dst_ws = wsheet
# Workbookが異なる場合スタイルをコピーする
if src_wb != dst_wb:
copy_styles_of_workbook(src_wb, dst_wb)
str_col, row = openpyxl.utils.cell.coordinate_from_string(coord_str)
col = openpyxl.utils.cell.column_index_from_string(str_col)
for irow, cells_row in enumerate(cells):
for icol, cell in enumerate(cells_row):
# copy cell
dcell = wsheet.cell(row+irow, col+icol, value=cell.value)
copy_styles_of_cell(cell, dcell)
これまでの内容で、まだ対応できていないものがあります。行列の高さや幅、シートの保護、セルのマージなどです。これらはドキュメントからではわかりにくいところかもしれません。
Worksheetのソースからみてみたいと思います。今更ですが、moduleに関するドキュメントではなく、Working with stylesについて読むととっかかりが得られるかと思います。そして、Applying Stylesを見ると、column_dimensions, row_dimensionsという属性にもStyleが関連しそうなことがわかります。
column_dimensionsとrow_dimensionsはDimensionHolderとして定義されていて、worksheet.dimensionsに含まれている。DimensionHolder自体はDimensionに依存しているわけではなく、単にDimensionを値に持つDictionaryで、BoundDictionaryからの派生クラスです。クラス定義とコンストラクタは以下のようになっています。
class DimensionHolder(BoundDictionary):
def __init__(self, worksheet, reference="index", default_factory=None):
self.worksheet = worksheet
self.max_outline = None
self.default_factory = default_factory
super(DimensionHolder, self).__init__(reference, default_factory)
そして、BoundDictionaryについては以下の通り。
class BoundDictionary(defaultdict):
def __init__(self, reference=None, *args, **kw):
self.reference = reference
super(BoundDictionary, self).__init__(*args, **kw)
def __getitem__(self, key):
value = super(BoundDictionary, self).__getitem__(key)
if self.reference is not None:
setattr(value, self.reference, key)
return value
最後に示した__getitem__を見ると、setattr(value, self.reference, key)の部分が特別で、それ以外は特に特殊なことはしていません。
例えば、row_dimentions[1]という参照をした場合、1番目に格納されているvalueのindex属性をキーにして、1番目というキーを値に設定しているということになります。
ここで、default_factoryには、Worksheetにある_add_row/_add_colmnが指定されています。それぞれ、以下のような実装です。
def _add_column(self):
"""Dimension factory for column information"""
return ColumnDimension(self)
def _add_row(self):
"""Dimension factory for row information"""
return RowDimension(self)
したがって、row_dimentions[1]という参照をした場合、RowDimensionのindexに1が設定され、そのオブジェクトが取得されます。column_dimentions['B']という参照をした場合には、ColumnDimensionのindexに’B'が設定され、そのオブジェクトが取得されます。
to_obaraさて、ColumnDimensionやRowDimensionのどれをコピーすれば良いのかがぱっと見良くわかりませんが、__fields__という属性に登録されているものが候補と思われます。
イメージとしては、以下のようなコードです。
for f in src_dim.__fields__:
value = getattr(src_rdim, f, None)
setattr(dst_dim, f, value)
動きはしますが、コピー禁止属性があるので、上記ではエラーが発生します。
確認すると、ColumnDimensionは、["customWidth", "style"]でエラー、RowDimensionは、["customFormat", "customHeight", "s"]でエラーでした。
また、ColumnDimensionについて、コピーしてもエラーにならないのですが、結果がうまくコピーできない状態になりました。調べてみると、minとmaxをコピーすると発生することが判明、minとmaxがどのように利用されているのかを調べたのですが、良くわからず。。とりあえず、コピー除外と言うことでお茶を濁します。
と言うわけで、以下のようなコードにより、コピー範囲にある部分のみの幅・高さなどの属性をコピーします。
for icol, cell in enumerate(cells[0]):
idx = openpyxl.utils.cell.get_column_letter(cell.column)
src_cdim = src_ws.column_dimensions[idx]
scol = openpyxl.utils.cell.get_column_letter(col+icol)
dst_cdim = dst_ws.column_dimensions[scol]
for f in src_cdim.__fields__:
value = getattr(src_cdim, f, None)
if f in ["customWidth", "style", "max", "min"]:
continue
setattr(dst_cdim, f, value)
for irow, cells_row in enumerate(cells):
cell = cells_row[0]
src_rdim = src_ws.row_dimensions[cell.row]
dst_rdim = dst_ws.row_dimensions[row+irow]
for f in src_rdim.__fields__:
value = getattr(src_rdim, f, None)
if f in ["customFormat", "customHeight", "s"]:
continue
setattr(dst_rdim, f, value)
さて、私が認識している最後のステップとなります、結合したセルのコピーです。
結合状態はWorksheetが保持しており、MultiCellRangeオブジェクトがmerged_cells属性に格納されています。そしてMultiCellRangeはcell_rangeパッケージで定義されています。
これについては、単にそこに含まれるcell（MergedCell）の位置をコピー先にずらせば良いのですが、ただ、merged_cellには、sheet全体を示す情報なので、コピー対象ではないCellも含まれるため、そこの考慮をする必要があります。
これを実現する関数を以下に示します。
def shift_multi_range(
multi_cell_range: openpyxl.worksheet.cell_range.MultiCellRange,
dst_coord_str: str,
src_range_str: str=None,
src_cells: tuple=None) -> openpyxl.worksheet.cell_range.MultiCellRange:
"""
マージされたセルをコピーした先で復元するため、次の処理を行う。
(1) コピー範囲に含まれるマージされたセルを抽出
(2） そのセルの位置をコピー先に合わせる
src_range_strかsrc_cellsのどちらか一方を指定すれば良い
Args:
multi_cell_range(in): merged_cellsで示される複数のセル範囲
dst_coord_str(in): コピー先のセル位置を示す文字列(ex. "C2")
src_range_str(in, optional): {min_col}{min_row}:{max_col}{max_row}
src_cells(in, optional): sheet[A1:Z10]で取得したcells
Returns:
MultiCellRange: multi_cell_rangeの中で、コピー範囲に含まれたセル範囲で、コピー先に合わせた位置に変換したもの
"""
if src_cells is None and src_range_str is None:
raise ValueError("Either src_cells or src_range_str must be specified not None.")
if src_cells:
if not isinstance(src_cells, (tuple)):
raise ValueError("type of src_cells must be tuple")
src_range_str = f"{src_cells[0][0].coordinate}:{src_cells[-1][-1].coordinate}"
# source cell range: コピー元の範囲を示すオブジェクト
scr = openpyxl.worksheet.cell_range.CellRange(src_range_str)
col, row = openpyxl.utils.cell.coordinate_from_string(dst_coord_str)
icol = openpyxl.utils.cell.column_index_from_string(col)
scol, srow, c, r = openpyxl.utils.cell.range_boundaries(src_range_str)
col = icol-scol
row = row-srow
result = openpyxl.worksheet.cell_range.MultiCellRange()
for cr in multi_cell_range:
"""
範囲に含まれているかをチェックする
この不等号は、CellRangeのメソッドで定義(__le__)されている。
"""
if cr <= scr:
# contain
cr.shift(col_shift=col, row_shift=row)
result.add(cr)
return result
使い方としては、以下のようになります。
mcells = src_ws.merged_cells
mc = shift_multi_range(mcells, coord_str, src_cells=cells)
for cr in mc:
dst_ws.merge_cells(cr.coord)