Development/Tutorials/KSpread Scripting: Difference between revisions

From KDE TechBase
No edit summary
(Mark for updating)
 
(55 intermediate revisions by 3 users not shown)
Line 1: Line 1:
{{Review|
* Port to KF5
* Rename to Calligra Sheets
}}
==Intro==
==Intro==


Line 36: Line 41:
===Scripting Handbook===
===Scripting Handbook===


The [http://kross.dipe.org/dox/kspread.html KSpread Scripting Handbook] contains a full reference of all objects and methods accessible from within the scripting backends.
The [http://kross.dipe.org/dox/kspread.html KSpread Scripting Handbook] ([http://kross.dipe.org/dox/kspread.pdf PDF]) contains a full reference of the functionality accessible from within the scripting backends.


The Handbook is generated from the sourcecode using doxygen and KWord's [[Development/Tutorials/KWord_Scripting#Import_combined_Doxygen_XML_File|Import Doxygen XML File]] python script.
The Handbook is generated from the sourcecode using doxygen and KWord's [[Development/Tutorials/KWord_Scripting#Import_combined_Doxygen_XML_File|Import Doxygen XML File]] python script.
Line 49: Line 54:
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripting.dox?view=markup KSpread Scripting Plugin ReadMe]
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripting.dox?view=markup KSpread Scripting Plugin ReadMe]
* [http://kross.dipe.org Kross Homepage]
* [http://kross.dipe.org Kross Homepage]
* [[Development/Tutorials/Kross-Tutorial|Kross Tutorial]]
* [[Development/Tutorials#Kross|Kross Tutorials]]


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 76: Line 81:
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/csvimport.py?view=markup csvimport.py] Import from a Comma-Separated-Value File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/csvimport.py?view=markup csvimport.py] Import from a Comma-Separated-Value File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/csvexport.py?view=markup csvexport.py] Export to a Comma-Separated-Value File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/csvexport.py?view=markup csvexport.py] Export to a Comma-Separated-Value File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/vcardimport.py?view=markup vcardimport.py] Import from a vCard File.
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/htmlexport.py?view=markup htmlexport.py] Export to a HTML File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/htmlexport.py?view=markup htmlexport.py] Export to a HTML File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/kexidbimport.py?view=markup kexidbimport.py] Import from a Kexi Project File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/kexidbimport.py?view=markup kexidbimport.py] Import from a Kexi Project File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/kexidbexport.py?view=markup kexidbexport.py] Export to a Kexi Project File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/kexidbexport.py?view=markup kexidbexport.py] Export to a Kexi Project File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/odfpyexport.py?view=markup odfpyexport.py] Export to an OpenDocument File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/odfpyexport.py?view=markup odfpyexport.py] Export to an OpenDocument File
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/logger.py?view=markup logger.py]
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/logger.py?view=markup logger.py] Log changes in cells to a file
 
Ideas for additional extensions users may like;
* SVN import/export or even something like a change-managment using [http://pysvn.tigris.org/ PySVN]
* PDF creator using [http://reportlab.org ReportLab]
* Connector to the [http://www.zope.org Zope] open source application server. See also the [[#Connect_KSpread_with_the_Zope_application_server_using_XML-RPC|Connect KSpread with the Zope application server using XML-RPC]] sample script.
* Connect with [http://www.openoffice.org/ OpenOffice.org] using [http://udk.openoffice.org/python/python-bridge.html PyUNO]
* Use the Python [http://www.freedesktop.org/wiki/Software/dbus DBus] module to e.g. connect with amarok, Kopete, Kontact, ...


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 94: Line 93:


KSpread has a rich set of formula functions for nearly every purpose. KSpread implements the whole set of the [http://wiki.oasis-open.org/office/About_OpenFormula OpenFormula] specification and could be easy extended with additional formula functions written in a scripting language.
KSpread has a rich set of formula functions for nearly every purpose. KSpread implements the whole set of the [http://wiki.oasis-open.org/office/About_OpenFormula OpenFormula] specification and could be easy extended with additional formula functions written in a scripting language.
===A first scripted formula function===


The [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/functions.py?view=markup functions.py]
The [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/functions.py?view=markup functions.py]
Python script demonstrates this by defining the KSpread formula function "SCRIPT_TEST1";
Python script demonstrates this by defining the KSpread formula function "SCRIPT_TEST1";
<code python>
<syntaxhighlight lang="python">
# create the new formula function "SCRIPT_TEST1"
# create the new formula function "SCRIPT_TEST1"
functest1 = self.kspread.function("SCRIPT_TEST1")
functest1 = self.kspread.function("SCRIPT_TEST1")
Line 133: Line 134:
# to use it within KSpread
# to use it within KSpread
functest1.registerFunction()
functest1.registerFunction()
</code>
</syntaxhighlight>


Now we run KSpread with the --scriptfile argument that points to the delivered [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/functions.py?view=markup functions.py]
Now we run KSpread with the --scriptfile argument that points to the delivered [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/functions.py?view=markup functions.py]
example.
example.
<code bash>
<syntaxhighlight lang="bash">
# make the script executable
# make the script executable
chmod 755 `kde4-config --install data`/kspread/scripts/functions/functions.py
chmod 755 `kde4-config --install data`/kspread/scripts/functions/functions.py
# run KSpread
# run KSpread
kspread --scriptfile `kde4-config --install data`/kspread/scripts/functions/functions.py
kspread --scriptfile `kde4-config --install data`/kspread/scripts/functions/functions.py
</code>
</syntaxhighlight>


The screenshot below shows KSpread using the new formula function "SCRIPT_TEST1" which was added and is handled in the
The screenshot below shows KSpread using the new formula function "SCRIPT_TEST1" which was added and is handled in the
Line 155: Line 156:


See also;
See also;
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yfinance.py?view=markup yfinance.py] Yahoo! Finance formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/rpyfunctions.py?view=markup rpyfunctions.py] Functions for the R programming environment for data analysis and graphics
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yweather.py?view=markup yweather.py] Yahoo! Weather formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yfinance.py?view=markup yfinance.py] ''Yahoo! Finance'' formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yweather.py?view=markup yweather.py] ''Yahoo! Weather'' formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/pytime.py?view=markup pytime.py] Time formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/pytime.py?view=markup pytime.py] Time formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/pyregexp.py?view=markup pyregexp.py] Regular Expression formula function
* [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/pyregexp.py?view=markup pyregexp.py] Regular Expression formula function


Ideas for additional formula functions users may like;
===The R Project===
* Additional formula functions KSpread does not support yet. Not only useful for rapid prototyping but also to just integrate fast and flexible functionality written in a scripting-language like Python, Ruby or KDE JavaScript.
 
* [http://wiki.koffice.org/index.php?title=Collaboration Collaboration] e.g. synchronous editing between 2 or more people. Propably using Kopete/dbus...
KSpread provides functions to access the R programming environment for data analysis and graphics. The [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/rpyfunctions.py?view=markup rpyfunctions.py] python script shipped with KSpread does implement 45 R-Project functions.
* Tunnel all formula functions through a script to be able to implement transparent logging, access-levels, merging, ...
 
The new 45 R-Project functions are the same Gnumeric supports. New functions are;
* r_dnorm, r_pnorm, r_qnorm ''(density function of the normal distribution)''
* r_dlnorm, r_plnorm, r_qlnorm ''(density function of the log-normal distribution)''
* r_dgamma, r_pgamma, r_qgamma ''(density function of the gamma distribution)''
* r_dbeta, r_pbeta, r_qbeta ''(density function of the beta distribution)''
* r_dt, r_pt, r_qt ''(density function of the Student t distribution)''
* r_df, r_pf, r_qf ''(density function of the F distribution)''
* r_dchisq, r_pchisq, r_qchisq ''(density function of the chi-square distribution)''
* r_dweibull, r_pweibull, r_qweibull ''(density function of the Weibull distribution)''
* r_dpois, r_ppois, r_qpois ''(density function of the Poisson distribution)''
* r_dexp, r_pexp, r_qexp ''(density function of the exponential distribution)''
* r_dbinom, r_pbinom, r_qbinom ''(density function of the binomial distribution)''
* r_dnbinom, r_pnbinom, r_qnbinom ''(density function of the negative binomial distribution)''
* r_dhyper, r_phyper, r_qhyper ''(density function of the hypergeometric distribution)''
* r_dgeom, r_pgeom, r_qgeom ''(density function of the geometric distribution)''
* r_dcauchy, r_pcauchy, r_qcauchy ''(density function of the Cauchy distribution)''
 
Once the [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/rpyfunctions.py?view=markup rpyfunctions.py] python script got executed those new functions are also available via the ''Insert=>Functions'' menu-item.
 
The following screenshot shows KSpread in action running the python script that provides us the R-Project functions.
 
[[Image:Kspread_rproject.jpg]]
 
See also;
* [http://www.r-project.org The R Project for Statistical Computing]
* [http://rpy.sourceforge.net RPy Python Module]
 
===Yahoo! Web-services===
 
KSpread ships the both python scripts [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yfinance.py?view=markup yfinance.py] and [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/yweather.py?view=markup yweather.py] that offer finance and weather formula functions using the Yahoo! Web-services.
 
 
See also;
* [http://developer.yahoo.com/weather/ Yahoo! Weather] and [http://developer.yahoo.com/finance/ Yahoo! Finance]
* The Python Web services developer:[http://www.ibm.com/developerworks/webservices/library/ws-pyth1.html The world of Python Web services]
* Dive Into Python: [http://www.diveintopython.org/http_web_services/  HTTP Web Services] and [http://www.diveintopython.org/soap_web_services/index.html SOAP Web Services]
 
<!-- ####################################################################### //-->
 
==Scripting Docker Widgets==
 
KOffice [[Development/Tutorials/KOffice_Overview#Dockers|Dockers]] are
plugins that provide graphical user interface elements within a QDockWidget embedded into a KOffice application like KSpread. Such docker plugins could be also be written in a scripting language like demonstrated below.
 
===Python Console===
 
The [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/consoledocker.py?view=markup consoledocker.py] Python script does provide a interactive Python Console Docker to KSpread.
 
[[Image:Kspreadpyconsole.jpg]]
 
The script does use PyQt4 to embed widgets like a QTextBrowser or a QTreeView for the GUI related stuff. For this we use PyQt's SIP to pass around void* pointers. The following python code demonstrates how KSpread's KoScriptingDocker provided on the fly via [[Development/Tutorials/Kross/Introduction|Kross]] got translated into the PyQt world.
 
<syntaxhighlight lang="python">
# Import needed modules
import KoDocker, PyQt4.Qt as Qt, sip
# Create a widget using PyQt4
widget = Qt.QWidget()
# Get the void* pointer for our docker
ptr = KoDocker.__toPointer__()
# Wrap the pointer into a PyQt4 object
docker = sip.wrapinstance(ptr, Qt.QDockWidget)
# Set the widget for the docker
docker.setWidget(widget)
</syntaxhighlight>
 
Those [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/consoledocker.py?view=markup consoledocker.py] Python script is defined within the
[http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/scripts.rc?view=markup scripts.rc] in a collection named "docker". If KSpread starts and the scripting plugin got loaded, for each script within that "docker" collection a Docker will be created. Once such a docker got shown/displayed, the defined scripting file will be executed.
 
<syntaxhighlight lang="text">
<collection name="docker" text="Docker">
    <script
        name="pyconsoledocker"
        text="Python Console"
        interpreter="python"
        file="docker/consoledocker.py" />
</collection>
</syntaxhighlight>
 
See also;
* [http://www.riverbankcomputing.com/Docs/PyQt4/pyqt4ref.html PyQt4 Reference Guide]
* [http://www.riverbankcomputing.co.uk/sip/index.php SIP Overview]
* [http://www.ics.uci.edu/~dock/manuals/sip/sipref.html SIP Reference Guide]
 
===Ruby Snippets===
 
The [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/snippetsdocker.rb?view=markup snippetsdocker.rb] Ruby script does use QtRuby to embed widgets as docker into KSpread. The script does provide different sample actions to the user.
 
[[Image:Kspreadrbsnippet.jpg]]
 
* The "Details about the Environment" action does show a dialog with some detailed informations like the Version-numbers and Environment-variables.
* The "Execute Scripting Code" action does display a non-modal window that provides an editor to the user that allows to execute Python, Ruby or JavaScript code on the fly.
 
We connect Kross with QtRuby using following ruby code;
 
<syntaxhighlight lang="ruby">
# Fetch the QDockWidget as void-pointer.
$voidptr = KoDocker.toVoidPtr()
# Translate the void-pointer into a QtRuby object.
$wdg = Qt::Internal.kross2smoke($voidptr, Qt::DockWidget)
# Create the SnippetsWidget QtRuby object...
$label = SnippetsWidget.new($wdg)
# ...and set is as the docker's widget.
$wdg.setWidget($label)
</syntaxhighlight>
 
Just like at the [[Development/Tutorials/KSpread_Scripting#Python_Console|Python Console]] script above we registered the [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/snippetsdocker.rb?view=markup snippetsdocker.rb] Ruby script in the [http://websvn.kde.org/trunk/koffice/kspread/plugins/scripting/scripts/scripts.rc?view=markup scripts.rc] file in the "docker" collection;
 
<syntaxhighlight lang="text">
<collection name="docker" text="Docker">
    <script
        name="rbsnippets"
        text="Ruby Snippets"
        interpreter="ruby"
        file="docker/snippetsdocker.rb" />
</collection>
</syntaxhighlight>
 
See also;
* [[Development/Tutorials/Qt4_Ruby_Tutorial|Qt4 Ruby Tutorial]]
* [[Development/Languages/Ruby|Ruby Development Language]]


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 189: Line 311:


The csvexport.py Python script looks like;
The csvexport.py Python script looks like;
<code python>
<syntaxhighlight lang="python">
#!/usr/bin/env kross
#!/usr/bin/env kross


Line 222: Line 344:
     if len(record) > 0:
     if len(record) > 0:
         csvwriter.writerow( record )
         csvwriter.writerow( record )
</code>
</syntaxhighlight>


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 231: Line 353:
script distributed with KSpread could also be executed from
script distributed with KSpread could also be executed from
the commandline;
the commandline;
<code bash>
<syntaxhighlight lang="bash">
# make the script executable
# make the script executable
chmod 755 `kde4-config --install data`/kspread/scripts/extensions/htmlexport.py
chmod 755 `kde4-config --install data`/kspread/scripts/extensions/htmlexport.py
# run the script
# run the script
`kde4-config --install data`/kspread/scripts/extensions/htmlexport.py
`kde4-config --install data`/kspread/scripts/extensions/htmlexport.py
</code>
</syntaxhighlight>


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 257: Line 379:


The invoice.py Python script looks like;
The invoice.py Python script looks like;
<code python>
<syntaxhighlight lang="python">
#!/usr/bin/env kross
#!/usr/bin/env kross


Line 270: Line 392:
import Kross
import Kross
kspread = Kross.module("kspread")
kspread = Kross.module("kspread")
if not kspread:
    raise "KSpread is not installed."
# Try to open the file.
# Try to open the file.
if not kspread.openUrl(templatefile):
if not kspread.openUrl(templatefile):
Line 283: Line 407:
if not kspread.saveUrl(savefile):
if not kspread.saveUrl(savefile):
     raise "Failed to save %s" % savefile
     raise "Failed to save %s" % savefile
</code>
</syntaxhighlight>


<!-- ####################################################################### //-->
<!-- ####################################################################### //-->
Line 298: Line 422:


The [http://kross.dipe.org/kspread2scripting/kspread2zope.tar.gz kspread2zope.tar.gz] tarball includes a Python script that demonstrates how to connect KSpread with the Zope application server using XML-RPC. What the script does is to download a resource from the Zope server, then parses it into KSpread sheets, changes some content and then either save it as OpenDocument Spreadsheet file or upload the content at the Zope server.
The [http://kross.dipe.org/kspread2scripting/kspread2zope.tar.gz kspread2zope.tar.gz] tarball includes a Python script that demonstrates how to connect KSpread with the Zope application server using XML-RPC. What the script does is to download a resource from the Zope server, then parses it into KSpread sheets, changes some content and then either save it as OpenDocument Spreadsheet file or upload the content at the Zope server.
==Snippets==
===Get and set values in a cell===
<syntaxhighlight lang="python">
import KSpread
sheet = KSpread.view().sheet()
# swap text of B5 and C6
t1 = sheet.text("B5")
t2 = sheet.text(6,3)
sheet.setText("B5", t2)
sheet.setText(6, 3, t1)
# swap value of D7 and E8
v1 = sheet.value("D7")
v2 = sheet.value(8,5)
sheet.setValue("D7", v2)
sheet.setValue(8, 5, v1)
</syntaxhighlight>
===Do something if a value in a cell changed===
<syntaxhighlight lang="python">
import KSpread
# the sheet we like to listen to
sheet = KSpread.view().sheet()
# the cells within that sheet we like to listen to
cellrange = "A1:F50"
# create the listener and connect an own handler for it
listener = KSpread.createListener(sheet.sheetName(), cellrange)
def cellChanged(column, row):
    t = sheet.text(column, row)
    if not t.endswith("test"):
        sheet.setText(column, row, "%s test" % t)
listener.connect("cellChanged(int,int)", cellChanged)
</syntaxhighlight>
===Print all values in all sheets===
<syntaxhighlight lang="python">
import KSpread
for n in KSpread.sheetNames():
    sheet = KSpread.sheetByName(n)
    for row in range(1, sheet.lastRow() + 1):
        record = []
        for col in range(1, sheet.lastColumn() + 1):
            record.append( sheet.text(col, row) )
        print ",".join(record)
</syntaxhighlight>
===Translate between name and coordinates of a cells===
<syntaxhighlight lang="python">
import KSpread
sheet = KSpread.view().sheet()
# should print B5
print sheet.cellName(5,2)
# should print [5,2]
print sheet.cellLocation("B5")
# should print 5
print sheet.cellRow("B5")
# should print 2
print sheet.cellColumn("B5")
</syntaxhighlight>
===Get and set active sheet===
<syntaxhighlight lang="python">
import KSpread
view = KSpread.view()
# following both calls are identical but the
# first one always needs a valid view while
# the second can be even used without view.
print view.sheet()
print KSpread.currentSheet()
# Activate a sheet named "Sheet 2"
view.showSheet("Sheet 2")
# Activate the previous sheet
view.previousSheet()
# Activate the next sheet
view.nextSheet()
</syntaxhighlight>
===Get and set selected cells===
<syntaxhighlight lang="python">
import KSpread
view = KSpread.view()
(x1, y1, x2, y2) = view.selection()
view.setSelection( [x1-1, y1-1, x2+1, y2+1] )
</syntaxhighlight>
===Do something if the selection changed===
<syntaxhighlight lang="python">
import KSpread
view = KSpread.view()
def selectionChanged():
    # this will be called either if the sheet or
    # the selection within a sheet changed.
    print "Selection changed"
view.connect("selectionChanged()", selectionChanged)
</syntaxhighlight>
===Change the name of a sheet===
<syntaxhighlight lang="python">
import KSpread
sheet = KSpread.currentSheet()
n = sheet.sheetName()
sheet.setSheetName("%s test" % n)
</syntaxhighlight>
===Do something if the name of a sheet changed===
<syntaxhighlight lang="python">
import KSpread
sheet = KSpread.currentSheet()
name = sheet.sheetName()
def nameChanged():
    print "name before: %s" % name
    name = sheet.sheetName()
    print "name after: %s" % name
view.connect("nameChanged()", nameChanged)
</syntaxhighlight>
===Hide or show sheets===
<syntaxhighlight lang="python">
import KSpread
for n in KSpread.sheetNames():
    sheet = KSpread.sheetByName(n)
    if sheet.isHidden():
        sheet.setHidden(False)
    else:
        sheet.setHidden(True)
</syntaxhighlight>
===Do something if a sheet got hidden/shown===
<syntaxhighlight lang="python">
import KSpread
sheet = KSpread.currentSheet()
def visibleStateChanged():
    print "Visibility of sheet changed"
sheet.connect("showChanged()", visibleStateChanged)
sheet.connect("hideChanged()", visibleStateChanged)
</syntaxhighlight>

Latest revision as of 08:41, 31 May 2019

Warning
This page needs a review and probably holds information that needs to be fixed.

Parts to be reviewed:

  • Port to KF5
  • Rename to Calligra Sheets

Intro

KSpread

KOffice is an interated Office Suite consisting of several applications where KSpread is the scriptable spreadsheet program which provides both table-oriented sheets and support for complex mathematical formulas and statistics.

The KSpread Scripting Plugin implements a plugin to dynamic access the scripting functionality from within KSpread. The plugin realizes usage-scenarios like;

  • Extend KSpread with new functionality
  • Dynamic add new formula functions
  • Use the KSpread library from a script to automate things

The whole KSpread Scripting Plugin, that does handle all things related to scripting for KSpread, consist of only a small hand full of files;

  • The ScriptingPart class (dox svn) implements a KParts::Plugin component to integrate scripting into KSpread. That plugin got loaded dynamic on startup and does all the things related to scripting.
  • The ScriptingModule class (dox svn) enables access to the KSpread functionality from within the scripting backends. To access functionality KSpread offers classes like KoApplicationAdaptor, KoDocumentAdaptor, SheetAdaptor and ViewAdaptor are distributed to the scripting backends.
  • The ScriptingFunction class (dox svn) provides access to the KSpread::Function functionality to deal with formula functions that are written in a scripting language like Python or Ruby.
  • The ScriptingCellListener class (dox svn) implements a listener to changes within cells of a sheet.
  • The ScriptingReader class (dox svn) provides abstract high-level functionality to read content from KSpread sheets.
  • The ScriptingWriter (dox svn) class provides abstract high-level functionality to write content to KSpread sheets and to manipulate the content of cells.
  • The ScriptingSheetsListView (dox svn) provides provides a listview-widget that displays all sheets and lets the user choose 0..n of them plus specify cell-ranges for all of them.


Kross

The Kross scripting framework provides full Python, Ruby and KDE JavaScript scripting support. The KSpread scripting plugin uses this framework to deal with scripting on an abstract level. Kross does handle interpreter-backend details while KSpread does not need to know anything about Kross, Python, Ruby or KDE JavaScript.

The goal was to limit the work needed on an application to have it full scriptable. To achieve this internaly Qt's introspection-functionality like signals, slots, properties, enums, QObject's and QMetaObject/QMetaType/etc. is used to deal with functionality at runtime. To get a more detailed overview you may like to take a look at my talk about scripting with Kross.

Kross is included in the kdelibs4 (KDE4 libraries). The Kross scripting framework consists of the Kross library which handles the Kross interpreter-backend details and the "kross" commandline-application which could be used to execute Python, Ruby or KDE JavaScript scripts direct from the commandline rather then embedded in an application like KSpread.


Scripting Handbook

The KSpread Scripting Handbook (PDF) contains a full reference of the functionality accessible from within the scripting backends.

The Handbook is generated from the sourcecode using doxygen and KWord's Import Doxygen XML File python script.


Links


Scripting Extensions

Extensions are used to extend KSpread with additional functionality written with Python, Ruby or KDE JavaScript scripts.

The htmlexport.py Python script demonstrates how extensions could be used. The script exports the content of the current sheet to a HTML file.

Extensions are located in the "Tools->Scripts" menu and are either distributed with KSpread (so some default extensions like the htmlexport.py script are installed together with KSpread as part of it) or could be later added and configured on demand using the "Script Manager".

Once the htmlexport.py Python script got executed, it displays the "HTML Export" dialog to let the user define the HTML file that should be written and some additional details like Document Informations or Styles. For GUI-related things you are also able to use PyQt4 or Tkinter in your python scripts, Korundum/QtRuby in your Ruby scripts, KjsEmbed4 (KjsEmbded4 is included in kdelibs4 now) in your JavaScript scripts or the more high-level Kross forms (used in the htmlexport.py as shown in the screenshot below) in all interpreter-backends.

If the user clicks on the "Ok" button the export-process starts and a progress-dialog is displayed to offer visual feedback while the Python script reads the content from the current sheet and writes it to a HTML file.

See also;


Scripting Formula Functions

KSpread has a rich set of formula functions for nearly every purpose. KSpread implements the whole set of the OpenFormula specification and could be easy extended with additional formula functions written in a scripting language.

A first scripted formula function

The functions.py Python script demonstrates this by defining the KSpread formula function "SCRIPT_TEST1";

# create the new formula function "SCRIPT_TEST1"
functest1 = self.kspread.function("SCRIPT_TEST1")
# set the minimal number of parameters
functest1.minparam = 1
# set the maximal number of parameters,
# -1 means unlimited.
functest1.maxparam = 1
# set the comment displayed at "Help"
functest1.comment = (
  "The SCRIPT_TEST1() function demonstrates how to "
  "use scripting functions. All it does is to take "
  "a string as argument and return the same string."
)
# set the syntax which is displayed at "Help".
functest1.syntax = "SCRIPT_TEST1(string)"
# set details about the parameter the formula
# functions expects.
functest1.addParameter("String",
  "The string that should be returned")
# add an example displayed at "Help".
functest1.addExample("SCRIPT_TEST1(\"Some string\")")

# this python function will be called by the
# KSpread formula function
def functest1callback(argument):
    # just return the first argument
    functest1.result = "%s" % argument[0]
# connect the python function with the KSpread
# formula function
functest1.connect(
  "called(QVariantList)", functest1callback)

# and finally register the function to be able
# to use it within KSpread
functest1.registerFunction()

Now we run KSpread with the --scriptfile argument that points to the delivered functions.py example.

# make the script executable
chmod 755 `kde4-config --install data`/kspread/scripts/functions/functions.py
# run KSpread
kspread --scriptfile `kde4-config --install data`/kspread/scripts/functions/functions.py

The screenshot below shows KSpread using the new formula function "SCRIPT_TEST1" which was added and is handled in the functions.py script.

All at the functions.py script added formula functions are accessible via "Insert=>Function..." in the "Scripts" category.

See also;

The R Project

KSpread provides functions to access the R programming environment for data analysis and graphics. The rpyfunctions.py python script shipped with KSpread does implement 45 R-Project functions.

The new 45 R-Project functions are the same Gnumeric supports. New functions are;

  • r_dnorm, r_pnorm, r_qnorm (density function of the normal distribution)
  • r_dlnorm, r_plnorm, r_qlnorm (density function of the log-normal distribution)
  • r_dgamma, r_pgamma, r_qgamma (density function of the gamma distribution)
  • r_dbeta, r_pbeta, r_qbeta (density function of the beta distribution)
  • r_dt, r_pt, r_qt (density function of the Student t distribution)
  • r_df, r_pf, r_qf (density function of the F distribution)
  • r_dchisq, r_pchisq, r_qchisq (density function of the chi-square distribution)
  • r_dweibull, r_pweibull, r_qweibull (density function of the Weibull distribution)
  • r_dpois, r_ppois, r_qpois (density function of the Poisson distribution)
  • r_dexp, r_pexp, r_qexp (density function of the exponential distribution)
  • r_dbinom, r_pbinom, r_qbinom (density function of the binomial distribution)
  • r_dnbinom, r_pnbinom, r_qnbinom (density function of the negative binomial distribution)
  • r_dhyper, r_phyper, r_qhyper (density function of the hypergeometric distribution)
  • r_dgeom, r_pgeom, r_qgeom (density function of the geometric distribution)
  • r_dcauchy, r_pcauchy, r_qcauchy (density function of the Cauchy distribution)

Once the rpyfunctions.py python script got executed those new functions are also available via the Insert=>Functions menu-item.

The following screenshot shows KSpread in action running the python script that provides us the R-Project functions.

See also;

Yahoo! Web-services

KSpread ships the both python scripts yfinance.py and yweather.py that offer finance and weather formula functions using the Yahoo! Web-services.


See also;


Scripting Docker Widgets

KOffice Dockers are plugins that provide graphical user interface elements within a QDockWidget embedded into a KOffice application like KSpread. Such docker plugins could be also be written in a scripting language like demonstrated below.

Python Console

The consoledocker.py Python script does provide a interactive Python Console Docker to KSpread.

The script does use PyQt4 to embed widgets like a QTextBrowser or a QTreeView for the GUI related stuff. For this we use PyQt's SIP to pass around void* pointers. The following python code demonstrates how KSpread's KoScriptingDocker provided on the fly via Kross got translated into the PyQt world.

# Import needed modules
import KoDocker, PyQt4.Qt as Qt, sip
# Create a widget using PyQt4
widget = Qt.QWidget()
# Get the void* pointer for our docker
ptr = KoDocker.__toPointer__()
# Wrap the pointer into a PyQt4 object
docker = sip.wrapinstance(ptr, Qt.QDockWidget)
# Set the widget for the docker
docker.setWidget(widget)

Those consoledocker.py Python script is defined within the scripts.rc in a collection named "docker". If KSpread starts and the scripting plugin got loaded, for each script within that "docker" collection a Docker will be created. Once such a docker got shown/displayed, the defined scripting file will be executed.

<collection name="docker" text="Docker">
    <script
        name="pyconsoledocker"
        text="Python Console"
        interpreter="python"
        file="docker/consoledocker.py" />
</collection>

See also;

Ruby Snippets

The snippetsdocker.rb Ruby script does use QtRuby to embed widgets as docker into KSpread. The script does provide different sample actions to the user.

  • The "Details about the Environment" action does show a dialog with some detailed informations like the Version-numbers and Environment-variables.
  • The "Execute Scripting Code" action does display a non-modal window that provides an editor to the user that allows to execute Python, Ruby or JavaScript code on the fly.

We connect Kross with QtRuby using following ruby code;

# Fetch the QDockWidget as void-pointer.
$voidptr = KoDocker.toVoidPtr()
# Translate the void-pointer into a QtRuby object.
$wdg = Qt::Internal.kross2smoke($voidptr, Qt::DockWidget)
# Create the SnippetsWidget QtRuby object...
$label = SnippetsWidget.new($wdg)
# ...and set is as the docker's widget.
$wdg.setWidget($label)

Just like at the Python Console script above we registered the snippetsdocker.rb Ruby script in the scripts.rc file in the "docker" collection;

<collection name="docker" text="Docker">
    <script
        name="rbsnippets"
        text="Ruby Snippets"
        interpreter="ruby"
        file="docker/snippetsdocker.rb" />
</collection>

See also;


Scripting with KSpread Library Samples

The functionality KSpread offers could also be used direct from the commandline without a running KSpread instance. In that case a script written in Python, Ruby or KDE JavaScript controls what should be done and the KSpread library is loaded and used in the background to deal with the whole KSpread-stack on a high-level. This enables to batch-process or automated creation and/or manipulation of OpenDocument Spreadsheet files. Complex calculations, document-manipulations and workflows without any user-interaction could be scripted and executed from the commandline that way.

Following samples are implementations of use-case scenarios KSpread could be used for. The scripts are simple executable files that run from within the commandline by using the "kross" application which is installed together with the Kross scripting framework.


Export OpenDocument Spreadsheet file to a CSV file

The following Python script reads the OpenDocument Spreadsheet file "/home/kde4/invoicetemplate.ods" and writes the first sheet to a CSV (Comma Separated Value) file.

The complete csvexport.py script including the invoicetemplate.ods OpenDocument Spreadsheet file used as template is available as csvexport.tar.gz. Download, extract, edit the csvexport.py to change the "filename" and "csvfile" variables to point to the correct locations and execute the csvexport.py Python script ("chmod 755 csvexport.py" to make the script executable and "./csvexport.py" to execute the script).

The csvexport.py Python script looks like;

#!/usr/bin/env kross

# The OpenDocument Spreadsheet file that we
# like to read.
filename = "/home/me/invoicetemplate.ods"
# The CSV file we like to write to.
csvfile = "/home/me/invoicetemplate.csv"

# Import Kross and fetch the KSpread module.
import Kross
kspread = Kross.module("kspread")
# Try to open the file.
if not kspread.openUrl(filename):
    raise "Failed to open %s" % filename

# Import the Python CSV module and create a writer.
import csv
csvwriter = csv.writer( open(csvfile,'w') )

# Get the sheet we like to export to the CSV file.
sheet = kspread.sheetByName(kspread.sheetNames()[0])
# Iterate now through all cells on the sheet.
for row in range(1, sheet.lastRow() + 1):
    # Put the content of the row into the record-list.
    record = []
    for col in range(sheet.lastColumn() + 1, 1, -1):
        value = sheet.text(col, row)
        if value or len(record) > 0:
            record.insert(0,value)
    # If the record has at least one cell print it.
    if len(record) > 0:
        csvwriter.writerow( record )


Export OpenDocument Spreadsheet file to a HTML file

The htmlexport.py Python script distributed with KSpread could also be executed from the commandline;

# make the script executable
chmod 755 `kde4-config --install data`/kspread/scripts/extensions/htmlexport.py
# run the script
`kde4-config --install data`/kspread/scripts/extensions/htmlexport.py


Export OpenDocument Spreadsheet file to a custom OpenDocument file

The odfpyexport.py Python script distributed with KSpread uses the OdfPy Python module to write an OpenDocument text file (ODT) from KSpread. Take the script as example how easy scripting with KSpread is and modify it to your needs to produce custom OpenDocument files.


Write OpenDocument Spreadsheet file from invoice template

The following Python script reads the OpenDocument Spreadsheet template-file "/home/kde4/invoicetemplate.ods", fills some cells within a sheet and writes a new OpenDocument Spreadsheet file.

The complete invoice-example including the invoice.py Python script and the invoicetemplate.ods OpenDocument Spreadsheet file used as template is available as invoice.tar.gz. Download, extract, edit the invoice.py to change the "templatefile" and "savefile" variables to point to the correct locations and execute the invoice.py Python script ("chmod 755 invoice.py" to make the script executable and "./invoice.py" to execute the script).

The invoice.py Python script looks like;

#!/usr/bin/env kross

# The OpenDocument Spreadsheet file that we
# like to read from.
templatefile = "/home/me/invoicetemplate.ods"
# The OpenDocument Spreadsheet file that we
# like to write to.
savefile = "/home/me/invoice.ods"

# Import Kross and fetch the KSpread module.
import Kross
kspread = Kross.module("kspread")
if not kspread:
    raise "KSpread is not installed."
# Try to open the file.
if not kspread.openUrl(templatefile):
    raise "Failed to open %s" % templatefile
# Get the sheet we like to manipulate.
sheet = kspread.sheetByName(kspread.sheetNames()[0])
# Set the content of some cells.
sheet.setText(1,7,"Joe User")
sheet.setText(1,8,"Userstreet. 1")
sheet.setText(1,9,"Testcasecity")
# Finally write the new OpenDocument
# Spreadsheet file.
if not kspread.saveUrl(savefile):
    raise "Failed to save %s" % savefile


Read and write from/to a Kexi Database Project

The both python scripts kexidbimport.py and kexidbexport.py are providing functionality to import content from or export content to a Kexi project file.


Connect KSpread with the Zope application server using XML-RPC

Zope is an open source application server for building content management systems, intranets, portals, and custom applications with a large community of hundreds of companies and thousands of developers all over the world. Zope is written in Python, a highly-productive, object-oriented scripting language and supports the XML-RPC and SOAP protocols.

The kspread2zope.tar.gz tarball includes a Python script that demonstrates how to connect KSpread with the Zope application server using XML-RPC. What the script does is to download a resource from the Zope server, then parses it into KSpread sheets, changes some content and then either save it as OpenDocument Spreadsheet file or upload the content at the Zope server.


Snippets

Get and set values in a cell

import KSpread
sheet = KSpread.view().sheet()
# swap text of B5 and C6
t1 = sheet.text("B5")
t2 = sheet.text(6,3)
sheet.setText("B5", t2)
sheet.setText(6, 3, t1)
# swap value of D7 and E8
v1 = sheet.value("D7")
v2 = sheet.value(8,5)
sheet.setValue("D7", v2)
sheet.setValue(8, 5, v1)

Do something if a value in a cell changed

import KSpread
# the sheet we like to listen to
sheet = KSpread.view().sheet()
# the cells within that sheet we like to listen to
cellrange = "A1:F50"
# create the listener and connect an own handler for it
listener = KSpread.createListener(sheet.sheetName(), cellrange)
def cellChanged(column, row):
    t = sheet.text(column, row)
    if not t.endswith("test"):
        sheet.setText(column, row, "%s test" % t)
listener.connect("cellChanged(int,int)", cellChanged)

Print all values in all sheets

import KSpread
for n in KSpread.sheetNames():
    sheet = KSpread.sheetByName(n)
    for row in range(1, sheet.lastRow() + 1):
        record = []
        for col in range(1, sheet.lastColumn() + 1):
            record.append( sheet.text(col, row) )
        print ",".join(record)

Translate between name and coordinates of a cells

import KSpread
sheet = KSpread.view().sheet()
# should print B5
print sheet.cellName(5,2)
# should print [5,2]
print sheet.cellLocation("B5")
# should print 5
print sheet.cellRow("B5")
# should print 2
print sheet.cellColumn("B5")

Get and set active sheet

import KSpread
view = KSpread.view()
# following both calls are identical but the
# first one always needs a valid view while
# the second can be even used without view.
print view.sheet()
print KSpread.currentSheet()
# Activate a sheet named "Sheet 2"
view.showSheet("Sheet 2")
# Activate the previous sheet
view.previousSheet()
# Activate the next sheet
view.nextSheet()

Get and set selected cells

import KSpread
view = KSpread.view()
(x1, y1, x2, y2) = view.selection()
view.setSelection( [x1-1, y1-1, x2+1, y2+1] )

Do something if the selection changed

import KSpread
view = KSpread.view()
def selectionChanged():
    # this will be called either if the sheet or
    # the selection within a sheet changed.
    print "Selection changed"
view.connect("selectionChanged()", selectionChanged)

Change the name of a sheet

import KSpread
sheet = KSpread.currentSheet()
n = sheet.sheetName()
sheet.setSheetName("%s test" % n)

Do something if the name of a sheet changed

import KSpread
sheet = KSpread.currentSheet()
name = sheet.sheetName()
def nameChanged():
    print "name before: %s" % name
    name = sheet.sheetName()
    print "name after: %s" % name
view.connect("nameChanged()", nameChanged)

Hide or show sheets

import KSpread
for n in KSpread.sheetNames():
    sheet = KSpread.sheetByName(n)
    if sheet.isHidden():
        sheet.setHidden(False)
    else:
        sheet.setHidden(True)

Do something if a sheet got hidden/shown

import KSpread
sheet = KSpread.currentSheet()
def visibleStateChanged():
    print "Visibility of sheet changed"
sheet.connect("showChanged()", visibleStateChanged)
sheet.connect("hideChanged()", visibleStateChanged)