Phpexcel Do I Have To Draw The Chart Or Can I Just Include One In The Excel?

Microsoft Excel is widely used in nigh every manufacture. Its intuitive interface and ease of use for organising information, performing calculations, and analysis of data sets has led to it being usually used in countless unlike fields globally.

Whether you lot're a fan of Excel or not, at some point y'all will have to deal with information technology! For many applications you lot won't want to do complex calculations or manage large data sets in Excel itself, but yous may need to take values from Excel as inputs, produce reports in an Excel format, or provide tools to Excel users. Python can be a better selection for complex tasks and fortunately there are many tools for the Python developer to piece of work with and so Excel and Python can be used together.

This post gives an overview of some of the most pop and useful tools out there to assist yous choose which is the right 1 for your specific application.

Below in that location'south a feature matrix outlining the different features of the packages for calling Python from Excel.

Building Interactive Python tools with Excel as a front-stop
- PyXLL – The Python Excel Add together-In
- pywin32, win32com and comtypes
- xlwings
- DataNitro
Reading and writing Excel workbooks
- OpenPyXL
- XlsxWriter
- XLTable
- Pandas
- xlrd and xlwt
- pyexcel
Boosted Resources

Building Interactive Python Tools with Excel as a Forepart-Terminate

Excel is a well known and really skillful user interface for many tasks. When you get into more complex tasks and processing larger datasets however y'all can shortly reach the limits of what can sensibly be achieved in Excel. Python is a popular choice for data science and other disciplines as information technology can handle these complex cases far better than Excel lonely. Past using both together and recognising the strengths of each, it's possible for you lot to build actually powerful interactive tools using Excel equally a convenient front end end, with all the heavy lifting done in Python.

Python is an extremely powerful language with an extensive ecosystem of 3rd party libraries. Leveraging Python in Excel spreadsheets tin be a fantastic way to enhance your productivity and remove the need for importing and exporting data into and out of Excel. Interactive worksheets can exist developed using Python lawmaking in the same way every bit you might employ VBA, but with all of the advantages of Python.

At that place are a few tools bachelor that can be used to bring Python to Excel and information technology can be difficult to know which 1 is right for dissimilar situations. Beneath is an overview of each, which I hope will highlight the differences betwixt them and aid y'all decide which ones are correct for what you demand to achieve.

Come across the table of features along with the packages that support them below.

PyXLL – The Python Excel Add-In

PyXLL is currently the only package that enables developers to write fully featured Excel addins in Python. Information technology embeds the Python interpreter into Excel so that it can be used as a complete VBA replacement. You tin think of it conceptually as being like to something like Excel-DNA for C#, except that it is dynamic and imports your Python lawmaking while Excel is running – so there's no add-in to build and no demand to restart Excel when modifying your Python code.

See PyXLL's Features

Using PyXLL, you tin can write Python code to create:

Worksheet functions (user defined functions, chosen from Excel worksheet formulas)
Macros
Menus
Custom Ribbon Bars
Existent Time Data feeds

Writing a user defined function with PyXLL requires the 'xl_func' decorator to exist applied to a normal Python function:

from pyxll import xl_func  @xl_func def py_test(a, b, c):     return (a + b) * c

PyXLL has a config file (pyxll.cfg) which contains a list of all the modules that will be imported when Excel starts. By adding the module in a higher place to the list in that file, PyXLL will expose the 'py_test' function to Excel equally a user defined function to be called from a worksheet.

Some boosted features of PyXLL are:

Array functions
PyXLL can work with arrays of data and has back up for NumPy and Pandas types. Functions returning arrays can automatically resize to avoid errors when the dimensions of a outcome change.
Real Time Information
Stream real fourth dimension data into Excel from Python with PyXLL's Real Time Information feature.
Object Cache
For functions that return Python objects, rather than elementary types (strings, numbers etc) or arrays (NumPy arrays and Pandas DataFrames or Series) PyXLL has a clever 'object cache'. Object identifiers are returned, and when passed into another office the identifier is used to find the original object. This allows objects to be passed between Python functions using Excel formulas. This tin can be very useful when dealing with large data sets where the whole data set doesn't need to be visible in Excel all at once, but instead is passed between Python functions – for instance, loading a large data set up and performing some assemblage operations and presenting the aggregate results in Excel.
Excel Object Model
PyXLL has integration with the main COM packages, pywin32 and comtypes, which allow the unabridged Excel Object Model to be used from Excel macros and functions written with PyXLL. This enables anything that could be done in VBA to exist done in Python. It likewise integrates with xlwings so that the xlwings API can as well be used to read and write from Excel.

For more than features take a expect at the feature matrix beneath.

Home Page | Download PyXLL | Documentation

Download PyXLL

pywin32 / comtypes

The unabridged Excel API (or Object Model) is exposed via COM. Everything that tin can be written equally a VBA macro can likewise be written using the Excel COM API in Python by using pywin32 or comtypes.

The Excel COM API tin can be used from outside of Excel (e.1000. from a running Python prompt, script or Jupyter notebook). If you already know how to do something in VBA then doing the equivalent job in Python via the COM API is generally quite straightforward. Calling a routine using pywin32 or comtypes from Excel (due east.m. from a button on the ribbon bar, carte du jour item or macro) can be done using PyXLL.

The Excel Object Model is documented hither https://docs.microsoft.com/en-gb/office/vba/api/overview/Excel/object-model and in one case you lot empathize the basic differences betwixt VBA and Python you will observe it'southward adequately simple to translate between the two.

To demonstrate let's go though an example. Suppose yous had the post-obit VBA code and want to translate it into Python:

          Sub Macro1()     Range('B11:K11').Select     Selection.AutoFill Destination:=Range('B11:K16'), Type:=xlFillDefault     Columns('B:K').Select     Pick.ColumnWidth = 4 End Sub

First of all we must get the Excel Awarding object in Python. This code can exist run from an interactive Python prompt or a Jupyter notebook, or even run within Excel itself using PyXLL.

          from win32com.client.gencache import EnsureDispatch  # Get the Excel Awarding COM object xl = EnsureDispatch('Excel.Application')

Now nosotros have the Application object we tin can telephone call the Range method in the same manner equally the VBA code above. The first of import divergence to find is that in VBA merely calling 'Range().Select' calls the Select method, but in Python we need to utilise '()' to call the method.

          40.Range('B11:K11').Select()

The next line requires a constant, 'xlFillDefault'. To access the same abiding in Python we use the 'win32com.customer.constants' module. Too observe that in VBA no parentheses are used when calling an object method, but in Python there are.

          from win32com.customer import constants  xl.Selection.AutoFill(Destination=xl.Range('B11:K16'), Type=constants.xlFillDefault)

The rest of the code is similar to those lines nosotros're just translated, so the entire part looks like

          from win32com.client.gencache import EnsureDispatch from win32com.client import constants  def Macro1():     xl = EnsureDispatch('Excel.Application')     xl.Range('B11:K11').Select()     xl.Option.AutoFill(Destination=40.Range('B11:K16'), Type=constants.xlFillDefault)     xl.Columns('B:Chiliad').Select()     xl.Selection.ColumnWidth = 4

The translated Python code looks very similar to the original VBA code! Automating tasks in Excel, or merely calling information technology interactively in this style from a Jupyter notebook can exist very powerful.

This Python code could exist called from Excel as a macro using PyXLL's "@xl_macro" decorator. Instead of using EnsureDispatch, pyxll.xl_app() should be used to ensure that if there are multiple Excel processes running the correct one is returned.

pywin32
comtypes
Python/Excel COM API Mini-cookbook

xlwings

xlwings provides a wrapper around the Excel COM API described to a higher place for simplifying many common tasks, such as writing Pandas DataFrames to an open Excel workbook. Information technology uses pywin32's COM wrappers and gives yous access to those, so you can ever drop down to using the normal Excel API should you demand to.

In the same way as pywin32 and comtypes, xlwings can talk to Excel from a normal Python prompt or Jupyter notebook. For calling lawmaking using xlwings from Excel itself, PyXLL provides a convenient fashion of getting the Excel Application object as an xlwings object. This allows you to script Excel in Python and trigger running your code from a ribbon button or bill of fare item. An example use-case could exist a ribbon button for fetching data from a database, building a report, and writing information technology direct into the running Excel.

The post-obit shows how values tin exist read and written to a running Excel workbook, including a Pandas DataFrame.

          import xlwings as xw  wb = xw.Volume('workbook.xlsx')  # Open an existing Workbook canvass = wb.sheets['Sheet1']  # read and write values from the worksheet canvass.range('A1').value = 'Foo' print(sheet.range('A1').value)  # Write a Pandas DataFrames straight to the Excel sheet import pandas every bit pd df = pd.DataFrame([[1,2], [3,4]], columns=['a', 'b'])  sht.range('A1').value = df  # Read the DataFrame back, using the 'expand' option to read the whole tabular array sht.range('A1').options(pd.DataFrame, expand='table').value

xlwings includes a way of writing user defined functions (UDFs) or worksheet functions in Python that are chosen from a formula in Excel, similar to the user defined functions offered past PyXLL. These rely on a server procedure running outside of Excel and VBA wrappers to call into that server. It's a simple solution with some drawbacks, such equally poor performance and that those functions are merely available from the workbook containing the VBA wrappers.

DataNitro

DataNitro is another API to control Excel from Python. It's non clear what the advantage over its API and the existing and well understood Microsoft Excel COM API is, but information technology does permit you to write and run scripts without leaving Excel. Information technology has rudimentary support for user defined functions (worksheet functions), simply they run outside of the Excel process and only work if there is but ane Excel process running.

DataNitro is no longer under active evolution and is not available to license whatever more, merely it was included here for completeness.

Characteristic Matrix For Integrating Python and Excel

Characteristic	DataNitro	xlwings	PyXLL	Comments
Basic worksheet functions	✔	✔	✔	DataNitro and xlwings use an external Python procedure, xlwings requires VBA wrapper code
Existent time information	✘	✘	✔	Stream real fourth dimension data into Excel worksheets
Ribbon customisation	✘	✘	✔	Give users a rich user feel with custom ribbon menus
Custom chore panes	✘	✘	✔	Integrate Python UI components into Excel
Menu functions	✘	✘	✔	Call Python code from the Excel card
Object Enshroud	✘	✘	✔	Pass Python objects between worksheet functions seamlessly via an object cache
IntelliSense	✘	✘	✔	IntelliSense tooltip as y'all type – PyXLL integrates with the ExcelDNA Intellisense Addin
Thread safe worksheet functions	✘	✘	✔	Meliorate worksheet responsiveness by using Excel's own threadpool to run worksheet functions concurrently
Asynchronous functions	✘	✘	✔	Don't block Excel waiting for long running functions
Macros	✘	✔	✔	Macros are functions that tin exist attached to UI elements like buttons or called from VBA
Keyboard shortcuts	✘	✘	✔	Keyboard shortcuts can be assigned to macros with PyXLL
Macro sheet equivalent functions	✘	✘	✔	Retrieve into Excel from a worksheet function
Function documentation	✘	✔	✔	Include Python part docstrings in the Excel function wizard
Automatically resize arrays	✘	✔	✔	Array functions can resize automatically
Volatile Functions	✘	✔	✔	Volatile functions are called every time a worksheet is recalculated
Total Excel API exposed	✘	✔	✔	xlwings uses pywin32, PyXLL users can cull betwixt pywin32, comtypes or xlwings
Reload without restarting Excel	✔	✔	✔	Modules can be reloaded without restarting Excel. PyXLL also supports 'deep reloading' where all module dependencies are also reloaded.
Automated reloading	✘	✘	✔	Reload automatically whenever changes are made to your code.

See PyXLL's Features Download PyXLL

Reading and Writing Excel workbooks

For some tasks you may need to read or write an Excel file directly. For batch processing or tasks running on a server Excel may non be installed. The post-obit packages let you to read and write Excel files directly without needing to utilise Excel.

OpenPyXL

For working with Excel 2010 onwards, OpenPyXL is a great all round choice. Using OpenPyXL you can read and write xlsx, xlsm, xltx and xltm files. The following code shows how an Excel workbook can be written as an xlsx file with a few lines of Python.

          from openpyxl import Workbook wb = Workbook()  # catch the active worksheet ws = wb.active  # Data tin be assigned directly to cells ws['A1'] = 42  # Rows can besides be appended ws.append([1, 2, 3])  # Save the file wb.salve('sample.xlsx')

Don't confuse OpenPyXL with PyXLL. The two are completely different and serve different purposes. OpenPyXL is a package for reading and writing Excel files, whereas PyXLL is a tool for edifice fully featured Excel Add together-Ins for integrating Python code into Excel.

OpenPyXL covers more advanced features of Excel such equally charts, styles, number formatting and conditional formatting. Information technology fifty-fifty includes a tokeniser for parsing Excel formulas!

One really nice feature for writing reports is its built-in support for NumPy and Pandas data. To write a Pandas DataFrame all that's required is the included 'dataframe_to_rows' function:

          from openpyxl.utils.dataframe import dataframe_to_rows  wb = Workbook() ws = wb.agile  for r in dataframe_to_rows(df, index=Truthful, header=True): ws.append(r)  wb.save('pandas_openpyxl.xlsx')

If you demand to read Excel files to extract information then OpenPyXL can do that as well. The Excel file types are incredibly complicated and openpyxl does an amazing job of reading them into a form that's easy to access in Python. There are some things that openpyxl can't load though, such as charts and images, so if you open up a file and save it with the same name then some elements may exist lost.

          from openpyxl import load_workbook  wb = load_workbook(filename = 'book.xlsx') sheet_ranges = wb['range names'] print(sheet_ranges['D18'].value)

A possible downside of OpenPyXL is that it can exist quite slow for handling large files. If you take to write reports with thousands of rows and your application is time-sensitive then XlsxWriter or PyExcelerate may be better choices.

download openpyxl
openpyxl documentation

XlsxWriter

If you lot only demand to write Excel workbooks and not read them so XlsxWriter is an piece of cake to use package to utilize that works well. If you are working with big files or are particularly concerned almost speed then you may notice XlsxWriter a meliorate pick than OpenPyXL.

XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file. It supports features such as formatting and many more than, including:

100% compatible Excel XLSX files.
Full formatting.
Merged cells.
Defined names.
Charts.
Autofilters.
Information validation and drop down lists.
Provisional formatting.
Worksheet PNG/JPEG/BMP/WMF/EMF images.
Rich multi-format strings.
Prison cell comments.
Textboxes.
Integration with Pandas.
Retentivity optimization mode for writing large files.

Writing Excel workbooks using XlsxWriter is uncomplicated enough. Cells tin can be written to using the Excel address notation (eg 'A1') or row and column numbers. Below is a basic instance that shows creating a workbook, adding some data and saving information technology every bit an xlsx file.

          import xlsxwriter  workbook = xlsxwriter.Workbook('hello.xlsx') worksheet = workbook.add_worksheet()  worksheet.write('A1', 'How-do-you-do world')  workbook.close()

If y'all are using Pandas and so you'll desire to use XlsxWriter'southward Pandas integration. It takes the hard piece of work out of writing Pandas DataFrames to Excel, and even creating charts.

          import pandas every bit pd  # Create a Pandas dataframe from the information. df = pd.DataFrame({'Data': [10, 20, thirty, 20, 15, 30, 45]})  # Create a Pandas Excel writer using XlsxWriter as the engine. writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')  # Get the xlsxwriter objects from the dataframe writer object. workbook  = author.book worksheet = writer.sheets['Sheet1']  # Create a chart object. chart = workbook.add_chart({'type': 'cavalcade'})  # Configure the serial of the chart from the dataframe data. chart.add_series({'values': '=Sheet1!$B$ii:$B$eight'})  # Insert the nautical chart into the worksheet. worksheet.insert_chart('D2', chart)  # Catechumen the dataframe to an XlsxWriter Excel object. df.to_excel(writer, sheet_name='Sheet1')  # Close the Pandas Excel writer and output the Excel file. writer.relieve()

When referencing the Pandas data in the worksheet (every bit the formula in the nautical chart above does), you take to figure out where the data volition exist in the worksheet so that the formulas betoken to the correct cells. For reports involving a lot of formulas or charts this can get problematic as doing something as as simple as adding an actress row requires adjusting all affected formulas. For reports like that the parcel 'xltable' tin help.

download xlsxwriter
xlsxwriter documentation

XLTable

XLTable is a higher level library for building Excel reports from pandas DataFrames. Rather than writing the workbook cell by cell or row by row, whole tables are added and tin include formulas that reference other tables without having to know ahead of time where those tables will exist. For more than complex reports involving formulas xltable tin be very useful.

The chief characteristic that makes xltable more useful than but writing the Excel files directly is that it can handle tables with formulas that relate to cells in the workbook, without having to know in advance where those tables will be placed on a worksheet. Therefore just when all the tables have been added to the workbook and the workbook is being written are formulas resolved to their final jail cell addresses.

If you need to write a report that includes formulas rather than just information, XLTable makes information technology easier by tracking the prison cell references and so you don't have to construct the formulas by hand and worry about references changing when tables grow or new rows or columns are added.

          from xltable import * import pandas equally pd  # create a dataframe with 3 columns where the last is the sum of the outset ii dataframe = pd.DataFrame({         'col_1': [1, 2, 3],         'col_2': [4, 5, six],         'col_3': Cell('col_1') + Cell('col_2'),     }, columns=['col_1', 'col_2', 'col_3'])  # create the named xltable Tabular array instance table = Table('table', dataframe)  # create the Workbook and Worksheet objects and add table to the sail sheet = Worksheet('Sheet1') sheet.add_table(table)  workbook = Workbook('example.xlsx') workbook.add_sheet(sheet)  # write the workbook to the file using xlsxwriter workbook.to_xlsx()

XLTable can employ either XlsxWriter to write an xlsx file, or it tin utilize pywin32 (win32com) to write direct to an open Excel awarding (Windows only). Writing directly to Excel is good for interactive reports. For example, y'all could take a button in the Excel ribbon that a user could press to query some data and produce a written report. By writing it directly to Excel they can go that study immediately in Excel without having it written to a file first. For details of how to customise the Excel ribbon in Excel see PyXLL: Customizing the Ribbon.

download xltable
xltable documentation

Pandas

For working with ranges of data and reading or writing them to Excel workbooks with no frills, using pandas can be a very quick and effective method. If you don't need much in the way of formatting and simply intendance about getting data into or out of Excel workbooks so the pandas functions "read_excel" and "to_excel" may be just what you need.

          df = pd.DataFrame([         ('string1', 1),         ('string2', 2),         ('string3', 3)      ], columns=['Proper noun', 'Value'])  # Write dataframe to an xlsx file df.to_excel('tmp.xlsx')

For more complex tasks considering XlsxWriter, OpenPyXL and XLTable all have Pandas integration any of those tin can as well exist used to write Pandas DataFrames to Excel. But, for only getting data into Excel using Pandas straight every bit above is very convenient.

pandas homepage

xlrd/xlwt

xlrd and xlwt read and write the former Excel .xls files respectively. These are included in this list for completeness, but are now really merely used when you lot are forced to deal with the legacy xls file format. They are both extremely mature packages that are very capable and stable, but xlwt will never be extended to support the newer xlsx/xlsm file formats therefore for new code dealing with modern Excel file formats they are no longer the best choice.

download xlrd
xlrd documentation
download xlwt
xlwt documentation

pyexcel

If you lot need to deal with multiple file formats (eg xlsx, xls, ods or csv) then pyexcel can be used to handle all of them. Information technology wraps some of the packages above (xlrd/xlwt, openpyxl and xlxswriter and others) to give a single consequent API regardless of the file format you are working with.

The pyexcel packages focuses on data rather than formatting, then if you are looking to produce loftier quality reports for Excel users and so you should consider the alternatives, but if you need to extract data from a spreadsheet without worrying so much nigh the file type then this bundle will help you lot do that.

          import pyexcel every bit pe records = pe.iget_records(file_name='your_file.xls') for tape in records:     impress('%s is anile at %d' % (record['Name'], tape['Age']))  # Prints... # Adam is aged at 28 # Beatrice is anile at 29 # Ceri is anile at thirty # Dean is aged at 26  pe.free_resources()