Creating an excel table in python

Question

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

OK, so your code failed? Or there is some problem? Because it seems nice. — jezrael
– jezrael, Commented Jul 2, 2018 at 8:44
The code works fine - i'm wondering if you can create an excel table (e.g using excel formatting) from python — Colleen
– Colleen, Commented Jul 2, 2018 at 8:46
@jezrael OP probably means Excel tables like support.office.com/en-us/article/… — AKX
– AKX, Commented Jul 2, 2018 at 8:47
Do you mean an Excel table that is created using Ctrl + t in Excel? — ScottMcC
– ScottMcC, Commented Jul 2, 2018 at 8:47

hondvryer · Accepted Answer · 2018-07-02 09:26:20Z

4

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

edited Jul 2, 2018 at 9:26

answered Jul 2, 2018 at 8:57

hondvryer

4521 gold badge3 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Colleen Over a year ago

Thank you - this looks perfect!

hondvryer Over a year ago

@Colleen if it was useful, green-tick the answer

Colleen Over a year ago

Sure thing - sorry i'm new to posting on the site. This did exactly what I needed

Collectives™ on Stack Overflow

Creating an excel table in python

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related