import csv
import os
from openpyxl import load_workbook
# 取得目前的工作目錄
current_directory = os.getcwd()
# 取得目前的工作目錄內input
current_directory_input = current_directory+"\\input\\"
# 取得工作目錄input內所有檔案名稱
current_directory_input_files = os.listdir(current_directory_input)
# 以副檔名來判斷設定相對應的檔案
for i in current_directory_input_files:
TempFileName,TempFileExtension = os.path.splitext(i)
if TempFileExtension == ".csv":
TempCSVFile = i
elif TempFileExtension == ".xlsx":
TempXLSXFile = i
else:
TempTXT = i
# 設定CSVList為一個 list
CSVList = []
# 開啟 CSV 檔案
with open(current_directory_input+TempCSVFile, mode='r', encoding='utf-8') as file:
# 建立 CSV 讀取器
reader = csv.reader(file)
# 逐行讀取 CSV 檔案內容
for row in reader:
CSVList.append(row)
# 設定 ExcelList 為一個list
ExcelList = []
# 載入 Excel 文件
workbook = load_workbook(current_directory_input+TempXLSXFile)
# 取得所有工作表名稱
sheet_names = workbook.sheetnames
# 選擇特定工作表
sheet = workbook[sheet_names[0]]
# 讀取工作表內容
for row in sheet.iter_rows(values_only=True):
ExcelList.append([str(row[0])+str(row[1]).zfill(2)+str(row[2]).zfill(2),row[7]])
# 關閉工作簿
workbook.close()
# CSVListTitle 取得 CSVList[0]
CSVListTitle = CSVList[0]
# 刪除CSVList[0]
CSVList.pop(0)
# 對CSVList 進行排序
CSVList.sort(key=lambda x: x[0])
# ExcelListTitle 取得 CSVList[0]
ExcelListTitle = ExcelList[0]
# 刪除ExcelList[0]
ExcelList.pop(0)
# 對ExcelList 進行排序
ExcelList.sort(key=lambda x: x[0])
# 複製一份ExcelList
CopyExcelList = ExcelList[:]
ResultList = []
ResultList.append(CSVListTitle)
# 當兩者檔案學生名單長度不一致
if len(ExcelList)-len(CSVList) != 0:
for i in ExcelList:
for j in CSVList:
if i[0] == j[0]:
ResultList.append([str(j[0]),str(j[1]),str(i[1])])
CopyExcelList.remove(i)
# 印出不一致的學生名單
#print("印出不一致的學生名單:")
#print(CopyExcelList)
with open(current_directory+"\\CloudSchool_pwd2EIP_pwd_Exception.txt", "w",encoding="utf-8") as file:
file.write("印出不一致的學生名單:\n")
for i in CopyExcelList:
file.write(str(i)+"\n")
print("CloudSchool_pwd2EIP_pwd_Exception.txt 檔案已成功寫入!")
else: # 當兩者檔案學生名單長度一致
for i in range(len(CSVList)):
if ExcelList[i][0] == CSVList[i][0]:
ResultList.append([str(CSVList[i][0]),str(CSVList[i][1]),str(ExcelList[i][1])])
with open(current_directory+"\\CloudSchool_pwd2EIP_pwd.csv", mode="w", newline="", encoding="utf-8") as file:
writer = csv.writer(file)
writer.writerows(ResultList)
print("CloudSchool_pwd2EIP_pwd.csv檔案已成功寫入!")
沒有留言:
張貼留言