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;
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 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.
The Handbook is generated from the sourcecode using doxygen and KWord's Import Doxygen XML File python script.
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".
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.
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.
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 # 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.
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;
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.
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.
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>
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.
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)
<collection name="docker" text="Docker"> <script name="rbsnippets" text="Ruby Snippets" interpreter="ruby" file="docker/snippetsdocker.rb" /> </collection>
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.
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()) # 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 )
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
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.
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()) # 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
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.
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)
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)
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)
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")
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()
import KSpread view = KSpread.view() (x1, y1, x2, y2) = view.selection() view.setSelection( [x1-1, y1-1, x2+1, y2+1] )
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)
import KSpread sheet = KSpread.currentSheet() n = sheet.sheetName() sheet.setSheetName("%s test" % n)
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)
import KSpread for n in KSpread.sheetNames(): sheet = KSpread.sheetByName(n) if sheet.isHidden(): sheet.setHidden(False) else: sheet.setHidden(True)
import KSpread sheet = KSpread.currentSheet() def visibleStateChanged(): print "Visibility of sheet changed" sheet.connect("showChanged()", visibleStateChanged) sheet.connect("hideChanged()", visibleStateChanged)