class Gui:
def __init__(self):
self.HandleConfig = HandleConfig()
def ret_bool(self, source):
if source == 'D':
return True
else:
return False
def generate_layout(self):
default_values = self.HandleConfig.get_defaults()
if default_values['language'] == 'English':
# English gui
# menu
menu_def = [
['&Language', ['&中文', '&English']],
['&Database', ['&MySQL', '&Oracle', '&SQL Server']],
['&Data Source', ['&Directory', '&Files']],
['&Help', ['&About']],
]
# general
layout_general = [
[sg.Menu(menu_def)],
[sg.Text('Excel', size=(12, 1), text_color='red')],
[
sg.Input('{}'.format(default_values['file_dir']),
key='file_dir',
size=(42, 1),
visible=self.ret_bool(default_values['source'])),
sg.FolderBrowse(
initial_folder='{}'.format(default_values['file_dir']),
button_text='Choose Directory',
visible=self.ret_bool(default_values['source'])),
sg.Input(
'{}'.format(default_values['files']),
key='files',
size=(45, 1),
visible=not self.ret_bool(default_values['source'])),
sg.FilesBrowse(
button_text='Choose Files',
visible=not self.ret_bool(default_values['source'])),
],
[
sg.Text('{}'.format(default_values['dbtype']),
text_color='red',
key='dbtype'),
sg.Text('Connection', text_color='red'),
],
[
sg.Text('Host:', size=(5, 1)),
sg.Input('{}'.format(default_values['host']),
key='host',
size=(15, 1)),
sg.Text(' ' * 11),
sg.Text('Port:', size=(7, 1)),
sg.Input('{}'.format(default_values['port']),
key='port',
size=(15, 1)),
],
[
sg.Text('User:'******'{}'.format(default_values['user']),
key='user',
size=(15, 1)),
sg.Text(' ' * 11),
sg.Text('Password:'******'{}'.format(default_values['passwd']),
key='passwd',
size=(15, 1)),
],
[
sg.Text('Database:', size=(7, 1)),
sg.Input('{}'.format(default_values['dbname']),
key='dbname',
size=(48, 1)),
sg.Text(' ' * 1),
],
[
sg.Text('Mode:', text_color='red'),
sg.Text(' ' * 10),
sg.Radio('Overwrite',
group_id='mode',
key='mode1',
default=default_values['mode1']),
sg.Text(' ' * 10),
sg.Radio('Append',
group_id='mode',
key='mode2',
default=default_values['mode2']),
],
[sg.Button('Start', size=(52, 1), key='start')],
[sg.MLine(key='output', size=(58, 10), auto_refresh=True)],
]
# advanced
layout_advanced = [
[
sg.Text('CSV Encoding:', size=(12, 1)),
sg.Combo(['AUTO', 'UTF-8', 'ANSI', 'GBK'],
default_value=default_values['csv_encoding'],
key='csv_encoding',
size=(10, 1))
],
[
sg.Text('Replace To NULL:', size=(15, 1)),
sg.Input('{}'.format(default_values['na_values']),
key='na_values',
size=(40, 1)),
],
[
sg.Text('Append all data to one exists table:',
size=(25, 1)),
sg.Input(
default_values['tname'],
key='tname',
size=(25, 1),
),
],
[
sg.Text('Add Table Prefix:', size=(13, 1)),
sg.Input(
default_values['prefix'],
key='prefix',
size=(10, 1),
),
sg.Checkbox('Add a column is table name',
key='add_tname',
size=(22, 1),
default=default_values['add_tname']),
],
[
sg.Text('The column on row:', size=(15, 1)),
sg.Input(default_values['header'],
key='header',
size=(10, 1)),
sg.Text('', size=(3, 1)),
sg.Checkbox('Include Sub Directories',
key='loop_subdir',
size=(18, 1),
default=default_values['loop_subdir']),
],
[
sg.Checkbox('Skip Blank Rows',
key='del_blank_lines',
size=(15, 1),
default=default_values['del_blank_lines']),
sg.Checkbox('Trim Spaces',
key='trim',
size=(12, 1),
default=default_values['trim']),
sg.Checkbox('Skip Blank Sheets',
key='skip_blank_sheet',
size=(12, 1),
default=default_values['skip_blank_sheet']),
],
[
sg.Text('Run sql before starting:', size=(17, 1)),
sg.Input('{}'.format(default_values['sql_b4']),
key='sql_b4',
size=(32, 1)),
sg.FileBrowse(initial_folder='{}'.format(
default_values['sql_b4']),
button_text=' 选择 ')
],
[
sg.Text('Run sql after comleting:', size=(17, 1)),
sg.Input('{}'.format(default_values['sql_after']),
key='sql_after',
size=(32, 1)),
sg.FileBrowse(initial_folder='{}'.format(
default_values['sql_after']),
button_text=' 选择 ')
],
]
tab_layouts = [
sg.Tab('General', layout_general),
sg.Tab('Advanced', layout_advanced)
]
layout = [
[
sg.TabGroup([tab_layouts],
selected_background_color='red',
key='tabgroup')
],
]
else:
# menu
menu_def = [
['&语言', ['&中文', '&English']],
['&数据库', ['&MySQL', '&Oracle', '&SQL Server']],
['&数据源', ['&选择目录', '&选择文件']],
['&帮助', ['&关于']],
]
# general
layout_general = [
[sg.Menu(menu_def)],
[sg.Text('Excel 文件', size=(12, 1), text_color='red')],
[
sg.Input('{}'.format(default_values['file_dir']),
key='file_dir',
size=(50, 1),
visible=self.ret_bool(default_values['source'])),
sg.FolderBrowse(
initial_folder='{}'.format(default_values['file_dir']),
button_text='选择目录',
visible=self.ret_bool(default_values['source'])),
sg.Input(
'{}'.format(default_values['files']),
key='files',
size=(50, 1),
visible=not self.ret_bool(default_values['source'])),
sg.FilesBrowse(
button_text='选择文件',
visible=not self.ret_bool(default_values['source'])),
],
[
sg.Text('{}'.format(default_values['dbtype']),
text_color='red',
key='dbtype'),
sg.Text('连接', text_color='red'),
],
[
sg.Text('主机:', size=(5, 1)),
sg.Input('{}'.format(default_values['host']),
key='host',
size=(15, 1)),
sg.Text(' ' * 11),
sg.Text('端口:', size=(7, 1)),
sg.Input('{}'.format(default_values['port']),
key='port',
size=(15, 1)),
],
[
sg.Text('用户:', size=(5, 1)),
sg.Input('{}'.format(default_values['user']),
key='user',
size=(15, 1)),
sg.Text(' ' * 11),
sg.Text('密码:', size=(7, 1)),
sg.Input('{}'.format(default_values['passwd']),
key='passwd',
size=(15, 1)),
],
[
sg.Text('数据库:', size=(5, 1)),
sg.Input('{}'.format(default_values['dbname']),
key='dbname',
size=(50, 1)),
sg.Text(' ' * 1),
],
[
sg.Text('模 式:', text_color='red'),
sg.Text(' ' * 6),
sg.Radio('覆 盖',
group_id='mode',
key='mode1',
default=default_values['mode1']),
sg.Text(' ' * 15),
sg.Radio('追 加',
group_id='mode',
key='mode2',
default=default_values['mode2']),
],
[sg.Button('开 始', size=(52, 1), key='start')],
[sg.MLine(key='output', size=(58, 10), auto_refresh=True)],
]
# advanced
layout_advanced = [
[
sg.Text('CSV文件编码:', size=(12, 1)),
sg.Combo(['AUTO', 'UTF-8', 'ANSI', 'GBK'],
default_value=default_values['csv_encoding'],
key='csv_encoding',
size=(10, 1))
],
[
sg.Text('将这些值替换为null:', size=(15, 1)),
sg.Input('{}'.format(default_values['na_values']),
key='na_values',
size=(40, 1)),
],
[
sg.Text('为创建的表名添加前缀:', size=(18, 1)),
sg.Input(
default_values['prefix'],
key='prefix',
size=(20, 1),
),
],
[
sg.Text('将数据追加到已存在的表（追加模式有效）:', size=(34, 1)),
sg.Input(
default_values['tname'],
key='tname',
size=(20, 1),
),
],
[
sg.Text('指定列名所在行数:', size=(18, 1)),
sg.Input(default_values['header'],
key='header',
size=(10, 1)),
sg.Checkbox('添加一列值为表名',
key='add_tname',
size=(15, 1),
default=default_values['add_tname']),
],
[
sg.Checkbox('删除空行',
key='del_blank_lines',
size=(7, 1),
default=default_values['del_blank_lines']),
sg.Checkbox('去除字符前后空格',
key='trim',
size=(14, 1),
default=default_values['trim']),
sg.Checkbox('跳过空表',
key='skip_blank_sheet',
size=(6, 1),
default=default_values['skip_blank_sheet']),
sg.Checkbox('遍历子目录',
key='loop_subdir',
size=(9, 1),
default=default_values['loop_subdir']),
],
[
sg.Text('导入开始前运行sql:', size=(15, 1)),
sg.Input('{}'.format(default_values['sql_b4']),
key='sql_b4',
size=(32, 1)),
sg.FileBrowse(initial_folder='{}'.format(
default_values['sql_b4']),
button_text=' 选择 ')
],
[
sg.Text('导入结束后运行sql:', size=(15, 1)),
sg.Input('{}'.format(default_values['sql_after']),
key='sql_after',
size=(32, 1)),
sg.FileBrowse(initial_folder='{}'.format(
default_values['sql_after']),
button_text=' 选择 ')
],
]
tab_layouts = [
sg.Tab('常规', layout_general),
sg.Tab('高级', layout_advanced)
]
layout = [
[
sg.TabGroup([tab_layouts],
selected_background_color='red',
key='tabgroup')
],
]
return layout
# normal start, run with a gui
# cmd:python D:\Projects\ExcelToDatabase\main.py
# exe:ExcelToDatabase.exe
sg.ChangeLookAndFeel('dark')
HandleConfig = HandleConfig()
Gui = Gui()
def exception_format():
# format exception output
return "".join(
traceback.format_exception(sys.exc_info()[0],
sys.exc_info()[1],
sys.exc_info()[2],
limit=-1))
default_values = HandleConfig.get_defaults()
window = sg.Window('ExcelToDatabase {0}'.format(Version),
Gui.generate_layout(),
location=(700, 50))
while True:
try:
event, values = window.read()
if values is not None:
values['dbtype'] = default_values['dbtype']
values['schedule'] = False
values['source'] = default_values['source']
if event == "start":
# when click start button