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

Yes, Xlsxwriter[docs][pypi] has a lot of utility for creating excel charts in Python. However, you will need to use the 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 2

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.