performance might be an issue. Basically, performance is bound by Excel input/output
(I/O) speed. The following rules should be followed whenever possible:
Reading/writing
Do not alternate reading with writing operations, since this might lower performance
significantly.
Vectorization
Use CellRange objects or Cell().table objects to read and write data in (large)
blocks instead of loops.
[ 49 ]
Use Python
For example, when you have to transform a data block, it is better to read it in total
with Python, to manipulate it with Python, and to write it back to the spreadsheet as a
block; cell-by-cell operations can be really slow.
Store data in Python
Store values in Python when possible rather than rereading them, especially for
performance-critical loops or similar operations.
See the relevant sections in the DataNitro documentation for details on how to work with
whole Worksheet and Workbook objects.
Plotting with DataNitro
A special topic when scripting Excel spreadsheets with DataNitro is plotting data
contained in a spreadsheet with Python instead of using Excel’s plotting capabilities.
Example 12-1 shows a Python script that is only executable if DataNitro is installed. It
retrieves Apple Inc. stock price data with the DataReader function from pandas (cf.
Chapter 6), writes the data to a newly generated Workbook object, and then plots the data
stored in the respective Worksheet object with Python — i.e., with the help of
DataNitro’s matplotlib.pyplot wrapper nitroplot — and exposes the result to the
spreadsheet.
Example 12-1. Plotting data stored in a spreadsheet with DataNitro and displaying a
matplotlib plot in the same spreadsheet
Plotting with DataNitro in Excel
dn_plotting.py
import pandas.io.data as web
import nitroplot as nplt
wrapper for matplotlib.pyplot (plt)
make a new workbook
wb = new_wkbk()
active_wkbk(wb)
rename_sheet(“Sheet1”, “Apple_Stock”)
read Apple Inc. stock data
aapl = web.DataReader(‘aapl’, data_source=‘yahoo’)[[‘Open’, ‘Close’]]
write the data to the new workbook
Cell(“A1”).df = aapl