CODEWITHSUNDEEP
pythonexcelpandas
RCarmody
RCarmody

Reputation: 720

Pandas Create Excel with Table formatted as a Table

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)

Upvotes: 2

Views: 6824

Answers (1)

malvoisen
malvoisen

Reputation: 317

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()

Upvotes: 3

Related Questions