/** Returns the text address of the cell range. * @param aCellRange The cell range address. * @return A string containing the cell range address. */ public String getCellRangeAddressString( unoidl.com.sun.star.table.CellRangeAddress aCellRange) { return (getCellAddressString(aCellRange.StartColumn, aCellRange.StartRow) + ":" + getCellAddressString(aCellRange.EndColumn, aCellRange.EndRow)); }
/** Draws a colored border around the range and writes the headline * in the first cell. * * @param xSheet The XSpreadsheet interface of the spreadsheet. * @param aRange The address of the cell range (or a named range). * @param aHeadline The headline text. */ public void prepareRange( unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange, String aHeadline) { unoidl.com.sun.star.beans.XPropertySet xPropSet = null; unoidl.com.sun.star.table.XCellRange xCellRange = null; // draw border xCellRange = xSheet.getCellRangeByName(aRange); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; unoidl.com.sun.star.table.BorderLine aLine = new unoidl.com.sun.star.table.BorderLine(); aLine.Color = 0x99CCFF; aLine.InnerLineWidth = aLine.LineDistance = 0; aLine.OuterLineWidth = 100; unoidl.com.sun.star.table.TableBorder aBorder = new unoidl.com.sun.star.table.TableBorder(); aBorder.TopLine = aBorder.BottomLine = aBorder.LeftLine = aBorder.RightLine = aLine; aBorder.IsTopLineValid = aBorder.IsBottomLineValid = true; aBorder.IsLeftLineValid = aBorder.IsRightLineValid = true; xPropSet.setPropertyValue( "TableBorder", new uno.Any( typeof(unoidl.com.sun.star.table.TableBorder), aBorder)); // draw headline unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = (unoidl.com.sun.star.sheet.XCellRangeAddressable)xCellRange; unoidl.com.sun.star.table.CellRangeAddress aAddr = xAddr.getRangeAddress(); xCellRange = xSheet.getCellRangeByPosition( aAddr.StartColumn, aAddr.StartRow, aAddr.EndColumn, aAddr.StartRow); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)0x99CCFF)); // write headline unoidl.com.sun.star.table.XCell xCell = xCellRange.getCellByPosition(0, 0); xCell.setFormula(aHeadline); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell; xPropSet.setPropertyValue( "CharColor", new uno.Any((Int32)0x003399)); xPropSet.setPropertyValue( "CharWeight", new uno.Any((Single)unoidl.com.sun.star.awt.FontWeight.BOLD)); }
/** Inserts a cell range address into a cell range container and prints * a message. * @param xContainer unoidl.com.sun.star.sheet.XSheetCellRangeContainer * interface of the container. * @param nSheet Index of sheet of the range. * @param nStartCol Index of first column of the range. * @param nStartRow Index of first row of the range. * @param nEndCol Index of last column of the range. * @param nEndRow Index of last row of the range. * @param bMerge Determines whether the new range should be merged * with the existing ranges. */ public void insertRange( unoidl.com.sun.star.sheet.XSheetCellRangeContainer xContainer, int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, bool bMerge) { unoidl.com.sun.star.table.CellRangeAddress aAddress = new unoidl.com.sun.star.table.CellRangeAddress(); aAddress.Sheet = (short)nSheet; aAddress.StartColumn = nStartCol; aAddress.StartRow = nStartRow; aAddress.EndColumn = nEndCol; aAddress.EndRow = nEndRow; xContainer.addRangeAddress(aAddress, bMerge); //Console.WriteLine( // "Inserting " + getCellRangeAddressString(aAddress) // + " " + (bMerge ? " with" : "without") + " merge," // + " resulting list: " + xContainer.getRangeAddressesAsString()); }
// ________________________________________________________________ /// This sample function modifies cells and cell ranges. public void doSampleFunction() { // for common usage unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); unoidl.com.sun.star.beans.XPropertySet xPropSet = null; unoidl.com.sun.star.table.XCell xCell = null; unoidl.com.sun.star.table.XCellRange xCellRange = null; // *** Access and modify a VALUE CELL *** Console.WriteLine( "*** Sample for service table.Cell ***" ); xCell = xSheet.getCellByPosition( 0, 0 ); // Set cell value. xCell.setValue( 1234 ); // Get cell value. double nDblValue = xCell.getValue() * 2; xSheet.getCellByPosition( 0, 1 ).setValue( nDblValue ); // *** Create a FORMULA CELL and query error type *** xCell = xSheet.getCellByPosition( 0, 2 ); // Set formula string. xCell.setFormula( "=1/0" ); // Get error type. bool bValid = (xCell.getError() == 0); // Get formula string. String aText = "The formula " + xCell.getFormula() + " is "; aText += bValid ? "valid." : "erroneous."; // *** Insert a TEXT CELL using the XText interface *** xCell = xSheet.getCellByPosition( 0, 3 ); unoidl.com.sun.star.text.XText xCellText = (unoidl.com.sun.star.text.XText) xCell; unoidl.com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor(); xCellText.insertString( xTextCursor, aText, false ); // *** Change cell properties *** int nValue = bValid ? 0x00FF00 : 0xFF4040; xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell; xPropSet.setPropertyValue( "CellBackColor", new uno.Any( (Int32) nValue ) ); // *** Accessing a CELL RANGE *** Console.WriteLine( "*** Sample for service table.CellRange ***" ); // Accessing a cell range over its position. xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 ); // Change properties of the range. xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any( (Int32) 0x8080FF ) ); // Accessing a cell range over its name. xCellRange = xSheet.getCellRangeByName( "C4:D5" ); // Change properties of the range. xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any( (Int32) 0xFFFF80 ) ); // *** Using the CELL CURSOR to add some data below of // the filled area *** Console.WriteLine( "*** Sample for service table.CellCursor ***" ); // Create a cursor using the XSpreadsheet method createCursorByRange() xCellRange = xSheet.getCellRangeByName( "A1" ); unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange = (unoidl.com.sun.star.sheet.XSheetCellRange) xCellRange; unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor = xSheet.createCursorByRange( xSheetCellRange ); unoidl.com.sun.star.table.XCellCursor xCursor = (unoidl.com.sun.star.table.XCellCursor) xSheetCellCursor; // Move to the last filled cell. xCursor.gotoEnd(); // Move one row down. xCursor.gotoOffset( 0, 1 ); xCursor.getCellByPosition( 0, 0 ).setFormula( "Beyond of the last filled cell." ); // *** Modifying COLUMNS and ROWS *** Console.WriteLine( "*** Sample for services table.TableRows and " + "table.TableColumns ***" ); unoidl.com.sun.star.table.XColumnRowRange xCRRange = (unoidl.com.sun.star.table.XColumnRowRange) xSheet; unoidl.com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns(); unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows(); // Get column C by index (interface XIndexAccess). uno.Any aColumnObj = xColumns.getByIndex( 2 ); xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value; xPropSet.setPropertyValue( "Width", new uno.Any( (Int32) 5000 ) ); // Get the name of the column. unoidl.com.sun.star.container.XNamed xNamed = (unoidl.com.sun.star.container.XNamed) aColumnObj.Value; aText = "The name of this column is " + xNamed.getName() + "."; xSheet.getCellByPosition( 2, 2 ).setFormula( aText ); // Get column D by name (interface XNameAccess). unoidl.com.sun.star.container.XNameAccess xColumnsName = (unoidl.com.sun.star.container.XNameAccess) xColumns; aColumnObj = xColumnsName.getByName( "D" ); xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value; xPropSet.setPropertyValue( "IsVisible", new uno.Any( (Boolean) false ) ); // Get row 7 by index (interface XIndexAccess) uno.Any aRowObj = xRows.getByIndex( 6 ); xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aRowObj.Value; xPropSet.setPropertyValue( "Height", new uno.Any( (Int32) 5000 ) ); xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." ); // Create a cell series with the values 1 ... 7. for (int nRow = 8; nRow < 15; ++nRow) xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 ); // Insert a row between 1 and 2 xRows.insertByIndex( 9, 1 ); // Delete the rows with the values 3 and 4. xRows.removeByIndex( 11, 2 ); // *** Inserting CHARTS *** Console.WriteLine( "*** Sample for service table.TableCharts ***" ); unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp = (unoidl.com.sun.star.table.XTableChartsSupplier) xSheet; unoidl.com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts(); // The chart will base on the last cell series, initializing all values. String aName = "newChart"; unoidl.com.sun.star.awt.Rectangle aRect = new unoidl.com.sun.star.awt.Rectangle(); aRect.X = 10000; aRect.Y = 3000; aRect.Width = aRect.Height = 5000; unoidl.com.sun.star.table.CellRangeAddress[] aRanges = new unoidl.com.sun.star.table.CellRangeAddress[1]; aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" ); // Create the chart. xCharts.addNewByName( aName, aRect, aRanges, false, false ); // Get the chart by name. uno.Any aChartObj = xCharts.getByName( aName ); unoidl.com.sun.star.table.XTableChart xChart = (unoidl.com.sun.star.table.XTableChart) aChartObj.Value; // Query the state of row and column headers. aText = "Chart has column headers: "; aText += xChart.getHasColumnHeaders() ? "yes" : "no"; xSheet.getCellByPosition( 2, 8 ).setFormula( aText ); aText = "Chart has row headers: "; aText += xChart.getHasRowHeaders() ? "yes" : "no"; xSheet.getCellByPosition( 2, 9 ).setFormula( aText ); }
// ________________________________________________________________ /// This sample function modifies cells and cell ranges. public void doSampleFunction() { // for common usage unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet(0); unoidl.com.sun.star.beans.XPropertySet xPropSet = null; unoidl.com.sun.star.table.XCell xCell = null; unoidl.com.sun.star.table.XCellRange xCellRange = null; // *** Access and modify a VALUE CELL *** Console.WriteLine("*** Sample for service table.Cell ***"); xCell = xSheet.getCellByPosition(0, 0); // Set cell value. xCell.setValue(1234); // Get cell value. double nDblValue = xCell.getValue() * 2; xSheet.getCellByPosition(0, 1).setValue(nDblValue); // *** Create a FORMULA CELL and query error type *** xCell = xSheet.getCellByPosition(0, 2); // Set formula string. xCell.setFormula("=1/0"); // Get error type. bool bValid = (xCell.getError() == 0); // Get formula string. String aText = "The formula " + xCell.getFormula() + " is "; aText += bValid ? "valid." : "erroneous."; // *** Insert a TEXT CELL using the XText interface *** xCell = xSheet.getCellByPosition(0, 3); unoidl.com.sun.star.text.XText xCellText = (unoidl.com.sun.star.text.XText)xCell; unoidl.com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor(); xCellText.insertString(xTextCursor, aText, false); // *** Change cell properties *** int nValue = bValid ? 0x00FF00 : 0xFF4040; xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCell; xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)nValue)); // *** Accessing a CELL RANGE *** Console.WriteLine("*** Sample for service table.CellRange ***"); // Accessing a cell range over its position. xCellRange = xSheet.getCellRangeByPosition(2, 0, 3, 1); // Change properties of the range. xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)0x8080FF)); // Accessing a cell range over its name. xCellRange = xSheet.getCellRangeByName("C4:D5"); // Change properties of the range. xPropSet = (unoidl.com.sun.star.beans.XPropertySet)xCellRange; xPropSet.setPropertyValue( "CellBackColor", new uno.Any((Int32)0xFFFF80)); // *** Using the CELL CURSOR to add some data below of // the filled area *** Console.WriteLine("*** Sample for service table.CellCursor ***"); // Create a cursor using the XSpreadsheet method createCursorByRange() xCellRange = xSheet.getCellRangeByName("A1"); unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange = (unoidl.com.sun.star.sheet.XSheetCellRange)xCellRange; unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor = xSheet.createCursorByRange(xSheetCellRange); unoidl.com.sun.star.table.XCellCursor xCursor = (unoidl.com.sun.star.table.XCellCursor)xSheetCellCursor; // Move to the last filled cell. xCursor.gotoEnd(); // Move one row down. xCursor.gotoOffset(0, 1); xCursor.getCellByPosition(0, 0).setFormula( "Beyond of the last filled cell."); // *** Modifying COLUMNS and ROWS *** Console.WriteLine("*** Sample for services table.TableRows and " + "table.TableColumns ***"); unoidl.com.sun.star.table.XColumnRowRange xCRRange = (unoidl.com.sun.star.table.XColumnRowRange)xSheet; unoidl.com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns(); unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows(); // Get column C by index (interface XIndexAccess). uno.Any aColumnObj = xColumns.getByIndex(2); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value; xPropSet.setPropertyValue("Width", new uno.Any((Int32)5000)); // Get the name of the column. unoidl.com.sun.star.container.XNamed xNamed = (unoidl.com.sun.star.container.XNamed)aColumnObj.Value; aText = "The name of this column is " + xNamed.getName() + "."; xSheet.getCellByPosition(2, 2).setFormula(aText); // Get column D by name (interface XNameAccess). unoidl.com.sun.star.container.XNameAccess xColumnsName = (unoidl.com.sun.star.container.XNameAccess)xColumns; aColumnObj = xColumnsName.getByName("D"); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aColumnObj.Value; xPropSet.setPropertyValue( "IsVisible", new uno.Any((Boolean)false)); // Get row 7 by index (interface XIndexAccess) uno.Any aRowObj = xRows.getByIndex(6); xPropSet = (unoidl.com.sun.star.beans.XPropertySet)aRowObj.Value; xPropSet.setPropertyValue("Height", new uno.Any((Int32)5000)); xSheet.getCellByPosition(2, 6).setFormula("What a big cell."); // Create a cell series with the values 1 ... 7. for (int nRow = 8; nRow < 15; ++nRow) { xSheet.getCellByPosition(0, nRow).setValue(nRow - 7); } // Insert a row between 1 and 2 xRows.insertByIndex(9, 1); // Delete the rows with the values 3 and 4. xRows.removeByIndex(11, 2); // *** Inserting CHARTS *** Console.WriteLine("*** Sample for service table.TableCharts ***"); unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp = (unoidl.com.sun.star.table.XTableChartsSupplier)xSheet; unoidl.com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts(); // The chart will base on the last cell series, initializing all values. String aName = "newChart"; unoidl.com.sun.star.awt.Rectangle aRect = new unoidl.com.sun.star.awt.Rectangle(); aRect.X = 10000; aRect.Y = 3000; aRect.Width = aRect.Height = 5000; unoidl.com.sun.star.table.CellRangeAddress[] aRanges = new unoidl.com.sun.star.table.CellRangeAddress[1]; aRanges[0] = createCellRangeAddress(xSheet, "A9:A14"); // Create the chart. xCharts.addNewByName(aName, aRect, aRanges, false, false); // Get the chart by name. uno.Any aChartObj = xCharts.getByName(aName); unoidl.com.sun.star.table.XTableChart xChart = (unoidl.com.sun.star.table.XTableChart)aChartObj.Value; // Query the state of row and column headers. aText = "Chart has column headers: "; aText += xChart.getHasColumnHeaders() ? "yes" : "no"; xSheet.getCellByPosition(2, 8).setFormula(aText); aText = "Chart has row headers: "; aText += xChart.getHasRowHeaders() ? "yes" : "no"; xSheet.getCellByPosition(2, 9).setFormula(aText); }
/** 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); }
/** 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. */ private 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 ); }
/** Inserts a cell range address into a cell range container and prints a message. @param xContainer unoidl.com.sun.star.sheet.XSheetCellRangeContainer interface of the container. @param nSheet Index of sheet of the range. @param nStartCol Index of first column of the range. @param nStartRow Index of first row of the range. @param nEndCol Index of last column of the range. @param nEndRow Index of last row of the range. @param bMerge Determines whether the new range should be merged with the existing ranges. */ private void insertRange( unoidl.com.sun.star.sheet.XSheetCellRangeContainer xContainer, int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow, bool bMerge ) { unoidl.com.sun.star.table.CellRangeAddress aAddress = new unoidl.com.sun.star.table.CellRangeAddress(); aAddress.Sheet = (short)nSheet; aAddress.StartColumn = nStartCol; aAddress.StartRow = nStartRow; aAddress.EndColumn = nEndCol; aAddress.EndRow = nEndRow; xContainer.addRangeAddress( aAddress, bMerge ); Console.WriteLine( "Inserting " + getCellRangeAddressString( aAddress ) + " " + (bMerge ? " with" : "without") + " merge," + " resulting list: " + xContainer.getRangeAddressesAsString() ); }
// ________________________________________________________________ private void doNamedRangesSamples() { Console.WriteLine( "\n*** Samples for named ranges ***\n" ); unoidl.com.sun.star.sheet.XSpreadsheetDocument xDocument = getDocument(); unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); // --- Named ranges --- prepareRange( xSheet, "G42:H45", "Named ranges" ); xSheet.getCellByPosition( 6, 42 ).setValue( 1 ); xSheet.getCellByPosition( 6, 43 ).setValue( 2 ); xSheet.getCellByPosition( 7, 42 ).setValue( 3 ); xSheet.getCellByPosition( 7, 43 ).setValue( 4 ); // insert a named range unoidl.com.sun.star.beans.XPropertySet xDocProp = (unoidl.com.sun.star.beans.XPropertySet) xDocument; uno.Any aRangesObj = xDocProp.getPropertyValue( "NamedRanges" ); unoidl.com.sun.star.sheet.XNamedRanges xNamedRanges = (unoidl.com.sun.star.sheet.XNamedRanges) aRangesObj.Value; unoidl.com.sun.star.table.CellAddress aRefPos = new unoidl.com.sun.star.table.CellAddress(); aRefPos.Sheet = 0; aRefPos.Column = 6; aRefPos.Row = 44; xNamedRanges.addNewByName( "ExampleName", "SUM(G43:G44)", aRefPos, 0 ); // use the named range in formulas xSheet.getCellByPosition( 6, 44 ).setFormula( "=ExampleName" ); xSheet.getCellByPosition( 7, 44 ).setFormula( "=ExampleName" ); // --- Label ranges --- prepareRange( xSheet, "G47:I50", "Label ranges" ); unoidl.com.sun.star.table.XCellRange xRange = xSheet.getCellRangeByPosition( 6, 47, 7, 49 ); unoidl.com.sun.star.sheet.XCellRangeData xData = ( unoidl.com.sun.star.sheet.XCellRangeData ) xRange; uno.Any [][] aValues = { new uno.Any [] { new uno.Any( "Apples" ), new uno.Any( "Oranges" ) }, new uno.Any [] { new uno.Any( (Double) 5 ), new uno.Any( (Double) 7 ) }, new uno.Any [] { new uno.Any( (Double) 6 ), new uno.Any( (Double) 8 ) } }; xData.setDataArray( aValues ); // insert a column label range uno.Any aLabelsObj = xDocProp.getPropertyValue( "ColumnLabelRanges" ); unoidl.com.sun.star.sheet.XLabelRanges xLabelRanges = (unoidl.com.sun.star.sheet.XLabelRanges) aLabelsObj.Value; unoidl.com.sun.star.table.CellRangeAddress aLabelArea = new unoidl.com.sun.star.table.CellRangeAddress(); aLabelArea.Sheet = 0; aLabelArea.StartColumn = 6; aLabelArea.StartRow = 47; aLabelArea.EndColumn = 7; aLabelArea.EndRow = 47; unoidl.com.sun.star.table.CellRangeAddress aDataArea = new unoidl.com.sun.star.table.CellRangeAddress(); aDataArea.Sheet = 0; aDataArea.StartColumn = 6; aDataArea.StartRow = 48; aDataArea.EndColumn = 7; aDataArea.EndRow = 49; xLabelRanges.addNew( aLabelArea, aDataArea ); // use the label range in formulas xSheet.getCellByPosition( 8, 48 ).setFormula( "=Apples+Oranges" ); xSheet.getCellByPosition( 8, 49 ).setFormula( "=Apples+Oranges" ); }