wxpython菜单界面从mysql导出excel/csv

导出函数

这里使用的是wx.FileDialog,style=wx.FD_SAVE是自带的保存用的内置。
值得注意的一点是workbook = xlsxwriter.Workbook(pathname)这里一定要用完整路径，如果只是用文件名，最后也能看见导出文件，只不过会是空白文件。

    def export_excel(self, event):
        op = Sql_operation("login_users")
        np, fields = op.FindAll("stu_info")  # np：数据主体, fields：字段名

        with wx.FileDialog(self, "导出excel至所选目录", wildcard="csv files(.csv)|.csv|xlsx files(.xlsx)|.xlsx|xls files (.xls)|.xls",
                           style=wx.FD_SAVE | wx.FD_OVERWRITE_PROMPT, defaultFile='new.csv') as fileDialog:

            if fileDialog.ShowModal() == wx.ID_CANCEL:
                return  # the user changed their mind
            else:

                # save the current contents in the file
                pathname = fileDialog.GetPath()
                if pathname.split('.')[-1] == 'csv':
                    try:
                        with open(pathname, 'w') as file:
                            write = csv.writer(file,dialect='excel')
                            for item in np:
                                write.writerow(item)
                    except IOError:
                        wx.LogError("Cannot save current data in file '%s'." % pathname)
                elif pathname.split('.')[-1] in ['xls','xlsx']:
                    try:
                        title = ("姓名", "性别", "年龄", "ID账号", "学习课程", "联系方式")
                        workbook = xlsxwriter.Workbook(pathname)
                        booksheet = workbook.add_worksheet('Sheet 1')
                        for i, t in enumerate(title):
                            booksheet.write(0, i, t)
                        for i, row in enumerate(np):
                            for j, col in enumerate(row):
                                booksheet.write(i, j, col)
                        workbook.close()
                    except IOError:
                        wx.LogError("Cannot save current data in file '%s'." % pathname)

数据库

主要用到的是FindALL()

class Sql_operation(object):
    '''
    数据库操作
    '''

    # 用构造函数实现数据库连接，并引入mydb参数，实现调用不同的数据库
    def __init__(self, mydb):
        # 实例变量
        self.mydb = mydb
        # 打开数据库连接
        self.db = pymysql.connect(host="localhost",port=3306, user="root", password="1111", db=self.mydb, charset="utf8")
        # 创建游标对象
        self.cursor = self.db.cursor()

    # 定义查看数据表信息函数，并引入table_field、table_name参数，实现查看不同数据表的建表语句
    def FindAll(self, table_name):
        # 实例变量
        self.table_name = table_name
        # 定义SQL语句
        sql = "select * from %s" % (self.table_name)
        try:
            # 执行数据库操作
            self.cursor.execute(sql)
            # 处理结果
            data = self.cursor.fetchall()
            # 获取MySQL中的数据字段名称
            fields = self.cursor.description
            return data,fields
        except Exception as err:
            print("SQL执行错误，原因：", err)

创建菜单控件

    def menu(self):
        # 菜单
        menuBar = wx.MenuBar()  # 创建菜单栏
        #################################################
        exit_menu = wx.Menu()  # 创建一个 菜单
        m_exit = wx.MenuItem(exit_menu, id=wx.ID_CLOSE, text='&关闭\tCtrl+W', helpString="Close window and exit program.",
                             kind=wx.ITEM_NORMAL)  # 创建菜单项
        self.Bind(wx.EVT_MENU, self.OnClose, m_exit)  # 绑定事件
        menuBar.Append(exit_menu, "&StudentSys")  # 把 菜单 添加到菜单栏
        exit_menu.Append(m_exit)  # 菜单项添加到菜单
        #################################################
        info_menu = wx.Menu()  # 创建一个 菜单
        m_about = wx.MenuItem(info_menu, id=wx.ID_ABOUT, text="&关于\tCtrl-I",
                              helpString="Information about this  program", kind=wx.ITEM_NORMAL)
        self.Bind(wx.EVT_MENU, self.OnAbout, m_about)
        menuBar.Append(info_menu, "&Help")  # 这行要放在下一行的上面，尚不知原因，否则会导致菜单项不显示
        info_menu.Append(m_about)  # 菜单项添加到菜单
        #################################################
        export_menu = wx.Menu()  # 创建一个 菜单
        m_export_excel = wx.MenuItem(export_menu, id=1, text='&Excel\tCtrl+E', helpString="导出Excel",
                             kind=wx.ITEM_NORMAL)  # 创建菜单项
        export_menu.Append(m_export_excel)  # 菜单项添加到菜单
        self.Bind(wx.EVT_MENU, self.export_excel, m_export_excel)  # 绑定事件
        exit_menu.AppendSubMenu(export_menu, "选择导出格式")  # 把 子菜单 添加到菜单

        self.SetMenuBar(menuBar)  # 设置窗口框架的菜单栏为 menuBar
        # self.statusbar = self.CreateStatusBar()
        entries = [wx.AcceleratorEntry() for i in range(3)]


        entries[0].Set(wx.ACCEL_CTRL, ord('W'), wx.ID_CLOSE)  # CMD+W退出
        entries[1].Set(wx.ACCEL_CTRL, ord('I'), wx.ID_ABOUT)
        entries[2].Set(wx.ACCEL_CTRL, ord('E'), 1)
        # entries[3].Set(wx.ACCEL_NORMAL, wx.WXK_DELETE, wx.ID_CUT)

        accel = wx.AcceleratorTable(entries)
        self.SetAcceleratorTable(accel)