Beispiel #1
0
        /** 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);
        }
Beispiel #2
0
        /** 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);
        }