1*34dd1e25SAndrew Rist /************************************************************** 2*34dd1e25SAndrew Rist * 3*34dd1e25SAndrew Rist * Licensed to the Apache Software Foundation (ASF) under one 4*34dd1e25SAndrew Rist * or more contributor license agreements. See the NOTICE file 5*34dd1e25SAndrew Rist * distributed with this work for additional information 6*34dd1e25SAndrew Rist * regarding copyright ownership. The ASF licenses this file 7*34dd1e25SAndrew Rist * to you under the Apache License, Version 2.0 (the 8*34dd1e25SAndrew Rist * "License"); you may not use this file except in compliance 9*34dd1e25SAndrew Rist * with the License. You may obtain a copy of the License at 10*34dd1e25SAndrew Rist * 11*34dd1e25SAndrew Rist * http://www.apache.org/licenses/LICENSE-2.0 12*34dd1e25SAndrew Rist * 13*34dd1e25SAndrew Rist * Unless required by applicable law or agreed to in writing, 14*34dd1e25SAndrew Rist * software distributed under the License is distributed on an 15*34dd1e25SAndrew Rist * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16*34dd1e25SAndrew Rist * KIND, either express or implied. See the License for the 17*34dd1e25SAndrew Rist * specific language governing permissions and limitations 18*34dd1e25SAndrew Rist * under the License. 19*34dd1e25SAndrew Rist * 20*34dd1e25SAndrew Rist *************************************************************/ 21*34dd1e25SAndrew Rist 22*34dd1e25SAndrew Rist 23cdf0e10cSrcweir 24cdf0e10cSrcweir import com.sun.star.uno.UnoRuntime; 25cdf0e10cSrcweir import com.sun.star.uno.RuntimeException; 26cdf0e10cSrcweir import com.sun.star.uno.AnyConverter; 27cdf0e10cSrcweir 28cdf0e10cSrcweir // __________ implementation ____________________________________ 29cdf0e10cSrcweir 30cdf0e10cSrcweir /** Create and modify a spreadsheet document. 31cdf0e10cSrcweir */ 32cdf0e10cSrcweir public class SpreadsheetSample extends SpreadsheetDocHelper 33cdf0e10cSrcweir { 34cdf0e10cSrcweir 35cdf0e10cSrcweir // ________________________________________________________________ 36cdf0e10cSrcweir main( String args[] )37cdf0e10cSrcweir public static void main( String args[] ) 38cdf0e10cSrcweir { 39cdf0e10cSrcweir try 40cdf0e10cSrcweir { 41cdf0e10cSrcweir SpreadsheetSample aSample = new SpreadsheetSample( args ); 42cdf0e10cSrcweir aSample.doSampleFunction(); 43cdf0e10cSrcweir } 44cdf0e10cSrcweir catch (Exception ex) 45cdf0e10cSrcweir { 46cdf0e10cSrcweir System.out.println( "Error: Sample caught exception!\nException Message = " 47cdf0e10cSrcweir + ex.getMessage()); 48cdf0e10cSrcweir ex.printStackTrace(); 49cdf0e10cSrcweir System.exit( 1 ); 50cdf0e10cSrcweir } 51cdf0e10cSrcweir System.out.println( "\nSamples done." ); 52cdf0e10cSrcweir System.exit( 0 ); 53cdf0e10cSrcweir } 54cdf0e10cSrcweir 55cdf0e10cSrcweir // ________________________________________________________________ 56cdf0e10cSrcweir SpreadsheetSample( String[] args )57cdf0e10cSrcweir public SpreadsheetSample( String[] args ) 58cdf0e10cSrcweir { 59cdf0e10cSrcweir super( args ); 60cdf0e10cSrcweir } 61cdf0e10cSrcweir 62cdf0e10cSrcweir // ________________________________________________________________ 63cdf0e10cSrcweir 64cdf0e10cSrcweir /** This sample function performs all changes on the document. */ doSampleFunction()65cdf0e10cSrcweir public void doSampleFunction() 66cdf0e10cSrcweir { 67cdf0e10cSrcweir try 68cdf0e10cSrcweir { 69cdf0e10cSrcweir doCellSamples(); 70cdf0e10cSrcweir } 71cdf0e10cSrcweir catch (Exception ex) 72cdf0e10cSrcweir { 73cdf0e10cSrcweir System.out.println( "\nError: Cell sample caught exception!\nException Message = " 74cdf0e10cSrcweir + ex.getMessage()); 75cdf0e10cSrcweir ex.printStackTrace(); 76cdf0e10cSrcweir } 77cdf0e10cSrcweir 78cdf0e10cSrcweir try 79cdf0e10cSrcweir { 80cdf0e10cSrcweir doCellRangeSamples(); 81cdf0e10cSrcweir } 82cdf0e10cSrcweir catch (Exception ex) 83cdf0e10cSrcweir { 84cdf0e10cSrcweir System.out.println( "\nError: Cell range sample caught exception!\nException Message = " 85cdf0e10cSrcweir + ex.getMessage()); 86cdf0e10cSrcweir ex.printStackTrace(); 87cdf0e10cSrcweir } 88cdf0e10cSrcweir 89cdf0e10cSrcweir try 90cdf0e10cSrcweir { 91cdf0e10cSrcweir doCellRangesSamples(); 92cdf0e10cSrcweir } 93cdf0e10cSrcweir catch (Exception ex) 94cdf0e10cSrcweir { 95cdf0e10cSrcweir System.out.println( "\nError: Cell range container sample caught exception!\nException Message = " 96cdf0e10cSrcweir + ex.getMessage()); 97cdf0e10cSrcweir ex.printStackTrace(); 98cdf0e10cSrcweir } 99cdf0e10cSrcweir 100cdf0e10cSrcweir try 101cdf0e10cSrcweir { 102cdf0e10cSrcweir doCellCursorSamples(); 103cdf0e10cSrcweir } 104cdf0e10cSrcweir catch (Exception ex) 105cdf0e10cSrcweir { 106cdf0e10cSrcweir System.out.println( "\nError: Cell cursor sample caught exception!\nException Message = " 107cdf0e10cSrcweir + ex.getMessage()); 108cdf0e10cSrcweir ex.printStackTrace(); 109cdf0e10cSrcweir } 110cdf0e10cSrcweir 111cdf0e10cSrcweir try 112cdf0e10cSrcweir { 113cdf0e10cSrcweir doFormattingSamples(); 114cdf0e10cSrcweir } 115cdf0e10cSrcweir catch (Exception ex) 116cdf0e10cSrcweir { 117cdf0e10cSrcweir System.out.println( "\nError: Formatting sample caught exception!\nException Message = " 118cdf0e10cSrcweir + ex.getMessage()); 119cdf0e10cSrcweir ex.printStackTrace(); 120cdf0e10cSrcweir } 121cdf0e10cSrcweir 122cdf0e10cSrcweir try 123cdf0e10cSrcweir { 124cdf0e10cSrcweir doDocumentSamples(); 125cdf0e10cSrcweir } 126cdf0e10cSrcweir catch (Exception ex) 127cdf0e10cSrcweir { 128cdf0e10cSrcweir System.out.println( "\nError: Document sample caught exception!\nException Message = " 129cdf0e10cSrcweir + ex.getMessage()); 130cdf0e10cSrcweir ex.printStackTrace(); 131cdf0e10cSrcweir } 132cdf0e10cSrcweir 133cdf0e10cSrcweir try 134cdf0e10cSrcweir { 135cdf0e10cSrcweir doDatabaseSamples(); 136cdf0e10cSrcweir } 137cdf0e10cSrcweir catch( Exception ex ) 138cdf0e10cSrcweir { 139cdf0e10cSrcweir System.out.println( "\nError: Database sample caught exception!\nException Message = " 140cdf0e10cSrcweir + ex.getMessage()); 141cdf0e10cSrcweir ex.printStackTrace(); 142cdf0e10cSrcweir } 143cdf0e10cSrcweir 144cdf0e10cSrcweir try 145cdf0e10cSrcweir { 146cdf0e10cSrcweir doDataPilotSamples(); 147cdf0e10cSrcweir } 148cdf0e10cSrcweir catch (Exception ex) 149cdf0e10cSrcweir { 150cdf0e10cSrcweir System.out.println( "\nError: Dota pilot sample caught exception!\nException Message = " 151cdf0e10cSrcweir + ex.getMessage()); 152cdf0e10cSrcweir ex.printStackTrace(); 153cdf0e10cSrcweir } 154cdf0e10cSrcweir 155cdf0e10cSrcweir try 156cdf0e10cSrcweir { 157cdf0e10cSrcweir doNamedRangesSamples(); 158cdf0e10cSrcweir } 159cdf0e10cSrcweir catch( Exception ex ) 160cdf0e10cSrcweir { 161cdf0e10cSrcweir System.out.println( "\nError: Named ranges sample caught exception!\nException Message = " 162cdf0e10cSrcweir + ex.getMessage()); 163cdf0e10cSrcweir ex.printStackTrace(); 164cdf0e10cSrcweir } 165cdf0e10cSrcweir 166cdf0e10cSrcweir try 167cdf0e10cSrcweir { 168cdf0e10cSrcweir doFunctionAccessSamples(); 169cdf0e10cSrcweir } 170cdf0e10cSrcweir catch (Exception ex) 171cdf0e10cSrcweir { 172cdf0e10cSrcweir System.out.println( "\nError: Function access sample caught exception!\nException Message = " 173cdf0e10cSrcweir + ex.getMessage()); 174cdf0e10cSrcweir ex.printStackTrace(); 175cdf0e10cSrcweir } 176cdf0e10cSrcweir 177cdf0e10cSrcweir try 178cdf0e10cSrcweir { 179cdf0e10cSrcweir doApplicationSettingsSamples(); 180cdf0e10cSrcweir } 181cdf0e10cSrcweir catch (Exception ex) 182cdf0e10cSrcweir { 183cdf0e10cSrcweir System.out.println( "\nError: Application settings sample caught exception!\nException Message = " 184cdf0e10cSrcweir + ex.getMessage()); 185cdf0e10cSrcweir ex.printStackTrace(); 186cdf0e10cSrcweir } 187cdf0e10cSrcweir } 188cdf0e10cSrcweir 189cdf0e10cSrcweir // ________________________________________________________________ 190cdf0e10cSrcweir 191cdf0e10cSrcweir /** All samples regarding the service com.sun.star.sheet.SheetCell. */ doCellSamples()192cdf0e10cSrcweir private void doCellSamples() throws RuntimeException, Exception 193cdf0e10cSrcweir { 194cdf0e10cSrcweir System.out.println( "\n*** Samples for service sheet.SheetCell ***\n" ); 195cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 196cdf0e10cSrcweir com.sun.star.table.XCell xCell = null; 197cdf0e10cSrcweir com.sun.star.beans.XPropertySet xPropSet = null; 198cdf0e10cSrcweir String aText; 199cdf0e10cSrcweir prepareRange( xSheet, "A1:C7", "Cells and Cell Ranges" ); 200cdf0e10cSrcweir 201cdf0e10cSrcweir // --- Get cell B3 by position - (column, row) --- 202cdf0e10cSrcweir xCell = xSheet.getCellByPosition( 1, 2 ); 203cdf0e10cSrcweir 204cdf0e10cSrcweir 205cdf0e10cSrcweir // --- Insert two text paragraphs into the cell. --- 206cdf0e10cSrcweir com.sun.star.text.XText xText = (com.sun.star.text.XText) 207cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell ); 208cdf0e10cSrcweir com.sun.star.text.XTextCursor xTextCursor = xText.createTextCursor(); 209cdf0e10cSrcweir 210cdf0e10cSrcweir xText.insertString( xTextCursor, "Text in first line.", false ); 211cdf0e10cSrcweir xText.insertControlCharacter( xTextCursor, 212cdf0e10cSrcweir com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false ); 213cdf0e10cSrcweir xText.insertString( xTextCursor, "And a ", false ); 214cdf0e10cSrcweir 215cdf0e10cSrcweir // create a hyperlink 216cdf0e10cSrcweir com.sun.star.lang.XMultiServiceFactory xServiceMan = (com.sun.star.lang.XMultiServiceFactory) 217cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); 218cdf0e10cSrcweir Object aHyperlinkObj = xServiceMan.createInstance( "com.sun.star.text.TextField.URL" ); 219cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 220cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aHyperlinkObj ); 221cdf0e10cSrcweir xPropSet.setPropertyValue( "URL", "http://www.example.org" ); 222cdf0e10cSrcweir xPropSet.setPropertyValue( "Representation", "hyperlink" ); 223cdf0e10cSrcweir // ... and insert 224cdf0e10cSrcweir com.sun.star.text.XTextContent xContent = (com.sun.star.text.XTextContent) 225cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.text.XTextContent.class, aHyperlinkObj ); 226cdf0e10cSrcweir xText.insertTextContent( xTextCursor, xContent, false ); 227cdf0e10cSrcweir 228cdf0e10cSrcweir 229cdf0e10cSrcweir // --- Query the separate paragraphs. --- 230cdf0e10cSrcweir com.sun.star.container.XEnumerationAccess xParaEA = 231cdf0e10cSrcweir (com.sun.star.container.XEnumerationAccess) UnoRuntime.queryInterface( 232cdf0e10cSrcweir com.sun.star.container.XEnumerationAccess.class, xCell ); 233cdf0e10cSrcweir com.sun.star.container.XEnumeration xParaEnum = xParaEA.createEnumeration(); 234cdf0e10cSrcweir // Go through the paragraphs 235cdf0e10cSrcweir while( xParaEnum.hasMoreElements() ) 236cdf0e10cSrcweir { 237cdf0e10cSrcweir Object aPortionObj = xParaEnum.nextElement(); 238cdf0e10cSrcweir com.sun.star.container.XEnumerationAccess xPortionEA = 239cdf0e10cSrcweir (com.sun.star.container.XEnumerationAccess) UnoRuntime.queryInterface( 240cdf0e10cSrcweir com.sun.star.container.XEnumerationAccess.class, aPortionObj ); 241cdf0e10cSrcweir com.sun.star.container.XEnumeration xPortionEnum = xPortionEA.createEnumeration(); 242cdf0e10cSrcweir aText = ""; 243cdf0e10cSrcweir // Go through all text portions of a paragraph and construct string. 244cdf0e10cSrcweir Object nextElement; 245cdf0e10cSrcweir while( xPortionEnum.hasMoreElements() ) 246cdf0e10cSrcweir { 247cdf0e10cSrcweir com.sun.star.text.XTextRange xRange = (com.sun.star.text.XTextRange) 248cdf0e10cSrcweir UnoRuntime.queryInterface(com.sun.star.text.XTextRange.class, 249cdf0e10cSrcweir xPortionEnum.nextElement()); 250cdf0e10cSrcweir aText += xRange.getString(); 251cdf0e10cSrcweir } 252cdf0e10cSrcweir System.out.println( "Paragraph text: " + aText ); 253cdf0e10cSrcweir } 254cdf0e10cSrcweir 255cdf0e10cSrcweir 256cdf0e10cSrcweir // --- Change cell properties. --- 257cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 258cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell ); 259cdf0e10cSrcweir // from styles.CharacterProperties 260cdf0e10cSrcweir xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) ); 261cdf0e10cSrcweir xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) ); 262cdf0e10cSrcweir // from styles.ParagraphProperties 263cdf0e10cSrcweir xPropSet.setPropertyValue( "ParaLeftMargin", new Integer( 500 ) ); 264cdf0e10cSrcweir // from table.CellProperties 265cdf0e10cSrcweir xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) ); 266cdf0e10cSrcweir xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) ); 267cdf0e10cSrcweir 268cdf0e10cSrcweir 269cdf0e10cSrcweir // --- Get cell address. --- 270cdf0e10cSrcweir com.sun.star.sheet.XCellAddressable xCellAddr = (com.sun.star.sheet.XCellAddressable) 271cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, xCell ); 272cdf0e10cSrcweir com.sun.star.table.CellAddress aAddress = xCellAddr.getCellAddress(); 273cdf0e10cSrcweir aText = "Address of this cell: Column=" + aAddress.Column; 274cdf0e10cSrcweir aText += "; Row=" + aAddress.Row; 275cdf0e10cSrcweir aText += "; Sheet=" + aAddress.Sheet; 276cdf0e10cSrcweir System.out.println( aText ); 277cdf0e10cSrcweir 278cdf0e10cSrcweir 279cdf0e10cSrcweir // --- Insert an annotation --- 280cdf0e10cSrcweir com.sun.star.sheet.XSheetAnnotationsSupplier xAnnotationsSupp = 281cdf0e10cSrcweir (com.sun.star.sheet.XSheetAnnotationsSupplier) UnoRuntime.queryInterface( 282cdf0e10cSrcweir com.sun.star.sheet.XSheetAnnotationsSupplier.class, xSheet ); 283cdf0e10cSrcweir com.sun.star.sheet.XSheetAnnotations xAnnotations = xAnnotationsSupp.getAnnotations(); 284cdf0e10cSrcweir xAnnotations.insertNew( aAddress, "This is an annotation" ); 285cdf0e10cSrcweir 286cdf0e10cSrcweir com.sun.star.sheet.XSheetAnnotationAnchor xAnnotAnchor = (com.sun.star.sheet.XSheetAnnotationAnchor) 287cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetAnnotationAnchor.class, xCell ); 288cdf0e10cSrcweir com.sun.star.sheet.XSheetAnnotation xAnnotation = xAnnotAnchor.getAnnotation(); 289cdf0e10cSrcweir xAnnotation.setIsVisible( true ); 290cdf0e10cSrcweir } 291cdf0e10cSrcweir 292cdf0e10cSrcweir // ________________________________________________________________ 293cdf0e10cSrcweir 294cdf0e10cSrcweir /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */ doCellRangeSamples()295cdf0e10cSrcweir private void doCellRangeSamples() throws RuntimeException, Exception 296cdf0e10cSrcweir { 297cdf0e10cSrcweir System.out.println( "\n*** Samples for service sheet.SheetCellRange ***\n" ); 298cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 299cdf0e10cSrcweir com.sun.star.table.XCellRange xCellRange = null; 300cdf0e10cSrcweir com.sun.star.beans.XPropertySet xPropSet = null; 301cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aRangeAddress = null; 302cdf0e10cSrcweir String aText; 303cdf0e10cSrcweir 304cdf0e10cSrcweir // Preparation 305cdf0e10cSrcweir setFormula( xSheet, "B5", "First cell" ); 306cdf0e10cSrcweir setFormula( xSheet, "B6", "Second cell" ); 307cdf0e10cSrcweir // Get cell range B5:B6 by position - (column, row, column, row) 308cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByPosition( 1, 4, 1, 5 ); 309cdf0e10cSrcweir 310cdf0e10cSrcweir 311cdf0e10cSrcweir // --- Change cell range properties. --- 312cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 313cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 314cdf0e10cSrcweir // from com.sun.star.styles.CharacterProperties 315cdf0e10cSrcweir xPropSet.setPropertyValue( "CharColor", new Integer( 0x003399 ) ); 316cdf0e10cSrcweir xPropSet.setPropertyValue( "CharHeight", new Float( 20.0 ) ); 317cdf0e10cSrcweir // from com.sun.star.styles.ParagraphProperties 318cdf0e10cSrcweir xPropSet.setPropertyValue( "ParaLeftMargin", new Integer( 500 ) ); 319cdf0e10cSrcweir // from com.sun.star.table.CellProperties 320cdf0e10cSrcweir xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) ); 321cdf0e10cSrcweir xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x99CCFF ) ); 322cdf0e10cSrcweir 323cdf0e10cSrcweir 324cdf0e10cSrcweir // --- Replace text in all cells. --- 325cdf0e10cSrcweir com.sun.star.util.XReplaceable xReplace = (com.sun.star.util.XReplaceable) 326cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XReplaceable.class, xCellRange ); 327cdf0e10cSrcweir com.sun.star.util.XReplaceDescriptor xReplaceDesc = xReplace.createReplaceDescriptor(); 328cdf0e10cSrcweir xReplaceDesc.setSearchString( "cell" ); 329cdf0e10cSrcweir xReplaceDesc.setReplaceString( "text" ); 330cdf0e10cSrcweir // property SearchWords searches for whole cells! 331cdf0e10cSrcweir xReplaceDesc.setPropertyValue( "SearchWords", new Boolean( false ) ); 332cdf0e10cSrcweir int nCount = xReplace.replaceAll( xReplaceDesc ); 333cdf0e10cSrcweir System.out.println( "Search text replaced " + nCount + " times." ); 334cdf0e10cSrcweir 335cdf0e10cSrcweir 336cdf0e10cSrcweir // --- Merge cells. --- 337cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "F3:G6" ); 338cdf0e10cSrcweir prepareRange( xSheet, "E1:H7", "XMergeable" ); 339cdf0e10cSrcweir com.sun.star.util.XMergeable xMerge = (com.sun.star.util.XMergeable) 340cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XMergeable.class, xCellRange ); 341cdf0e10cSrcweir xMerge.merge( true ); 342cdf0e10cSrcweir 343cdf0e10cSrcweir 344cdf0e10cSrcweir // --- Change indentation. --- 345cdf0e10cSrcweir /* does not work (bug in XIndent implementation) 346cdf0e10cSrcweir prepareRange( xSheet, "I20:I23", "XIndent" ); 347cdf0e10cSrcweir setValue( xSheet, "I21", 1 ); 348cdf0e10cSrcweir setValue( xSheet, "I22", 1 ); 349cdf0e10cSrcweir setValue( xSheet, "I23", 1 ); 350cdf0e10cSrcweir 351cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "I21:I22" ); 352cdf0e10cSrcweir com.sun.star.util.XIndent xIndent = (com.sun.star.util.XIndent) 353cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange ); 354cdf0e10cSrcweir xIndent.incrementIndent(); 355cdf0e10cSrcweir 356cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "I22:I23" ); 357cdf0e10cSrcweir xIndent = (com.sun.star.util.XIndent) 358cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XIndent.class, xCellRange ); 359cdf0e10cSrcweir xIndent.incrementIndent(); 360cdf0e10cSrcweir */ 361cdf0e10cSrcweir 362cdf0e10cSrcweir 363cdf0e10cSrcweir // --- Column properties. --- 364cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "B1" ); 365cdf0e10cSrcweir com.sun.star.table.XColumnRowRange xColRowRange = (com.sun.star.table.XColumnRowRange) 366cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xCellRange ); 367cdf0e10cSrcweir com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns(); 368cdf0e10cSrcweir 369cdf0e10cSrcweir Object aColumnObj = xColumns.getByIndex( 0 ); 370cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( 371cdf0e10cSrcweir com.sun.star.beans.XPropertySet.class, aColumnObj ); 372cdf0e10cSrcweir xPropSet.setPropertyValue( "Width", new Integer( 6000 ) ); 373cdf0e10cSrcweir 374cdf0e10cSrcweir com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed) 375cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj ); 376cdf0e10cSrcweir System.out.println( "The name of the wide column is " + xNamed.getName() + "." ); 377cdf0e10cSrcweir 378cdf0e10cSrcweir 379cdf0e10cSrcweir // --- Cell range data --- 380cdf0e10cSrcweir prepareRange( xSheet, "A9:C30", "XCellRangeData" ); 381cdf0e10cSrcweir 382cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A10:C30" ); 383cdf0e10cSrcweir com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData) 384cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange ); 385cdf0e10cSrcweir Object[][] aValues = 386cdf0e10cSrcweir { 387cdf0e10cSrcweir { "Name", "Fruit", "Quantity" }, 388cdf0e10cSrcweir { "Alice", "Apples", new Double( 3.0 ) }, 389cdf0e10cSrcweir { "Alice", "Oranges", new Double( 7.0 ) }, 390cdf0e10cSrcweir { "Bob", "Apples", new Double( 3.0 ) }, 391cdf0e10cSrcweir { "Alice", "Apples", new Double( 9.0 ) }, 392cdf0e10cSrcweir { "Bob", "Apples", new Double( 5.0 ) }, 393cdf0e10cSrcweir { "Bob", "Oranges", new Double( 6.0 ) }, 394cdf0e10cSrcweir { "Alice", "Oranges", new Double( 3.0 ) }, 395cdf0e10cSrcweir { "Alice", "Apples", new Double( 8.0 ) }, 396cdf0e10cSrcweir { "Alice", "Oranges", new Double( 1.0 ) }, 397cdf0e10cSrcweir { "Bob", "Oranges", new Double( 2.0 ) }, 398cdf0e10cSrcweir { "Bob", "Oranges", new Double( 7.0 ) }, 399cdf0e10cSrcweir { "Bob", "Apples", new Double( 1.0 ) }, 400cdf0e10cSrcweir { "Alice", "Apples", new Double( 8.0 ) }, 401cdf0e10cSrcweir { "Alice", "Oranges", new Double( 8.0 ) }, 402cdf0e10cSrcweir { "Alice", "Apples", new Double( 7.0 ) }, 403cdf0e10cSrcweir { "Bob", "Apples", new Double( 1.0 ) }, 404cdf0e10cSrcweir { "Bob", "Oranges", new Double( 9.0 ) }, 405cdf0e10cSrcweir { "Bob", "Oranges", new Double( 3.0 ) }, 406cdf0e10cSrcweir { "Alice", "Oranges", new Double( 4.0 ) }, 407cdf0e10cSrcweir { "Alice", "Apples", new Double( 9.0 ) } 408cdf0e10cSrcweir }; 409cdf0e10cSrcweir xData.setDataArray( aValues ); 410cdf0e10cSrcweir 411cdf0e10cSrcweir 412cdf0e10cSrcweir // --- Get cell range address. --- 413cdf0e10cSrcweir com.sun.star.sheet.XCellRangeAddressable xRangeAddr = (com.sun.star.sheet.XCellRangeAddressable) 414cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); 415cdf0e10cSrcweir aRangeAddress = xRangeAddr.getRangeAddress(); 416cdf0e10cSrcweir System.out.println( "Address of this range: Sheet=" + aRangeAddress.Sheet ); 417cdf0e10cSrcweir System.out.println( "Start column=" + aRangeAddress.StartColumn + "; Start row=" + aRangeAddress.StartRow ); 418cdf0e10cSrcweir System.out.println( "End column =" + aRangeAddress.EndColumn + "; End row =" + aRangeAddress.EndRow ); 419cdf0e10cSrcweir 420cdf0e10cSrcweir 421cdf0e10cSrcweir // --- Sheet operation. --- 422cdf0e10cSrcweir // uses the range filled with XCellRangeData 423cdf0e10cSrcweir com.sun.star.sheet.XSheetOperation xSheetOp = (com.sun.star.sheet.XSheetOperation) 424cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetOperation.class, xData ); 425cdf0e10cSrcweir double fResult = xSheetOp.computeFunction( com.sun.star.sheet.GeneralFunction.AVERAGE ); 426cdf0e10cSrcweir System.out.println( "Average value of the data table A10:C30: " + fResult ); 427cdf0e10cSrcweir 428cdf0e10cSrcweir 429cdf0e10cSrcweir // --- Fill series --- 430cdf0e10cSrcweir // Prepare the example 431cdf0e10cSrcweir setValue( xSheet, "E10", 1 ); 432cdf0e10cSrcweir setValue( xSheet, "E11", 4 ); 433cdf0e10cSrcweir setDate( xSheet, "E12", 30, 1, 2002 ); 434cdf0e10cSrcweir setFormula( xSheet, "I13", "Text 10" ); 435cdf0e10cSrcweir setFormula( xSheet, "E14", "Jan" ); 436cdf0e10cSrcweir setValue( xSheet, "K14", 10 ); 437cdf0e10cSrcweir setValue( xSheet, "E16", 1 ); 438cdf0e10cSrcweir setValue( xSheet, "F16", 2 ); 439cdf0e10cSrcweir setDate( xSheet, "E17", 28, 2, 2002 ); 440cdf0e10cSrcweir setDate( xSheet, "F17", 28, 1, 2002 ); 441cdf0e10cSrcweir setValue( xSheet, "E18", 6 ); 442cdf0e10cSrcweir setValue( xSheet, "F18", 4 ); 443cdf0e10cSrcweir 444cdf0e10cSrcweir com.sun.star.sheet.XCellSeries xSeries = null; 445cdf0e10cSrcweir // Fill 2 rows linear with end value -> 2nd series is not filled completely 446cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "E10:I11" ); 447cdf0e10cSrcweir xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.LINEAR, 448cdf0e10cSrcweir com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9 ); 449cdf0e10cSrcweir // Add months to a date 450cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "E12:I12" ); 451cdf0e10cSrcweir xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.DATE, 452cdf0e10cSrcweir com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, 1, 0x7FFFFFFF ); 453cdf0e10cSrcweir // Fill right to left with a text containing a value 454cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "E13:I13" ); 455cdf0e10cSrcweir xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_LEFT, com.sun.star.sheet.FillMode.LINEAR, 456cdf0e10cSrcweir com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 10, 0x7FFFFFFF ); 457cdf0e10cSrcweir // Fill with an user defined list 458cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "E14:I14" ); 459cdf0e10cSrcweir xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_RIGHT, com.sun.star.sheet.FillMode.AUTO, 460cdf0e10cSrcweir com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 1, 0x7FFFFFFF ); 461cdf0e10cSrcweir // Fill bottom to top with a geometric series 462cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "K10:K14" ); 463cdf0e10cSrcweir xSeries.fillSeries( com.sun.star.sheet.FillDirection.TO_TOP, com.sun.star.sheet.FillMode.GROWTH, 464cdf0e10cSrcweir com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 0x7FFFFFFF ); 465cdf0e10cSrcweir // Auto fill 466cdf0e10cSrcweir xSeries = getCellSeries( xSheet, "E16:K18" ); 467cdf0e10cSrcweir xSeries.fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 2 ); 468cdf0e10cSrcweir // Fill series copies cell formats -> draw border here 469cdf0e10cSrcweir prepareRange( xSheet, "E9:K18", "XCellSeries" ); 470cdf0e10cSrcweir 471cdf0e10cSrcweir 472cdf0e10cSrcweir // --- Array formulas --- 473cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "E21:G23" ); 474cdf0e10cSrcweir prepareRange( xSheet, "E20:G23", "XArrayFormulaRange" ); 475cdf0e10cSrcweir com.sun.star.sheet.XArrayFormulaRange xArrayFormula = (com.sun.star.sheet.XArrayFormulaRange) 476cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCellRange ); 477cdf0e10cSrcweir // Insert a 3x3 unit matrix. 478cdf0e10cSrcweir xArrayFormula.setArrayFormula( "=A10:C12" ); 479cdf0e10cSrcweir System.out.println( "Array formula is: " + xArrayFormula.getArrayFormula() ); 480cdf0e10cSrcweir 481cdf0e10cSrcweir 482cdf0e10cSrcweir // --- Multiple operations --- 483cdf0e10cSrcweir setFormula( xSheet, "E26", "=E27^F26" ); 484cdf0e10cSrcweir setValue( xSheet, "E27", 1 ); 485cdf0e10cSrcweir setValue( xSheet, "F26", 1 ); 486cdf0e10cSrcweir getCellSeries( xSheet, "E27:E31" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 ); 487cdf0e10cSrcweir getCellSeries( xSheet, "F26:J26" ).fillAuto( com.sun.star.sheet.FillDirection.TO_RIGHT, 1 ); 488cdf0e10cSrcweir setFormula( xSheet, "F33", "=SIN(E33)" ); 489cdf0e10cSrcweir setFormula( xSheet, "G33", "=COS(E33)" ); 490cdf0e10cSrcweir setFormula( xSheet, "H33", "=TAN(E33)" ); 491cdf0e10cSrcweir setValue( xSheet, "E34", 0 ); 492cdf0e10cSrcweir setValue( xSheet, "E35", 0.2 ); 493cdf0e10cSrcweir getCellSeries( xSheet, "E34:E38" ).fillAuto( com.sun.star.sheet.FillDirection.TO_BOTTOM, 2 ); 494cdf0e10cSrcweir prepareRange( xSheet, "E25:J38", "XMultipleOperation" ); 495cdf0e10cSrcweir 496cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aFormulaRange = createCellRangeAddress( xSheet, "E26" ); 497cdf0e10cSrcweir com.sun.star.table.CellAddress aColCell = createCellAddress( xSheet, "E27" ); 498cdf0e10cSrcweir com.sun.star.table.CellAddress aRowCell = createCellAddress( xSheet, "F26" ); 499cdf0e10cSrcweir 500cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "E26:J31" ); 501cdf0e10cSrcweir com.sun.star.sheet.XMultipleOperation xMultOp = (com.sun.star.sheet.XMultipleOperation) 502cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange ); 503cdf0e10cSrcweir xMultOp.setTableOperation( 504cdf0e10cSrcweir aFormulaRange, com.sun.star.sheet.TableOperationMode.BOTH, aColCell, aRowCell ); 505cdf0e10cSrcweir 506cdf0e10cSrcweir aFormulaRange = createCellRangeAddress( xSheet, "F33:H33" ); 507cdf0e10cSrcweir aColCell = createCellAddress( xSheet, "E33" ); 508cdf0e10cSrcweir // Row cell not needed 509cdf0e10cSrcweir 510cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "E34:H38" ); 511cdf0e10cSrcweir xMultOp = (com.sun.star.sheet.XMultipleOperation) 512cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XMultipleOperation.class, xCellRange ); 513cdf0e10cSrcweir xMultOp.setTableOperation( 514cdf0e10cSrcweir aFormulaRange, com.sun.star.sheet.TableOperationMode.COLUMN, aColCell, aRowCell ); 515cdf0e10cSrcweir 516cdf0e10cSrcweir 517cdf0e10cSrcweir // --- Cell Ranges Query --- 518cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A10:C30" ); 519cdf0e10cSrcweir com.sun.star.sheet.XCellRangesQuery xRangesQuery = (com.sun.star.sheet.XCellRangesQuery) 520cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangesQuery.class, xCellRange ); 521cdf0e10cSrcweir com.sun.star.sheet.XSheetCellRanges xCellRanges = 522cdf0e10cSrcweir xRangesQuery.queryContentCells( (short)com.sun.star.sheet.CellFlags.STRING ); 523cdf0e10cSrcweir System.out.println( 524cdf0e10cSrcweir "Cells in A10:C30 containing text: " 525cdf0e10cSrcweir + xCellRanges.getRangeAddressesAsString() ); 526cdf0e10cSrcweir } 527cdf0e10cSrcweir 528cdf0e10cSrcweir /** Returns the XCellSeries interface of a cell range. 529cdf0e10cSrcweir @param xSheet The spreadsheet containing the cell range. 530cdf0e10cSrcweir @param aRange The address of the cell range. 531cdf0e10cSrcweir @return The XCellSeries interface. */ getCellSeries( com.sun.star.sheet.XSpreadsheet xSheet, String aRange )532cdf0e10cSrcweir private com.sun.star.sheet.XCellSeries getCellSeries( 533cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) 534cdf0e10cSrcweir { 535cdf0e10cSrcweir return (com.sun.star.sheet.XCellSeries) UnoRuntime.queryInterface( 536cdf0e10cSrcweir com.sun.star.sheet.XCellSeries.class, xSheet.getCellRangeByName( aRange ) ); 537cdf0e10cSrcweir } 538cdf0e10cSrcweir 539cdf0e10cSrcweir // ________________________________________________________________ 540cdf0e10cSrcweir 541cdf0e10cSrcweir /** All samples regarding cell range collections. */ doCellRangesSamples()542cdf0e10cSrcweir private void doCellRangesSamples() throws RuntimeException, Exception 543cdf0e10cSrcweir { 544cdf0e10cSrcweir System.out.println( "\n*** Samples for cell range collections ***\n" ); 545cdf0e10cSrcweir 546cdf0e10cSrcweir // Create a new cell range container 547cdf0e10cSrcweir com.sun.star.lang.XMultiServiceFactory xDocFactory = 548cdf0e10cSrcweir (com.sun.star.lang.XMultiServiceFactory) UnoRuntime.queryInterface( 549cdf0e10cSrcweir com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); 550cdf0e10cSrcweir com.sun.star.sheet.XSheetCellRangeContainer xRangeCont = 551cdf0e10cSrcweir (com.sun.star.sheet.XSheetCellRangeContainer) UnoRuntime.queryInterface( 552cdf0e10cSrcweir com.sun.star.sheet.XSheetCellRangeContainer.class, 553cdf0e10cSrcweir xDocFactory.createInstance( "com.sun.star.sheet.SheetCellRanges" ) ); 554cdf0e10cSrcweir 555cdf0e10cSrcweir 556cdf0e10cSrcweir // --- Insert ranges --- 557cdf0e10cSrcweir insertRange( xRangeCont, 0, 0, 0, 0, 0, false ); // A1:A1 558cdf0e10cSrcweir insertRange( xRangeCont, 0, 0, 1, 0, 2, true ); // A2:A3 559cdf0e10cSrcweir insertRange( xRangeCont, 0, 1, 0, 1, 2, false ); // B1:B3 560cdf0e10cSrcweir 561cdf0e10cSrcweir 562cdf0e10cSrcweir // --- Query the list of filled cells --- 563cdf0e10cSrcweir System.out.print( "All filled cells: " ); 564cdf0e10cSrcweir com.sun.star.container.XEnumerationAccess xCellsEA = xRangeCont.getCells(); 565cdf0e10cSrcweir com.sun.star.container.XEnumeration xEnum = xCellsEA.createEnumeration(); 566cdf0e10cSrcweir while( xEnum.hasMoreElements() ) 567cdf0e10cSrcweir { 568cdf0e10cSrcweir Object aCellObj = xEnum.nextElement(); 569cdf0e10cSrcweir com.sun.star.sheet.XCellAddressable xAddr = (com.sun.star.sheet.XCellAddressable) 570cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellAddressable.class, aCellObj ); 571cdf0e10cSrcweir com.sun.star.table.CellAddress aAddr = xAddr.getCellAddress(); 572cdf0e10cSrcweir System.out.print( getCellAddressString( aAddr.Column, aAddr.Row ) + " " ); 573cdf0e10cSrcweir } 574cdf0e10cSrcweir System.out.println(); 575cdf0e10cSrcweir } 576cdf0e10cSrcweir 577cdf0e10cSrcweir /** Inserts a cell range address into a cell range container and prints 578cdf0e10cSrcweir a message. 579cdf0e10cSrcweir @param xContainer The com.sun.star.sheet.XSheetCellRangeContainer interface of the container. 580cdf0e10cSrcweir @param nSheet Index of sheet of the range. 581cdf0e10cSrcweir @param nStartCol Index of first column of the range. 582cdf0e10cSrcweir @param nStartRow Index of first row of the range. 583cdf0e10cSrcweir @param nEndCol Index of last column of the range. 584cdf0e10cSrcweir @param nEndRow Index of last row of the range. 585cdf0e10cSrcweir @param bMerge Determines whether the new range should be merged with the existing ranges. */ insertRange( com.sun.star.sheet.XSheetCellRangeContainer xContainer, int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, boolean bMerge )586cdf0e10cSrcweir private void insertRange( 587cdf0e10cSrcweir com.sun.star.sheet.XSheetCellRangeContainer xContainer, 588cdf0e10cSrcweir int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, 589cdf0e10cSrcweir boolean bMerge ) throws RuntimeException, Exception 590cdf0e10cSrcweir { 591cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aAddress = new com.sun.star.table.CellRangeAddress(); 592cdf0e10cSrcweir aAddress.Sheet = (short)nSheet; 593cdf0e10cSrcweir aAddress.StartColumn = nStartCol; 594cdf0e10cSrcweir aAddress.StartRow = nStartRow; 595cdf0e10cSrcweir aAddress.EndColumn = nEndCol; 596cdf0e10cSrcweir aAddress.EndRow = nEndRow; 597cdf0e10cSrcweir xContainer.addRangeAddress( aAddress, bMerge ); 598cdf0e10cSrcweir System.out.println( 599cdf0e10cSrcweir "Inserting " + getCellRangeAddressString( aAddress ) 600cdf0e10cSrcweir + " " + (bMerge ? " with" : "without") + " merge," 601cdf0e10cSrcweir + " resulting list: " + xContainer.getRangeAddressesAsString() ); 602cdf0e10cSrcweir } 603cdf0e10cSrcweir 604cdf0e10cSrcweir // ________________________________________________________________ 605cdf0e10cSrcweir 606cdf0e10cSrcweir /** All samples regarding cell cursors. */ doCellCursorSamples()607cdf0e10cSrcweir private void doCellCursorSamples() throws RuntimeException, Exception 608cdf0e10cSrcweir { 609cdf0e10cSrcweir System.out.println( "\n*** Samples for cell cursor ***\n" ); 610cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 611cdf0e10cSrcweir 612cdf0e10cSrcweir 613cdf0e10cSrcweir // --- Find the array formula using a cell cursor --- 614cdf0e10cSrcweir com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "F22" ); 615cdf0e10cSrcweir com.sun.star.sheet.XSheetCellRange xCellRange = (com.sun.star.sheet.XSheetCellRange) 616cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xRange ); 617cdf0e10cSrcweir com.sun.star.sheet.XSheetCellCursor xCursor = xSheet.createCursorByRange( xCellRange ); 618cdf0e10cSrcweir 619cdf0e10cSrcweir xCursor.collapseToCurrentArray(); 620cdf0e10cSrcweir com.sun.star.sheet.XArrayFormulaRange xArray = (com.sun.star.sheet.XArrayFormulaRange) 621cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XArrayFormulaRange.class, xCursor ); 622cdf0e10cSrcweir System.out.println( 623cdf0e10cSrcweir "Array formula in " + getCellRangeAddressString( xCursor, false ) 624cdf0e10cSrcweir + " contains formula " + xArray.getArrayFormula() ); 625cdf0e10cSrcweir 626cdf0e10cSrcweir 627cdf0e10cSrcweir // --- Find the used area --- 628cdf0e10cSrcweir com.sun.star.sheet.XUsedAreaCursor xUsedCursor = (com.sun.star.sheet.XUsedAreaCursor) 629cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XUsedAreaCursor.class, xCursor ); 630cdf0e10cSrcweir xUsedCursor.gotoStartOfUsedArea( false ); 631cdf0e10cSrcweir xUsedCursor.gotoEndOfUsedArea( true ); 632cdf0e10cSrcweir // xUsedCursor and xCursor are interfaces of the same object - 633cdf0e10cSrcweir // so modifying xUsedCursor takes effect on xCursor: 634cdf0e10cSrcweir System.out.println( "The used area is: " + getCellRangeAddressString( xCursor, true ) ); 635cdf0e10cSrcweir } 636cdf0e10cSrcweir 637cdf0e10cSrcweir // ________________________________________________________________ 638cdf0e10cSrcweir 639cdf0e10cSrcweir /** All samples regarding the formatting of cells and ranges. */ doFormattingSamples()640cdf0e10cSrcweir private void doFormattingSamples() throws RuntimeException, Exception 641cdf0e10cSrcweir { 642cdf0e10cSrcweir System.out.println( "\n*** Formatting samples ***\n" ); 643cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 ); 644cdf0e10cSrcweir com.sun.star.table.XCellRange xCellRange; 645cdf0e10cSrcweir com.sun.star.beans.XPropertySet xPropSet = null; 646cdf0e10cSrcweir com.sun.star.container.XIndexAccess xRangeIA = null; 647cdf0e10cSrcweir com.sun.star.lang.XMultiServiceFactory xDocServiceManager; 648cdf0e10cSrcweir 649cdf0e10cSrcweir 650cdf0e10cSrcweir // --- Cell styles --- 651cdf0e10cSrcweir // get the cell style container 652cdf0e10cSrcweir com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier = (com.sun.star.style.XStyleFamiliesSupplier) 653cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.style.XStyleFamiliesSupplier.class, getDocument() ); 654cdf0e10cSrcweir com.sun.star.container.XNameAccess xFamiliesNA = xFamiliesSupplier.getStyleFamilies(); 655cdf0e10cSrcweir Object aCellStylesObj = xFamiliesNA.getByName( "CellStyles" ); 656cdf0e10cSrcweir com.sun.star.container.XNameContainer xCellStylesNA = (com.sun.star.container.XNameContainer) 657cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.container.XNameContainer.class, aCellStylesObj ); 658cdf0e10cSrcweir 659cdf0e10cSrcweir // create a new cell style 660cdf0e10cSrcweir xDocServiceManager = (com.sun.star.lang.XMultiServiceFactory) 661cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); 662cdf0e10cSrcweir Object aCellStyle = xDocServiceManager.createInstance( "com.sun.star.style.CellStyle" ); 663cdf0e10cSrcweir String aStyleName = "MyNewCellStyle"; 664cdf0e10cSrcweir xCellStylesNA.insertByName( aStyleName, aCellStyle ); 665cdf0e10cSrcweir 666cdf0e10cSrcweir // modify properties of the new style 667cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 668cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aCellStyle ); 669cdf0e10cSrcweir xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x888888 ) ); 670cdf0e10cSrcweir xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) ); 671cdf0e10cSrcweir 672cdf0e10cSrcweir 673cdf0e10cSrcweir 674cdf0e10cSrcweir // --- Query equal-formatted cell ranges --- 675cdf0e10cSrcweir // prepare example, use the new cell style 676cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "D2:F2" ); 677cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( 678cdf0e10cSrcweir com.sun.star.beans.XPropertySet.class, xCellRange ); 679cdf0e10cSrcweir xPropSet.setPropertyValue( "CellStyle", aStyleName ); 680cdf0e10cSrcweir 681cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A3:G3" ); 682cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) UnoRuntime.queryInterface( 683cdf0e10cSrcweir com.sun.star.beans.XPropertySet.class, xCellRange ); 684cdf0e10cSrcweir xPropSet.setPropertyValue( "CellStyle", aStyleName ); 685cdf0e10cSrcweir 686cdf0e10cSrcweir // All ranges in one container 687cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A1:G3" ); 688cdf0e10cSrcweir System.out.println( "Service CellFormatRanges:" ); 689cdf0e10cSrcweir com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp = 690cdf0e10cSrcweir (com.sun.star.sheet.XCellFormatRangesSupplier) UnoRuntime.queryInterface( 691cdf0e10cSrcweir com.sun.star.sheet.XCellFormatRangesSupplier.class, xCellRange ); 692cdf0e10cSrcweir xRangeIA = xFormatSupp.getCellFormatRanges(); 693cdf0e10cSrcweir System.out.println( getCellRangeListString( xRangeIA ) ); 694cdf0e10cSrcweir 695cdf0e10cSrcweir // Ranges sorted in SheetCellRanges containers 696cdf0e10cSrcweir System.out.println( "\nService UniqueCellFormatRanges:" ); 697cdf0e10cSrcweir com.sun.star.sheet.XUniqueCellFormatRangesSupplier xUniqueFormatSupp = 698cdf0e10cSrcweir (com.sun.star.sheet.XUniqueCellFormatRangesSupplier) UnoRuntime.queryInterface( 699cdf0e10cSrcweir com.sun.star.sheet.XUniqueCellFormatRangesSupplier.class, xCellRange ); 700cdf0e10cSrcweir com.sun.star.container.XIndexAccess xRangesIA = xUniqueFormatSupp.getUniqueCellFormatRanges(); 701cdf0e10cSrcweir int nCount = xRangesIA.getCount(); 702cdf0e10cSrcweir for (int nIndex = 0; nIndex < nCount; ++nIndex) 703cdf0e10cSrcweir { 704cdf0e10cSrcweir Object aRangesObj = xRangesIA.getByIndex( nIndex ); 705cdf0e10cSrcweir xRangeIA = (com.sun.star.container.XIndexAccess) UnoRuntime.queryInterface( 706cdf0e10cSrcweir com.sun.star.container.XIndexAccess.class, aRangesObj ); 707cdf0e10cSrcweir System.out.println( 708cdf0e10cSrcweir "Container " + (nIndex + 1) + ": " + getCellRangeListString( xRangeIA ) ); 709cdf0e10cSrcweir } 710cdf0e10cSrcweir 711cdf0e10cSrcweir 712cdf0e10cSrcweir // --- Table auto formats --- 713cdf0e10cSrcweir // get the global collection of table auto formats, use global service 714cdf0e10cSrcweir // manager 715cdf0e10cSrcweir com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); 716cdf0e10cSrcweir 717cdf0e10cSrcweir Object aAutoFormatsObj = xServiceManager.createInstanceWithContext( 718cdf0e10cSrcweir "com.sun.star.sheet.TableAutoFormats", getContext()); 719cdf0e10cSrcweir com.sun.star.container.XNameContainer xAutoFormatsNA = 720cdf0e10cSrcweir (com.sun.star.container.XNameContainer)UnoRuntime.queryInterface( 721cdf0e10cSrcweir com.sun.star.container.XNameContainer.class, aAutoFormatsObj ); 722cdf0e10cSrcweir 723cdf0e10cSrcweir // create a new table auto format and insert into the container 724cdf0e10cSrcweir String aAutoFormatName = "Temp_Example"; 725cdf0e10cSrcweir boolean bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName ); 726cdf0e10cSrcweir Object aAutoFormatObj = null; 727cdf0e10cSrcweir if (bExistsAlready) 728cdf0e10cSrcweir // auto format already exists -> use it 729cdf0e10cSrcweir aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName ); 730cdf0e10cSrcweir else 731cdf0e10cSrcweir { 732cdf0e10cSrcweir // create a new auto format (with document service manager!) 733cdf0e10cSrcweir // xDocServiceManager = (com.sun.star.lang.XMultiServiceFactory) 734cdf0e10cSrcweir // UnoRuntime.queryInterface( com.sun.star.lang.XMultiServiceFactory.class, getDocument() ); 735cdf0e10cSrcweir aAutoFormatObj = xDocServiceManager.createInstance( 736cdf0e10cSrcweir "com.sun.star.sheet.TableAutoFormat" ); 737cdf0e10cSrcweir xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj ); 738cdf0e10cSrcweir } 739cdf0e10cSrcweir // index access to the auto format fields 740cdf0e10cSrcweir com.sun.star.container.XIndexAccess xAutoFormatIA = 741cdf0e10cSrcweir (com.sun.star.container.XIndexAccess)UnoRuntime.queryInterface( 742cdf0e10cSrcweir com.sun.star.container.XIndexAccess.class, aAutoFormatObj ); 743cdf0e10cSrcweir 744cdf0e10cSrcweir // set properties of all auto format fields 745cdf0e10cSrcweir for (int nRow = 0; nRow < 4; ++nRow) 746cdf0e10cSrcweir { 747cdf0e10cSrcweir int nRowColor = 0; 748cdf0e10cSrcweir switch (nRow) 749cdf0e10cSrcweir { 750cdf0e10cSrcweir case 0: nRowColor = 0x999999; break; 751cdf0e10cSrcweir case 1: nRowColor = 0xFFFFCC; break; 752cdf0e10cSrcweir case 2: nRowColor = 0xEEEEEE; break; 753cdf0e10cSrcweir case 3: nRowColor = 0x999999; break; 754cdf0e10cSrcweir } 755cdf0e10cSrcweir 756cdf0e10cSrcweir for (int nColumn = 0; nColumn < 4; ++nColumn) 757cdf0e10cSrcweir { 758cdf0e10cSrcweir int nColor = nRowColor; 759cdf0e10cSrcweir if ((nColumn == 0) || (nColumn == 3)) 760cdf0e10cSrcweir nColor -= 0x333300; 761cdf0e10cSrcweir 762cdf0e10cSrcweir // get the auto format field and apply properties 763cdf0e10cSrcweir Object aFieldObj = xAutoFormatIA.getByIndex( 4 * nRow + nColumn ); 764cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 765cdf0e10cSrcweir UnoRuntime.queryInterface( 766cdf0e10cSrcweir com.sun.star.beans.XPropertySet.class, aFieldObj ); 767cdf0e10cSrcweir xPropSet.setPropertyValue( "CellBackColor", new Integer( nColor ) ); 768cdf0e10cSrcweir } 769cdf0e10cSrcweir } 770cdf0e10cSrcweir 771cdf0e10cSrcweir // set the auto format to the spreadsheet 772cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A5:H25" ); 773cdf0e10cSrcweir com.sun.star.table.XAutoFormattable xAutoForm = (com.sun.star.table.XAutoFormattable) 774cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.table.XAutoFormattable.class, xCellRange ); 775cdf0e10cSrcweir xAutoForm.autoFormat( aAutoFormatName ); 776cdf0e10cSrcweir 777cdf0e10cSrcweir // remove the auto format 778cdf0e10cSrcweir if (!bExistsAlready) 779cdf0e10cSrcweir xAutoFormatsNA.removeByName( aAutoFormatName ); 780cdf0e10cSrcweir 781cdf0e10cSrcweir 782cdf0e10cSrcweir // --- Conditional formats --- 783cdf0e10cSrcweir xSheet = getSpreadsheet( 0 ); 784cdf0e10cSrcweir prepareRange( xSheet, "K20:K23", "Cond. Format" ); 785cdf0e10cSrcweir setValue( xSheet, "K21", 1 ); 786cdf0e10cSrcweir setValue( xSheet, "K22", 2 ); 787cdf0e10cSrcweir setValue( xSheet, "K23", 3 ); 788cdf0e10cSrcweir 789cdf0e10cSrcweir // get the conditional format object of the cell range 790cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "K21:K23" ); 791cdf0e10cSrcweir xPropSet = (com.sun.star.beans.XPropertySet) 792cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 793cdf0e10cSrcweir com.sun.star.sheet.XSheetConditionalEntries xEntries = 794cdf0e10cSrcweir (com.sun.star.sheet.XSheetConditionalEntries) UnoRuntime.queryInterface( 795cdf0e10cSrcweir com.sun.star.sheet.XSheetConditionalEntries.class, 796cdf0e10cSrcweir xPropSet.getPropertyValue( "ConditionalFormat" )); 797cdf0e10cSrcweir 798cdf0e10cSrcweir // create a condition and apply it to the range 799cdf0e10cSrcweir com.sun.star.beans.PropertyValue[] aCondition = new com.sun.star.beans.PropertyValue[3]; 800cdf0e10cSrcweir aCondition[0] = new com.sun.star.beans.PropertyValue(); 801cdf0e10cSrcweir aCondition[0].Name = "Operator"; 802cdf0e10cSrcweir aCondition[0].Value = com.sun.star.sheet.ConditionOperator.GREATER; 803cdf0e10cSrcweir aCondition[1] = new com.sun.star.beans.PropertyValue(); 804cdf0e10cSrcweir aCondition[1].Name = "Formula1"; 805cdf0e10cSrcweir aCondition[1].Value = "1"; 806cdf0e10cSrcweir aCondition[2] = new com.sun.star.beans.PropertyValue(); 807cdf0e10cSrcweir aCondition[2].Name = "StyleName"; 808cdf0e10cSrcweir aCondition[2].Value = aStyleName; 809cdf0e10cSrcweir xEntries.addNew( aCondition ); 810cdf0e10cSrcweir xPropSet.setPropertyValue( "ConditionalFormat", xEntries ); 811cdf0e10cSrcweir } 812cdf0e10cSrcweir 813cdf0e10cSrcweir // ________________________________________________________________ 814cdf0e10cSrcweir 815cdf0e10cSrcweir /** All samples regarding the spreadsheet document. */ doDocumentSamples()816cdf0e10cSrcweir private void doDocumentSamples() throws RuntimeException, Exception 817cdf0e10cSrcweir { 818cdf0e10cSrcweir System.out.println( "\n*** Samples for spreadsheet document ***\n" ); 819cdf0e10cSrcweir 820cdf0e10cSrcweir 821cdf0e10cSrcweir // --- Insert a new spreadsheet --- 822cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = insertSpreadsheet( "A new sheet", (short)0x7FFF ); 823cdf0e10cSrcweir 824cdf0e10cSrcweir 825cdf0e10cSrcweir // --- Copy a cell range --- 826cdf0e10cSrcweir prepareRange( xSheet, "A1:B3", "Copy from" ); 827cdf0e10cSrcweir prepareRange( xSheet, "D1:E3", "To" ); 828cdf0e10cSrcweir setValue( xSheet, "A2", 123 ); 829cdf0e10cSrcweir setValue( xSheet, "B2", 345 ); 830cdf0e10cSrcweir setFormula( xSheet, "A3", "=SUM(A2:B2)" ); 831cdf0e10cSrcweir setFormula( xSheet, "B3", "=FORMULA(A3)" ); 832cdf0e10cSrcweir 833cdf0e10cSrcweir com.sun.star.sheet.XCellRangeMovement xMovement = (com.sun.star.sheet.XCellRangeMovement) 834cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeMovement.class, xSheet ); 835cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aSourceRange = createCellRangeAddress( xSheet, "A2:B3" ); 836cdf0e10cSrcweir com.sun.star.table.CellAddress aDestCell = createCellAddress( xSheet, "D2" ); 837cdf0e10cSrcweir xMovement.copyRange( aDestCell, aSourceRange ); 838cdf0e10cSrcweir 839cdf0e10cSrcweir 840cdf0e10cSrcweir // --- Print automatic column page breaks --- 841cdf0e10cSrcweir com.sun.star.sheet.XSheetPageBreak xPageBreak = (com.sun.star.sheet.XSheetPageBreak) 842cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetPageBreak.class, xSheet ); 843cdf0e10cSrcweir com.sun.star.sheet.TablePageBreakData[] aPageBreakArray = xPageBreak.getColumnPageBreaks(); 844cdf0e10cSrcweir 845cdf0e10cSrcweir System.out.print( "Automatic column page breaks:" ); 846cdf0e10cSrcweir for (int nIndex = 0; nIndex < aPageBreakArray.length; ++nIndex) 847cdf0e10cSrcweir if (!aPageBreakArray[nIndex].ManualBreak) 848cdf0e10cSrcweir System.out.print( " " + aPageBreakArray[nIndex].Position ); 849cdf0e10cSrcweir System.out.println(); 850cdf0e10cSrcweir 851cdf0e10cSrcweir 852cdf0e10cSrcweir // --- Document properties --- 853cdf0e10cSrcweir com.sun.star.beans.XPropertySet xPropSet = (com.sun.star.beans.XPropertySet) 854cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() ); 855cdf0e10cSrcweir 856cdf0e10cSrcweir AnyConverter aAnyConv = new AnyConverter(); 857cdf0e10cSrcweir String aText = "Value of property IsIterationEnabled: "; 858cdf0e10cSrcweir aText += aAnyConv.toBoolean(xPropSet.getPropertyValue( "IsIterationEnabled" )); 859cdf0e10cSrcweir System.out.println( aText ); 860cdf0e10cSrcweir aText = "Value of property IterationCount: "; 861cdf0e10cSrcweir aText += aAnyConv.toInt(xPropSet.getPropertyValue( "IterationCount" )); 862cdf0e10cSrcweir System.out.println( aText ); 863cdf0e10cSrcweir aText = "Value of property NullDate: "; 864cdf0e10cSrcweir com.sun.star.util.Date aDate = (com.sun.star.util.Date) 865cdf0e10cSrcweir aAnyConv.toObject(com.sun.star.util.Date.class, xPropSet.getPropertyValue( "NullDate" )); 866cdf0e10cSrcweir aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day; 867cdf0e10cSrcweir System.out.println( aText ); 868cdf0e10cSrcweir 869cdf0e10cSrcweir 870cdf0e10cSrcweir // --- Data validation --- 871cdf0e10cSrcweir prepareRange( xSheet, "A5:C7", "Validation" ); 872cdf0e10cSrcweir setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" ); 873cdf0e10cSrcweir 874cdf0e10cSrcweir com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( "A7:C7" ); 875cdf0e10cSrcweir com.sun.star.beans.XPropertySet xCellPropSet = (com.sun.star.beans.XPropertySet) 876cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange ); 877cdf0e10cSrcweir // validation properties 878cdf0e10cSrcweir com.sun.star.beans.XPropertySet xValidPropSet = (com.sun.star.beans.XPropertySet) 879cdf0e10cSrcweir UnoRuntime.queryInterface(com.sun.star.beans.XPropertySet.class, 880cdf0e10cSrcweir xCellPropSet.getPropertyValue( "Validation" )); 881cdf0e10cSrcweir xValidPropSet.setPropertyValue( "Type", com.sun.star.sheet.ValidationType.DECIMAL ); 882cdf0e10cSrcweir xValidPropSet.setPropertyValue( "ShowErrorMessage", new Boolean( true ) ); 883cdf0e10cSrcweir xValidPropSet.setPropertyValue( "ErrorMessage", "This is an invalid value!" ); 884cdf0e10cSrcweir xValidPropSet.setPropertyValue( "ErrorAlertStyle", com.sun.star.sheet.ValidationAlertStyle.STOP ); 885cdf0e10cSrcweir // condition 886cdf0e10cSrcweir com.sun.star.sheet.XSheetCondition xCondition = (com.sun.star.sheet.XSheetCondition) 887cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCondition.class, xValidPropSet ); 888cdf0e10cSrcweir xCondition.setOperator( com.sun.star.sheet.ConditionOperator.BETWEEN ); 889cdf0e10cSrcweir xCondition.setFormula1( "0.0" ); 890cdf0e10cSrcweir xCondition.setFormula2( "5.0" ); 891cdf0e10cSrcweir // apply on cell range 892cdf0e10cSrcweir xCellPropSet.setPropertyValue( "Validation", xValidPropSet ); 893cdf0e10cSrcweir 894cdf0e10cSrcweir // --- Scenarios --- 895cdf0e10cSrcweir Object[][] aValues = new Object[2][2]; 896cdf0e10cSrcweir 897cdf0e10cSrcweir aValues[0][0] = new Double( 11 ); 898cdf0e10cSrcweir aValues[0][1] = new Double( 12 ); 899cdf0e10cSrcweir aValues[1][0] = "Test13"; 900cdf0e10cSrcweir aValues[1][1] = "Test14"; 901cdf0e10cSrcweir insertScenario( xSheet, "B10:C11", aValues, "First Scenario", "The first scenario." ); 902cdf0e10cSrcweir 903cdf0e10cSrcweir aValues[0][0] = "Test21"; 904cdf0e10cSrcweir aValues[0][1] = "Test22"; 905cdf0e10cSrcweir aValues[1][0] = new Double( 23 ); 906cdf0e10cSrcweir aValues[1][1] = new Double( 24 ); 907cdf0e10cSrcweir insertScenario( xSheet, "B10:C11", aValues, "Second Scenario", "The visible scenario." ); 908cdf0e10cSrcweir 909cdf0e10cSrcweir aValues[0][0] = new Double( 31 ); 910cdf0e10cSrcweir aValues[0][1] = new Double( 32 ); 911cdf0e10cSrcweir aValues[1][0] = "Test33"; 912cdf0e10cSrcweir aValues[1][1] = "Test34"; 913cdf0e10cSrcweir insertScenario( xSheet, "B10:C11", aValues, "Third Scenario", "The last scenario." ); 914cdf0e10cSrcweir 915cdf0e10cSrcweir // show second scenario 916cdf0e10cSrcweir showScenario( xSheet, "Second Scenario" ); 917cdf0e10cSrcweir } 918cdf0e10cSrcweir 919cdf0e10cSrcweir /** Inserts a scenario containing one cell range into a sheet and 920cdf0e10cSrcweir applies the value array. 921cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 922cdf0e10cSrcweir @param aRange The range address for the scenario. 923cdf0e10cSrcweir @param aValueArray The array of cell contents. 924cdf0e10cSrcweir @param aScenarioName The name of the new scenario. 925cdf0e10cSrcweir @param aScenarioComment The user comment for the scenario. */ insertScenario( com.sun.star.sheet.XSpreadsheet xSheet, String aRange, Object[][] aValueArray, String aScenarioName, String aScenarioComment )926cdf0e10cSrcweir private void insertScenario( 927cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet, 928cdf0e10cSrcweir String aRange, 929cdf0e10cSrcweir Object[][] aValueArray, 930cdf0e10cSrcweir String aScenarioName, 931cdf0e10cSrcweir String aScenarioComment ) throws RuntimeException, Exception 932cdf0e10cSrcweir { 933cdf0e10cSrcweir // get the cell range with the given address 934cdf0e10cSrcweir com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName( aRange ); 935cdf0e10cSrcweir 936cdf0e10cSrcweir // create the range address sequence 937cdf0e10cSrcweir com.sun.star.sheet.XCellRangeAddressable xAddr = (com.sun.star.sheet.XCellRangeAddressable) 938cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeAddressable.class, xCellRange ); 939cdf0e10cSrcweir com.sun.star.table.CellRangeAddress[] aRangesSeq = new com.sun.star.table.CellRangeAddress[1]; 940cdf0e10cSrcweir aRangesSeq[0] = xAddr.getRangeAddress(); 941cdf0e10cSrcweir 942cdf0e10cSrcweir // create the scenario 943cdf0e10cSrcweir com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier) 944cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet ); 945cdf0e10cSrcweir com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios(); 946cdf0e10cSrcweir xScenarios.addNewByName( aScenarioName, aRangesSeq, aScenarioComment ); 947cdf0e10cSrcweir 948cdf0e10cSrcweir // insert the values into the range 949cdf0e10cSrcweir com.sun.star.sheet.XCellRangeData xData = (com.sun.star.sheet.XCellRangeData) 950cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCellRange ); 951cdf0e10cSrcweir xData.setDataArray( aValueArray ); 952cdf0e10cSrcweir } 953cdf0e10cSrcweir 954cdf0e10cSrcweir /** Activates a scenario. 955cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 956cdf0e10cSrcweir @param aScenarioName The name of the scenario. */ showScenario( com.sun.star.sheet.XSpreadsheet xSheet, String aScenarioName )957cdf0e10cSrcweir private void showScenario( 958cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet, 959cdf0e10cSrcweir String aScenarioName ) throws RuntimeException, Exception 960cdf0e10cSrcweir { 961cdf0e10cSrcweir // get the scenario set 962cdf0e10cSrcweir com.sun.star.sheet.XScenariosSupplier xScenSupp = (com.sun.star.sheet.XScenariosSupplier) 963cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XScenariosSupplier.class, xSheet ); 964cdf0e10cSrcweir com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios(); 965cdf0e10cSrcweir 966cdf0e10cSrcweir // get the scenario and activate it 967cdf0e10cSrcweir Object aScenarioObj = xScenarios.getByName( aScenarioName ); 968cdf0e10cSrcweir com.sun.star.sheet.XScenario xScenario = (com.sun.star.sheet.XScenario) 969cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XScenario.class, aScenarioObj ); 970cdf0e10cSrcweir xScenario.apply(); 971cdf0e10cSrcweir } 972cdf0e10cSrcweir 973cdf0e10cSrcweir // ________________________________________________________________ 974cdf0e10cSrcweir doNamedRangesSamples()975cdf0e10cSrcweir private void doNamedRangesSamples() throws RuntimeException, Exception 976cdf0e10cSrcweir { 977cdf0e10cSrcweir System.out.println( "\n*** Samples for named ranges ***\n" ); 978cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheetDocument xDocument = getDocument(); 979cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 980cdf0e10cSrcweir 981cdf0e10cSrcweir 982cdf0e10cSrcweir // --- Named ranges --- 983cdf0e10cSrcweir prepareRange( xSheet, "G42:H45", "Named ranges" ); 984cdf0e10cSrcweir xSheet.getCellByPosition( 6, 42 ).setValue( 1 ); 985cdf0e10cSrcweir xSheet.getCellByPosition( 6, 43 ).setValue( 2 ); 986cdf0e10cSrcweir xSheet.getCellByPosition( 7, 42 ).setValue( 3 ); 987cdf0e10cSrcweir xSheet.getCellByPosition( 7, 43 ).setValue( 4 ); 988cdf0e10cSrcweir 989cdf0e10cSrcweir // insert a named range 990cdf0e10cSrcweir com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet) 991cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xDocument ); 992cdf0e10cSrcweir Object aRangesObj = xDocProp.getPropertyValue( "NamedRanges" ); 993cdf0e10cSrcweir com.sun.star.sheet.XNamedRanges xNamedRanges = (com.sun.star.sheet.XNamedRanges) 994cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XNamedRanges.class, aRangesObj ); 995cdf0e10cSrcweir com.sun.star.table.CellAddress aRefPos = new com.sun.star.table.CellAddress(); 996cdf0e10cSrcweir aRefPos.Sheet = 0; 997cdf0e10cSrcweir aRefPos.Column = 6; 998cdf0e10cSrcweir aRefPos.Row = 44; 999cdf0e10cSrcweir xNamedRanges.addNewByName( "ExampleName", "SUM(G43:G44)", aRefPos, 0 ); 1000cdf0e10cSrcweir 1001cdf0e10cSrcweir // use the named range in formulas 1002cdf0e10cSrcweir xSheet.getCellByPosition( 6, 44 ).setFormula( "=ExampleName" ); 1003cdf0e10cSrcweir xSheet.getCellByPosition( 7, 44 ).setFormula( "=ExampleName" ); 1004cdf0e10cSrcweir 1005cdf0e10cSrcweir 1006cdf0e10cSrcweir // --- Label ranges --- 1007cdf0e10cSrcweir prepareRange( xSheet, "G47:I50", "Label ranges" ); 1008cdf0e10cSrcweir com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByPosition( 6, 47, 7, 49 ); 1009cdf0e10cSrcweir com.sun.star.sheet.XCellRangeData xData = ( com.sun.star.sheet.XCellRangeData ) 1010cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange ); 1011cdf0e10cSrcweir Object[][] aValues = 1012cdf0e10cSrcweir { 1013cdf0e10cSrcweir { "Apples", "Oranges" }, 1014cdf0e10cSrcweir { new Double( 5 ), new Double( 7 ) }, 1015cdf0e10cSrcweir { new Double( 6 ), new Double( 8 ) } 1016cdf0e10cSrcweir }; 1017cdf0e10cSrcweir xData.setDataArray( aValues ); 1018cdf0e10cSrcweir 1019cdf0e10cSrcweir // insert a column label range 1020cdf0e10cSrcweir Object aLabelsObj = xDocProp.getPropertyValue( "ColumnLabelRanges" ); 1021cdf0e10cSrcweir com.sun.star.sheet.XLabelRanges xLabelRanges = (com.sun.star.sheet.XLabelRanges) 1022cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XLabelRanges.class, aLabelsObj ); 1023cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aLabelArea = new com.sun.star.table.CellRangeAddress(); 1024cdf0e10cSrcweir aLabelArea.Sheet = 0; 1025cdf0e10cSrcweir aLabelArea.StartColumn = 6; 1026cdf0e10cSrcweir aLabelArea.StartRow = 47; 1027cdf0e10cSrcweir aLabelArea.EndColumn = 7; 1028cdf0e10cSrcweir aLabelArea.EndRow = 47; 1029cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aDataArea = new com.sun.star.table.CellRangeAddress(); 1030cdf0e10cSrcweir aDataArea.Sheet = 0; 1031cdf0e10cSrcweir aDataArea.StartColumn = 6; 1032cdf0e10cSrcweir aDataArea.StartRow = 48; 1033cdf0e10cSrcweir aDataArea.EndColumn = 7; 1034cdf0e10cSrcweir aDataArea.EndRow = 49; 1035cdf0e10cSrcweir xLabelRanges.addNew( aLabelArea, aDataArea ); 1036cdf0e10cSrcweir 1037cdf0e10cSrcweir // use the label range in formulas 1038cdf0e10cSrcweir xSheet.getCellByPosition( 8, 48 ).setFormula( "=Apples+Oranges" ); 1039cdf0e10cSrcweir xSheet.getCellByPosition( 8, 49 ).setFormula( "=Apples+Oranges" ); 1040cdf0e10cSrcweir } 1041cdf0e10cSrcweir 1042cdf0e10cSrcweir // ________________________________________________________________ 1043cdf0e10cSrcweir 1044cdf0e10cSrcweir /** Helper for doDatabaseSamples: get name of first database. */ getFirstDatabaseName()1045cdf0e10cSrcweir private String getFirstDatabaseName() 1046cdf0e10cSrcweir { 1047cdf0e10cSrcweir String aDatabase = null; 1048cdf0e10cSrcweir try 1049cdf0e10cSrcweir { 1050cdf0e10cSrcweir com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); 1051cdf0e10cSrcweir com.sun.star.container.XNameAccess xContext = 1052cdf0e10cSrcweir (com.sun.star.container.XNameAccess) UnoRuntime.queryInterface( 1053cdf0e10cSrcweir com.sun.star.container.XNameAccess.class, 1054cdf0e10cSrcweir xServiceManager.createInstanceWithContext( 1055cdf0e10cSrcweir "com.sun.star.sdb.DatabaseContext", getContext()) ); 1056cdf0e10cSrcweir String[] aNames = xContext.getElementNames(); 1057cdf0e10cSrcweir if ( aNames.length > 0 ) 1058cdf0e10cSrcweir aDatabase = aNames[0]; 1059cdf0e10cSrcweir } 1060cdf0e10cSrcweir catch ( Exception e ) 1061cdf0e10cSrcweir { 1062cdf0e10cSrcweir System.out.println( "\nError: caught exception in getFirstDatabaseName()!\n" + 1063cdf0e10cSrcweir "Exception Message = " 1064cdf0e10cSrcweir + e.getMessage()); 1065cdf0e10cSrcweir e.printStackTrace(); 1066cdf0e10cSrcweir } 1067cdf0e10cSrcweir return aDatabase; 1068cdf0e10cSrcweir } 1069cdf0e10cSrcweir 1070cdf0e10cSrcweir /** Helper for doDatabaseSamples: get name of first table in a database. */ getFirstTableName( String aDatabase )1071cdf0e10cSrcweir private String getFirstTableName( String aDatabase ) 1072cdf0e10cSrcweir { 1073cdf0e10cSrcweir if ( aDatabase == null ) 1074cdf0e10cSrcweir return null; 1075cdf0e10cSrcweir 1076cdf0e10cSrcweir String aTable = null; 1077cdf0e10cSrcweir try 1078cdf0e10cSrcweir { 1079cdf0e10cSrcweir com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); 1080cdf0e10cSrcweir com.sun.star.container.XNameAccess xContext = (com.sun.star.container.XNameAccess) 1081cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, 1082cdf0e10cSrcweir xServiceManager.createInstanceWithContext( 1083cdf0e10cSrcweir "com.sun.star.sdb.DatabaseContext", getContext()) ); 1084cdf0e10cSrcweir com.sun.star.sdb.XCompletedConnection xSource = 1085cdf0e10cSrcweir (com.sun.star.sdb.XCompletedConnection)UnoRuntime.queryInterface( 1086cdf0e10cSrcweir com.sun.star.sdb.XCompletedConnection.class, 1087cdf0e10cSrcweir xContext.getByName( aDatabase ) ); 1088cdf0e10cSrcweir com.sun.star.task.XInteractionHandler xHandler = 1089cdf0e10cSrcweir (com.sun.star.task.XInteractionHandler)UnoRuntime.queryInterface( 1090cdf0e10cSrcweir com.sun.star.task.XInteractionHandler.class, 1091cdf0e10cSrcweir xServiceManager.createInstanceWithContext( 1092cdf0e10cSrcweir "com.sun.star.task.InteractionHandler", getContext()) ); 1093cdf0e10cSrcweir com.sun.star.sdbcx.XTablesSupplier xSupplier = 1094cdf0e10cSrcweir (com.sun.star.sdbcx.XTablesSupplier)UnoRuntime.queryInterface( 1095cdf0e10cSrcweir com.sun.star.sdbcx.XTablesSupplier.class, 1096cdf0e10cSrcweir xSource.connectWithCompletion( xHandler ) ); 1097cdf0e10cSrcweir com.sun.star.container.XNameAccess xTables = xSupplier.getTables(); 1098cdf0e10cSrcweir String[] aNames = xTables.getElementNames(); 1099cdf0e10cSrcweir if ( aNames.length > 0 ) 1100cdf0e10cSrcweir aTable = aNames[0]; 1101cdf0e10cSrcweir } 1102cdf0e10cSrcweir catch ( Exception e ) 1103cdf0e10cSrcweir { 1104cdf0e10cSrcweir System.out.println( "\nError: caught exception in getFirstTableName()!\n" + 1105cdf0e10cSrcweir "Exception Message = " 1106cdf0e10cSrcweir + e.getMessage()); 1107cdf0e10cSrcweir e.printStackTrace(); 1108cdf0e10cSrcweir } 1109cdf0e10cSrcweir return aTable; 1110cdf0e10cSrcweir } 1111cdf0e10cSrcweir doDatabaseSamples()1112cdf0e10cSrcweir private void doDatabaseSamples() throws Exception 1113cdf0e10cSrcweir { 1114cdf0e10cSrcweir System.out.println( "\n*** Samples for database operations ***\n" ); 1115cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 2 ); 1116cdf0e10cSrcweir 1117cdf0e10cSrcweir 1118cdf0e10cSrcweir // --- put some example data into the sheet --- 1119cdf0e10cSrcweir com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByName( "B3:D24" ); 1120cdf0e10cSrcweir com.sun.star.sheet.XCellRangeData xData = ( com.sun.star.sheet.XCellRangeData ) 1121cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xRange ); 1122cdf0e10cSrcweir Object[][] aValues = 1123cdf0e10cSrcweir { 1124cdf0e10cSrcweir { "Name", "Year", "Sales" }, 1125cdf0e10cSrcweir { "Alice", new Double( 2001 ), new Double( 4.0 ) }, 1126cdf0e10cSrcweir { "Carol", new Double( 1997 ), new Double( 3.0 ) }, 1127cdf0e10cSrcweir { "Carol", new Double( 1998 ), new Double( 8.0 ) }, 1128cdf0e10cSrcweir { "Bob", new Double( 1997 ), new Double( 8.0 ) }, 1129cdf0e10cSrcweir { "Alice", new Double( 2002 ), new Double( 9.0 ) }, 1130cdf0e10cSrcweir { "Alice", new Double( 1999 ), new Double( 7.0 ) }, 1131cdf0e10cSrcweir { "Alice", new Double( 1996 ), new Double( 3.0 ) }, 1132cdf0e10cSrcweir { "Bob", new Double( 2000 ), new Double( 1.0 ) }, 1133cdf0e10cSrcweir { "Carol", new Double( 1999 ), new Double( 5.0 ) }, 1134cdf0e10cSrcweir { "Bob", new Double( 2002 ), new Double( 1.0 ) }, 1135cdf0e10cSrcweir { "Carol", new Double( 2001 ), new Double( 5.0 ) }, 1136cdf0e10cSrcweir { "Carol", new Double( 2000 ), new Double( 1.0 ) }, 1137cdf0e10cSrcweir { "Carol", new Double( 1996 ), new Double( 8.0 ) }, 1138cdf0e10cSrcweir { "Bob", new Double( 1996 ), new Double( 7.0 ) }, 1139cdf0e10cSrcweir { "Alice", new Double( 1997 ), new Double( 3.0 ) }, 1140cdf0e10cSrcweir { "Alice", new Double( 2000 ), new Double( 9.0 ) }, 1141cdf0e10cSrcweir { "Bob", new Double( 1998 ), new Double( 1.0 ) }, 1142cdf0e10cSrcweir { "Bob", new Double( 1999 ), new Double( 6.0 ) }, 1143cdf0e10cSrcweir { "Carol", new Double( 2002 ), new Double( 8.0 ) }, 1144cdf0e10cSrcweir { "Alice", new Double( 1998 ), new Double( 5.0 ) }, 1145cdf0e10cSrcweir { "Bob", new Double( 2001 ), new Double( 6.0 ) } 1146cdf0e10cSrcweir }; 1147cdf0e10cSrcweir xData.setDataArray( aValues ); 1148cdf0e10cSrcweir 1149cdf0e10cSrcweir 1150cdf0e10cSrcweir // --- filter for second column >= 1998 --- 1151cdf0e10cSrcweir com.sun.star.sheet.XSheetFilterable xFilter = ( com.sun.star.sheet.XSheetFilterable ) 1152cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterable.class, xRange ); 1153cdf0e10cSrcweir com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc = 1154cdf0e10cSrcweir xFilter.createFilterDescriptor( true ); 1155cdf0e10cSrcweir com.sun.star.sheet.TableFilterField[] aFilterFields = 1156cdf0e10cSrcweir new com.sun.star.sheet.TableFilterField[1]; 1157cdf0e10cSrcweir aFilterFields[0] = new com.sun.star.sheet.TableFilterField(); 1158cdf0e10cSrcweir aFilterFields[0].Field = 1; 1159cdf0e10cSrcweir aFilterFields[0].IsNumeric = true; 1160cdf0e10cSrcweir aFilterFields[0].Operator = com.sun.star.sheet.FilterOperator.GREATER_EQUAL; 1161cdf0e10cSrcweir aFilterFields[0].NumericValue = 1998; 1162cdf0e10cSrcweir xFilterDesc.setFilterFields( aFilterFields ); 1163cdf0e10cSrcweir com.sun.star.beans.XPropertySet xFilterProp = (com.sun.star.beans.XPropertySet) 1164cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xFilterDesc ); 1165cdf0e10cSrcweir xFilterProp.setPropertyValue( "ContainsHeader", new Boolean( true ) ); 1166cdf0e10cSrcweir xFilter.filter( xFilterDesc ); 1167cdf0e10cSrcweir 1168cdf0e10cSrcweir 1169cdf0e10cSrcweir // --- do the same filter as above, using criteria from a cell range --- 1170cdf0e10cSrcweir com.sun.star.table.XCellRange xCritRange = xSheet.getCellRangeByName( "B27:B28" ); 1171cdf0e10cSrcweir com.sun.star.sheet.XCellRangeData xCritData = ( com.sun.star.sheet.XCellRangeData ) 1172cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeData.class, xCritRange ); 1173cdf0e10cSrcweir Object[][] aCritValues = 1174cdf0e10cSrcweir { 1175cdf0e10cSrcweir { "Year" }, 1176cdf0e10cSrcweir { ">= 1998" } 1177cdf0e10cSrcweir }; 1178cdf0e10cSrcweir xCritData.setDataArray( aCritValues ); 1179cdf0e10cSrcweir com.sun.star.sheet.XSheetFilterableEx xCriteria = ( com.sun.star.sheet.XSheetFilterableEx ) 1180cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSheetFilterableEx.class, xCritRange ); 1181cdf0e10cSrcweir xFilterDesc = xCriteria.createFilterDescriptorByObject( xFilter ); 1182cdf0e10cSrcweir if ( xFilterDesc != null ) 1183cdf0e10cSrcweir xFilter.filter( xFilterDesc ); 1184cdf0e10cSrcweir 1185cdf0e10cSrcweir 1186cdf0e10cSrcweir // --- sort by second column, ascending --- 1187cdf0e10cSrcweir com.sun.star.table.TableSortField[] aSortFields = new com.sun.star.table.TableSortField[1]; 1188cdf0e10cSrcweir aSortFields[0] = new com.sun.star.table.TableSortField(); 1189cdf0e10cSrcweir aSortFields[0].Field = 1; 1190cdf0e10cSrcweir aSortFields[0].IsAscending = false; 1191cdf0e10cSrcweir aSortFields[0].IsCaseSensitive = false; 1192cdf0e10cSrcweir 1193cdf0e10cSrcweir 1194cdf0e10cSrcweir com.sun.star.beans.PropertyValue[] aSortDesc = new com.sun.star.beans.PropertyValue[2]; 1195cdf0e10cSrcweir aSortDesc[0] = new com.sun.star.beans.PropertyValue(); 1196cdf0e10cSrcweir aSortDesc[0].Name = "SortFields"; 1197cdf0e10cSrcweir aSortDesc[0].Value = aSortFields; 1198cdf0e10cSrcweir aSortDesc[1] = new com.sun.star.beans.PropertyValue(); 1199cdf0e10cSrcweir aSortDesc[1].Name = "ContainsHeader"; 1200cdf0e10cSrcweir aSortDesc[1].Value = new Boolean( true ); 1201cdf0e10cSrcweir 1202cdf0e10cSrcweir com.sun.star.util.XSortable xSort = ( com.sun.star.util.XSortable ) 1203cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XSortable.class, xRange ); 1204cdf0e10cSrcweir xSort.sort( aSortDesc ); 1205cdf0e10cSrcweir 1206cdf0e10cSrcweir 1207cdf0e10cSrcweir // --- insert subtotals --- 1208cdf0e10cSrcweir com.sun.star.sheet.XSubTotalCalculatable xSub = ( com.sun.star.sheet.XSubTotalCalculatable ) 1209cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XSubTotalCalculatable.class, xRange ); 1210cdf0e10cSrcweir com.sun.star.sheet.XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor( true ); 1211cdf0e10cSrcweir com.sun.star.sheet.SubTotalColumn[] aColumns = new com.sun.star.sheet.SubTotalColumn[1]; 1212cdf0e10cSrcweir // calculate sum of third column 1213cdf0e10cSrcweir aColumns[0] = new com.sun.star.sheet.SubTotalColumn(); 1214cdf0e10cSrcweir aColumns[0].Column = 2; 1215cdf0e10cSrcweir aColumns[0].Function = com.sun.star.sheet.GeneralFunction.SUM; 1216cdf0e10cSrcweir // group by first column 1217cdf0e10cSrcweir xSubDesc.addNew( aColumns, 0 ); 1218cdf0e10cSrcweir xSub.applySubTotals( xSubDesc, true ); 1219cdf0e10cSrcweir 1220cdf0e10cSrcweir String aDatabase = getFirstDatabaseName(); 1221cdf0e10cSrcweir String aTableName = getFirstTableName( aDatabase ); 1222cdf0e10cSrcweir if ( aDatabase != null && aTableName != null ) 1223cdf0e10cSrcweir { 1224cdf0e10cSrcweir // --- import from database --- 1225cdf0e10cSrcweir com.sun.star.beans.PropertyValue[] aImportDesc = new com.sun.star.beans.PropertyValue[3]; 1226cdf0e10cSrcweir aImportDesc[0] = new com.sun.star.beans.PropertyValue(); 1227cdf0e10cSrcweir aImportDesc[0].Name = "DatabaseName"; 1228cdf0e10cSrcweir aImportDesc[0].Value = aDatabase; 1229cdf0e10cSrcweir aImportDesc[1] = new com.sun.star.beans.PropertyValue(); 1230cdf0e10cSrcweir aImportDesc[1].Name = "SourceType"; 1231cdf0e10cSrcweir aImportDesc[1].Value = com.sun.star.sheet.DataImportMode.TABLE; 1232cdf0e10cSrcweir aImportDesc[2] = new com.sun.star.beans.PropertyValue(); 1233cdf0e10cSrcweir aImportDesc[2].Name = "SourceObject"; 1234cdf0e10cSrcweir aImportDesc[2].Value = aTableName; 1235cdf0e10cSrcweir 1236cdf0e10cSrcweir com.sun.star.table.XCellRange xImportRange = xSheet.getCellRangeByName( "B35:B35" ); 1237cdf0e10cSrcweir com.sun.star.util.XImportable xImport = ( com.sun.star.util.XImportable ) 1238cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.util.XImportable.class, xImportRange ); 1239cdf0e10cSrcweir xImport.doImport( aImportDesc ); 1240cdf0e10cSrcweir 1241cdf0e10cSrcweir 1242cdf0e10cSrcweir // --- use the temporary database range to find the imported data's size --- 1243cdf0e10cSrcweir com.sun.star.beans.XPropertySet xDocProp = (com.sun.star.beans.XPropertySet) 1244cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, getDocument() ); 1245cdf0e10cSrcweir Object aRangesObj = xDocProp.getPropertyValue( "DatabaseRanges" ); 1246cdf0e10cSrcweir com.sun.star.container.XNameAccess xRanges = 1247cdf0e10cSrcweir (com.sun.star.container.XNameAccess) UnoRuntime.queryInterface( 1248cdf0e10cSrcweir com.sun.star.container.XNameAccess.class, aRangesObj ); 1249cdf0e10cSrcweir String[] aNames = xRanges.getElementNames(); 1250cdf0e10cSrcweir AnyConverter aAnyConv = new AnyConverter(); 1251cdf0e10cSrcweir for ( int i=0; i<aNames.length; i++ ) 1252cdf0e10cSrcweir { 1253cdf0e10cSrcweir Object aRangeObj = xRanges.getByName( aNames[i] ); 1254cdf0e10cSrcweir com.sun.star.beans.XPropertySet xRangeProp = (com.sun.star.beans.XPropertySet) 1255cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRangeObj ); 1256cdf0e10cSrcweir boolean bUser = aAnyConv.toBoolean(xRangeProp.getPropertyValue( "IsUserDefined" )); 1257cdf0e10cSrcweir if ( !bUser ) 1258cdf0e10cSrcweir { 1259cdf0e10cSrcweir // this is the temporary database range - get the cell range and format it 1260cdf0e10cSrcweir com.sun.star.sheet.XCellRangeReferrer xRef = ( com.sun.star.sheet.XCellRangeReferrer ) 1261cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XCellRangeReferrer.class, aRangeObj ); 1262cdf0e10cSrcweir com.sun.star.table.XCellRange xResultRange = xRef.getReferredCells(); 1263cdf0e10cSrcweir com.sun.star.beans.XPropertySet xResultProp = (com.sun.star.beans.XPropertySet) 1264cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xResultRange ); 1265cdf0e10cSrcweir xResultProp.setPropertyValue( "IsCellBackgroundTransparent", new Boolean( false ) ); 1266cdf0e10cSrcweir xResultProp.setPropertyValue( "CellBackColor", new Integer( 0xFFFFCC ) ); 1267cdf0e10cSrcweir } 1268cdf0e10cSrcweir } 1269cdf0e10cSrcweir } 1270cdf0e10cSrcweir else 1271cdf0e10cSrcweir System.out.println("can't get database"); 1272cdf0e10cSrcweir } 1273cdf0e10cSrcweir 1274cdf0e10cSrcweir // ________________________________________________________________ 1275cdf0e10cSrcweir doDataPilotSamples()1276cdf0e10cSrcweir private void doDataPilotSamples() throws Exception 1277cdf0e10cSrcweir { 1278cdf0e10cSrcweir System.out.println( "\n*** Samples for Data Pilot ***\n" ); 1279cdf0e10cSrcweir com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 1280cdf0e10cSrcweir 1281cdf0e10cSrcweir 1282cdf0e10cSrcweir // --- Create a new DataPilot table --- 1283cdf0e10cSrcweir prepareRange( xSheet, "A38:C38", "Data Pilot" ); 1284cdf0e10cSrcweir com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = (com.sun.star.sheet.XDataPilotTablesSupplier) 1285cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotTablesSupplier.class, xSheet ); 1286cdf0e10cSrcweir com.sun.star.sheet.XDataPilotTables xDPTables = xDPSupp.getDataPilotTables(); 1287cdf0e10cSrcweir com.sun.star.sheet.XDataPilotDescriptor xDPDesc = xDPTables.createDataPilotDescriptor(); 1288cdf0e10cSrcweir // set source range (use data range from CellRange test) 1289cdf0e10cSrcweir com.sun.star.table.CellRangeAddress aSourceAddress = createCellRangeAddress( xSheet, "A10:C30" ); 1290cdf0e10cSrcweir xDPDesc.setSourceRange( aSourceAddress ); 1291cdf0e10cSrcweir // settings for fields 1292cdf0e10cSrcweir com.sun.star.container.XIndexAccess xFields = xDPDesc.getDataPilotFields(); 1293cdf0e10cSrcweir Object aFieldObj; 1294cdf0e10cSrcweir com.sun.star.beans.XPropertySet xFieldProp; 1295cdf0e10cSrcweir // use first column as column field 1296cdf0e10cSrcweir aFieldObj = xFields.getByIndex(0); 1297cdf0e10cSrcweir xFieldProp = (com.sun.star.beans.XPropertySet) 1298cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); 1299cdf0e10cSrcweir xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ); 1300cdf0e10cSrcweir // use second column as row field 1301cdf0e10cSrcweir aFieldObj = xFields.getByIndex(1); 1302cdf0e10cSrcweir xFieldProp = (com.sun.star.beans.XPropertySet) 1303cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); 1304cdf0e10cSrcweir xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.ROW ); 1305cdf0e10cSrcweir // use third column as data field, calculating the sum 1306cdf0e10cSrcweir aFieldObj = xFields.getByIndex(2); 1307cdf0e10cSrcweir xFieldProp = (com.sun.star.beans.XPropertySet) 1308cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); 1309cdf0e10cSrcweir xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA ); 1310cdf0e10cSrcweir xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.SUM ); 1311cdf0e10cSrcweir // select output position 1312cdf0e10cSrcweir com.sun.star.table.CellAddress aDestAddress = createCellAddress( xSheet, "A40" ); 1313cdf0e10cSrcweir xDPTables.insertNewByName( "DataPilotExample", aDestAddress, xDPDesc ); 1314cdf0e10cSrcweir 1315cdf0e10cSrcweir 1316cdf0e10cSrcweir // --- Modify the DataPilot table --- 1317cdf0e10cSrcweir Object aDPTableObj = xDPTables.getByName( "DataPilotExample" ); 1318cdf0e10cSrcweir xDPDesc = (com.sun.star.sheet.XDataPilotDescriptor) 1319cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.sheet.XDataPilotDescriptor.class, aDPTableObj ); 1320cdf0e10cSrcweir xFields = xDPDesc.getDataPilotFields(); 1321cdf0e10cSrcweir // add a second data field from the third column, calculating the average 1322cdf0e10cSrcweir aFieldObj = xFields.getByIndex(2); 1323cdf0e10cSrcweir xFieldProp = (com.sun.star.beans.XPropertySet) 1324cdf0e10cSrcweir UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aFieldObj ); 1325cdf0e10cSrcweir xFieldProp.setPropertyValue( "Orientation", com.sun.star.sheet.DataPilotFieldOrientation.DATA ); 1326cdf0e10cSrcweir xFieldProp.setPropertyValue( "Function", com.sun.star.sheet.GeneralFunction.AVERAGE ); 1327cdf0e10cSrcweir } 1328cdf0e10cSrcweir 1329cdf0e10cSrcweir // ________________________________________________________________ 1330cdf0e10cSrcweir doFunctionAccessSamples()1331cdf0e10cSrcweir private void doFunctionAccessSamples() throws RuntimeException, Exception 1332cdf0e10cSrcweir { 1333cdf0e10cSrcweir System.out.println( "\n*** Samples for function handling ***\n" ); 1334cdf0e10cSrcweir com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); 1335cdf0e10cSrcweir 1336cdf0e10cSrcweir 1337cdf0e10cSrcweir // --- Calculate a function --- 1338cdf0e10cSrcweir Object aFuncInst = xServiceManager.createInstanceWithContext( 1339cdf0e10cSrcweir "com.sun.star.sheet.FunctionAccess", getContext()); 1340cdf0e10cSrcweir com.sun.star.sheet.XFunctionAccess xFuncAcc = 1341cdf0e10cSrcweir (com.sun.star.sheet.XFunctionAccess)UnoRuntime.queryInterface( 1342cdf0e10cSrcweir com.sun.star.sheet.XFunctionAccess.class, aFuncInst ); 1343cdf0e10cSrcweir // put the data in a two-dimensional array 1344cdf0e10cSrcweir double[][] aData = { { 1.0, 2.0, 3.0 } }; 1345cdf0e10cSrcweir // construct the array of function arguments 1346cdf0e10cSrcweir Object[] aArgs = new Object[2]; 1347cdf0e10cSrcweir aArgs[0] = aData; 1348cdf0e10cSrcweir aArgs[1] = new Double( 2.0 ); 1349cdf0e10cSrcweir Object aResult = xFuncAcc.callFunction( "ZTEST", aArgs ); 1350cdf0e10cSrcweir System.out.println("ZTEST result for data {1,2,3} and value 2 is " 1351cdf0e10cSrcweir + ((Double)aResult).doubleValue() ); 1352cdf0e10cSrcweir 1353cdf0e10cSrcweir 1354cdf0e10cSrcweir // --- Get the list of recently used functions --- 1355cdf0e10cSrcweir Object aRecInst = xServiceManager.createInstanceWithContext( 1356cdf0e10cSrcweir "com.sun.star.sheet.RecentFunctions", getContext()); 1357cdf0e10cSrcweir com.sun.star.sheet.XRecentFunctions xRecFunc = 1358cdf0e10cSrcweir (com.sun.star.sheet.XRecentFunctions)UnoRuntime.queryInterface( 1359cdf0e10cSrcweir com.sun.star.sheet.XRecentFunctions.class, aRecInst ); 1360cdf0e10cSrcweir int[] nRecentIds = xRecFunc.getRecentFunctionIds(); 1361cdf0e10cSrcweir 1362cdf0e10cSrcweir 1363cdf0e10cSrcweir // --- Get the names for these functions --- 1364cdf0e10cSrcweir Object aDescInst = xServiceManager.createInstanceWithContext( 1365cdf0e10cSrcweir "com.sun.star.sheet.FunctionDescriptions", getContext()); 1366cdf0e10cSrcweir com.sun.star.sheet.XFunctionDescriptions xFuncDesc = 1367cdf0e10cSrcweir (com.sun.star.sheet.XFunctionDescriptions)UnoRuntime.queryInterface( 1368cdf0e10cSrcweir com.sun.star.sheet.XFunctionDescriptions.class, aDescInst ); 1369cdf0e10cSrcweir System.out.print("Recently used functions: "); 1370cdf0e10cSrcweir for (int nFunction=0; nFunction<nRecentIds.length; nFunction++) 1371cdf0e10cSrcweir { 1372cdf0e10cSrcweir com.sun.star.beans.PropertyValue[] aProperties = 1373cdf0e10cSrcweir xFuncDesc.getById( nRecentIds[nFunction] ); 1374cdf0e10cSrcweir for (int nProp=0; nProp<aProperties.length; nProp++) 1375cdf0e10cSrcweir if ( aProperties[nProp].Name.equals( "Name" ) ) 1376cdf0e10cSrcweir System.out.print( aProperties[nProp].Value + " " ); 1377cdf0e10cSrcweir } 1378cdf0e10cSrcweir System.out.println(); 1379cdf0e10cSrcweir } 1380cdf0e10cSrcweir 1381cdf0e10cSrcweir // ________________________________________________________________ 1382cdf0e10cSrcweir doApplicationSettingsSamples()1383cdf0e10cSrcweir private void doApplicationSettingsSamples() throws RuntimeException, Exception 1384cdf0e10cSrcweir { 1385cdf0e10cSrcweir System.out.println( "\n*** Samples for application settings ***\n" ); 1386cdf0e10cSrcweir com.sun.star.lang.XMultiComponentFactory xServiceManager = getServiceManager(); 1387cdf0e10cSrcweir 1388cdf0e10cSrcweir 1389cdf0e10cSrcweir // --- Get the user defined sort lists --- 1390cdf0e10cSrcweir Object aSettings = xServiceManager.createInstanceWithContext( 1391cdf0e10cSrcweir "com.sun.star.sheet.GlobalSheetSettings", getContext()); 1392cdf0e10cSrcweir com.sun.star.beans.XPropertySet xPropSet = 1393cdf0e10cSrcweir (com.sun.star.beans.XPropertySet)UnoRuntime.queryInterface( 1394cdf0e10cSrcweir com.sun.star.beans.XPropertySet.class, aSettings ); 1395cdf0e10cSrcweir AnyConverter aAnyConv = new AnyConverter(); 1396cdf0e10cSrcweir String[] aEntries = (String[]) 1397cdf0e10cSrcweir aAnyConv.toObject(String[].class, 1398cdf0e10cSrcweir xPropSet.getPropertyValue( "UserLists" )); 1399cdf0e10cSrcweir System.out.println("User defined sort lists:"); 1400cdf0e10cSrcweir for ( int i=0; i<aEntries.length; i++ ) 1401cdf0e10cSrcweir System.out.println( aEntries[i] ); 1402cdf0e10cSrcweir } 1403cdf0e10cSrcweir 1404cdf0e10cSrcweir // ________________________________________________________________ 1405cdf0e10cSrcweir 1406cdf0e10cSrcweir } 1407