/** Inserts a scenario containing one cell range into a sheet and * applies the value array. * @param xSheet The XSpreadsheet interface of the spreadsheet. * @param aRange The range address for the scenario. * @param aValueArray The array of cell contents. * @param aScenarioName The name of the new scenario. * @param aScenarioComment The user comment for the scenario. */ public void insertScenario( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, uno.Any[][] aValueArray, String aScenarioName, String aScenarioComment) { // get the cell range with the given address unoidl.com.sun.star.table.XCellRange xCellRange = xSheet.getCellRangeByName(aRange); // create the range address sequence unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = (unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange; unoidl.com.sun.star.table.CellRangeAddress[] aRangesSeq = new unoidl.com.sun.star.table.CellRangeAddress[1]; aRangesSeq[0] = xAddr.getRangeAddress(); // create the scenario unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp = (unoidl.com.sun.star.sheet.XScenariosSupplier)xSheet; unoidl.com.sun.star.sheet.XScenarios xScenarios = xScenSupp.getScenarios(); xScenarios.addNewByName(aScenarioName, aRangesSeq, aScenarioComment); // insert the values into the range unoidl.com.sun.star.sheet.XCellRangeData xData = (unoidl.com.sun.star.sheet.XCellRangeData)xCellRange; xData.setDataArray(aValueArray); }
/** All samples regarding the service com.sun.star.sheet.SheetCellRange. */ public void doCellRangeSamples() { unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet(0); unoidl.com.sun.star.table.XCellRange xCellRange = null; unoidl.com.sun.star.beans.XPropertySet xPropSet = null; unoidl.com.sun.star.table.CellRangeAddress aRangeAddress = null; // Preparation setFormula(xSheet, "B5", "First cell"); setFormula(xSheet, "B6", "Second cell"); // Get cell range B5:B6 by position - (column, row, column, row) xCellRange = xSheet.getCellRangeByPosition(1, 4, 1, 5); // --- Change cell range properties. --- xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; // from com.sun.star.styles.CharacterProperties xPropSet.setPropertyValue( "CharColor", new uno.Any((Int32)0x003399)); xPropSet.setPropertyValue( "CharHeight", new uno.Any((Single)20.0)); // from com.sun.star.styles.ParagraphProperties xPropSet.setPropertyValue( "ParaLeftMargin", new uno.Any((Int32)500)); // from com.sun.star.table.CellProperties xPropSet.setPropertyValue( "IsCellBackgroundTransparent", new uno.Any(false)); xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)0x99CCFF)); // --- Replace text in all cells. --- unoidl.com.sun.star.util.XReplaceable xReplace = (unoidl.com.sun.star.util.XReplaceable)xCellRange; unoidl.com.sun.star.util.XReplaceDescriptor xReplaceDesc = xReplace.createReplaceDescriptor(); xReplaceDesc.setSearchString("cell"); xReplaceDesc.setReplaceString("text"); // property SearchWords searches for whole cells! xReplaceDesc.setPropertyValue("SearchWords", new uno.Any(false)); int nCount = xReplace.replaceAll(xReplaceDesc); // --- Merge cells. --- xCellRange = xSheet.getCellRangeByName("F3:G6"); prepareRange(xSheet, "E1:H7", "XMergeable"); unoidl.com.sun.star.util.XMergeable xMerge = (unoidl.com.sun.star.util.XMergeable)xCellRange; xMerge.merge(true); // --- Column properties. --- xCellRange = xSheet.getCellRangeByName("B1"); unoidl.com.sun.star.table.XColumnRowRange xColRowRange = (unoidl.com.sun.star.table.XColumnRowRange)xCellRange; unoidl.com.sun.star.table.XTableColumns xColumns = xColRowRange.getColumns(); uno.Any aColumnObj = xColumns.getByIndex(0); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value; xPropSet.setPropertyValue("Width", new uno.Any((Int32)6000)); unoidl.com.sun.star.container.XNamed xNamed = (unoidl.com.sun.star.container.XNamed)aColumnObj.Value; // --- Cell range data --- prepareRange(xSheet, "A9:C30", "XCellRangeData"); xCellRange = xSheet.getCellRangeByName("A10:C30"); unoidl.com.sun.star.sheet.XCellRangeData xData = (unoidl.com.sun.star.sheet.XCellRangeData)xCellRange; uno.Any [][] aValues = { new uno.Any [] { new uno.Any("Name"), new uno.Any("Fruit"), new uno.Any("Quantity") }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)3.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Oranges"), new uno.Any((Double)7.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Apples"), new uno.Any((Double)3.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)9.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Apples"), new uno.Any((Double)5.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Oranges"), new uno.Any((Double)6.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Oranges"), new uno.Any((Double)3.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)8.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Oranges"), new uno.Any((Double)1.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Oranges"), new uno.Any((Double)2.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Oranges"), new uno.Any((Double)7.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Apples"), new uno.Any((Double)1.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)8.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Oranges"), new uno.Any((Double)8.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)7.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Apples"), new uno.Any((Double)1.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Oranges"), new uno.Any((Double)9.0) }, new uno.Any [] { new uno.Any("Bob"), new uno.Any("Oranges"), new uno.Any((Double)3.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Oranges"), new uno.Any((Double)4.0) }, new uno.Any [] { new uno.Any("Alice"), new uno.Any("Apples"), new uno.Any((Double)9.0) } }; xData.setDataArray(aValues); // --- Get cell range address. --- unoidl.com.sun.star.sheet.XCellRangeAddressable xRangeAddr = (unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange; aRangeAddress = xRangeAddr.getRangeAddress(); // --- Sheet operation. --- // uses the range filled with XCellRangeData unoidl.com.sun.star.sheet.XSheetOperation xSheetOp = (unoidl.com.sun.star.sheet.XSheetOperation)xData; double fResult = xSheetOp.computeFunction( unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE); // --- Fill series --- // Prepare the example setValue(xSheet, "E10", 1); setValue(xSheet, "E11", 4); setDate(xSheet, "E12", 30, 1, 2002); setFormula(xSheet, "I13", "Text 10"); setFormula(xSheet, "E14", "Jan"); setValue(xSheet, "K14", 10); setValue(xSheet, "E16", 1); setValue(xSheet, "F16", 2); setDate(xSheet, "E17", 28, 2, 2002); setDate(xSheet, "F17", 28, 1, 2002); setValue(xSheet, "E18", 6); setValue(xSheet, "F18", 4); }