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