Name
itcluno::SpreadSheet - A SpreadSheet Class for Openoffice.org's Scalc
Synopsis
itcluno::
SpreadSheet objectname ?options?
Inheritance
Standard Options
-delimiter - used for CSV- and Txt-Files to set the field delimiter, no effect on other file types
-separator - used for CSV- and Txt-Files to set the field delimiter, no effect on other file types
Methods
objectname activateSheet sheetName
activates the sheet "sheetName"
Return-Value: the name of the activated sheet, an empty string when the sheet does not exist
objectname columnWidth sheetName columnList width
sets the column width. columnlist is a tcl list with the corresponding columns (starting at 0). width is the width in 1/100th millimeters or the value "optimal"
Return-Value: the name of the corresponding sheet, an empty string when the sheet does not exist
objectname createChart targetSheetName graphicRectangle options sourceSheetName sourceRange
creates a new chart in the sheet "targetSheetname" (will be created if it does not exist) within the rectangle "graphicRectangle" with the options "options". The values for the chart should be found in the sheet "sourceSheetName" in the range "sourceRange"
Return-Value: an identifier of the form "chart_<number>" which can be used for further references to the chart, an empty string if the source sheet does not exist
objectname createPivotTable targetSheetName targetCell sourceSheetName sourceRange columnFields rowFields dataFields {propertyList ""}
creates a datapilot table (aka pivot table) in targetSheetName (will be created if it does not exist) at targetCell. The values for the new table are taken from sourceSheetName at sourceRange. Column and row headers are given by the columnFields and rowFields arguments. DataFields is a list of lists mit two elements: the first containg the field name, the second one the function to be applied on the field. Possible values for the function are: AUTO AVERAGE COUNT COUNTNUMS MAX MIN NONE PRODUCT STDEV STDEVP SUM VAR VARP
Return-Value:
objectname deleteSheet sheetName
deletes the sheet "sheetName"
Return-Value: the name of the deleted sheet, an empty string when the sheet does not exist
objectname exportToPdf filename
export the document to pdf
Return-Value:
objectname getCellProperties sheetName cellIndex {propertyList ""}
gets all or the specified properties of the cell
Return-Value: a list of the appropriate properties and values
objectname getCellRangeProperties sheetName cellRange {propertyList ""}
gets all or the specified properties of the cell range
Return-Value: a list of the appropriate properties and values
objectname getColumnProperties sheetName columnIndex {propertyList ""}
gets all or the specified properties of the column
Return-Value: a list of the appropriate properties and values
objectname getDocumentProperties {propertyList ""}
gets all or the specified properties of the document
Return-Value: a list of the appropriate properties and values
objectname getPageProperties pageName {pageStyleName ""} {propertyList ""}
gets all or the specified properties of the page
Return-Value: a list of the appropriate properties and values
objectname getPageStyles sheetName
gets all styles of the page
Return-Value: a list of all page styles
objectname getRowProperties sheetName rowIndex {propertyList ""}
gets all or the specified properties of the row
Return-Value: a list of the appropriate properties and values
objectname getSheetBasename
The default name of a newly created sheet is language dependent. So here you have a way to make your scripts language independent
Return-Value: the default sheet name without the trailing index number
objectname getSheetProperties sheetName {propertyList ""}
gets all or the specified properties of the sheet
Return-Value: a list of the appropriate properties and values
objectname importExternalDataSheet targetSheet url sourceSheet filterName filterOptions {linkMode NORMAL}
import the sourcesheet (must exist) in file "url" (may be a normal filename) into the targetsheet
Return-Value: the targetsheet
objectname insertColumns sheetName insertBefore {insertCount 1}
insert insertCount columns into the sheet
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname insertRows sheetName insertBefore {insertCount 1}
insert insertCount rows into the sheet
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname insertSheet sheetName {position end}
inserts a new sheet with name "sheetName" at the given position
Return-Value: the name of the inserted sheet, an empty string when the sheet already exists
objectname mergeCells sheetName cellRange
merges the cells of the given range in the sheet "sheetname" (unmerges them when they are already merged)
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname printArea sheetName columnRange rowRange
sets the defined range of the sheet as the print area
Return-Value:
objectname moveSheet sheetName {position end}
moves the sheet "sheetName" to the given position
Return-Value: the name of the moved sheet, an empty string when the sheet does not exist
objectname replace sheetName cellRange searchString replaceString {propertyList ""}
replaces the string searchString by replaceString in the given cell range.
propertylist must be a list containing properties and values. For the allowed properties see: SearchDescriptor in the OpenOffice.org documentation
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname removeColumns sheetName removeStart {removeCount 1}
removes removeCount rows starting at removeStart
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname removeRows sheetName removeStart {removeCount 1}
removes removeCount columns starting at removeStart
Return-Value: the name of the sheet, an empty string when the sheet does not exist
objectname renameSheet oldname newname
renames the sheet "oldname" to "newname"
Return-Value: the name of the renamed sheet, an empty string when the sheet does not exist
objectname setBorder sheetName cellRange whichBorder properties
sets the border for the cellrange. whichBorder can only be "TopBorder", "BottomBorder", "LeftBorder", "RightBorder" or "TableBorder". properties contains a list for describing the border.
Return-Value:
objectname setCellFormat sheetName cellRange type {subType ""}
sets the format type for the cell range in the given sheet.Right now the parameter subType is not used
Return-Value:
objectname setCellProperties sheetName cellIndex propertyList
sets the specified properties for the cell
Return-Value:
objectname setCellValue sheetName cellIndex valueList
sets values starting at cellIndex. valuelist is a list of lists. Each list contains the values for a row.
Return-Value:
objectname setColumnProperties sheetName columnIndex propertyList
sets the specified properties for the column
Return-Value:
objectname setConditionalFormat sheetName cellRange propertyList
sets the specified properties for the cellRange (see also: http://wiki.services.openoffice.org/wiki/Documentation/DevGuide/Spreadsheets/Conditional_Formats
Return-Value:
objectname setDocumentProperties propertyList
sets the specified properties for the document
Return-Value:
objectname setFreezePosition sheetName cell
fixes the scrolling at position cell in sheetName
Return-Value:
objectname setPageProperties pageName pageStyleName propertyList
sets the specified properties for the page
Return-Value:
objectname setRowProperties sheetName rowIndex propertyList
sets the specified properties for the row
Return-Value:
objectname setSheetProperties sheetName propertyList
sets the specified properties for the sheet
Return-Value:
Examples
Simply fill cells
simply fill cells with text, numbers and currencies
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set values [list \
[list Bundesland Stadt Konzern Kunde AE UMB] \
[list Bayern München Konzern1 Hallo -10 234] \
[list NRW Köln Konzern2 Blabla 20 456] \
[list NRW Köln Konzern2 Blabla 50 789] \
[list NRW Düsseldorf Konzern2 "Weiß nicht" 30 123] \
]
set tableBasename [new_document getSheetBasename]
new_document setCellValue ${tableBasename}1 [list 3 4] [list [list "a centered header within merged cells"]]
new_document mergeCells ${tableBasename}1 [list 3 4 8 4]
set properties(HoriJustify) [itcluno::OfficeUtilities::enum com.sun.star.table.CellHoriJustify CENTER]
new_document setCellProperties ${tableBasename}1 [list 3 4] [array get properties]
new_document setCellValue ${tableBasename}1 [list 3 5] $values
new_document setCellValue ${tableBasename}1 A1 \
[list [list "This text simply appears at the upper left corner"]]
new_document setCellValue ${tableBasename}1 A2 [list [list 1.1 2.2 3.3 =SUM(A2:C2)]]
new_document setCellValue ${tableBasename}1 A3 -1.1
new_document setCellFormat ${tableBasename}1 H7:I10 CURRENCY
Inserting, activate, move and Renaming Sheets
Each step within this example is displayed in the SpreadSheet.
Have a look at it
package require itcluno
set waitTime 3000
set row 0
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set sheetNames [new_document getSheetNames]
set tableBaseName [new_document getSheetBasename]
after $waitTime
new_document setCellValue ${tableBaseName}1 [list 0 $row] \
[list [list "Inserted new Sheet at end"]]
incr row
new_document insertSheet "Position at end"
update idletasks
after $waitTime
new_document setCellValue ${tableBaseName}1 [list 0 $row] \
[list [list "Inserted new Sheet at beginning"]]
incr row
new_document insertSheet "First Sheet" 0
update idletasks
after $waitTime
new_document setCellValue ${tableBaseName}1 [list 0 $row] \
[list [list "Renamed this sheet to \"Renamed first Sheet\""]]
incr row
new_document renameSheet ${tableBaseName}1 "Renamed first Sheet"
update idletasks
after $waitTime
new_document setCellValue "Renamed first Sheet" [list 0 $row] \
[list [list "moved Sheet ${tableBaseName}2 to sheet with index 1"]]
incr row
new_document moveSheet ${tableBaseName}2 1
update idletasks
after $waitTime
new_document setCellValue "Position at end" \
[list 0 0] [list [list "now this sheet is active"]]
new_document activateSheet "Position at end"
Properties
Fills the cells A1:B3 and displays their properties below them
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set sheetNames [new_document getSheetNames]
set tableBasename [new_document getSheetBasename]
set sheet0 [lindex $sheetNames 0]
new_document columnWidth $sheet0 [list 0 1 2 3] 5000
array set properties \
[itcluno::OfficeUtilities::tkFont2ooFont \
[list Times 30 bold italic underline]]
array set properties \
[itcluno::OfficeUtilities::tkColor2ooColor CellBackColor green]
set properties(CharUnderlineColor) 0xff0000
set properties(CharUnderlineHasColor) True
new_document setCellRangeProperties $sheet0 A1:B3 [array get properties]
new_document setCellValue $sheet0 [list 0 0] [list [list A1 B1] [list A2 B2]]
array set properties [new_document getCellRangeProperties $sheet0 A1:B3]
set propList [list]
foreach e [lsort [array names properties]] {
lappend propList [list $e $properties($e)]
}
new_document setCellValue $sheet0 A4 $propList
new_document setBorder $sheet0 \
[list 1 3 1 [expr {[llength [array names properties]]+2}]] \
LeftBorder \
[list Color 0x0000ff InnerLineWidth 10 OuterLineWidth 20 LineDistance 30]
Pivot tables
Fills a cellrange in the first sheet and creates DataPilot-tables (also known as Pivot-tables) in the second and third sheet
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set tableBasename [new_document getSheetBasename]
set values [list \
[list Bundesland Stadt Konzern Kunde AE UMB] \
[list Bayern München Konzern1 Hallo -10 234] \
[list NRW Köln Konzern2 Blabla 20 456] \
[list NRW Köln Konzern2 Blabla 50 789] \
[list NRW Düsseldorf Konzern2 "Weiß nicht" 30 123] \
]
set noColumns [expr {[llength [lindex $values 0]]-1}]
set noRows [expr {[llength $values]-1}]
set sourceRange [list 0 0 $noColumns $noRows]
new_document setCellValue ${tableBasename}1 [list 0 0] $values
new_document setCellFormat ${tableBasename}1 $sourceRange CURRENCY
new_document saveFile pivot_table.sxc
set t1 [new_document createPivotTable \
${tableBasename}2 A1 \
${tableBasename}1 $sourceRange \
[list Bundesland] \
[list Konzern Kunde] \
[list [list AE SUM] [list UMB AVERAGE]]]
new_document activateSheet ${tableBasename}2
new_document createPivotTable \
${tableBasename}3 A1 \
${tableBasename}1 $sourceRange \
[list Bundesland Stadt] \
[list Konzern Kunde] \
[list [list AE AVERAGE] [list UMB SUM]]
Charts
Creates a chart from a cellrange, as barchart and linechart
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set tableBasename [new_document getSheetBasename]
set values [list \
[list Month Temperature] \
[list January -5] \
[list February -3] \
[list March 3] \
[list April 15] \
]
set sourceRange [list 0 0 [expr {[llength [lindex $values 0]]-1}] [expr {[llength $values]-1}]]
new_document setCellValue ${tableBasename}1 [list 0 0] $values
new_document saveFile chart_table.sxc
new_document createChart ${tableBasename}1 \
[list 5 5 10 5 cm] \
[list hasRowHeader 1 hasColumnHeader 1 title "Temperature Chart" type BarDiagram] \
${tableBasename}1 $sourceRange
new_document createChart ${tableBasename}1 \
[list 5 11 10 5 cm] \
[list hasRowHeader 1 hasColumnHeader 1 title "Temperature Chart" type LineDiagram] \
${tableBasename}1 $sourceRange
Converter
Fills a cellrange, saves the document as Scalc, MS Excel, .txt (tabstop separated) and .csv (comma separated).
The files are named new_document.<suffix>. You are not asked, whether to overwrite them or not.
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set tableBasename [new_document getSheetBasename]
set values [list \
[list Bundesland Stadt Konzern Kunde AE UMB] \
[list Bayern München Konzern1 Hallo 10 234] \
[list NRW Köln Konzern2 Blabla 20 456] \
[list NRW Köln Konzern2 Blabla 50 789] \
[list NRW Düsseldorf Konzern2 "Weiß nicht" 30 123] \
]
set sourceRange [list 0 0 [expr {[llength [lindex $values 0]]-1}] [expr {[llength $values]-1}]]
new_document setCellValue ${tableBasename}1 [list 0 0] $values
new_document saveFile new_document.sxc
new_document close
itcl::delete object new_document
::itcluno::SpreadSheet new_document -filename new_document.sxc
new_document saveFile new_document.xls
new_document saveFile new_document.txt
new_document saveFile new_document.csv
Borders
creates different borders
package require itcluno
catch {itcl::delete object new_document}
::itcluno::SpreadSheet new_document
set tableBasename [new_document getSheetBasename]
set values [list \
[list Month Temperature] \
[list January -5] \
[list February -3] \
[list March 3] \
[list April 15] \
]
new_document setCellValue ${tableBasename}1 [list 0 0] $values
new_document setBorder ${tableBasename}1 A2:A5 RightBorder [list OuterLineWidth 100]
set greenColor [itcluno::OfficeUtilities::tkColor2ooColor Color Green]
new_document setBorder ${tableBasename}1 B1:B1 BottomBorder \
[eval list $greenColor [list OuterLineWidth 100]]
new_document setBorder ${tableBasename}1 A1 TableBorder [list \
BottomLine [eval list $greenColor [list OuterLineWidth 100]] \
RightLine [list OuterLineWidth 100] \
]
new_document setBorder ${tableBasename}1 C7:F10 TableBorder [list \
AllLines [list InnerLineWidth 50 OuterLineWidth 50 LineDistance 50] \
ValidLines [list Horizontal Vertical Top Right Bottom Left]]
Bugs
no bugs known, but of course no software is bug free. If you found one, file them to the bug section on our sourceforge page.
ToDo
a lot of new features which I find useful. If you find that anything useful is missing, please let me know via the bug report
Top