はじめに
こんにちは。今回はtxtファイル形式のデータをエクセルで解析する必要があったため、OpenPyXLを利用して自動的に解析用のエクセルファイルを生成するコードを作成します。
使用したものとバージョン
Windows 10 Home: 21H2
VS Code: 1.71.0
Python: 3.10.1, pip: 22.2.2
ライブラリ
OpenPyXL: 3.0.9
目的のテキストと作りたいエクセル
今回は以下のようなテキストファイルを変換します。
Operator Oshiruko_RI
start /V 1 stop /V 6 step /V 0.01 scan 4
comment
loop1 loop2 loop3 loop4
9.120831078 7.378233299 0.633661965 3.102591471
8.552435485 9.605124997 4.030918299 8.978475393
4.605143084 2.879371424 5.898760153 3.441645441
~以下省略~
構成としては実験者(Operator)、開始電圧、終了電圧、スキャン時のステップの値とスキャン回数、コメントが初めの３行にあり、４行目が何loop目かを表すヘッダー、出てきた実験データが５行目以降に並んでいます。なお、全てタブ区切りになっています。
このデータを次のようなエクセルファイルに変換しようと思います。
ここで、x軸はある電圧、AVGはある電圧xでのloopの平均値を表しています。
使用したコード(全体)
以下が今回使用したコード全文になります。
import openpyxl as px # openpyxlをインポート
import os
# 読みだしたいファイルのディレクトリ
currentDir = "C:\Python\Example_code\\"
# ファイル名の指定
fileName = "testData"
# 拡張子の指定
extention1 = ".txt"
extention2 = ".xlsx"
# {currentDir}の{fileName}のtxtファイルを開く
txtPath = currentDir + fileName + extention1
file = open(txtPath)
# すべての行をlinesとして読み込む
lines = file.readlines()
# ファイルを閉じる
file.close()
# 新規エクセルファイルの作成
wb = px.Workbook()
wb.save(currentDir + fileName + extention2) # ファイル名はtxtの拡張子がxlsxに変わったもの
# 先頭のシート名を変更
wsMain = wb.worksheets[0]
wsMain.title = fileName
# 現在のワークシートの構成
# ファイル名[currentDir + fileName + extention2]
# シート名(定義) エクセルファイルで表示される名前
# [wsMain] [fileName]
# wsMainをアクティブにする
wsMain = wb.active
# セルへの書き込み
# 1行目
wsMain.cell(row=1, column=1).value = lines[0].split("\t")[0]
wsMain.cell(row=1, column=2).value = lines[0].split("\t")[1]
# 2行目
wsMain.cell(row=2, column=1).value = lines[1].split("\t")[0]
wsMain.cell(row=2, column=2).value = float(lines[1].split("\t")[1])
wsMain.cell(row=2, column=3).value = lines[1].split("\t")[2]
wsMain.cell(row=2, column=4).value = float(lines[1].split("\t")[3])
wsMain.cell(row=2, column=5).value = lines[1].split("\t")[4]
wsMain.cell(row=2, column=6).value = float(lines[1].split("\t")[5])
wsMain.cell(row=2, column=7).value = lines[1].split("\t")[6]
wsMain.cell(row=2, column=8).value = int(lines[1].split("\t")[7])
# 3行目
wsMain.cell(row=3, column=1).value = lines[2].split("\t")[0]
# 4行目
for i in range(0, 4):
wsMain.cell(row=4, column=i+1).value = lines[3].split("\t")[i]
# データ書き込みの準備
start = wsMain.cell(row=2, column=2).value
stop = wsMain.cell(row=2, column=4).value
step = wsMain.cell(row=2, column=6).value
scan = wsMain.cell(row=2, column=8).value
end = int((stop - start) / step)
# 本データ書き込み
# セル情報 (row, col) = (n+1, m+1)
# 5 <= n <= 5 + end + 1 5行目からスタートしてend行分書き込む
for n in range(4, 4 + end + 1):
for m in range(0, scan):
wsMain.cell(row= n+1, column= m+1).value = float(lines[n].split("\t")[m])
# 平均を出す"Average"列を作成
wsMain.cell(row=4, column= scan + 1).value = "x" # ヘッダー(x)
wsMain.cell(row=4, column= scan + 2).value = "AVG" # ヘッダー(AVG)
for i_x in range(0, end + 1):
wsMain.cell(row=i_x + 5, column= scan + 1).value = float(start + i_x * step)
for i_avg in range(0, end + 1):
wsMain.cell(row=i_avg + 5, column= scan + 2).value = \
str("=AVERAGE(" + wsMain.cell(row=i_avg + 5, column=1).coordinate + ":" \
+ wsMain.cell(row=i_avg + 5, column=scan).coordinate + ")")
# 新規エクセルファイルの保存
wb.save(currentDir + fileName + extention2)
wb.close()
print("start /V = " + str(start) + "\t" + "stop /V = " + str(stop) + "\t" + "step /V = " + str(step))
print("scan = " + str(scan) + "\t" + "end = " + str(end))
os.startfile(currentDir + fileName + extention2, operation="open")
各部位の説明
OpenPyXLの導入
OpenPyXLはコマンドプロンプトで
pip install openpyxl
とします。pipを最新版にするには
pip install pip --upgrade
とします。
テキストファイルを読み込む
テキストファイルを読み込むにはfile関数を使用します。
まず、読みたいファイルのディレクトリを指定します。\をエスケープするのをお忘れなきよう。
currentDir = "C:\Python\Example_code\\"
fileName = "testData"
extention1 = ".txt"
extention2 = ".xlsx"
# {currentDir}の{fileName}のtxtファイルを開く
txtPath = currentDir + fileName + extention1
file = open(txtPath)
ファイルを開くにはopen(開きたいファイルのパス)とします。
開いたファイルはreadlines()を使って一行ずつ読みます。読み込んだファイルはclose()で閉じます。
lines = file.readlines()
file.close()
エクセルのファイル・シートの作成
OpenPyXL(以下、px)を使ってエクセルファイルを作成します。
# 新規エクセルファイルの作成
wb = px.Workbook()
wb.save(currentDir + fileName + extention2)
wsMain = wb.worksheets[0]
wsMain.title = fileName
まず、pxのWorkbook()を使ってワークブック（エクセルファイルのこと）を作成、先ほど開いたテキストファイルと同じ名前で同じ場所に保存します。
次に作成したファイルにワークシートを追加します。これをしないとワークシートのない虚無のエクセルファイルが出来上がります。ワークシートのタイトルはwsMain.titleで指定します。（今回はファイルの名前と同じ）
タイトルは31文字以内になるようにしてください。
エクセル側がエラーを吐きます。
もしテキストファイルと異なる場所に保存したい場合はcurrentDirのところを変えて
wb = px.Workbook()
wb.save("C:\Python\Others\\" + fileName + extention2)
などとすれば大丈夫です。
データの書き込み
readlines()で読み込んだlinesの値を各セルに書き込んでいきます。
特に重要だと思われる1行目、2行目、5行目以降の3つについて説明します。
1行目
1行目は実験者の情報でした。
Operator Oshiruko_RI
linesは各行が格納されている配列なので、一行ごとにタブ区切りの配列として処理する必要があります。そこで.splite("\t")を利用します。１行１列目のセル(下記コードの左辺)にlinesの一行目の一つ目の配列要素(下記コードの右辺)を書き込むため
wsMain.cell(row=1, column=1).value = lines[0].split("\t")[0]
となります。
linesのn行m列目の配列要素、つまり
lines[n-1].splite("\t")[m]の各配列要素はString値となっています。
同様にして、１行２列目は
wsMain.cell(row=1, column=2).value = lines[0].split("\t")[1]
となります。
2行目
2行目は開始電圧、終了電圧、ステップ、スキャン数です。
ここでは値の名前をString値で、具体的な値をfloat値で書き込みたいと思います。
開始電圧のセル（エクセルの2行1列目、2列目）に注目すると
wsMain.cell(row=2, column=1).value = lines[1].split("\t")[0] # 値の名前
wsMain.cell(row=2, column=2).value = float(lines[1].split("\t")[1]) # 具体的な値
と書くことができます。
5行目
5行目以降では実際の実験データをセルに入れていきます。今回用意したデータは1 Vから6 Vまで0.01 V刻みなので、各行をいちいち指定するのではなくfor文を使って一気に片づけたいと思います。
まず、行数endを用意します。
start = wsMain.cell(row=2, column=2).value
stop = wsMain.cell(row=2, column=4).value
step = wsMain.cell(row=2, column=6).value
scan = wsMain.cell(row=2, column=8).value
end = int((stop - start) / step)
実験データは５行目からちょうどend行分存在するため、読み込んだlinesを基準として（n行目はlines[n-1]となることに留意する）、スキャン数だけ列があるため
# 5行目からスタートしてend行分書き込む
for n in range(4, 4 + end + 1): # n行目についての操作
for m in range(0, scan): # n行m列目についての操作
wsMain.cell(row= n+1, column= m+1).value = \ # (n+1, m+1)のセルに
float(lines[n].split("\t")[m]) # linesのn行m列の値を書き込む
とします。
エクセル内のAVERAGE関数を使う
ここまでのコードを動かすと（書き込み後の保存は後述）
が出来上がります。このままでは得られた実験データが意味不明なので、ある電圧xとその時の平均値AVG列、および各ヘッダーを追加したいと思います。
ヘッダー
loopのヘッダーは4行目固定だったので、xとAVGも同様に４行目に追加します。
スキャン分だけloop列があり、その次にヘッダーを作るため
# 平均を出す"Average"列を作成
wsMain.cell(row=4, column= scan + 1).value = "x" # ヘッダー(x)
wsMain.cell(row=4, column= scan + 2).value = "AVG" # ヘッダー(AVG)
となります。
x列とAVG列
まずx列についてですが、これは5行 scan+1列目をスタートとして縦にend個だけあればよいので
for i_x in range(0, end + 1):
wsMain.cell(row=i_x + 5, column= scan + 1).value = \
float(start + i_x * step)
なお、コード２行目のバックスラッシュは長くなった行を改行するためのものです。
同様にAVG列を作成していきます。AVG列は5行 scan+2列目をスタートとして縦にend個だけあればよいので、for文の前半はx列のパクリです。
for i_avg in range(0, end + 1):
wsMain.cell(row=i_avg + 5, column= scan + 2).value = \
str("=AVERAGE(" + wsMain.cell(row=i_avg + 5, column=1).coordinate + ":" \
+ wsMain.cell(row=i_avg + 5, column=scan).coordinate + ")")
AVG列の後半が本節のキモになります。ご存じの通り、Excelで関数を使う場合、セル内に
=SUM(A1:A5) \\ セルA1からA5の合計
と入力する必要があります。しかしOpenPyXLではセルを何行何列目かどうかで指定するため、アドレスが必要な場合には.coordinateを使います。以下は.coordinateの使用例です。
# シートwsMainの1行1列目のセルのアドレス(=A1)
print(wsMain.cell(row=1, column=1).coordinate)
A1
今回は同じ行(i_avg + 5行目)について、1列目からscan列目までを.coordinateを使って取得します。取得したアドレスがString値であることに注意しつつ結合すると
str("=AVERAGE(" + wsMain.cell(row=i_avg + 5, column=1).coordinate + ":" \
+ wsMain.cell(row=i_avg + 5, column=scan).coordinate + ")")
となります。
ファイルの保存
ここまでの作業を保存します。また、作成したファイルは一度開いて保存するまでExcel内の関数が反映されない（正確にはエクセルファイルを開ないと計算が行われず、計算式が入力されただけの状態になってしまう）ので自動で開く機能も付けようと思います。
コード自体は簡単です。.saveで作業中のワークブックを保存、.closeで閉じたのちにosライブラリの.startfileというファイルを開く関数でファイルを開いています。
なお、今回のコードでは確認のために開始電圧などの情報をプロンプトに表示させています。
# 新規エクセルファイルの保存
wb.save(currentDir + fileName + extention2)
wb.close()
print("start /V = " + str(start) + "\t" + "stop /V = " + str(stop) + "\t" + "step /V = " + str(step))
print("scan = " + str(scan) + "\t" + "end = " + str(end))
os.startfile(currentDir + fileName + extention2, operation="open")
終わりに
いかがでしたでしょうか。今回のコードは私が2020年に書いたものを再構成したものです。当時はOpenPyXLの説明が少なく、この程度の分量でも相当苦労しました。次回はこのコードを改良してコードの短縮化や3項移動平均の列の追加、TKinterを使用したデスクトップアプリ化を行いたいと思います。
次回 -> ・OpenPyXLを使用してtxtをExcelファイルに変換してみた #2
参考等