備忘禄
複数の規定フォーマットエクセルから、特定セルを抜き出すコード
不具合票のブックが大量にあるときに利用
##############################################
#
# 出力結果
# PGID、 ステータス、記載日、起票者、タイトル、説明
##############################################
import openpyxl
import pprint
import os
###読み込むエクセルの情報
#Sheet名
sheetName = "Sheet1"
#取得項目のセル
cellIDfoldorColumn = "A1"
cellIDpgidColumn = "B1"
cellIDstatusColumn = "C1"
cellIDdateColum = "D1"
cellIDpersonColum = "E1"
cellIDtitleColum = "F1"
cellIDdiscriptionColum = "G1"
####書き出しエクセルの情報
#書き出すエクセルファイル
resultFile = openpyxl.load_workbook('out.xlsx')
# シートを取得
sheetResult = resultFile['Sheet1']
#カラム
foldorColumn = "A"
pgidColumn = "B"
statusColumn = "C"
dateColum = "D"
personColum = "E"
titleColum = "F"
discriptionColum = "G"
filenameColum = "H"
count = 0
#検索対象のフォルダ
folder1 = "wk2/"
files = os.listdir(folder1)
for fol in files:
path = os.path.join(folder1, fol)
if os.path.isdir(path):
# print("[folder]", fol)
files2 = folder1 + fol + "/"
path2 = os.listdir(files2)
for f in path2:
count = count + 1
# print(path2)
wb = openpyxl.load_workbook( path + "/" + f)
sheet = wb[sheetName]
# cell1 = sheet[cellIDfoldorColumn]
# print(f, ",", cell1.value )
sheetResult[foldorColumn + str(count)] = sheet[cellIDfoldorColumn ].value
sheetResult[pgidColumn + str(count)] = sheet[cellIDpgidColumn ].value
sheetResult[statusColumn + str(count)] = sheet[cellIDstatusColumn ].value
sheetResult[dateColum + str(count)] = sheet[cellIDdateColum ].value
sheetResult[personColum + str(count)] = sheet[cellIDpersonColum ].value
sheetResult[titleColum + str(count)] = sheet[cellIDtitleColum ].value
sheetResult[discriptionColum+ str(count)] = sheet[cellIDdiscriptionColum].value
sheetResult[filenameColum + str(count)] = f
# 保存する
resultFile.save('out.xlsx')
