odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java

   1 /*************************************************************************
   2  *
   3  *  The Contents of this file are made available subject to the terms of
   4  *  the BSD license.
   5  *
   6  *  Copyright 2000, 2010 Oracle and/or its affiliates.
   7  *  All rights reserved.
   8  *
   9  *  Redistribution and use in source and binary forms, with or without
  10  *  modification, are permitted provided that the following conditions
  11  *  are met:
  12  *  1. Redistributions of source code must retain the above copyright
  13  *     notice, this list of conditions and the following disclaimer.
  14  *  2. Redistributions in binary form must reproduce the above copyright
  15  *     notice, this list of conditions and the following disclaimer in the
  16  *     documentation and/or other materials provided with the distribution.
  17  *  3. Neither the name of Sun Microsystems, Inc. nor the names of its
  18  *     contributors may be used to endorse or promote products derived
  19  *     from this software without specific prior written permission.
  20  *
  21  *  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  22  *  "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  23  *  LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS
  24  *  FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE
  25  *  COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,
  26  *  INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  27  *  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS
  28  *  OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
  29  *  ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR
  30  *  TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE
  31  *  USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  32  *
  33  *************************************************************************/
  34
  35 import com.sun.star.uno.UnoRuntime;
  36 import com.sun.star.uno.RuntimeException;
  37
  38
  39 // __________  implementation  ____________________________________
  40
  41 /** Create a spreadsheet document and provide access to table contents.
  42  */
  43 public class GeneralTableSample extends SpreadsheetDocHelper
  44 {
  45
  46
  47
  48     public static void main( String args[] )
  49     {
  50         try
  51         {
  52             GeneralTableSample aSample = new GeneralTableSample( args );
  53             aSample.doSampleFunction();
  54         }
  55         catch (Exception ex)
  56         {
  57             System.out.println( "Error: Sample caught exception!\nException Message = "
  58                                 + ex.getMessage());
  59             ex.printStackTrace();
  60             System.exit( 1 );
  61         }
  62
  63         System.out.println( "Sample done." );
  64         System.exit( 0 );
  65     }
  66
  67
  68
  69     /// This sample function modifies cells and cell ranges.
  70     public void doSampleFunction() throws RuntimeException, Exception
  71     {
  72         // for common usage
  73         com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
  74         com.sun.star.beans.XPropertySet xPropSet = null;
  75         com.sun.star.table.XCell xCell = null;
  76         com.sun.star.table.XCellRange xCellRange = null;
  77
  78     // *** Access and modify a VALUE CELL ***
  79         System.out.println( "*** Sample for service table.Cell ***" );
  80
  81         xCell = xSheet.getCellByPosition( 0, 0 );
  82         // Set cell value.
  83         xCell.setValue( 1234 );
  84
  85         // Get cell value.
  86         double nDblValue = xCell.getValue() * 2;
  87         xSheet.getCellByPosition( 0, 1 ).setValue( nDblValue );
  88
  89     // *** Create a FORMULA CELL and query error type ***
  90         xCell = xSheet.getCellByPosition( 0, 2 );
  91         // Set formula string.
  92         xCell.setFormula( "=1/0" );
  93
  94         // Get error type.
  95         boolean bValid = (xCell.getError() == 0);
  96         // Get formula string.
  97         String aText = "The formula " + xCell.getFormula() + " is ";
  98         aText += bValid ? "valid." : "erroneous.";
  99
 100     // *** Insert a TEXT CELL using the XText interface ***
 101         xCell = xSheet.getCellByPosition( 0, 3 );
 102         com.sun.star.text.XText xCellText = UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell );
 103         com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
 104         xCellText.insertString( xTextCursor, aText, false );
 105
 106     // *** Change cell properties ***
 107         int nValue = bValid ? 0x00FF00 : 0xFF4040;
 108         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );
 109         xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( nValue ) );
 110
 111
 112     // *** Accessing a CELL RANGE ***
 113         System.out.println( "*** Sample for service table.CellRange ***" );
 114
 115         // Accessing a cell range over its position.
 116         xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 );
 117
 118         // Change properties of the range.
 119         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
 120         xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0x8080FF ) );
 121
 122         // Accessing a cell range over its name.
 123         xCellRange = xSheet.getCellRangeByName( "C4:D5" );
 124
 125         // Change properties of the range.
 126         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
 127         xPropSet.setPropertyValue( "CellBackColor", Integer.valueOf( 0xFFFF80 ) );
 128
 129
 130     // *** Using the CELL CURSOR to add some data below of the filled area ***
 131         System.out.println( "*** Sample for service table.CellCursor ***" );
 132
 133         // Create a cursor using the XSpreadsheet method createCursorByRange()
 134         xCellRange = xSheet.getCellRangeByName( "A1" );
 135         com.sun.star.sheet.XSheetCellRange xSheetCellRange = UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xCellRange );
 136
 137         com.sun.star.sheet.XSheetCellCursor xSheetCellCursor =
 138             xSheet.createCursorByRange( xSheetCellRange );
 139         com.sun.star.table.XCellCursor xCursor = UnoRuntime.queryInterface( com.sun.star.table.XCellCursor.class, xSheetCellCursor );
 140
 141         // Move to the last filled cell.
 142         xCursor.gotoEnd();
 143         // Move one row down.
 144         xCursor.gotoOffset( 0, 1 );
 145         xCursor.getCellByPosition( 0, 0 ).setFormula( "Beyond of the last filled cell." );
 146
 147
 148     // *** Modifying COLUMNS and ROWS ***
 149         System.out.println( "*** Sample for services table.TableRows and table.TableColumns ***" );
 150
 151         com.sun.star.table.XColumnRowRange xCRRange = UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xSheet );
 152         com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns();
 153         com.sun.star.table.XTableRows xRows = xCRRange.getRows();
 154
 155         // Get column C by index (interface XIndexAccess).
 156         Object aColumnObj = xColumns.getByIndex( 2 );
 157         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj );
 158         xPropSet.setPropertyValue( "Width", Integer.valueOf( 5000 ) );
 159
 160         // Get the name of the column.
 161         com.sun.star.container.XNamed xNamed = UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj );
 162         aText = "The name of this column is " + xNamed.getName() + ".";
 163         xSheet.getCellByPosition( 2, 2 ).setFormula( aText );
 164
 165         // Get column D by name (interface XNameAccess).
 166         com.sun.star.container.XNameAccess xColumnsName = UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, xColumns );
 167
 168         aColumnObj = xColumnsName.getByName( "D" );
 169         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj );
 170         xPropSet.setPropertyValue( "IsVisible", Boolean.FALSE );
 171
 172         // Get row 7 by index (interface XIndexAccess)
 173         Object aRowObj = xRows.getByIndex( 6 );
 174         xPropSet = UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRowObj );
 175         xPropSet.setPropertyValue( "Height", Integer.valueOf( 5000 ) );
 176
 177         xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." );
 178
 179         // Create a cell series with the values 1 ... 7.
 180         for (int nRow = 8; nRow < 15; ++nRow)
 181             xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 );
 182         // Insert a row between 1 and 2
 183         xRows.insertByIndex( 9, 1 );
 184         // Delete the rows with the values 3 and 4.
 185         xRows.removeByIndex( 11, 2 );
 186
 187     // *** Inserting CHARTS ***
 188         System.out.println( "*** Sample for service table.TableCharts ***" );
 189
 190         com.sun.star.table.XTableChartsSupplier xChartsSupp =
 191             UnoRuntime.queryInterface(
 192             com.sun.star.table.XTableChartsSupplier.class, xSheet );
 193         com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts();
 194
 195         // The chart will base on the last cell series, initializing all values.
 196         String aName = "newChart";
 197         com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle();
 198         aRect.X = 10000;
 199         aRect.Y = 3000;
 200         aRect.Width = aRect.Height = 5000;
 201         com.sun.star.table.CellRangeAddress[] aRanges = new com.sun.star.table.CellRangeAddress[1];
 202         aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" );
 203
 204         // Create the chart.
 205         xCharts.addNewByName( aName, aRect, aRanges, false, false );
 206
 207         // Get the chart by name.
 208         Object aChartObj = xCharts.getByName( aName );
 209         com.sun.star.table.XTableChart xChart = UnoRuntime.queryInterface( com.sun.star.table.XTableChart.class, aChartObj );
 210
 211         // Query the state of row and column headers.
 212         aText = "Chart has column headers: ";
 213         aText += xChart.getHasColumnHeaders() ? "yes" : "no";
 214         xSheet.getCellByPosition( 2, 8 ).setFormula( aText );
 215         aText = "Chart has row headers: ";
 216         aText += xChart.getHasRowHeaders() ? "yes" : "no";
 217         xSheet.getCellByPosition( 2, 9 ).setFormula( aText );
 218     }
 219
 220
 221
 222     public GeneralTableSample( String[] args )
 223     {
 224         super( args );
 225     }
 226
 227
 228 }