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 = (com.sun.star.text.XText) 103 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 = (com.sun.star.beans.XPropertySet) 110 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); 111 xPropSet.setPropertyValue( "CellBackColor", new Integer( nValue ) ); 112 113 114 // *** Accessing a CELL RANGE *** 115 System.out.println( "*** Sample for service table.CellRange ***" ); 116 117 // Accessing a cell range over its position. 118 xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 ); 119 120 // Change properties of the range. 121 xPropSet = (com.sun.star.beans.XPropertySet) 122 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 123 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x8080FF ) ); 124 125 // Accessing a cell range over its name. 126 xCellRange = xSheet.getCellRangeByName( "C4:D5" ); 127 128 // Change properties of the range. 129 xPropSet = (com.sun.star.beans.XPropertySet) 130 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 131 xPropSet.setPropertyValue( "CellBackColor", new Integer( 0xFFFF80 ) ); 132 133 134 // *** Using the CELL CURSOR to add some data below of the filled area *** 135 System.out.println( "*** Sample for service table.CellCursor ***" ); 136 137 // Create a cursor using the XSpreadsheet method createCursorByRange() 138 xCellRange = xSheet.getCellRangeByName( "A1" ); 139 com.sun.star.sheet.XSheetCellRange xSheetCellRange = (com.sun.star.sheet.XSheetCellRange) 140 UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xCellRange ); 141 142 com.sun.star.sheet.XSheetCellCursor xSheetCellCursor = 143 xSheet.createCursorByRange( xSheetCellRange ); 144 com.sun.star.table.XCellCursor xCursor = (com.sun.star.table.XCellCursor) 145 UnoRuntime.queryInterface( com.sun.star.table.XCellCursor.class, xSheetCellCursor ); 146 147 // Move to the last filled cell. 148 xCursor.gotoEnd(); 149 // Move one row down. 150 xCursor.gotoOffset( 0, 1 ); 151 xCursor.getCellByPosition( 0, 0 ).setFormula( "Beyond of the last filled cell." ); 152 153 154 // *** Modifying COLUMNS and ROWS *** 155 System.out.println( "*** Sample for services table.TableRows and table.TableColumns ***" ); 156 157 com.sun.star.table.XColumnRowRange xCRRange = (com.sun.star.table.XColumnRowRange) 158 UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xSheet ); 159 com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns(); 160 com.sun.star.table.XTableRows xRows = xCRRange.getRows(); 161 162 // Get column C by index (interface XIndexAccess). 163 Object aColumnObj = xColumns.getByIndex( 2 ); 164 xPropSet = (com.sun.star.beans.XPropertySet) 165 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj ); 166 xPropSet.setPropertyValue( "Width", new Integer( 5000 ) ); 167 168 // Get the name of the column. 169 com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed) 170 UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj ); 171 aText = "The name of this column is " + xNamed.getName() + "."; 172 xSheet.getCellByPosition( 2, 2 ).setFormula( aText ); 173 174 // Get column D by name (interface XNameAccess). 175 com.sun.star.container.XNameAccess xColumnsName = (com.sun.star.container.XNameAccess) 176 UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, xColumns ); 177 178 aColumnObj = xColumnsName.getByName( "D" ); 179 xPropSet = (com.sun.star.beans.XPropertySet) 180 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj ); 181 xPropSet.setPropertyValue( "IsVisible", new Boolean( false ) ); 182 183 // Get row 7 by index (interface XIndexAccess) 184 Object aRowObj = xRows.getByIndex( 6 ); 185 xPropSet = (com.sun.star.beans.XPropertySet) 186 UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRowObj ); 187 xPropSet.setPropertyValue( "Height", new Integer( 5000 ) ); 188 189 xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." ); 190 191 // Create a cell series with the values 1 ... 7. 192 for (int nRow = 8; nRow < 15; ++nRow) 193 xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 ); 194 // Insert a row between 1 and 2 195 xRows.insertByIndex( 9, 1 ); 196 // Delete the rows with the values 3 and 4. 197 xRows.removeByIndex( 11, 2 ); 198 199 // *** Inserting CHARTS *** 200 System.out.println( "*** Sample for service table.TableCharts ***" ); 201 202 com.sun.star.table.XTableChartsSupplier xChartsSupp = 203 (com.sun.star.table.XTableChartsSupplier) UnoRuntime.queryInterface( 204 com.sun.star.table.XTableChartsSupplier.class, xSheet ); 205 com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts(); 206 207 // The chart will base on the last cell series, initializing all values. 208 String aName = "newChart"; 209 com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle(); 210 aRect.X = 10000; 211 aRect.Y = 3000; 212 aRect.Width = aRect.Height = 5000; 213 com.sun.star.table.CellRangeAddress[] aRanges = new com.sun.star.table.CellRangeAddress[1]; 214 aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" ); 215 216 // Create the chart. 217 xCharts.addNewByName( aName, aRect, aRanges, false, false ); 218 219 // Get the chart by name. 220 Object aChartObj = xCharts.getByName( aName ); 221 com.sun.star.table.XTableChart xChart = (com.sun.star.table.XTableChart) 222 UnoRuntime.queryInterface( com.sun.star.table.XTableChart.class, aChartObj ); 223 224 // Query the state of row and column headers. 225 aText = "Chart has column headers: "; 226 aText += xChart.getHasColumnHeaders() ? "yes" : "no"; 227 xSheet.getCellByPosition( 2, 8 ).setFormula( aText ); 228 aText = "Chart has row headers: "; 229 aText += xChart.getHasRowHeaders() ? "yes" : "no"; 230 xSheet.getCellByPosition( 2, 9 ).setFormula( aText ); 231 } 232 233 // ________________________________________________________________ 234 235 public GeneralTableSample( String[] args ) 236 { 237 super( args ); 238 } 239 240 // ________________________________________________________________ 241 } 242