Difference between revisions of "Documentation/DevGuide/Spreadsheets/Multiple Operations"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Initial author Sun Microsystems, Inc.)
 
m (1 revision(s))
(No difference)

Revision as of 13:05, 15 February 2008



A multiple operation combines a series of formulas with a variable and a series of values. The results of each formula with each value is shown in the table. Additionally, it is possible to calculate a single formula with two variables using a 2-value series. The method setTableOperation() of the interface [IDL:com.sun.star.sheet.XMultipleOperation] inserts a multiple operation range.

The following example shows how to calculate the values 1 to 5 raised to the powers of 1 to 5 (each value to each power). The first column contains the base values, and the first row the exponents, for example, cell E3 contains the result of 24. Below there are three trigonometrical functions calculated based on a series of values, for example, cell C11 contains the result of cos(0.2).

A B C D E F G
1
=A2^B1
1
2
3
4
5
2
1
3
2
4
3
5
4
6
5
7
8
=SIN(A8)
=COS(A8)
=TAN(A8)
9
0
10
0.1
11
0.2
12
0.3
13
0.4

Note that the value series have to be included in the multiple operations cell range, but not the formula cell range (in the second example). The references in the formulas address any cell outside of the area to be filled. The column cell and row cell parameter have to reference these cells exactly. In the second example, a row cell address does not have to be used, because the row contains the formulas.

 public void InsertMultipleOperation(com.sun.star.sheet.XSpreadsheet xSheet)
         throws RuntimeException, Exception {
     // --- Two independent value series ---
     com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress(xSheet, "A1");
     com.sun.star.table.CellAddress aColCell = createCellAddress(xSheet, "A2");
     com.sun.star.table.CellAddress aRowCell = createCellAddress(xSheet, "B1");
     
     com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName("A1:F6");
     com.sun.star.sheet.XMultipleOperation xMultOp = (com.sun.star.sheet.XMultipleOperation)
         UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
     xMultOp.setTableOperation(
         aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell);
     
     // --- A value series, a formula series ---
     aFormulaRange = createCellRangeAddress(xSheet, "B8:D8");
     aColCell = createCellAddress(xSheet, "A8");
     // Row cell not needed
     
     xCellRange = xSheet.getCellRangeByName("A9:D13");
     xMultOp = (com.sun.star.sheet.XMultipleOperation)
         UnoRuntime.queryInterface(com.sun.star.sheet.XMultipleOperation.class, xCellRange);
     xMultOp.setTableOperation(
         aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell);
 }
     
 /** Creates a com.sun.star.table.CellAddress and initializes it
     with the given range.
     @param xSheet The XSpreadsheet interface of the spreadsheet.
     @param aCell The address of the cell (or a named cell).
  */
 public com.sun.star.table.CellAddress createCellAddress(
         com.sun.star.sheet.XSpreadsheet xSheet,
         String aCell ) throws RuntimeException, Exception {
     com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable)
         UnoRuntime.queryInterface(com.sun.star.sheet.XCellAddressable.class,
             xSheet.getCellRangeByName(aCell).getCellByPosition(0, 0));
     return xAddr.getCellAddress();
 }
 
 /** Creates a com.sun.star.table.CellRangeAddress and initializes
     it with the given range.
     @param xSheet The XSpreadsheet interface of the spreadsheet.
     @param aRange The address of the cell range (or a named range).
  */
 public com.sun.star.table.CellRangeAddress createCellRangeAddress(
         com.sun.star.sheet.XSpreadsheet xSheet, String aRange) {
     com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable)
         UnoRuntime.queryInterface(com.sun.star.sheet.XCellRangeAddressable.class,
             xSheet.getCellRangeByName(aRange));
     return xAddr.getRangeAddress();
 }


Content on this page is licensed under the Public Documentation License (PDL).
Personal tools