CODEWITHSUNDEEP
pythonpandasxlsxwriter
Ben Long
Ben Long

Reputation: 3

Having Trouble Writing Table to Excel with Python

Hi I am trying to create a table in excel using a dataframe from another excel spreadsheet and writing the table to a new one. I believe my code is correct but the table isn't writing to the new excel spreadsheet. Can someone take a look at my code and tell me what's wrong?

import xlsxwriter

import pandas as pd
import openpyxl as pxl
import xlsxwriter
import numpy as np
from openpyxl import load_workbook
path = '/Users/benlong/Downloads/unemployment.xlsx'

df = pd.read_excel(path)
rows = df.shape[0]
columns = df.shape[1]
wb = xlsxwriter.Workbook('UE2.xlsx')
ws = wb.add_worksheet('Sheet1')
ws.add_table(0,0,rows,columns, {'df': df})
wb.close()

Upvotes: 0

Views: 295

Answers (2)

ShinNShirley
ShinNShirley

Reputation: 388

You should convert your dataframe to list . By using df.values.tolist() and use the key data.

In your case , you also should set the header of df and avoid getting a nan value error. eg:

import xlsxwriter as xlw

# while got NaN/Inf values from ur dataframe , u'll get a value of '#NUM!' instead in saved excel
wb = xlw.Workbook('UE2.xlsx',{'nan_inf_to_errors': True})
ws = wb.add_worksheet('Sheet1')
cell_range = xlw.utility.xl_range(0, 0, rows, columns-1)
header = [{'header': str(di)} for di in df.columns.tolist()]
ws.add_table(cell_range, {'header_row': True,'first_column': False,'columns':header,'data':df.values.tolist()})
wb.close()

Upvotes: 1

micah
micah

Reputation: 21

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

You can try converting the dataframe to a list of lists and use the data keyword.

ws.add_table(0,0,rows,columns, {'data': df.values.T.tolist()})

Upvotes: 1

Related Questions