備忘録
Pythonで特定フォルダにあるエクセル群から、セルを抜き出すPG
一階層パターン
ー-----------------------------
#######
#使い方メモ
# py test.py >> test.txt
#######
import openpyxl
import pprint
import os
#ケースNoがセットされる列
caseNameColumn = "A"
#ケース数がセットされる列
caseQtyColumn = "B"
#Sheet名
SheetName = "Sheet1"
#検索対象のフォルダ
files = os.listdir("test/")
for f in files:
#とりあえず10行分まわす
for num in range(10):
caseGyo = str(num + 1)
# print(caseGyo)
caseName = caseNameColumn + caseGyo
caseQty = caseQtyColumn + caseGyo
wb = openpyxl.load_workbook('test/' + f)
sheet = wb[SheetName]
cell1 = sheet[caseName]
cell2 = sheet[caseQty]
if (cell1.value == None):
break
print(f, ",", cell1.value, "," , cell2.value)
ー-----------------------------
以下2階層パターン
ー--------------------------
#######
#使い方メモ
# py test.py >> test.txt
#######
import openpyxl
import pprint
import os
#ケースNoがセットされる列
caseNameColumn = "A"
#ケース数がセットされる列
caseQtyColumn = "B"
#Sheet名
SheetName = "Sheet1"
folder1 = "test2/"
#検索対象のフォルダ
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:
# print(f)
#とりあえず10行分まわす
for num in range(10):
caseGyo = str(num + 1)
# print(caseGyo)
caseName = caseNameColumn + caseGyo
caseQty = caseQtyColumn + caseGyo
# wb = openpyxl.load_workbook('test/' + f)
wb = openpyxl.load_workbook(folder1 + fol + "/" + f)
sheet = wb[SheetName]
cell1 = sheet[caseName]
cell2 = sheet[caseQty]
if (cell1.value == None):
break
print(fol, "," , f, ",", cell1.value, "," , cell2.value)
ー--------------------------