1*cdf0e10cSrcweir /************************************************************************* 2*cdf0e10cSrcweir * 3*cdf0e10cSrcweir * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER. 4*cdf0e10cSrcweir * 5*cdf0e10cSrcweir * Copyright 2000, 2010 Oracle and/or its affiliates. 6*cdf0e10cSrcweir * 7*cdf0e10cSrcweir * OpenOffice.org - a multi-platform office productivity suite 8*cdf0e10cSrcweir * 9*cdf0e10cSrcweir * This file is part of OpenOffice.org. 10*cdf0e10cSrcweir * 11*cdf0e10cSrcweir * OpenOffice.org is free software: you can redistribute it and/or modify 12*cdf0e10cSrcweir * it under the terms of the GNU Lesser General Public License version 3 13*cdf0e10cSrcweir * only, as published by the Free Software Foundation. 14*cdf0e10cSrcweir * 15*cdf0e10cSrcweir * OpenOffice.org is distributed in the hope that it will be useful, 16*cdf0e10cSrcweir * but WITHOUT ANY WARRANTY; without even the implied warranty of 17*cdf0e10cSrcweir * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 18*cdf0e10cSrcweir * GNU Lesser General Public License version 3 for more details 19*cdf0e10cSrcweir * (a copy is included in the LICENSE file that accompanied this code). 20*cdf0e10cSrcweir * 21*cdf0e10cSrcweir * You should have received a copy of the GNU Lesser General Public License 22*cdf0e10cSrcweir * version 3 along with OpenOffice.org. If not, see 23*cdf0e10cSrcweir * <http://www.openoffice.org/license.html> 24*cdf0e10cSrcweir * for a copy of the LGPLv3 License. 25*cdf0e10cSrcweir * 26*cdf0e10cSrcweir ************************************************************************/ 27*cdf0e10cSrcweir 28*cdf0e10cSrcweir using System; 29*cdf0e10cSrcweir using unoidl.com.sun.star.lang; 30*cdf0e10cSrcweir using unoidl.com.sun.star.uno; 31*cdf0e10cSrcweir using unoidl.com.sun.star.frame; 32*cdf0e10cSrcweir using unoidl.com.sun.star.util; 33*cdf0e10cSrcweir 34*cdf0e10cSrcweir namespace cliversion 35*cdf0e10cSrcweir { 36*cdf0e10cSrcweir public class Version 37*cdf0e10cSrcweir { 38*cdf0e10cSrcweir public Version() 39*cdf0e10cSrcweir { 40*cdf0e10cSrcweir try 41*cdf0e10cSrcweir { 42*cdf0e10cSrcweir // System.Diagnostics.Debugger.Launch(); 43*cdf0e10cSrcweir 44*cdf0e10cSrcweir //link with cli_ure.dll 45*cdf0e10cSrcweir uno.util.WeakBase wb = new uno.util.WeakBase(); 46*cdf0e10cSrcweir using ( SpreadsheetSample aSample = new SpreadsheetSample() ) 47*cdf0e10cSrcweir { 48*cdf0e10cSrcweir aSample.doCellRangeSamples(); 49*cdf0e10cSrcweir aSample.terminate(); 50*cdf0e10cSrcweir } 51*cdf0e10cSrcweir } 52*cdf0e10cSrcweir catch (System.Exception ) 53*cdf0e10cSrcweir { 54*cdf0e10cSrcweir //This exception is thrown if we link with a library which is not 55*cdf0e10cSrcweir //available 56*cdf0e10cSrcweir throw; 57*cdf0e10cSrcweir } 58*cdf0e10cSrcweir } 59*cdf0e10cSrcweir } 60*cdf0e10cSrcweir 61*cdf0e10cSrcweir class SpreadsheetSample: SpreadsheetDocHelper 62*cdf0e10cSrcweir { 63*cdf0e10cSrcweir public SpreadsheetSample() 64*cdf0e10cSrcweir { 65*cdf0e10cSrcweir } 66*cdf0e10cSrcweir /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */ 67*cdf0e10cSrcweir public void doCellRangeSamples() 68*cdf0e10cSrcweir { 69*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); 70*cdf0e10cSrcweir unoidl.com.sun.star.table.XCellRange xCellRange = null; 71*cdf0e10cSrcweir unoidl.com.sun.star.beans.XPropertySet xPropSet = null; 72*cdf0e10cSrcweir unoidl.com.sun.star.table.CellRangeAddress aRangeAddress = null; 73*cdf0e10cSrcweir 74*cdf0e10cSrcweir // Preparation 75*cdf0e10cSrcweir setFormula( xSheet, "B5", "First cell" ); 76*cdf0e10cSrcweir setFormula( xSheet, "B6", "Second cell" ); 77*cdf0e10cSrcweir // Get cell range B5:B6 by position - (column, row, column, row) 78*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByPosition( 1, 4, 1, 5 ); 79*cdf0e10cSrcweir 80*cdf0e10cSrcweir 81*cdf0e10cSrcweir // --- Change cell range properties. --- 82*cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 83*cdf0e10cSrcweir // from com.sun.star.styles.CharacterProperties 84*cdf0e10cSrcweir xPropSet.setPropertyValue( 85*cdf0e10cSrcweir "CharColor", new uno.Any( (Int32) 0x003399 ) ); 86*cdf0e10cSrcweir xPropSet.setPropertyValue( 87*cdf0e10cSrcweir "CharHeight", new uno.Any( (Single) 20.0 ) ); 88*cdf0e10cSrcweir // from com.sun.star.styles.ParagraphProperties 89*cdf0e10cSrcweir xPropSet.setPropertyValue( 90*cdf0e10cSrcweir "ParaLeftMargin", new uno.Any( (Int32) 500 ) ); 91*cdf0e10cSrcweir // from com.sun.star.table.CellProperties 92*cdf0e10cSrcweir xPropSet.setPropertyValue( 93*cdf0e10cSrcweir "IsCellBackgroundTransparent", new uno.Any( false ) ); 94*cdf0e10cSrcweir xPropSet.setPropertyValue( 95*cdf0e10cSrcweir "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) ); 96*cdf0e10cSrcweir 97*cdf0e10cSrcweir 98*cdf0e10cSrcweir // --- Replace text in all cells. --- 99*cdf0e10cSrcweir unoidl.com.sun.star.util.XReplaceable xReplace = 100*cdf0e10cSrcweir (unoidl.com.sun.star.util.XReplaceable) xCellRange; 101*cdf0e10cSrcweir unoidl.com.sun.star.util.XReplaceDescriptor xReplaceDesc = 102*cdf0e10cSrcweir xReplace.createReplaceDescriptor(); 103*cdf0e10cSrcweir xReplaceDesc.setSearchString( "cell" ); 104*cdf0e10cSrcweir xReplaceDesc.setReplaceString( "text" ); 105*cdf0e10cSrcweir // property SearchWords searches for whole cells! 106*cdf0e10cSrcweir xReplaceDesc.setPropertyValue( "SearchWords", new uno.Any( false ) ); 107*cdf0e10cSrcweir int nCount = xReplace.replaceAll( xReplaceDesc ); 108*cdf0e10cSrcweir 109*cdf0e10cSrcweir // --- Merge cells. --- 110*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "F3:G6" ); 111*cdf0e10cSrcweir prepareRange( xSheet, "E1:H7", "XMergeable" ); 112*cdf0e10cSrcweir unoidl.com.sun.star.util.XMergeable xMerge = 113*cdf0e10cSrcweir (unoidl.com.sun.star.util.XMergeable) xCellRange; 114*cdf0e10cSrcweir xMerge.merge( true ); 115*cdf0e10cSrcweir 116*cdf0e10cSrcweir 117*cdf0e10cSrcweir // --- Column properties. --- 118*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "B1" ); 119*cdf0e10cSrcweir unoidl.com.sun.star.table.XColumnRowRange xColRowRange = 120*cdf0e10cSrcweir (unoidl.com.sun.star.table.XColumnRowRange) xCellRange; 121*cdf0e10cSrcweir unoidl.com.sun.star.table.XTableColumns xColumns = 122*cdf0e10cSrcweir xColRowRange.getColumns(); 123*cdf0e10cSrcweir 124*cdf0e10cSrcweir uno.Any aColumnObj = xColumns.getByIndex( 0 ); 125*cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value; 126*cdf0e10cSrcweir xPropSet.setPropertyValue( "Width", new uno.Any( (Int32) 6000 ) ); 127*cdf0e10cSrcweir 128*cdf0e10cSrcweir unoidl.com.sun.star.container.XNamed xNamed = 129*cdf0e10cSrcweir (unoidl.com.sun.star.container.XNamed) aColumnObj.Value; 130*cdf0e10cSrcweir 131*cdf0e10cSrcweir // --- Cell range data --- 132*cdf0e10cSrcweir prepareRange( xSheet, "A9:C30", "XCellRangeData" ); 133*cdf0e10cSrcweir 134*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( "A10:C30" ); 135*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellRangeData xData = 136*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellRangeData) xCellRange; 137*cdf0e10cSrcweir uno.Any [][] aValues = 138*cdf0e10cSrcweir { 139*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Name" ), 140*cdf0e10cSrcweir new uno.Any( "Fruit" ), 141*cdf0e10cSrcweir new uno.Any( "Quantity" ) }, 142*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 143*cdf0e10cSrcweir new uno.Any( "Apples" ), 144*cdf0e10cSrcweir new uno.Any( (Double) 3.0 ) }, 145*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 146*cdf0e10cSrcweir new uno.Any( "Oranges" ), 147*cdf0e10cSrcweir new uno.Any( (Double) 7.0 ) }, 148*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 149*cdf0e10cSrcweir new uno.Any( "Apples" ), 150*cdf0e10cSrcweir new uno.Any( (Double) 3.0 ) }, 151*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 152*cdf0e10cSrcweir new uno.Any( "Apples" ), 153*cdf0e10cSrcweir new uno.Any( (Double) 9.0 ) }, 154*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 155*cdf0e10cSrcweir new uno.Any( "Apples" ), 156*cdf0e10cSrcweir new uno.Any( (Double) 5.0 ) }, 157*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 158*cdf0e10cSrcweir new uno.Any( "Oranges" ), 159*cdf0e10cSrcweir new uno.Any( (Double) 6.0 ) }, 160*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 161*cdf0e10cSrcweir new uno.Any( "Oranges" ), 162*cdf0e10cSrcweir new uno.Any( (Double) 3.0 ) }, 163*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 164*cdf0e10cSrcweir new uno.Any( "Apples" ), 165*cdf0e10cSrcweir new uno.Any( (Double) 8.0 ) }, 166*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 167*cdf0e10cSrcweir new uno.Any( "Oranges" ), 168*cdf0e10cSrcweir new uno.Any( (Double) 1.0 ) }, 169*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 170*cdf0e10cSrcweir new uno.Any( "Oranges" ), 171*cdf0e10cSrcweir new uno.Any( (Double) 2.0 ) }, 172*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 173*cdf0e10cSrcweir new uno.Any( "Oranges" ), 174*cdf0e10cSrcweir new uno.Any( (Double) 7.0 ) }, 175*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 176*cdf0e10cSrcweir new uno.Any( "Apples" ), 177*cdf0e10cSrcweir new uno.Any( (Double) 1.0 ) }, 178*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 179*cdf0e10cSrcweir new uno.Any( "Apples" ), 180*cdf0e10cSrcweir new uno.Any( (Double) 8.0 ) }, 181*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 182*cdf0e10cSrcweir new uno.Any( "Oranges" ), 183*cdf0e10cSrcweir new uno.Any( (Double) 8.0 ) }, 184*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 185*cdf0e10cSrcweir new uno.Any( "Apples" ), 186*cdf0e10cSrcweir new uno.Any( (Double) 7.0 ) }, 187*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 188*cdf0e10cSrcweir new uno.Any( "Apples" ), 189*cdf0e10cSrcweir new uno.Any( (Double) 1.0 ) }, 190*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 191*cdf0e10cSrcweir new uno.Any( "Oranges" ), 192*cdf0e10cSrcweir new uno.Any( (Double) 9.0 ) }, 193*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Bob" ), 194*cdf0e10cSrcweir new uno.Any( "Oranges" ), 195*cdf0e10cSrcweir new uno.Any( (Double) 3.0 ) }, 196*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 197*cdf0e10cSrcweir new uno.Any( "Oranges" ), 198*cdf0e10cSrcweir new uno.Any( (Double) 4.0 ) }, 199*cdf0e10cSrcweir new uno.Any [] { new uno.Any( "Alice" ), 200*cdf0e10cSrcweir new uno.Any( "Apples" ), 201*cdf0e10cSrcweir new uno.Any( (Double) 9.0 ) } 202*cdf0e10cSrcweir }; 203*cdf0e10cSrcweir xData.setDataArray( aValues ); 204*cdf0e10cSrcweir 205*cdf0e10cSrcweir 206*cdf0e10cSrcweir // --- Get cell range address. --- 207*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellRangeAddressable xRangeAddr = 208*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; 209*cdf0e10cSrcweir aRangeAddress = xRangeAddr.getRangeAddress(); 210*cdf0e10cSrcweir 211*cdf0e10cSrcweir // --- Sheet operation. --- 212*cdf0e10cSrcweir // uses the range filled with XCellRangeData 213*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSheetOperation xSheetOp = 214*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XSheetOperation) xData; 215*cdf0e10cSrcweir double fResult = xSheetOp.computeFunction( 216*cdf0e10cSrcweir unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE ); 217*cdf0e10cSrcweir 218*cdf0e10cSrcweir // --- Fill series --- 219*cdf0e10cSrcweir // Prepare the example 220*cdf0e10cSrcweir setValue( xSheet, "E10", 1 ); 221*cdf0e10cSrcweir setValue( xSheet, "E11", 4 ); 222*cdf0e10cSrcweir setDate( xSheet, "E12", 30, 1, 2002 ); 223*cdf0e10cSrcweir setFormula( xSheet, "I13", "Text 10" ); 224*cdf0e10cSrcweir setFormula( xSheet, "E14", "Jan" ); 225*cdf0e10cSrcweir setValue( xSheet, "K14", 10 ); 226*cdf0e10cSrcweir setValue( xSheet, "E16", 1 ); 227*cdf0e10cSrcweir setValue( xSheet, "F16", 2 ); 228*cdf0e10cSrcweir setDate( xSheet, "E17", 28, 2, 2002 ); 229*cdf0e10cSrcweir setDate( xSheet, "F17", 28, 1, 2002 ); 230*cdf0e10cSrcweir setValue( xSheet, "E18", 6 ); 231*cdf0e10cSrcweir setValue( xSheet, "F18", 4 ); 232*cdf0e10cSrcweir 233*cdf0e10cSrcweir } 234*cdf0e10cSrcweir 235*cdf0e10cSrcweir /** Returns the XCellSeries interface of a cell range. 236*cdf0e10cSrcweir @param xSheet The spreadsheet containing the cell range. 237*cdf0e10cSrcweir @param aRange The address of the cell range. 238*cdf0e10cSrcweir @return The XCellSeries interface. */ 239*cdf0e10cSrcweir private unoidl.com.sun.star.sheet.XCellSeries getCellSeries( 240*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) 241*cdf0e10cSrcweir { 242*cdf0e10cSrcweir return (unoidl.com.sun.star.sheet.XCellSeries) 243*cdf0e10cSrcweir xSheet.getCellRangeByName( aRange ); 244*cdf0e10cSrcweir } 245*cdf0e10cSrcweir 246*cdf0e10cSrcweir } 247*cdf0e10cSrcweir 248*cdf0e10cSrcweir /** This is a helper class for the spreadsheet and table samples. 249*cdf0e10cSrcweir It connects to a running office and creates a spreadsheet document. 250*cdf0e10cSrcweir Additionally it contains various helper functions. 251*cdf0e10cSrcweir */ 252*cdf0e10cSrcweir class SpreadsheetDocHelper : System.IDisposable 253*cdf0e10cSrcweir { 254*cdf0e10cSrcweir 255*cdf0e10cSrcweir // __ private members ___________________________________________ 256*cdf0e10cSrcweir 257*cdf0e10cSrcweir private const String msDataSheetName = "Data"; 258*cdf0e10cSrcweir 259*cdf0e10cSrcweir private unoidl.com.sun.star.uno.XComponentContext m_xContext; 260*cdf0e10cSrcweir private unoidl.com.sun.star.lang.XMultiServiceFactory mxMSFactory; 261*cdf0e10cSrcweir private unoidl.com.sun.star.sheet.XSpreadsheetDocument mxDocument; 262*cdf0e10cSrcweir 263*cdf0e10cSrcweir // ________________________________________________________________ 264*cdf0e10cSrcweir 265*cdf0e10cSrcweir public SpreadsheetDocHelper() 266*cdf0e10cSrcweir { 267*cdf0e10cSrcweir // System.Diagnostics.Debugger.Launch(); 268*cdf0e10cSrcweir // Connect to a running office and get the service manager 269*cdf0e10cSrcweir mxMSFactory = connect(); 270*cdf0e10cSrcweir // Create a new spreadsheet document 271*cdf0e10cSrcweir mxDocument = initDocument(); 272*cdf0e10cSrcweir } 273*cdf0e10cSrcweir 274*cdf0e10cSrcweir // __ helper methods ____________________________________________ 275*cdf0e10cSrcweir 276*cdf0e10cSrcweir /** Returns the service manager. 277*cdf0e10cSrcweir @return XMultiServiceFactory interface of the service manager. */ 278*cdf0e10cSrcweir public unoidl.com.sun.star.lang.XMultiServiceFactory getServiceManager() 279*cdf0e10cSrcweir { 280*cdf0e10cSrcweir return mxMSFactory; 281*cdf0e10cSrcweir } 282*cdf0e10cSrcweir 283*cdf0e10cSrcweir /** Returns the whole spreadsheet document. 284*cdf0e10cSrcweir @return XSpreadsheetDocument interface of the document. */ 285*cdf0e10cSrcweir public unoidl.com.sun.star.sheet.XSpreadsheetDocument getDocument() 286*cdf0e10cSrcweir { 287*cdf0e10cSrcweir return mxDocument; 288*cdf0e10cSrcweir } 289*cdf0e10cSrcweir 290*cdf0e10cSrcweir /** Returns the spreadsheet with the specified index (0-based). 291*cdf0e10cSrcweir @param nIndex The index of the sheet. 292*cdf0e10cSrcweir @return XSpreadsheet interface of the sheet. */ 293*cdf0e10cSrcweir public unoidl.com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) 294*cdf0e10cSrcweir { 295*cdf0e10cSrcweir // Collection of sheets 296*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheets xSheets = 297*cdf0e10cSrcweir mxDocument.getSheets(); 298*cdf0e10cSrcweir 299*cdf0e10cSrcweir unoidl.com.sun.star.container.XIndexAccess xSheetsIA = 300*cdf0e10cSrcweir (unoidl.com.sun.star.container.XIndexAccess) xSheets; 301*cdf0e10cSrcweir 302*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 303*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XSpreadsheet) 304*cdf0e10cSrcweir xSheetsIA.getByIndex( nIndex ).Value; 305*cdf0e10cSrcweir 306*cdf0e10cSrcweir return xSheet; 307*cdf0e10cSrcweir } 308*cdf0e10cSrcweir 309*cdf0e10cSrcweir /** Inserts a new empty spreadsheet with the specified name. 310*cdf0e10cSrcweir @param aName The name of the new sheet. 311*cdf0e10cSrcweir @param nIndex The insertion index. 312*cdf0e10cSrcweir @return The XSpreadsheet interface of the new sheet. */ 313*cdf0e10cSrcweir public unoidl.com.sun.star.sheet.XSpreadsheet insertSpreadsheet( 314*cdf0e10cSrcweir String aName, short nIndex ) 315*cdf0e10cSrcweir { 316*cdf0e10cSrcweir // Collection of sheets 317*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheets xSheets = 318*cdf0e10cSrcweir mxDocument.getSheets(); 319*cdf0e10cSrcweir 320*cdf0e10cSrcweir xSheets.insertNewByName( aName, nIndex ); 321*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 322*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XSpreadsheet) 323*cdf0e10cSrcweir xSheets.getByName( aName ).Value; 324*cdf0e10cSrcweir 325*cdf0e10cSrcweir return xSheet; 326*cdf0e10cSrcweir } 327*cdf0e10cSrcweir 328*cdf0e10cSrcweir // ________________________________________________________________ 329*cdf0e10cSrcweir // Methods to fill values into cells. 330*cdf0e10cSrcweir 331*cdf0e10cSrcweir /** Writes a double value into a spreadsheet. 332*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 333*cdf0e10cSrcweir @param aCellName The address of the cell (or a named range). 334*cdf0e10cSrcweir @param fValue The value to write into the cell. */ 335*cdf0e10cSrcweir public void setValue( 336*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 337*cdf0e10cSrcweir String aCellName, 338*cdf0e10cSrcweir double fValue ) 339*cdf0e10cSrcweir { 340*cdf0e10cSrcweir xSheet.getCellRangeByName( aCellName ).getCellByPosition( 341*cdf0e10cSrcweir 0, 0 ).setValue( fValue ); 342*cdf0e10cSrcweir } 343*cdf0e10cSrcweir 344*cdf0e10cSrcweir /** Writes a formula into a spreadsheet. 345*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 346*cdf0e10cSrcweir @param aCellName The address of the cell (or a named range). 347*cdf0e10cSrcweir @param aFormula The formula to write into the cell. */ 348*cdf0e10cSrcweir public void setFormula( 349*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 350*cdf0e10cSrcweir String aCellName, 351*cdf0e10cSrcweir String aFormula ) 352*cdf0e10cSrcweir { 353*cdf0e10cSrcweir xSheet.getCellRangeByName( aCellName ).getCellByPosition( 354*cdf0e10cSrcweir 0, 0 ).setFormula( aFormula ); 355*cdf0e10cSrcweir } 356*cdf0e10cSrcweir 357*cdf0e10cSrcweir /** Writes a date with standard date format into a spreadsheet. 358*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 359*cdf0e10cSrcweir @param aCellName The address of the cell (or a named range). 360*cdf0e10cSrcweir @param nDay The day of the date. 361*cdf0e10cSrcweir @param nMonth The month of the date. 362*cdf0e10cSrcweir @param nYear The year of the date. */ 363*cdf0e10cSrcweir public void setDate( 364*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 365*cdf0e10cSrcweir String aCellName, 366*cdf0e10cSrcweir int nDay, int nMonth, int nYear ) 367*cdf0e10cSrcweir { 368*cdf0e10cSrcweir // Set the date value. 369*cdf0e10cSrcweir unoidl.com.sun.star.table.XCell xCell = 370*cdf0e10cSrcweir xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); 371*cdf0e10cSrcweir String aDateStr = nMonth + "/" + nDay + "/" + nYear; 372*cdf0e10cSrcweir xCell.setFormula( aDateStr ); 373*cdf0e10cSrcweir 374*cdf0e10cSrcweir // Set standard date format. 375*cdf0e10cSrcweir unoidl.com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = 376*cdf0e10cSrcweir (unoidl.com.sun.star.util.XNumberFormatsSupplier) getDocument(); 377*cdf0e10cSrcweir unoidl.com.sun.star.util.XNumberFormatTypes xFormatTypes = 378*cdf0e10cSrcweir (unoidl.com.sun.star.util.XNumberFormatTypes) 379*cdf0e10cSrcweir xFormatsSupplier.getNumberFormats(); 380*cdf0e10cSrcweir int nFormat = xFormatTypes.getStandardFormat( 381*cdf0e10cSrcweir unoidl.com.sun.star.util.NumberFormat.DATE, 382*cdf0e10cSrcweir new unoidl.com.sun.star.lang.Locale() ); 383*cdf0e10cSrcweir 384*cdf0e10cSrcweir unoidl.com.sun.star.beans.XPropertySet xPropSet = 385*cdf0e10cSrcweir (unoidl.com.sun.star.beans.XPropertySet) xCell; 386*cdf0e10cSrcweir xPropSet.setPropertyValue( 387*cdf0e10cSrcweir "NumberFormat", 388*cdf0e10cSrcweir new uno.Any( (Int32) nFormat ) ); 389*cdf0e10cSrcweir } 390*cdf0e10cSrcweir 391*cdf0e10cSrcweir /** Draws a colored border around the range and writes the headline 392*cdf0e10cSrcweir in the first cell. 393*cdf0e10cSrcweir 394*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 395*cdf0e10cSrcweir @param aRange The address of the cell range (or a named range). 396*cdf0e10cSrcweir @param aHeadline The headline text. */ 397*cdf0e10cSrcweir public void prepareRange( 398*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 399*cdf0e10cSrcweir String aRange, String aHeadline ) 400*cdf0e10cSrcweir { 401*cdf0e10cSrcweir unoidl.com.sun.star.beans.XPropertySet xPropSet = null; 402*cdf0e10cSrcweir unoidl.com.sun.star.table.XCellRange xCellRange = null; 403*cdf0e10cSrcweir 404*cdf0e10cSrcweir // draw border 405*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByName( aRange ); 406*cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 407*cdf0e10cSrcweir unoidl.com.sun.star.table.BorderLine aLine = 408*cdf0e10cSrcweir new unoidl.com.sun.star.table.BorderLine(); 409*cdf0e10cSrcweir aLine.Color = 0x99CCFF; 410*cdf0e10cSrcweir aLine.InnerLineWidth = aLine.LineDistance = 0; 411*cdf0e10cSrcweir aLine.OuterLineWidth = 100; 412*cdf0e10cSrcweir unoidl.com.sun.star.table.TableBorder aBorder = 413*cdf0e10cSrcweir new unoidl.com.sun.star.table.TableBorder(); 414*cdf0e10cSrcweir aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = 415*cdf0e10cSrcweir aBorder.RightLine = aLine; 416*cdf0e10cSrcweir aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; 417*cdf0e10cSrcweir aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; 418*cdf0e10cSrcweir xPropSet.setPropertyValue( 419*cdf0e10cSrcweir "TableBorder", 420*cdf0e10cSrcweir new uno.Any( 421*cdf0e10cSrcweir typeof (unoidl.com.sun.star.table.TableBorder), aBorder ) ); 422*cdf0e10cSrcweir 423*cdf0e10cSrcweir // draw headline 424*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 425*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; 426*cdf0e10cSrcweir unoidl.com.sun.star.table.CellRangeAddress aAddr = 427*cdf0e10cSrcweir xAddr.getRangeAddress(); 428*cdf0e10cSrcweir 429*cdf0e10cSrcweir xCellRange = xSheet.getCellRangeByPosition( 430*cdf0e10cSrcweir aAddr.StartColumn, 431*cdf0e10cSrcweir aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow ); 432*cdf0e10cSrcweir 433*cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; 434*cdf0e10cSrcweir xPropSet.setPropertyValue( 435*cdf0e10cSrcweir "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) ); 436*cdf0e10cSrcweir // write headline 437*cdf0e10cSrcweir unoidl.com.sun.star.table.XCell xCell = 438*cdf0e10cSrcweir xCellRange.getCellByPosition( 0, 0 ); 439*cdf0e10cSrcweir xCell.setFormula( aHeadline ); 440*cdf0e10cSrcweir xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell; 441*cdf0e10cSrcweir xPropSet.setPropertyValue( 442*cdf0e10cSrcweir "CharColor", new uno.Any( (Int32) 0x003399 ) ); 443*cdf0e10cSrcweir xPropSet.setPropertyValue( 444*cdf0e10cSrcweir "CharWeight", 445*cdf0e10cSrcweir new uno.Any( (Single) unoidl.com.sun.star.awt.FontWeight.BOLD ) ); 446*cdf0e10cSrcweir } 447*cdf0e10cSrcweir 448*cdf0e10cSrcweir // ________________________________________________________________ 449*cdf0e10cSrcweir // Methods to create cell addresses and range addresses. 450*cdf0e10cSrcweir 451*cdf0e10cSrcweir /** Creates a unoidl.com.sun.star.table.CellAddress and initializes it 452*cdf0e10cSrcweir with the given range. 453*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 454*cdf0e10cSrcweir @param aCell The address of the cell (or a named cell). */ 455*cdf0e10cSrcweir public unoidl.com.sun.star.table.CellAddress createCellAddress( 456*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, 457*cdf0e10cSrcweir String aCell ) 458*cdf0e10cSrcweir { 459*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellAddressable xAddr = 460*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellAddressable) 461*cdf0e10cSrcweir xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ); 462*cdf0e10cSrcweir return xAddr.getCellAddress(); 463*cdf0e10cSrcweir } 464*cdf0e10cSrcweir 465*cdf0e10cSrcweir /** Creates a unoidl.com.sun.star.table.CellRangeAddress and initializes 466*cdf0e10cSrcweir it with the given range. 467*cdf0e10cSrcweir @param xSheet The XSpreadsheet interface of the spreadsheet. 468*cdf0e10cSrcweir @param aRange The address of the cell range (or a named range). */ 469*cdf0e10cSrcweir public unoidl.com.sun.star.table.CellRangeAddress createCellRangeAddress( 470*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) 471*cdf0e10cSrcweir { 472*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 473*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellRangeAddressable) 474*cdf0e10cSrcweir xSheet.getCellRangeByName( aRange ); 475*cdf0e10cSrcweir return xAddr.getRangeAddress(); 476*cdf0e10cSrcweir } 477*cdf0e10cSrcweir 478*cdf0e10cSrcweir // ________________________________________________________________ 479*cdf0e10cSrcweir // Methods to convert cell addresses and range addresses to strings. 480*cdf0e10cSrcweir 481*cdf0e10cSrcweir /** Returns the text address of the cell. 482*cdf0e10cSrcweir @param nColumn The column index. 483*cdf0e10cSrcweir @param nRow The row index. 484*cdf0e10cSrcweir @return A string containing the cell address. */ 485*cdf0e10cSrcweir public String getCellAddressString( int nColumn, int nRow ) 486*cdf0e10cSrcweir { 487*cdf0e10cSrcweir String aStr = ""; 488*cdf0e10cSrcweir if (nColumn > 25) 489*cdf0e10cSrcweir aStr += (char) ('A' + nColumn / 26 - 1); 490*cdf0e10cSrcweir aStr += (char) ('A' + nColumn % 26); 491*cdf0e10cSrcweir aStr += (nRow + 1); 492*cdf0e10cSrcweir return aStr; 493*cdf0e10cSrcweir } 494*cdf0e10cSrcweir 495*cdf0e10cSrcweir /** Returns the text address of the cell range. 496*cdf0e10cSrcweir @param aCellRange The cell range address. 497*cdf0e10cSrcweir @return A string containing the cell range address. */ 498*cdf0e10cSrcweir public String getCellRangeAddressString( 499*cdf0e10cSrcweir unoidl.com.sun.star.table.CellRangeAddress aCellRange ) 500*cdf0e10cSrcweir { 501*cdf0e10cSrcweir return 502*cdf0e10cSrcweir getCellAddressString( aCellRange.StartColumn, aCellRange.StartRow ) 503*cdf0e10cSrcweir + ":" 504*cdf0e10cSrcweir + getCellAddressString( aCellRange.EndColumn, aCellRange.EndRow ); 505*cdf0e10cSrcweir } 506*cdf0e10cSrcweir 507*cdf0e10cSrcweir /** Returns the text address of the cell range. 508*cdf0e10cSrcweir @param xCellRange The XSheetCellRange interface of the cell range. 509*cdf0e10cSrcweir @param bWithSheet true = Include sheet name. 510*cdf0e10cSrcweir @return A string containing the cell range address. */ 511*cdf0e10cSrcweir public String getCellRangeAddressString( 512*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSheetCellRange xCellRange, bool bWithSheet ) 513*cdf0e10cSrcweir { 514*cdf0e10cSrcweir String aStr = ""; 515*cdf0e10cSrcweir if (bWithSheet) 516*cdf0e10cSrcweir { 517*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSpreadsheet xSheet = 518*cdf0e10cSrcweir xCellRange.getSpreadsheet(); 519*cdf0e10cSrcweir unoidl.com.sun.star.container.XNamed xNamed = 520*cdf0e10cSrcweir (unoidl.com.sun.star.container.XNamed) xSheet; 521*cdf0e10cSrcweir aStr += xNamed.getName() + "."; 522*cdf0e10cSrcweir } 523*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = 524*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; 525*cdf0e10cSrcweir aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); 526*cdf0e10cSrcweir return aStr; 527*cdf0e10cSrcweir } 528*cdf0e10cSrcweir 529*cdf0e10cSrcweir /** Returns a list of addresses of all cell ranges contained in the 530*cdf0e10cSrcweir collection. 531*cdf0e10cSrcweir 532*cdf0e10cSrcweir @param xRangesIA The XIndexAccess interface of the collection. 533*cdf0e10cSrcweir @return A string containing the cell range address list. */ 534*cdf0e10cSrcweir public String getCellRangeListString( 535*cdf0e10cSrcweir unoidl.com.sun.star.container.XIndexAccess xRangesIA ) 536*cdf0e10cSrcweir { 537*cdf0e10cSrcweir String aStr = ""; 538*cdf0e10cSrcweir int nCount = xRangesIA.getCount(); 539*cdf0e10cSrcweir for (int nIndex = 0; nIndex < nCount; ++nIndex) 540*cdf0e10cSrcweir { 541*cdf0e10cSrcweir if (nIndex > 0) 542*cdf0e10cSrcweir aStr += " "; 543*cdf0e10cSrcweir uno.Any aRangeObj = xRangesIA.getByIndex( nIndex ); 544*cdf0e10cSrcweir unoidl.com.sun.star.sheet.XSheetCellRange xCellRange = 545*cdf0e10cSrcweir (unoidl.com.sun.star.sheet.XSheetCellRange) aRangeObj.Value; 546*cdf0e10cSrcweir aStr += getCellRangeAddressString( xCellRange, false ); 547*cdf0e10cSrcweir } 548*cdf0e10cSrcweir return aStr; 549*cdf0e10cSrcweir } 550*cdf0e10cSrcweir 551*cdf0e10cSrcweir // ________________________________________________________________ 552*cdf0e10cSrcweir 553*cdf0e10cSrcweir /** Connect to a running office that is accepting connections. 554*cdf0e10cSrcweir @return The ServiceManager to instantiate office components. */ 555*cdf0e10cSrcweir private XMultiServiceFactory connect() 556*cdf0e10cSrcweir { 557*cdf0e10cSrcweir 558*cdf0e10cSrcweir m_xContext = uno.util.Bootstrap.bootstrap(); 559*cdf0e10cSrcweir 560*cdf0e10cSrcweir return (XMultiServiceFactory) m_xContext.getServiceManager(); 561*cdf0e10cSrcweir } 562*cdf0e10cSrcweir 563*cdf0e10cSrcweir public void Dispose() 564*cdf0e10cSrcweir { 565*cdf0e10cSrcweir 566*cdf0e10cSrcweir } 567*cdf0e10cSrcweir 568*cdf0e10cSrcweir /** Creates an empty spreadsheet document. 569*cdf0e10cSrcweir @return The XSpreadsheetDocument interface of the document. */ 570*cdf0e10cSrcweir private unoidl.com.sun.star.sheet.XSpreadsheetDocument initDocument() 571*cdf0e10cSrcweir { 572*cdf0e10cSrcweir XComponentLoader aLoader = (XComponentLoader) 573*cdf0e10cSrcweir mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); 574*cdf0e10cSrcweir 575*cdf0e10cSrcweir XComponent xComponent = aLoader.loadComponentFromURL( 576*cdf0e10cSrcweir "private:factory/scalc", "_blank", 0, 577*cdf0e10cSrcweir new unoidl.com.sun.star.beans.PropertyValue[0] ); 578*cdf0e10cSrcweir 579*cdf0e10cSrcweir return (unoidl.com.sun.star.sheet.XSpreadsheetDocument) xComponent; 580*cdf0e10cSrcweir } 581*cdf0e10cSrcweir 582*cdf0e10cSrcweir 583*cdf0e10cSrcweir public void terminate() 584*cdf0e10cSrcweir { 585*cdf0e10cSrcweir XModifiable xMod = (XModifiable) mxDocument; 586*cdf0e10cSrcweir if (xMod != null) 587*cdf0e10cSrcweir xMod.setModified(false); 588*cdf0e10cSrcweir XDesktop aDesktop = (XDesktop) 589*cdf0e10cSrcweir mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); 590*cdf0e10cSrcweir if (aDesktop != null) 591*cdf0e10cSrcweir { 592*cdf0e10cSrcweir try 593*cdf0e10cSrcweir { 594*cdf0e10cSrcweir aDesktop.terminate(); 595*cdf0e10cSrcweir } 596*cdf0e10cSrcweir catch (DisposedException d) 597*cdf0e10cSrcweir { 598*cdf0e10cSrcweir //This exception may be thrown because shutting down OOo using 599*cdf0e10cSrcweir //XDesktop terminate does not really work. In the case of the 600*cdf0e10cSrcweir //Exception OOo will still terminate. 601*cdf0e10cSrcweir } 602*cdf0e10cSrcweir } 603*cdf0e10cSrcweir } 604*cdf0e10cSrcweir 605*cdf0e10cSrcweir // ________________________________________________________________ 606*cdf0e10cSrcweir } 607*cdf0e10cSrcweir 608*cdf0e10cSrcweir } 609