The KSpread scripting plugin offers flexible access to the Kross scripting framework. Languages like Python or Ruby could be used to script KSpread, extend the functionality with script-packages, automate tasks or control the runtime-environment.
http://www.koffice.org/kspread
http://api.kde.org/4.0-api/kdelibs-apidocs/kross/html/index.html
Python example to write content of a sheet to stdout;
#!/usr/bin/env kross
# The OpenDocument Spreadsheet file that we like to read.
filename = "/home/kde4/invoicetemplate.ods"
# 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 the file \"%s\"." % filename
# Get the sheet we like to print to stdout.
sheet = kspread.sheetByName( kspread.sheetNames()[0] )
# Iterate now through all cells on the sheet.
for row in range(sheet.maxRow()):
# Put the content of the row into the record-list.
record = []
for col in range(sheet.maxColumn(), 0, -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:
print record
Python example to read template, set content and write new file;
#!/usr/bin/env kross
# The OpenDocument Spreadsheet file that we like to read from.
templatefile = "/home/kde4/invoicetemplate.ods"
# The OpenDocument Spreadsheet file that we like to write to.
savefile = "/home/kde4/invoice.ods"
# Import Kross and fetch the KSpread module.
import Kross
kspread = Kross.module("kspread")
# Try to open the file.
if not kspread.openUrl(templatefile):
raise "Failed to open the file \"%s\"." % templatefile
# Get the sheet we like to manipulate.
sheet = kspread.sheetByName( kspread.sheetNames()[0] )
# Set the content of some cells.
sheet.setText(0,7,"Joe User")
sheet.setText(0,8,"Userstreet. 1")
sheet.setText(0,9,"Testcasecity")
# Finally write the new OpenDocument Spreadsheet file.
if not kspread.saveUrl(savefile):
raise "Failed to save the file \"%s\"." % savefile
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU Library General Public License for more details.
You should have received a copy of the GNU Library General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
The MapAdaptor class provides additional functionality to deal with sheets.
Return the objectName of the sheet with the defined sheet name.
Return the objectName of the sheet with the defined index.
Return the number of available sheets.
Return a list of available sheet names.
Return a list of available sheet objectName's.
Insert a new sheet with the defined sheet name.
The ScriptingCellListener class implements a listener to changes within cells of a sheet.
Python sample script that creates a listener for the range of cells A1:F50 within the sheet Sheet1.
import Kross, KSpread
listener = KSpread.createListener("Sheet1", "A1:F50")
if not listener:
raise "Failed to create listener."
def regionChanged(regions):
print "regions=%s" % regions
def cellChanged(column, row):
print "column=%i row=%i" % (column,row)
listener.connect("regionChanged(QVariantList)", regionChanged)
listener.connect("cellChanged(int,int)", cellChanged)
This signal got emitted if the content of a region of cells changed.
This signal got emitted if the content of a cell changed.
The ScriptingFunction class provides access to the KSpread::Function functionality to deal with formula functions that are written in a scripting language like Python or Ruby.
The following sample scripting formula function written in python displays the current datetime.
import time, Kross, KSpread
func = KSpread.function("PYTIME")
func.minparam = 0
func.maxparam = 1
func.comment = "The PYTIME() function displays the current datetime."
func.syntax = "PYTIME(string)"
func.addParameter("String", "The datetime format string.")
func.addExample("PYTIME()")
func.addExample("PYTIME(\"%Y-%M-%d %H:%M.%S\")")
def update(args):
try:
func.result = time.strftime(args[0] or "%H:%M.%S")
except:
func.error = "Invalid format"
func.connect("called(QVariantList)", update)
func.registerFunction()
The name the function has.
Minimum number of parameters the function expects.
Maximum number of parameters the function expects.
The comment that describes what the function does.
The syntax string the function looks like.
The error-message if there was an error.
The result of the function call.
Add an example to demonstrate the usage of the function.
Add details about a parameter the function may expect.
Valid values for typeName are String, Int, Float, Double, Boolean, Date, Range or Any while the comment is a describing string.
Register this function.
This signal is emitted if the function got called.
The ScriptingModule class enables access to the KSpread functionality from within the scripting backends.
Returns the KSpread::MapAdaptor object.
Returns the KSpread::ViewAdaptor object in which the document is displayed. Such a ViewAdaptor is only available if the script runs embedded in a running KSpread instance. If the script runs for example from within the commandline by using the kross-application there is no View and therefore no ViewAdaptor and this method returns NULL.
Returns the KSpread::SheetAdaptor object currently active in the document.
Returns a KSpread::SheetAdaptor object by the name name . The name should be listened in the list returned by the sheetNames() method. If there exists no sheet with such a name NULL is returned.
Returns a list of the sheet names. The sheetByName method could then be used to access the sheet object who's name is in the list.
Returns true if there is a ScriptingFunction object known under the identifier name .
Returns the ScriptingFunction object with the identifier name . The ScriptingFunction provides access to the KSpread formula function functionality. If there is no ScriptingFunction known yet with the identifier name then a new one is created, remembered and returned.
Returns a ScriptingCellListener object which provides us some kind of listener to changes in cells.
Set the document-content to the as argument passed XML string.
Return the document-content as XML string.
Open a document from the defined url .
Save the current document to the defined url .
Import a document from the defined url .
Export the current document to the defined url .
Create and return a new ScriptingReader object that provides us an abstract high-level API to read content from KSpread sheets.
Create and return a new ScriptingWriter object that provides us an abstract high-level API to write content to KSpread sheets.
Create and return a new ScriptingSheetsListView widget instance which could be used to select 0..n sheets from a list of all available sheets.
The ScriptingPart class implements a KPart component to integrate scripting into KSpread.
The ScriptingReader class provides abstract high-level functionality to read content from KSpread sheets.
The following python sample demonstrates how to use the ScriptingReader to read content from KSpread.
# Import the KSpread module
import KSpread
# Create a ScriptingReader object
reader = KSpread.reader()
# We like to read all of Sheet1
reader.setSheet("Sheet1")
# and from Sheet2 only the range A5:F12
reader.setSheet("Sheet2","A5:F12")
# This function got called in our case two times.
# One time if we switch to Sheet1 and the other
# time if switched to Sheet2.
def changedSheet(sheetname):
print "sheetname=%s" % sheetname
# This function got called for each row that got
# readed.
def changedRow(row):
values = reader.currentValues()
print "row=%i values=%s" % (row,values)
# Now let's connect our both functions to matching
# signals the reader provides.
reader.connect("changedSheet(QString)",changedSheet)
reader.connect("changedRow(int)",changedRow)
# and finally start the reader.
reader.start()
Start the reading. This let's the reader run over the defined sheets and there ranges and emits the signals this reader provides while walking over the data.
Stop the reading. This method does request to shutdown a reading process what means, that reading will stop as soon as possible. This does make it save to call this method also from within scripting functions that connected with the signals this reader provides.
true if the reader is still running what includes also shutdown. If the reader is stopped and can be started again by using the start() method false got returned.
the names of the sheets that should be readed. If the list is empty and the reader got started, then the reader will walk through all sheets the document has.
the defined ranges for the sheetname . If there was no range defined, then we walk over all the data the sheet has.
Set the defined ranges for the sheetname to range .
Set the names of the sheets that should be readed to sheetnames .
Set the sheets and there ranges.
For example in python following structure is a valid definition for 3 sheets where Sheet1 and Sheet2 are selected. Sheet1 also does define the range A1:B2. [['Sheet1', 1, [1, 1, 2, 2]], ['Sheet2', 1], ['Sheet3', 0]]
Each sheet contains a tuple of
- sheetname
- 1=enabled or 0=disabled
- optional range tuple [from column, from row, to column, to row]
Set a sheet that should be readed. The sheet with name sheetname and the optional defined range range will be appended to all already defined sheets if no such sheet with sheetname was defined before else we just replace the previous definition including the range. The range should looks like e.g. "A1:C3" while the defined sheetname should be an existing one if a range got defined.
the sheetname the reader currently is on. An empty/null string will be returned if there is no current sheet (e.g. if the reader just doesn't run currently).
the current row number the reader is on. This will be -1 if the reader isn't running.
the most left column the current row has or -1 if there is no current row.
the most right column the current row has or -1 if there is no current row.
a list of values for the current row. This will be an empty list of the reader isn't running.
This signal is emitted once the reading started with the start() method changed to the sheet with name sheetname cause e.g. reading the previous sheet was done.
This signal is emitted once the reading started with the start() method changed to the row rownumber where rownumber is >=0.
The ScriptingSheetsListView 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.
Set the selection type to selectiontype. This could be either "SingleSelect" or "MultiSelect".
Set the editor type to editortype. This could be either "Disabled", "Cell" or "Range".
Return the name of the sheet. This makes only sense of "SingleSelect" was defined as selection type else, if "MultiSelect" was defined, use the sheets() function.
Return the content of the editor. This makes only sense of "SingleSelect" was defined as selection type else, if "MultiSelect" was defined, use the sheets() function.
Return the list of all selected sheets. Selected are those sheets the user enabled the checkbutton for and the resulting list contains for each such sheetitem a list of the name and the range rectangle.
For example in python following structure got returned if there exist 3 sheets where Sheet1 and Sheet2 got selected. Sheet1 also does define the range A1:B2. [['Sheet1', 1, [1, 1, 2, 2]], ['Sheet2', 1], ['Sheet3', 0]]
Each sheet contains a tuple of
- sheetname
- 1=enabled or 0=disabled
- optional range tuple [from column, from row, to column, to row]
The ScriptingWriter class provides abstract high-level functionality to write content to KSpread sheets and to manipulate the content of cells.
The following python sample demonstrates how to use the ScriptingWriter to write content to KSpread.
# Import the KSpread module import KSpread # Create a writer instance. writer = KSpread.writer() # Set the sheet we like to write to. sheetname = "Sheet2" if not writer.setSheet(sheetname): raise "Invalid sheet \"%s\" defined." % sheetname # Set the cell(s) we like to start to write to. If this # is not defined, writing starts from A1. cellname = "B12" if not writer.setCell(cellname): raise "Invalid cell \"%s\" defined." % cellname # Now fill the cells with some content. for record in [ ["One,"Two"] , ["Three,"Four"] ]: # Write the tuple to the current row. if not writer.setValues(record): raise "Failed to set record %s" % record # Go to the next row. writer.next()
the current sheetname the writer is on. All operations done with the writer are done on this sheet.
Set the current sheetname the writer is on to sheetname . If there exist no sheet with such a sheetname false is returned else, so on success, true is returned.
the current cellname the writer is on. Operations like for example the value() and setValue() methods are done on the defined sheet in the defined cell. You may like to use it to manipulate the content of an explicit cell.
Set the current cellname the writer is on to cellname . If such a cell exist true is returned else, e.g. if the cellname was just wrong, false got returned.
Return the current row number.
Set the current row number to rownumber .
Return the current column number.
Set the current column number to columnnumber .
Go to the next row.
Set the value of the current cell.
value
The value that should be set.
parseIf this is true, the default, then the value got parsed to look for the type else we assume the value has the correct type.
true if the value was set successful else false is returned.
Set the values of the cells in the current row.
values
The list of values that should be set.
parseIf this is true, the default, then the value got parsed to look for the type else we assume the value has the correct type.
true if the values got set successful else false is returned.
Return the cellname for x,y (where x is the row and y is the column). For example for x=5 and y=2 the string "B5" got returned.
Return the x,y-point for the cellname.
Return the row-number for the cellname. For example for the cellname "B5" the integer 5 got returned.
Return the column-number for the cellname. For example for the cellname "B5" the integer 2 got returned.
Return the text for the row x and for the column y.
Return the text for a cellname.
Set the text of the cell with row x and column y. If the parse argument is true, the passed text got parsed (e.g. a text like "123" will be recognised as numeric value.
Set the text of the cell defined with cellname.
Return the value the cell at row x and column y has. The returned value is a variant and could be e.g. a number, a bool or a text depending on the format and the content the cell has.
Return the value for the cell defined with cellname.
Set the value in the cell at row x and column y.
Set the value in the cell defined with cellname.
Return the name of the sheet.
Set the name of the sheet.
Return the position the last column on this sheet has.
Return the position the last row on this sheet has.
Inserts nbCol number of new columns at the position col. All columns which are >= col are moved to the right.
Inserts nbRow number of new rows at the position row. All rows which are >= row are moved down.
Remove nbCol number of columns from the position col.
Remove nbRow number of columns from the position row.
Returns true if the sheet is hidden else false is returned.
Hide the sheet if the argument hidden is true or show it if hidden is false.
Return the height the paper of the printer has.
Set the height the paper of the printer has.
Return the width the paper of the printer has.
Set the width the paper of the printer has.
Return the left border the paper of the printer has.
Return the right border the paper of the printer has.
Return the top border the paper of the printer has.
Return the bottom border the paper of the printer has.
Return the name of the paper format (like "A4" or "Letter").
Return the name of the paper orientation (like "Portrait" or "Landscape").
Set the left, top, right and bottom border as well as the page format and orientation the paper of the printer has.
Return true if passwd is the correct password.
Return true if the sheet/document is protected.
Protect the document with the password passwd.
The ViewAdaptor class provides access to a view on a KSpread document.
Return the name of the document the view looks on.
Return the name of the map for the document. An map offers additional functionality to deal with the content of a document.
Return the name of the active sheet.
Hide the view.
Show the view.
Set the range of cells that should be selected.
Return the range of cells that is selected.
Display the find dialog to find something in the document.
Display the replace dialog to replace something in the document.
Display the Conditional dialog that allows to set cell style based on certain conditions.
Display the Validity dialog that enables to set tests to confirm cell data is valid.
Display the "Series" dialog that allows to insert series into cells.
Display the "Hyperlink" dialog that provides functionality to insert hyperlinks like URLs or E-Mails into a cell.
Display the "Go to..." dialog that enables to go to a defined cell.
Display the "Change Angle" dialog to offer to change the angle of a cell.
Select the next sheet as active sheet.
Select the previous sheet as active sheet.
Select the sheet with name sheetName as active sheet.
Display the dialog to create a custom lists for sorting or autofill.
Set a name for a region of the spreadsheet.
Show the dialog that allows to edit or select named areas.
Merge the selected region.
Unmerge the selected region.
Display the "Consolidate..." dialog.
Delete a column from the sheet.
Insert a new column into the sheet.
Delete a row from the sheet.
Insert a new row into the sheet.
Hide a row in the sheet.
Show a row in the sheet.
Hide a column in the sheet.
Show a column in the sheet.
Convert all letters to upper case.
Convert all letters to lower case.
Equalize Column.
Equalize Row.
Remove the contents of the current cell.
Remove comment from the selected cells.
Remove the validity tests on this cell.
Remove the conditional cell styles.
Displays the "Goal seek" dialog.
Calls the Insert/Database dialog.
Calls the Insert/Textfile dialog.
Calls the Insert/Clipboard dialog.
Display the "Text to Columns..." dialog.
Copy the content of the range of cells that is selected.
Set the cell formatting to look like your local currency.
Set the cell formatting to look like a percentage.
Make the cell text wrap onto multiple lines.
Set the font size of the selected cells to the defined size.
Convert all letters to upper case.
Convert all letters to lower case.
Convert First Letter to Upper Case.
Print cell contents vertically.
Set the comment of all selected cells to the defined comment string.
Set the angle of all selected cells to the defined angle value.
Set the text color of all selected cells to the defined color value.
Set the background color of all selected cells to the defined color value.
Set the border color of all selected cells to the defined color value.
Delete content of the range of cells that is selected.
Copy the content of the range of cells that is selected.
Cut the content of the range of cells that is selected.
Set the color of the left border.
Set the color of the top border.
Set the color of the right border.
Set the color of the bottom border.
Set the color of the all borders.
Set the color of the outline border.
Remove border.
Increase the indention.
Decrease the indention.
Increase the precision.
Decrease the precision.
Display the "Subtotals..." dialog.
Sort a group of cells in ascending (first to last) order.
Sort a group of cells in decreasing (last to first) order.
Display the "Layout..." dialog.
Increase the size of the font.
Decrease the size of the font.