Pandas Create Excel with Table formatted as a Table

Question

I have a .csv file that I am converting into a table format using the following python script. In order to make this useful, I need to create a table within the Excel that holds the data (actually formatted as a table (Insert > Table). Is this possible within python? I feel like it should be relatively easy, but can't find anything on the internet.

The idea here is that the python takes the csv file, converts it to xlsx with a table embedded on sheet1, and then moves it to the correct folder.

import os
import shutil
import pandas as pd

src = r"C:\Users\xxxx\Python\filename.csv"
src2 = r"C:\Users\xxxx\Python\filename.xlsx"

read_file = pd.read_csv (src) - convert to Excel
read_file.to_excel (src2, index = None, header=True)

dest = path = r"C:\Users\xxxx\Python\repository"

destination = shutil.copy2(src2, dest)

malvoisen · Accepted Answer · 2020-03-19 17:28:29Z

3

Edit: I got sidetracked by the original MWE.

This should work, using xlsxwriter:

import pandas as pd
import xlsxwriter

#Dummy data
my_data={"list1":[1,2,3,4], "list2":"a b c d".split()}
df1=pd.DataFrame(my_data)
df1.to_csv("myfile.csv", index=False)
df2=pd.read_csv("myfile.csv")

#List of column name dictionaries
headers=[{"header" : i} for i in list(df2.columns)]

#Create and propagate workbook
workbook=xlsxwriter.Workbook('output.xlsx')

worksheet1=workbook.add_worksheet()

worksheet1.add_table(0, 0, len(df2), len(df2.columns)-1, {"columns":headers, "data":df2.values.tolist()})

workbook.close()

edited Mar 19, 2020 at 17:28

answered Mar 19, 2020 at 15:38

malvoisen

3172 silver badges8 bronze badges

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

3 Comments

RCarmody Over a year ago

But this does not create a formal table within the xlsx - it only creates a sheet with the data in it.

Cavenfish Over a year ago

Hey @RCarmody I'm not sure you can actually make formal tables but you can use formatting to make informal tables. Take a look at the formatting documentation of xlsxwriter Edit: i was wrong, you can make tables

malvoisen Over a year ago

@RCarmody, my edited answer gives you code to create a formal table in Excel from any csv. If it helped you please consider marking my answer accepted.

Collectives™ on Stack Overflow

Pandas Create Excel with Table formatted as a Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related