import openpyxl import PySimpleGUI as sg from selenium import webdriver from selenium.webdriver.chrome.options import Options from selenium.webdriver.common.keys import Keys as keys from selenium.webdriver.common.by import By from selenium import webdriver from webdriver_manager.chrome import ChromeDriverManager import time from transformers import pipeline, AutoModelForSequenceClassification, BertJapaneseTokenizer import sys stdout1 = sys.stdout sg.theme('DarkPurple1') #　レイアウト（1段目：テキスト、２段目：テキスト、３段目：テキスト出力欄、４段目：テキスト入力欄、５段目：ボタン、ボタン、ボタン） layout = [ [sg.Text('Excelのパス名、シート名を入力してください:')], [sg.Text('「Clear」ボタンを押すと、画面をクリアします。:')], [sg.Text('パス名'),sg.In(size=(10,5),key='-IN-')], [sg.Text('シート名'),sg.In(size=(10,5),key='-IN-')], [sg.Text('出発地カラム'),sg.In(size=(3,1), key='-In-'),sg.In('到着地カラム'),sg.In(size=(3,1), key='-In-')], [sg.Text('金額カラム'),sg.In(size=(3,1), key='-In-'),sg.In('初期ロウ'),sg.In(size=(3,1), key='-In-')], [sg.Text('結果'),sg.Output(size=(10,5), key='-OUTPUT-')], [sg.Button('Go'), sg.Button('Clear'), sg.Button('Exit')] ] window = sg.Window('　Excel料金書き込みアプリ　', layout) event, values = window.read() if event == "Go": window.close() window.close() print(sys.stdout) sys.stdout = stdout1 print("OK") s,d,p = 22,29,35 wb = openpyxl.load_workbook(r'C:\Users\hirkatayama\Desktop\2022_インターンシップ\202209_dentsuu\lesson\TEI_Support_expenses1.xlsx') sheet = wb['支払伝票フォーマット'] options = Options() options.add_argument('--headless') path = r'C:\ProgramData\Anaconda3\chromedriver.exe' # driver = webdriver.Chrome(path, options = options) driver = webdriver.Chrome(ChromeDriverManager().install(), options = options) driver.get("https://www.jorudan.co.jp/norikae/") time.sleep(5) for i in range(10,99999): s1 = sheet.cell(row=i, column=s).value d1 = sheet.cell(row=i, column=d).value if s1 == None: break s2 = driver.find_element(By.ID,"eki1_in") d2 = driver.find_element(By.ID,"eki2_in") s2.send_keys(s1) d2.send_keys(d1) driver.find_element(By.NAME,"S").click(); time.sleep(5) p2 = driver.find_element(By.XPATH,"/html/body/div[1]/div[3]/div[3]/div[2]/div[4]/div[2]/table/tbody/tr[1]/td[5]").text p1 = p2.replace('円', '') p1 = p1.replace(',','') p1 = int(p1) sheet.cell(row=i, column=p).value = p1 driver.close() driver = webdriver.Chrome(executable_path="C:\Windows\System32\chromedriver.exe",options = options) driver.get("https://www.jorudan.co.jp/norikae/") time.sleep(5) driver.close() wb.save(r'C:\Users\hirkatayama\Desktop\2022_インターンシップ\202209_dentsuu\lesson\TEI_Support_expenses1_result.xlsx') wb.close()