Python Excel Charts:

If you want to create excel charts with python, openpyxl is there to help you with your Python excel charts. Openpyxl module supports creating all major type of charts like bar chart, line chart, scatter and pie chart with the data in the cells of a given sheet. To create excel charts with openpyxl, follow these simple steps.

1. Using rectangular selection of cells, you have to create a reference object.
2. Pass this reference object and create a Series object.
3. Next create a chart object for our chart.
4. Now you have to use append quality, append the series object created in step 2 to chart object created in step 3.
5. You can set top, left, width, height variables of the chart object.
6. Final step will be that we add the chart object to our Worksheet object.

If you want to Read, Write and Manipulate(Copy, cut, paste, delete or search for an item or value etc) Excel files in Python with simple and practical examples I will suggest you to see this simple and to the point Python Excel Openpyxl Course with examples about how to deal with MS Excel files in Python. This video course teaches efficiently how to manipulate excel files and automate tasks.

Everything you do in Microsoft Excel, can be automated with Python. So why not use the power of Python and make your life easy. You can make intelligent and thinking Excel sheets, bringing the power of logic and thinking of Python to Excel which is usually static, hence bringing flexibility in Excel and a number of opportunities.

Automate your Excel Tasks and save Time and Effort. You can save dozens or hundreds of hours by Python Excel Automation with just a single click get your tasks done with in seconds which used to take hours of Manual Excel and Data Entry Work.

 

A little explanation of openpyxl reference object:

 

When we want to create reference object in python excel charts using openpyxl, we have to call a specific function, openpyxl.charts.Reference(), this function needs three arguments,

• Worksheet object which contains data about chart
• Two integers, in the form of tuple. First integer is the row and second is column. This tuple represents the top left cell of the rectangular cell selection for chart depiction.
• Similarly another tuple of two numbers, for bottom right cell of the chart. Again first integer is the row and second integer is column.

 

Python excel chart code:

 

>>> import openpyxl

>>> chartwb = openpyxl.Workbook()

>>> sheet = chartwb.get_active_sheet()

>>> for x in range(1, 10):         # creating temp  data in column A

        sheet['A' + str(i)] = i

 

>>> referenceobj = openpyxl.charts.Reference(sheet, (1, 1), (10, 1))

 

>>> serObj = openpyxl.charts.Series(referenceObj, title='Chart Series')

 

>>> chartObject = openpyxl.charts.BarChart()

>>> chartObject.append(serObj)

>>> chartObject.drawing.top = 50       # set the position

>>> chartObject.drawing.left = 100

>>> chartObject.drawing.width = 300    # set the size

>>> chartObject.drawing.height = 200

 

>>> sheet.add_chart(chartObject)

>>> chartwb.save('example.xlsx')

 

 

The above code implements the steps mentioned above. Similarly with the help of openpyxl, we can also create pie charts, line charts and pie charts.

Openpyxl.charts.LineChart( ) will create a line chart.

Openpyxl.charts.ScatterChart( ) will create a scatter chart.

Openpyxl.charts.PieChart( ) will create a Pie chart.

 

Hence with the basic technique of creating reference objects and openpyxl functions, we can create most type of charts in our excel file using python, and ofcourse without using excel software.