はじめに
神エクセルとか、方眼紙エクセルとか、ネ申エクセルと言われる、デジタル化の弊害になっているやつを退治したい。
いや、退治するよりも活用を図りたい。
過去の様式の蓄積を無駄にすることなく、デジタル化を進めるには、神エクセル職人によって作成された様式を、HTMLのフォームに変換できるようにすればいいのではないか？
以前、神エクセルをHTMLのフォーム化するVBAマクロ について記事にしたのですが、
VBAの仕様がMacとwindowsで異なるところがあったり、MacではVBAの辞書機能が使えないらしいということがあったりして行き詰まっていました。
この記事では、最終的には、phtyonのライブラリopenpyxlを使用して、神エクセルをHTMLのフォームに変換します。
完成イメージ:
本記事では、エクセルの「行の高さ」と「列の幅」を取り出します。なお次の記事にはpythonスクリプトの完成版があります。完成版はこちら
openpyxlの読み込み
まずはライブラリのimport。
import openpyxl
エクセルワークブックの読み込み
変数wbにエクセルワークブックを読み込みます。
data_only=Trueを指定しているのは、数式が入力されているセルについて、「数式」ではなく「数式の計算結果」を読み込むためです。
path = "/path/to/your/excell.xls"
wb = openpyxl.load_workbook(path,data_only=True)
エクセルシートへのアクセス
sheetName = 'sheet1' #シート名でアクセス
sh = wb[sheetName] #Worksheetクラス
セルの位置と大きさを取得したい(要はセルのRect)
ここでセルの位置と言っているのは、セルのピクセル単位での位置のことです。左上を原点としてx, y, w(幅) ,h(高さ) が分かれば、HTMLでピッタリ同じ位置に描画することができます。
行の高さや列の幅は、行ごと、列ごとに異なるはずですので、それぞれリスト化しようとおもいます。
行方向
まずはそれぞれの行の高さを調べてリストに格納します。行のうち、高さの変更があった行についてだけ、行の高さデータがあります。変更がない行の高さは20ピクセルです。
worksheetのrow_dimensionsが、行のデータを保持しています。row_dimensionsはDimensionHolderクラスのオブジェクトです。
print(sh.row_dimensions.__class__.__name__) # DimensionHolder
keys()で、変更があった行のindexをリストとして取得できます。
list = sh.row_dimensions.keys() # 例えば [8,9] (dict_keysクラス)
添字でアクセスできます。
rd = sh.row_dimensions[8] # 8行目にアクセス (9行目ではない)
print (rd.__class__.__name__) # RowDimensionクラス
RowDimensionクラスのオブジェクトのheightが行の高さです。
print(str(rd.height)) # 例えば、30 (ピクセル)
以上を踏まえて、関数を作ります。この関数は、何行目までが欲しいかと、workseetを受け取って、行の高さを含むリストを返します。1行目の高さがindex 0に含まれるようにしています。
初期値20で埋め、行高の変更があったところを更新しています。
def listRowHeights(maxRow,sheet):
rowHeight = [20 for i in range(maxRow)] #デフォルト値20で埋める
rows = sheet.row_dimensions.keys()
for row in range(maxRow):
if row in rows:#rowは1_base
rowHeight[row-1] = sheet.row_dimensions[row].height
return rowHeight
参考までですが、
row_dimensionsに登録されていないkeyで参照すると、新しくオブジェクトが作られてheight = Noneがセットされるので注意が必要です。
print(sh.row_dimensions.keys()) # 例えば[8, 9]
sh.row_dimensions[2] # 参照しただけ。
print(sh.row_dimensions.keys()) # →[8, 9, 2]
これで行方向について、指定した行数(ここではmaxRow-1)まで、高さをリスト化できました。
列方向
列幅を調べるのは厄介でした。行方向と全く異なる形式でデータが管理されています。
列方向の情報は、
sh.column_dimensions
でアクセスできますが、多少注意が必要です。
たとえばB、C、D、E列目だけ列幅が変更されていて、幅が等しいエクセルシートがあった場合では、valueとしてcolumn_dimensions['B']だけが存在します。このvalueはColumnDimension オブジェクトです。
value = sh.column_dimensions['B']
print(value.__class__.) #<class 'openpyxl.worksheet.dimensions.ColumnDimension'>
このColumnDimensionオブジェクトが、何列目から何列目までが同じ幅であるかの情報を、変数minとmaxで保持しています。
デフォルトの列幅と異なり、かつ、直前の列幅と異なるところにだけColumnDimensionオブジェクトがある、ということです。
#B列からE列の幅がデフォルトと異なり、かつ同じ場合
print(str(value.min)) # 2
print(str(value.max)) # 5
# ... 2列目(B列)から5列目(E列)まで。
そして、列幅は、変数名widthでアクセスできるのですが、この値を7倍したものがピクセル単位での幅になるようです(widthの値が10なら70ピクセルです。謎の仕様)。
デフォルトの値は幾つなのか？ということなのですが、これは75ピクセルのようです(下スクリーンショット)。デフォルトの幅をプログラム的に取得したかったのですが、できませんでした(どなたか...)。 以下のようにして取得できます。エクセルシートで、全選択して、列幅を変更すると、その時の幅がデフォルトの幅になります。これもまた謎の仕様で、得られる値を6倍すると、ピクセル単位の幅になるようです(謎)。
sh.sheet_format.defaultColWidth * 6
このスクショに「幅:10.00(75ピクセル)」とありますが、これがまた謎の仕様で、幅10.00に7をかけて5を足したものが75ピクセル、という関係があるようです。ここでの幅は、おそらくデフォルトフォントを使ったときにセルに入力できる半角文字の文字数のようです(本当？？)が、気にしないことにします。
なお、sh.column_dimensions['A'] とすると、参照しているだけのつもりが、ColumnDimensionオブジェクトが新たに作られて、シート情報に反映されます。
print(sh.column_dimensions.keys()) # ['B']
sh.column_dimensions['A'] # 参照しただけ。
print(sh.column_dimensions.keys()) # ['B', 'A'] # 参照しただけで、追加されている！
print(str(sh.column_dimensions['A'].width)) # 13.0 ... つまり91ピクル幅(デフォルトより幅が広い)。
実際、A列の幅がデフォルトのエクセルシートを読み込んで以下のようなコードを動かすと、
import openpyxl
path = "/path/to/your/excell.xls"
wb = openpyxl.load_workbook(path,data_only=True)
sh = wb['Sheet1']
sh.column_dimensions['A']#アクセスしているだけのつもりだが、そうではない。
wb.save('/path/to/your/excell_after.xls')
以下のように、変更されてしまいます(上が変更前、下が変更後)。
後日修正箇所
エクセルシート上でカラムを選択して幅を0にすると、カラム幅自体は0にならずに、カラムディメンションのhiddenがTureになるようです。
列幅の取得
以上を踏まえて、関数を作ります。この関数は、何列目までが欲しいかと、workseetを受け取って、列の幅を含むリストを返します。リストのindex 0に、1列目の幅が入るようにしています
def listColmunWidths(maxC,sheet):
defaultWidth = sheet.sheet_format.defaultColWidth * 6
columnwidth = [defaultWidth for i in range(maxC)] # デフォルト値で埋める
for key in sheet.column_dimensions:
cd = sheet.column_dimensions[key]
limit = maxC if cd.max > maxC else cd.max
for ind in range(cd.min,limit+1):
if cd.hidden == True:
columnwidth[ind-1] = 0
else:
columnwidth[ind-1] = cd.width*7 #widthの値を7倍したのが、ピクセル単位でのカラムの幅
return columnwidth
おわりに
これで、エクセルシートを読み込んで、行の高さと列の幅をリストにしまう関数ができました。いずれ、続きを書きます....
今回のコード
import openpyxl
def listRowHeights(maxRow,sheet):
rowHeight = [20 for i in range(maxRow)] #デフォルト値20で埋める
rows = sheet.row_dimensions.keys()
for row in range(maxRow):
if row in rows:#rowは1_base
rowHeight[row-1] = sheet.row_dimensions[row].height
return rowHeight
def listColmunWidths(maxC,sheet):
defaultWidth = sheet.sheet_format.defaultColWidth * 6
columnwidth = [defaultWidth for i in range(maxC)]# デフォルトのカラム幅は70ピクセル
# カラムディメンションから、横幅を調べる。
# 連続する同じ幅のカラムが1つのディメンションが幅情報を持っている。
for xxx in sheet.column_dimensions:
cd = sheet.column_dimensions[xxx]
limit = maxC if cd.max > maxC else cd.max
for ind in range(cd.min,limit+1):
if cd.hidden == True:
columnwidth[ind-1] = 0
else:
columnwidth[ind-1] = cd.width*7 #widthの値を7倍したのが、ピクセル単位でのカラムの幅
return columnwidth
path = "/path/to/your/excell.xls"
wb = openpyxl.load_workbook(path,data_only=True)
sh = wb['sheet1'] #Worksheetクラス
rowHeights = listRowHeights(100,sh) #とりあえず100行目まで
colmunWidths = listColmunWidths(100,sh) #とりあえず100列目まで