New Feature Request: Export plots to excel/pdf

[ghost]

The current functionality to export dataframes to excel is excellent:
i.e. df.to_excel('filename')

In the process of introducing python to my company, I realized I needed to not only export the dataframes but also plots. I searched but did not find this functionality. This is more of a reporting need, but would be very useful.

[changhiskhan]

Do you want to export a matplotlib plot as an image to the Excel report or actually create an Excel plot?

[ghost]

If both can be done then great, but I was specifically looking to export a matplotlib plot as an image to the Excel report.

[changhiskhan]

Excel plots: openpyxl has a charting API you can use for now
Images: xlwt supports inserting bitmaps, so as a possible workaround for now, you can save the figure via matplolib (savefig) and then try inserting the image via xlwt?

[ghost]

Code to get a picture to excel:
from xlwt import *

Create a workbook

wb = Workbook()

Add a sheet/tab

ws0 = wb.add_sheet('Picture_Test')

Write text to cell at location (0,1)

ws0.write(0, 1, "Moving Average")

Add picture at location (2,1)

Note: Only accepts bmp files

i.e. ws0.insert_bitmap('C:\Users\username\Desktop/test.bmp', 2, 1)

ws0.insert_bitmap('test.bmp', 2, 1)

Write excel file

Note: This will overwrite any other files with the same name

wb.save('hello.xls')

** The only remaining issue is getting both the dataframe and the picture in the same excel file and in seperate tabs

[changhiskhan]

Please escape your #s with a slash :)

[ghost]

Entire code to export df and a bmp to excel using xlwt:

from xlwt import *
from pandas import *
import numpy as np

Create a dataframe with dates as your index

data = [1,2,3,4,5,6,7,8,9,10]
idx = date_range('1/1/2012', periods=10, freq='MS')
df = DataFrame(data, index=idx, columns=['Revenue'])

Create a workbook

wb = Workbook()

Add a sheet/tab

ws0 = wb.add_sheet('Picture_Test')
ws1 = wb.add_sheet('DataFrame_Test')

Write text to cell at location (0,1)

ws0.write(0, 1, "Moving Average")

Write dataframe

date_xf = easyxf(num_format_str='DD/MM/YYYY') # sets date format in Excel
num_xf = easyxf(num_format_str='#0.000000') # sets date format in Excel
for i, (date, row) in enumerate(df.T.iteritems()):
print i, date, row[0],type(row[0]).name
if type(date).name == 'Timestamp':
ws1.write(i,0,date,date_xf)
elif type(date).name == 'str':
ws1.write(i,0,date)
else:
ws1.write(i,0,date.astype(np.float),num_xf)
if type(row[0]).name == 'Timestamp':
ws1.write(i,1,row[0].astype(np.float),date_xf)
elif type(row[0]).name == 'str':
ws1.write(i,1,row[0].astype(np.float))
else:
ws1.write(i,1,row[0].astype(np.float),num_xf)

Add picture at location (2,1)

Note: Only accepts bmp files

i.e. ws0.insert_bitmap('C:\Users\username\Desktop/test.bmp', 2, 1)

ws0.insert_bitmap('test.bmp', 2, 1)

Write excel file

Note: This will overwrite any other files with the same name

wb.save('hello.xls')

[jtratner]

@pichonz you have another option with xlsxwriter - @jmcnamara (xlsxwriter package author) has docs on it here - http://pandas-xlsxwriter-charts.readthedocs.org/en/latest/index.html . Supports inserting real excel charts.

[ghost]

@jmcnamara package and docs are excellent, plenty good enough so that pandas needn't handle
this itself. Let a 1000 projects bloom!.

I added it to the cookbook a while ago, but not the pandas ecosystem section of the docs
since it's more a howto then a package. Maybe we should put it there to get more users
aware of it.

@jmcnamara all your docs are beautiful, what's your secret?

[jmcnamara]

@y-p

Maybe we should put it there to get more users aware of it.

Yes. I'll look into that.

all your docs are beautiful, what's your secret?

Late nights and good templates.

And thanks. :-)

[jtratner]

@jmcnamara I totally agree with @y-p - great work on that package - very impressive!