How to use xlsxwriter .add_table() method with a dataframe?

user_10283278 0
Viewed 7017

my data is dataframe ' how can i do it ?

import xlsxwriter

workbook = xlsxwriter.Workbook("test1.xlsx")
worksheet = workbook.add_worksheet("sheli22")
worksheet.add_table('B3:F7', {'data': data,'style': 'Table Style Light 17','header_row': False})

workbook.close()
xlsxwriter dataframe python
2 Answers

According the xlsxwriter Documentation Section covering the .add_table() method, it expects that "the data structure should be an list of lists" (link to docs).

To create this list of lists from your dataframe, it will be necessary to use .T to transpose the dataframe and then .tolist() to convert the transposed dataframe into a list of lists.

I have a fully reproducible example using a sample dataframe below.

import pandas as pd
import xlsxwriter

data = pd.DataFrame({'a':[1,2,3,4,5],
                   'b':[2,3,4,5,6],
                   'c':[3,4,5,6,7],
                   'd':[4,5,6,7,8],
                   'e':[5,6,7,8,9]})

workbook = xlsxwriter.Workbook("test1.xlsx")
worksheet = workbook.add_worksheet("sheli22")
worksheet.add_table('B3:F7', {'data': data.values.T.tolist(),'style': 'Table Style Light 17','header_row': False})

workbook.close()

With Expected Output:

Expected test1.xlsx

user_72284460 
writer = pd.ExcelWriter(excel_file_name, engine='xlsxwriter')
worksheet = writer.sheets[sheet_name]

worksheet.add_table(start_row,start_col,end_row,end_col,{'style':'Table style light 16','header_row': False})

start_row is where your starting row of excel from where your table starts, start_col is the same as start_row, and end_row is start_row+(len(data frame)) and end_col is start_col + len(data frame.columns)

user_194019780
Related