odk/examples/DevelopersGuide/Spreadsheet/GeneralTableSample.java

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