Use python to generate graph in excel

Question

I am trying to generate data in Excel. I created a .CSV file. Therefore, this is easy. But generating a graph is quite difficult in Excel ...

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

Or a workaround could be to use python to generate a graph in graphical format, such as a .jpg, etc or .pdf file, also good .. for a long time, since the workaround does not require a dependency, such as the need to install a boost library.

Answer 1

You have 2 options:

If you are in windows, you can use pywin32 (included in ActivePython ) 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 you just want to create basic graphs, etc., you can use matplotlib .

Answer 2

Yes, Xlsxwriter [ docs ] [ pypi ] has a lot of utility for creating excel diagrams in Python. However, you will need to use the xlsx file format, there are not many 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 3

I suggest you try gnuplot when drawing a graph from data files.

Answer 4

If you decide to use matplotlib, check out my advantage for PyWorkbooks python class to get data. It allows you to efficiently and easily retrieve data in the form of numpy arrays (native data type for matplotlib).

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

Answer 5

@ David Gao, I look at something like that. I am currently considering using raw csv or converting it to json and just dropping it into a folder that is read by jqplot. jquery plotting and graphing library . Then all I need to do is get the user or the plot to display in any web browser.