use python to generate graph in excel

Question

I have been trying to generate data in Excel. I generated .CSV file. So up to that point it's easy. But generating graph is quite hard in Excel...

I am wondering, is python able to generate data AND graph in excel? If there are examples or code snippets, feel free to post it :)

Or a workaround can be use python to generate graph in graphical format like .jpg, etc or .pdf file is also ok..as long as workaround doesn't need dependency such as the need to install boost library.

Answer 1

You have 2 options:

If you are on windows, you can use pywin32 (included in ActivePython) library to automate Excel using OLE automation.

from win32com.client import Dispatch
ex = Dispatch("Excel.Application")
# you can use the ex object to invoke Excel methods etc.

If all you want to just generate basic plots etc. you can use matplotlib.

Answer 2

I suggest you to try gnuplot while drawing graph from data files.

Answer 3

Yes, Xlsxwriter[docs][pypi] has a lot of utility for creating excel charts in Python. You will need to however use the an xlsx file format, there is not much feedback for incorrect parameters, and you cannot read your output.

import xlsxwriter
import random
# Example data
# Try to do as much processing outside of initializing the workbook
# Everything beetween Workbook() and close() gets trapped in an exception
random_data = [random.random() for _ in range(10)]
# Data location inside excel
data_start_loc = [0, 0] # xlsxwriter rquires list, no tuple
data_end_loc = [data_start_loc[0] + len(random_data), 0]

workbook = xlsxwriter.Workbook('file.xlsx')

# Charts are independent of worksheets
chart = workbook.add_chart({'type': 'line'})
chart.set_y_axis({'name': 'Random jiggly bit values'})
chart.set_x_axis({'name': 'Sequential order'})
chart.set_title({'name': 'Insecure randomly jiggly bits'})

worksheet = workbook.add_worksheet()

# A chart requires data to reference data inside excel
worksheet.write_column(*data_start_loc, data=random_data)
# The chart needs to explicitly reference data
chart.add_series({
    'values': [worksheet.name] + data_start_loc + data_end_loc,
    'name': "Random data",
})
worksheet.insert_chart('B1', chart)

workbook.close()  # Write to file

Answer 4

@David Gao, I am looking at doing something similar. Currently I am looking at using the raw csv or converting it to json and just dropping it in a folder that is being read by jqplot.jquery plotting and graphing library. Then all I need to do is have the user or myself display the plot in any web browser.

Answer 5

If you do decide to use matplotlib, check out my excel to python class PyWorkbooks to get the data. It lets you retrieve data efficiently and easily as numpy arrays (the native datatype for matplotlib).

https://sourceforge.net/projects/pyworkbooks/