Uniting two dataframes (pandas) into one excel file and save it using tkinter filedialog

Question

I have a SQLite database with two tables. One for clients and another one for processes of each client. I need a function to export a pandas dataframe using filedialog. If I use XslsWriter, the Excel file is automatically saved where the program is running, but I wish the user to choose his own location.

I have tried using Xlsxwriter, which saves in the same directory where the program is running:

def exporta_tabela(self):
    self.conecta_db()
    self.cursor.execute("""SELECT * FROM clientes""")
    tabelaCli = self.cursor.fetchall()
    tabelaCli = pd.DataFrame(tabelaCli, columns=['COD', 'CPF', 'Nome', 'Sobrenome', 'Telefone', 'Estado', 'Cidade','Bairro', 'Logradouro', 'Num.', 'Comp.', 'Area', 'RegistradoEm'])
    writer = pd.ExcelWriter('clientes_processos.xlsx', engine='xlsxwriter')
    self.cursor.execute("""SELECT * FROM processos """)
    tabelaProc = self.cursor.fetchall()
    tabelaProc = pd.DataFrame(tabelaProc, columns=['ID', 'Adv_Resp', 'Tipo', 'Processo', 'Status', 'Cliente_COD'])
        tabelaCli.to_excel(writer, sheet_name='Clientes') 
        tabelaProc.to_excel(writer, sheet_name='Processos')
        writer.save()
    self.conn.commit()
    self.desconecta_db()

=====================================================================

And i have also tried without xlsxWriter, but it saves just one or the other sheet, and i need both:

def exporta_tabela(self):
    self.conecta_db()
    self.cursor.execute("""SELECT * FROM clientes""")
    tabelaCli = self.cursor.fetchall()
    tabelaCli = pd.DataFrame(tabelaCli, columns=['COD', 'CPF', 'Nome', 'Sobrenome', 'Telefone', 'Estado', 'Cidade','Bairro', 'Logradouro', 'Num.', 'Comp.', 'Area', 'RegistradoEm'])
    self.cursor.execute("""SELECT * FROM processos """)
    tabelaProc = self.cursor.fetchall()
    tabelaProc = pd.DataFrame(tabelaProc, columns=['ID', 'Adv_Resp', 'Tipo', 'Processo', 'Status', 'Cliente_COD'])
    with filedialog.asksaveasfile(mode='w', defaultextension='.xlsx') as file:
        tabelaCli.to_excel(file.name, sheet_name='Clientes') 
        tabelaProc.to_excel(file.name, sheet_name='Processos')
    self.conn.commit()
    self.desconecta_db()

Please edit your title to avoid using all-caps - which is basically equivalent to shouting. — Jon Skeet, Feb 19 '23 at 17:38

score 0 · Answer 1 · answered Feb 19 '23 at 21:54

Got it to work as follows:

def exporta_tabela(self):
    self.conecta_db()
    self.cursor.execute("""SELECT * FROM clientes""")
    tabelaCli = self.cursor.fetchall()
    self.cursor.execute("""SELECT * FROM processos """)
    tabelaProc = self.cursor.fetchall()
    tabelaCli = pd.DataFrame(tabelaCli, columns=['COD', 'CPF', 'Nome', 'Sobrenome', 'Telefone', 'Estado', 'Cidade',
    'Bairro', 'Logradouro', 'Num.', 'Comp.', 'Area', 'RegistradoEm'])
    tabelaProc = pd.DataFrame(tabelaProc, columns=['ID', 'Adv_Resp', 'Tipo', 'Processo', 'Status', 'Cliente_COD'])
    outpath = filedialog.asksaveasfile(mode='wb', defaultextension='.xlsx')
    with pd.ExcelWriter(outpath, engine='xlsxwriter') as writer:
        tabelaCli.to_excel(writer, sheet_name='Clientes')
        tabelaProc.to_excel(writer, sheet_name='Processos')
    self.conn.commit()
    self.desconecta_db()

Uniting two dataframes (pandas) into one excel file and save it using tkinter filedialog

1 Answers1