3

I have exported data to excel using pandas dataframes and my next plan is to use the data I've exported with PowerApps. However, in order to do this the data needs to be formatted as an excel table and I was wondering if there's a way to automate this?

I've had a good look around and am struggling to find anything - please bear in mind I am in no way a python pro and am very new to pandas! Here's a condensed version of what I've got so far:

import pandas as pd

list = [['big', 'nested', 'list'],['big', 'nested', 'list'], ['big', 'nested', 'list'],['big', 'nested', 'list'],['big', 'nested', 'list']]
df = pd.DataFrame(data = list, columns = ['X','Y','Z'])
writer = pd.ExcelWriter('file.xlsx', engine='xlsxwriter')
df.to_excel(writer, index = False)
writer.save()

Ideally I'd like to do this in python rather than try and merge what I've done with VBA (which would seem like the more obvious choice I'm guessing) as I've no experience whatsoever in VBA nor would know how to link the code to signal the other.. However I'm happy to hear all suggestions! Thank you very much in advance

EDIT: What i'm wondering is if there's a way to turn this:

Current output

into this

Ideal Output

Colleen
  • 143
  • 1
  • 3
  • 14
  • OK, so your code failed? Or there is some problem? Because it seems nice. – jezrael Jul 02 '18 at 08:44
  • The code works fine - i'm wondering if you can create an excel table (e.g using excel formatting) from python – Colleen Jul 02 '18 at 08:46
  • hmm, so please be more specific, also check [this](https://xlsxwriter.readthedocs.io/format.html) – jezrael Jul 02 '18 at 08:47
  • @jezrael OP probably means Excel tables like https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c – AKX Jul 02 '18 at 08:47
  • Do you mean an Excel table that is created using `Ctrl + t` in Excel? – ScottMcC Jul 02 '18 at 08:47
  • You can manipulate the Microsoft Excel object model from Python and win32com; the code [will end up looking very similar to what you would have written in VBA](https://stackoverflow.com/questions/13509207/ms-word-r-w-in-python-python-docx-issue-and-win32com-references/13509702#13509702). – Zev Spitz Jul 02 '18 at 08:50
  • Yup that's exactly the one - sorry for the confusion. I've added an edit – Colleen Jul 02 '18 at 08:51

1 Answers1

3

Use XLSXWriter's add table option

worksheet.add_table('from_column:to_column', {'first_column': data, 'last_column': data})

For further reference you can refer the official Documentation HERE

hondvryer
  • 442
  • 1
  • 3
  • 18