371 lines
15 KiB
Python
371 lines
15 KiB
Python
import os, sys
|
|
import pandas as pd
|
|
import random
|
|
import configparser
|
|
from PyQt5.QtWidgets import (
|
|
QApplication, QWidget, QVBoxLayout, QHBoxLayout, QPushButton, QLabel,
|
|
QFileDialog, QRadioButton, QTextEdit, QMessageBox, QLineEdit, QCheckBox,
|
|
QSpinBox
|
|
)
|
|
from PyQt5.QtCore import Qt, QThread, pyqtSignal
|
|
import xlwings as xw # xlwings 모듈 임포트
|
|
import traceback # traceback 모듈 임포트
|
|
|
|
|
|
class CommaSpinBox(QSpinBox):
|
|
def textFromValue(self, value):
|
|
return f"{value:,}" # 숫자에 콤마 추가
|
|
|
|
def valueFromText(self, text):
|
|
return int(text.replace(",", "")) # 콤마 제거하고 숫자 변환
|
|
|
|
|
|
class ExportThread(QThread):
|
|
log_signal = pyqtSignal(str)
|
|
finished_signal = pyqtSignal()
|
|
|
|
def __init__(self, data, export_format, output_folder, original_file_name, exclude_words, ban_words, shuffle_title, price_limit_enabled, price_limit):
|
|
super().__init__()
|
|
self.data = data
|
|
self.export_format = export_format
|
|
self.output_folder = output_folder
|
|
self.original_file_name = original_file_name
|
|
self.exclude_words = exclude_words
|
|
self.ban_words = ban_words
|
|
self.shuffle_title = shuffle_title
|
|
self.price_limit_enabled = price_limit_enabled
|
|
self.price_limit = price_limit
|
|
|
|
def run(self):
|
|
try:
|
|
# 타오바오URL이 비어있는 데이터 제외
|
|
self.data = self.data[self.data['타오바오URL'].notna() & self.data['타오바오URL'].str.strip().astype(bool)]
|
|
|
|
# 가격 제한 적용
|
|
if self.price_limit_enabled:
|
|
self.data = self.data[self.data['가격'] >= self.price_limit]
|
|
|
|
chunk_size = 50
|
|
for i in range(0, len(self.data), chunk_size):
|
|
chunk = self.data.iloc[i:i+chunk_size].copy()
|
|
|
|
# Exclude words from tags
|
|
if self.exclude_words:
|
|
for word in self.exclude_words:
|
|
chunk['태그'] = chunk['태그'].str.replace(f'\\b{word}\\b', '', regex=True).str.strip()
|
|
|
|
# Exclude words from titles
|
|
if self.ban_words:
|
|
for word in self.ban_words:
|
|
chunk['상품명'] = chunk['상품명'].apply(lambda x: x.replace(word, '').strip())
|
|
|
|
# Shuffle title words if needed
|
|
if self.shuffle_title:
|
|
chunk['상품명'] = chunk['상품명'].apply(self.shuffle_title_words)
|
|
|
|
# output_file = os.path.join(self.output_folder, f"{self.original_file_name}_percenty-{i//chunk_size + 1:02d}")
|
|
output_file = os.path.join(self.output_folder, f"{os.path.splitext(self.original_file_name)[0]}_percenty-{i//chunk_size + 1:02d}.xlsx")
|
|
|
|
# Create a DataFrame with exactly 50 rows, filling with empty values if necessary
|
|
if len(chunk) < chunk_size:
|
|
empty_rows = pd.DataFrame("", index=range(chunk_size - len(chunk)), columns=chunk.columns)
|
|
chunk = pd.concat([chunk, empty_rows], ignore_index=True)
|
|
|
|
if self.export_format == "퍼센티":
|
|
template_file = "퍼센티양식.xlsx"
|
|
with xw.App(visible=False) as app:
|
|
template_wb = app.books.open(template_file)
|
|
template_ws = template_wb.sheets[0]
|
|
|
|
new_wb = xw.Book()
|
|
new_ws = new_wb.sheets[0]
|
|
|
|
# Copy the template sheet to the new workbook
|
|
template_ws.api.Copy(Before=new_ws.api)
|
|
new_ws = new_wb.sheets[0]
|
|
|
|
# Write data to the new workbook
|
|
new_ws.range('B4:B53').value = [[v] for v in chunk['타오바오URL']]
|
|
new_ws.range('C4:C53').value = [[v] for v in chunk['상품명']]
|
|
new_ws.range('D4:D53').value = [[v] for v in chunk['가격']]
|
|
new_ws.range('F4:F53').value = [[v] for v in chunk['태그']]
|
|
new_ws.range('G4:G53').value = [[v] for v in chunk['퍼센티카테고리']]
|
|
|
|
new_wb.save(output_file)
|
|
new_wb.close()
|
|
template_wb.close()
|
|
|
|
elif self.export_format == "헤이셀러":
|
|
template_file = "헤이셀러양식.xlsx"
|
|
with xw.App(visible=False) as app:
|
|
template_wb = app.books.open(template_file)
|
|
template_ws = template_wb.sheets[0]
|
|
|
|
new_wb = xw.Book()
|
|
new_ws = new_wb.sheets[0]
|
|
|
|
# Copy the template sheet to the new workbook
|
|
template_ws.api.Copy(Before=new_ws.api)
|
|
new_ws = new_wb.sheets[0]
|
|
|
|
# Write data to the new workbook
|
|
new_ws.range('E3:E53').value = [[v] for v in chunk['타오바오URL']]
|
|
new_ws.range('B3:B53').value = [[v] for v in chunk['상품명']]
|
|
new_ws.range('D3:D53').value = [[v] for v in chunk['가격']]
|
|
new_ws.range('A3:A53').value = [[v] for v in chunk['카테고리']]
|
|
|
|
new_wb.save(output_file)
|
|
new_wb.close()
|
|
template_wb.close()
|
|
self.log_signal.emit(f"Exported {output_file}")
|
|
|
|
os.startfile(self.output_folder)
|
|
self.log_signal.emit("Export completed and folder opened.")
|
|
except Exception as e:
|
|
self.log_signal.emit(f"Error during export: {str(e)}")
|
|
self.log_signal.emit(traceback.format_exc())
|
|
|
|
self.finished_signal.emit()
|
|
|
|
def shuffle_title_words(self, title):
|
|
words = title.split()
|
|
if len(words) > 4:
|
|
prefix = words[:4]
|
|
suffix = words[4:]
|
|
random.shuffle(suffix)
|
|
return ' '.join(prefix + suffix)
|
|
return title
|
|
|
|
|
|
class ExcelProcessorApp(QWidget):
|
|
def __init__(self):
|
|
super().__init__()
|
|
|
|
self.initUI()
|
|
self.load_config()
|
|
self.copy_data = None
|
|
self.source_data = None
|
|
self.copy_file_path = None
|
|
self.source_file_path = None
|
|
|
|
def initUI(self):
|
|
self.setWindowTitle('WRMC TaomanXLS')
|
|
self.setGeometry(100, 100, 300, 650)
|
|
self.setFixedSize(300, 650)
|
|
|
|
main_layout = QVBoxLayout()
|
|
|
|
# First layout with buttons
|
|
button_layout = QHBoxLayout()
|
|
self.copy_button = QPushButton('카피맨 읽기')
|
|
self.copy_button.setMinimumHeight(70)
|
|
self.copy_button.clicked.connect(self.read_copy)
|
|
button_layout.addWidget(self.copy_button)
|
|
|
|
self.source_button = QPushButton('소싱맨 읽기')
|
|
self.source_button.setMinimumHeight(70)
|
|
self.source_button.clicked.connect(self.read_source)
|
|
self.source_button.setEnabled(False) # 소싱맨 푸쉬 버튼 비활성화
|
|
button_layout.addWidget(self.source_button)
|
|
|
|
self.export_button = QPushButton('출력')
|
|
self.export_button.setMinimumHeight(70)
|
|
self.export_button.clicked.connect(self.export_data)
|
|
button_layout.addWidget(self.export_button)
|
|
|
|
main_layout.addLayout(button_layout)
|
|
|
|
# New layout with exclude words
|
|
exclude_layout = QHBoxLayout()
|
|
self.exclude_label = QLabel('제외단어:')
|
|
exclude_layout.addWidget(self.exclude_label)
|
|
self.exclude_input = QLineEdit()
|
|
self.exclude_input.setPlaceholderText('오늘출발, 오늘발송')
|
|
exclude_layout.addWidget(self.exclude_input)
|
|
|
|
main_layout.addLayout(exclude_layout)
|
|
|
|
# New layout with shuffle title and ban words
|
|
shuffle_layout = QVBoxLayout()
|
|
shuffle_title_layout = QHBoxLayout()
|
|
self.shuffle_checkbox = QCheckBox('상품명섞기')
|
|
shuffle_title_layout.addWidget(self.shuffle_checkbox)
|
|
shuffle_layout.addLayout(shuffle_title_layout)
|
|
|
|
ban_layout = QHBoxLayout()
|
|
self.ban_label = QLabel('상품명금지단어:')
|
|
ban_layout.addWidget(self.ban_label)
|
|
self.ban_input = QLineEdit()
|
|
self.ban_input.setPlaceholderText('샤오미, 나이키')
|
|
ban_layout.addWidget(self.ban_input)
|
|
shuffle_layout.addLayout(ban_layout)
|
|
|
|
# 가격제한 체크박스와 CommaSpinBox, QLabel 추가
|
|
price_limit_layout = QHBoxLayout()
|
|
self.price_limit_checkbox = QCheckBox('가격제한')
|
|
self.price_limit_checkbox.stateChanged.connect(self.toggle_price_limit)
|
|
price_limit_layout.addWidget(self.price_limit_checkbox)
|
|
|
|
self.price_limit_spinbox = CommaSpinBox()
|
|
self.price_limit_spinbox.setRange(1000, 1000000)
|
|
self.price_limit_spinbox.setValue(10000)
|
|
self.price_limit_spinbox.setSingleStep(1000)
|
|
self.price_limit_spinbox.setSuffix(" 원")
|
|
self.price_limit_spinbox.setEnabled(False) # 기본값은 비활성화
|
|
price_limit_layout.addWidget(self.price_limit_spinbox)
|
|
|
|
self.price_limit_label = QLabel('이상')
|
|
self.price_limit_label.setEnabled(False) # 기본값은 비활성화
|
|
price_limit_layout.addWidget(self.price_limit_label)
|
|
|
|
shuffle_layout.addLayout(price_limit_layout)
|
|
|
|
main_layout.addLayout(shuffle_layout)
|
|
|
|
# Second layout with radio buttons
|
|
radio_layout = QHBoxLayout()
|
|
self.percent_radio = QRadioButton('퍼센티')
|
|
self.percent_radio.setChecked(True)
|
|
radio_layout.addWidget(self.percent_radio)
|
|
|
|
self.hayseller_radio = QRadioButton('헤이셀러')
|
|
self.hayseller_radio.setEnabled(False) # 헤이셀러 라디오 버튼 비활성화
|
|
radio_layout.addWidget(self.hayseller_radio)
|
|
|
|
main_layout.addLayout(radio_layout)
|
|
|
|
# Third layout with log text
|
|
self.log_text = QTextEdit()
|
|
self.log_text.setReadOnly(True)
|
|
log_layout = QVBoxLayout()
|
|
log_layout.addWidget(self.log_text)
|
|
main_layout.addLayout(log_layout)
|
|
|
|
# Footer layout with developer label
|
|
self.developer_label = QLabel('by 내차는 언제타냐')
|
|
main_layout.addWidget(self.developer_label, alignment=Qt.AlignCenter)
|
|
|
|
self.setLayout(main_layout)
|
|
self.center()
|
|
|
|
def center(self):
|
|
qr = self.frameGeometry()
|
|
cp = QApplication.desktop().screen().rect().center()
|
|
qr.moveCenter(cp)
|
|
self.move(qr.topLeft())
|
|
|
|
def log(self, message):
|
|
self.log_text.append(message)
|
|
self.log_text.ensureCursorVisible()
|
|
print(message) # 콘솔에도 로그 출력
|
|
|
|
def load_config(self):
|
|
self.config = configparser.ConfigParser()
|
|
if os.path.exists('config.ini'):
|
|
self.config.read('config.ini')
|
|
else:
|
|
self.config['DEFAULT'] = {
|
|
'ExcludeWords': '오늘출발, 오늘발송',
|
|
'BanWords': '샤오미, 나이키'
|
|
}
|
|
with open('config.ini', 'w') as configfile:
|
|
self.config.write(configfile)
|
|
self.exclude_input.setText(self.config['DEFAULT'].get('ExcludeWords', '오늘출발, 오늘발송'))
|
|
self.ban_input.setText(self.config['DEFAULT'].get('BanWords', '샤오미, 나이키'))
|
|
|
|
def save_config(self):
|
|
self.config['DEFAULT']['ExcludeWords'] = self.exclude_input.text()
|
|
self.config['DEFAULT']['BanWords'] = self.ban_input.text()
|
|
with open('config.ini', 'w') as configfile:
|
|
self.config.write(configfile)
|
|
|
|
def read_excel(self, title):
|
|
options = QFileDialog.Options()
|
|
file_path, _ = QFileDialog.getOpenFileName(self, title, "", "Excel Files (*.xlsx)", options=options)
|
|
if file_path:
|
|
self.log(f"Reading file: {file_path}")
|
|
try:
|
|
df = pd.read_excel(file_path, sheet_name='추출상점')
|
|
required_columns = ["퍼센티카테고리", "가격", "상품명", "태그", "타오바오URL", "카테고리"]
|
|
if all(col in df.columns for col in required_columns):
|
|
self.log(f"Successfully read data from {file_path}")
|
|
return df[required_columns], file_path
|
|
else:
|
|
self.log(f"Required columns are missing in the file: {file_path}")
|
|
QMessageBox.critical(self, "Error", "The selected file does not contain all required columns.")
|
|
except Exception as e:
|
|
self.log(f"Error reading file: {str(e)}")
|
|
QMessageBox.critical(self, "Error", "An error occurred while reading the file.")
|
|
return None, None
|
|
|
|
def read_copy(self):
|
|
self.copy_data, self.copy_file_path = self.read_excel("카피맨 파일 선택")
|
|
if self.copy_data is not None:
|
|
self.log("카피맨 데이터 읽기 완료")
|
|
|
|
def read_source(self):
|
|
self.source_data, self.source_file_path = self.read_excel("소싱맨 파일 선택")
|
|
if self.source_data is not None:
|
|
self.log("소싱맨 데이터 읽기 완료")
|
|
|
|
def export_data(self):
|
|
data = self.copy_data if self.copy_data is not None else self.source_data
|
|
if data is None:
|
|
self.log("데이터가 없습니다.")
|
|
QMessageBox.warning(self, "Warning", "No data to export.")
|
|
return
|
|
|
|
exclude_words = self.exclude_input.text().split(',')
|
|
exclude_words = [word.strip() for word in exclude_words if word.strip()]
|
|
|
|
ban_words = self.ban_input.text().split(',')
|
|
ban_words = [word.strip() for word in ban_words if word.strip()]
|
|
|
|
shuffle_title = self.shuffle_checkbox.isChecked()
|
|
price_limit_enabled = self.price_limit_checkbox.isChecked()
|
|
price_limit = self.price_limit_spinbox.value()
|
|
|
|
export_format = "퍼센티" if self.percent_radio.isChecked() else "헤이셀러"
|
|
self.log(f"Exporting data in {export_format} format")
|
|
|
|
# Set default export folder based on the source of data
|
|
if self.copy_data is not None:
|
|
default_folder = os.path.dirname(self.copy_file_path)
|
|
original_file_name = os.path.basename(self.copy_file_path)
|
|
elif self.source_data is not None:
|
|
default_folder = os.path.dirname(self.source_file_path)
|
|
original_file_name = os.path.basename(self.source_file_path)
|
|
else:
|
|
default_folder = ""
|
|
original_file_name = "output.xlsx"
|
|
|
|
output_folder = QFileDialog.getExistingDirectory(self, "Export Folder", default_folder)
|
|
if not output_folder:
|
|
self.log("출력 폴더를 선택하지 않았습니다.")
|
|
return
|
|
|
|
self.export_thread = ExportThread(data, export_format, output_folder, original_file_name, exclude_words, ban_words, shuffle_title, price_limit_enabled, price_limit)
|
|
self.export_thread.log_signal.connect(self.log)
|
|
self.export_thread.finished_signal.connect(self.on_export_finished)
|
|
self.export_button.setEnabled(False)
|
|
self.export_thread.start()
|
|
|
|
def on_export_finished(self):
|
|
self.export_button.setEnabled(True)
|
|
|
|
def toggle_price_limit(self):
|
|
state = self.price_limit_checkbox.isChecked()
|
|
self.price_limit_spinbox.setEnabled(state)
|
|
self.price_limit_label.setEnabled(state)
|
|
|
|
|
|
if __name__ == '__main__':
|
|
app = QApplication(sys.argv)
|
|
ex = ExcelProcessorApp()
|
|
|
|
# Add signal to save config on exit
|
|
app.aboutToQuit.connect(ex.save_config)
|
|
|
|
ex.show()
|
|
sys.exit(app.exec_())
|