odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java

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