Calc Documents


Many of the document-level methods and properties affect the entire document-for example, the ability to save and print a document. Other methods and properties exist purely as a convenience and the information is also available at the sheet level. For example, the Calc document acts as a draw-page supplier to access all of the draw pages, even though they are available individually from the sheet that contains them.

Named Ranges

The official definition of a named range is a named formula expression. My first thought was, "What's a named formula expression?" Typically, a named range represents a cell range, but it may also refer to external data. Naming ranges allows you to give meaningful names to things that you reference. Named ranges may therefore be used as an address in a formula. The NamedRangeFlag constants define how a named range may be used (see Table 47 ).

Table 47: Constants defined by the com.sun.star.sheet.NamedRangeFlag constant group .

Numeric

Value

Description

1

com.sun.star.sheet.NamedRangeFlag.FILTER_CRITERIA

The range contains filter criteria.

2

com.sun.star.sheet.NamedRangeFlag.PRINT_AREA

The range can be used as a print range.

4

Com.sun.star.sheet.NamedRangeFlag.COLUMN_HEADER

The range can be used as column headers for printing.

8

com.sun.star.sheet.NamedRangeFlag.ROW_HEADER

The range can be used as row headers for printing.

Each named range service supports the methods in Table 48 .

Table 48: Methods implemented by the com. sun. star.sheet.NamedRange service.

Method

Description

getReferredCells()

Get the CellRange referenced by named range.

getContent()

The named range content is a string and can be a reference to a cell, cell range, or formula expression.

setContent(String)

Set the content of the named range.

getReferencePosition()

Get the CellAddress used as a base for relative references in the content.

setReferencePosition(CellAddress)

Set the reference position.

getType()

Get the type as a NamedRangeFlag constant (see Table 47).

setType(NamedRangeFlag)

Set the type of the named range.

The document's NamedRanges property contains the collection of named ranges in the document. You can extract each individual named range by using named and indexed access.

Use the method addNewByName(name, content, CellAddress, NamedRangeFlag) to create and add a new named range. The name and the content are both of type String. The CellAddress specifies the base address for relative cell references. The NamedRangeFlag can contain a combination of flags but it is typically zero.

Use the method addNewFromTitles(CellRangeAddress, Border) to create named cell ranges from titles in a cell range. The Border value is taken from Table 49 and it specifies where the titles are located in the cell range.

Table 49: Enumerated values defined by the com.sun.star.sheet.Border enumeration.

Value

Description

com.sun.star.sheet.Border.TOP

Select the top border.

com.sun.star.sheet.Border.BOTTOM

Select the bottom border.

com.sun.star.sheet.Border.RIGHT

Select the right border.

com.sun.star.sheet.Border.LEFT

Select the left border.

Use the method outputList(CellAddress) to write a list of the named ranges to a sheet. The first column contains the name of each named range, and the second column contains the content. Finally, use the removeByName(name) method to remove a named range.

Protecting Documents and Sheets

Calc documents and individual spreadsheets support the XProtectable interface. Use the methods protect(password) and unprotect (password) to activate or disable protection. The password is passed in as a String. The isProtected() method returns True if protection is currently active.

Controlling Recalculation

By default, a Calc document automatically recalculates formulas when the cells to which they refer are modified. At times, it is useful to disable automatic recalculation. The methods in Table 50 allow you to control recalculation in the entire document.

Table 50: Methods defined by the com.sun.star.sheet.XCalculatable interface.

Method

Description

calculate()

Recalculate all cells with changed content.

calculateAll()

Recalculate all cells.

isAutomaticCalculationEnabled()

True if automatic calculation is enabled.

enableAutomaticCalculation(Boolean)

Enable or disable automatic calculation.

Using Goal Seek

"Goal Seek" attempts to solve equations with one unknown variable. In other words, after defining a formula with multiple fixed values and one variable value, Goal Seek tries to find an acceptable value for the unknown variable.

Consider a very simple example. If you jump off a cliff, gravity will accelerate you toward the ground at 32 feet per second each second. In other words, in one second you will be traveling at 32 feet per second and in two seconds you will be traveling at 64 feet per second. The equation is given as "velocity = acceleration * time". I have a constant value for the acceleration due to gravity, and I want to know how long before I am traveling at 100 feet per second. Admittedly, this is a trivial example, but it is easy to understand.

Use the document method seekGoal(CellAddress, CellAddress, String) to perform a Goal Seek operation. The first cell address identifies the cell that contains the formula to solve. The second cell address identifies the cell that contains the variable that will change. Place the best guess that you can make into this cell. The final string contains the value that you want to obtain from the formula. The macro in Listing 38 sets the formula and then performs a Goal Seek operation.

Listing 38: GoalSeekExample is in the Calc module in this chapter's source code files as SC14.sxc.
start example
 Sub GoalSeekExample   Dim oSheet   'oSheet will contain the fourth sheet.   Dim oGCell   'B24, gravity value of 32   Dim oTCell   'C24, time value   Dim oRCell   'Resulting equation "=B24*C24"   Dim oGoal    'Returned goal object   oSheet = ThisComponent.Sheets(3)   oGCell = oSheet.getCellByPosition(1, 23)   oGCell.setValue(32)   oTCell = oSheet.getCellByPosition(2, 23)   oTCell.setValue(1)   oRCell = oSheet.getCellByPosition(0, 23)   oRCell.setFormula("=B24 * C24")   oGoal=ThisComponent.seekGoal(oRCell.CellAddress, oTCell.CellAddress, "100")   MsgBox "Result = " & oGoal.Result & CHR$(10) &_          "The result changed by " & oGoal.Divergence &_          " in the last iteration", 0, "Goal Seek" End Sub 
end example
 

The seekGoal() method returns a structure containing two floating-point Double values. The Result property identifies the proposed solution. The Divergence property identifies the difference between the last guessed result and the current result. If the divergence is small, the result is probably reasonably accurate. If, however, the divergence is large, the result is probably inaccurate. I performed a test where there was no solution. The divergence value was roughly 1.0E308.

Do not let the simplicity of the provided example fool you. I used the seekGoal() method to provide a solution to a problem that had no closed form solution-I had to use a numerical algorithm to solve the problem.




OpenOffice.org Macros Explained
OpenOffice.org Macros Explained
ISBN: 1930919514
EAN: 2147483647
Year: 2004
Pages: 203

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net