diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-07 09:06:44 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-07 09:06:44 +0000 |
| commit | ed5640d8b587fbcfed7dd7967f3de04b37a76f26 (patch) | |
| tree | 7a5f7c6c9d02226d7471cb3cc8fbbf631b415303 /odk/examples/CLI/CSharp | |
| parent | Initial commit. (diff) | |
| download | libreoffice-ed5640d8b587fbcfed7dd7967f3de04b37a76f26.tar.xz libreoffice-ed5640d8b587fbcfed7dd7967f3de04b37a76f26.zip | |
Adding upstream version 4:7.4.7.upstream/4%7.4.7upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'odk/examples/CLI/CSharp')
| -rw-r--r-- | odk/examples/CLI/CSharp/Spreadsheet/GeneralTableSample.cs | 222 | ||||
| -rw-r--r-- | odk/examples/CLI/CSharp/Spreadsheet/Makefile | 110 | ||||
| -rw-r--r-- | odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetDocHelper.cs | 361 | ||||
| -rw-r--r-- | odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetSample.cs | 1495 | ||||
| -rw-r--r-- | odk/examples/CLI/CSharp/Spreadsheet/ViewSample.cs | 182 |
5 files changed, 2370 insertions, 0 deletions
diff --git a/odk/examples/CLI/CSharp/Spreadsheet/GeneralTableSample.cs b/odk/examples/CLI/CSharp/Spreadsheet/GeneralTableSample.cs new file mode 100644 index 000000000..70942f737 --- /dev/null +++ b/odk/examples/CLI/CSharp/Spreadsheet/GeneralTableSample.cs @@ -0,0 +1,222 @@ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * This file incorporates work covered by the following license notice: + * + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed + * with this work for additional information regarding copyright + * ownership. The ASF licenses this file to you under the Apache + * License, Version 2.0 (the "License"); you may not use this file + * except in compliance with the License. You may obtain a copy of + * the License at http://www.apache.org/licenses/LICENSE-2.0 . + */ + +using System; + +// __________ implementation ____________________________________ + +/** Create a spreadsheet document and provide access to table contents. + */ +public class GeneralTableSample : SpreadsheetDocHelper +{ + + public static void Main( String [] args ) + { + try + { + using ( GeneralTableSample aSample = + new GeneralTableSample( args ) ) + { + aSample.doSampleFunction(); + } + Console.WriteLine( "Sample done." ); + } + catch (Exception ex) + { + Console.WriteLine( "Sample caught exception! " + ex ); + } + } + + + + public GeneralTableSample( String[] args ) : base( args ) + { + } + + + + /// 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 ); + } + +} diff --git a/odk/examples/CLI/CSharp/Spreadsheet/Makefile b/odk/examples/CLI/CSharp/Spreadsheet/Makefile new file mode 100644 index 000000000..6d03200e4 --- /dev/null +++ b/odk/examples/CLI/CSharp/Spreadsheet/Makefile @@ -0,0 +1,110 @@ +# +# This file is part of the LibreOffice project. +# +# This Source Code Form is subject to the terms of the Mozilla Public +# License, v. 2.0. If a copy of the MPL was not distributed with this +# file, You can obtain one at http://mozilla.org/MPL/2.0/. +# +# This file incorporates work covered by the following license notice: +# +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed +# with this work for additional information regarding copyright +# ownership. The ASF licenses this file to you under the Apache +# License, Version 2.0 (the "License"); you may not use this file +# except in compliance with the License. You may obtain a copy of +# the License at http://www.apache.org/licenses/LICENSE-2.0 . +# +# Builds the SpreadSheet examples of the Developers Guide. + +# This Makefile is supposed to be run by a GNU Make built for native +# Windows Not the Cygwin GNU Make, not dmake, not Microsoft's NMAKE. +# But yeah, that is what the setsdkenv_windows.bat file in +# ../../../.. presumably tries to set up. + +PRJ = ../../../.. +SETTINGS = $(PRJ)/settings + +include $(SETTINGS)/settings.mk +include $(SETTINGS)/std.mk + +CSC_FLAGS = -warnaserror+ -noconfig -platform:x86 +VBC_FLAGS = -warnaserror+ +ifeq "$(DEBUG)" "yes" +CC_FLAGS+=-Zi -MT +CSC_FLAGS += -debug+ -checked+ -define:DEBUG -define:TRACE +VBC_FLAGS += -debug+ -define:DEBUG=1 -define:TRACE=1 +else +CSC_FLAGS += -o +VBC_FLAGS += -o +endif + +THIS_OUT_MISC = $(OUT_MISC)/csharp_SpreadsheetExamples + +SPREADSHEET_OUT:= $(OUT_BIN)/CSharp_SpreadsheetExamples + +ifneq "$(OUTDIR)" "" +# We are running in a LO build environment, good +CLI_LIB_LOCATION = $(OUTDIR)\bin +else +# Bad guess, but hey, this is how it was +CLI_LIB_LOCATION = $(PRJ)\cli +endif + +# Targets +.PHONY : ALL +ALL : Info + +include $(SETTINGS)/stdtarget.mk + + +# build executables +#csc -lib:"d:\StarOffice 8" does not work. csc does not understand the quotes. +#but they are needed if the path contains a space. Therefore we use full path +#with the -reference switch +$(SPREADSHEET_OUT)/%.exe : %.cs SpreadsheetDocHelper.cs + -$(MKDIR) $(subst /,$(PS),$(@D)) + csc $(CSC_FLAGS) \ + -target:exe \ + -out:$@ \ + -reference:"$(CLI_LIB_LOCATION)"\cli_basetypes.dll \ + -reference:"$(CLI_LIB_LOCATION)"\cli_uretypes.dll \ + -reference:"$(CLI_LIB_LOCATION)"\cli_oootypes.dll \ + -reference:"$(CLI_LIB_LOCATION)"\cli_ure.dll \ + -reference:"$(CLI_LIB_LOCATION)"\cli_cppuhelper.dll \ + $^ + +SpreadsheetSample : $(SPREADSHEET_OUT)/SpreadsheetSample.exe + +GeneralTableSample : $(SPREADSHEET_OUT)/GeneralTableSample.exe + +ViewSample : $(SPREADSHEET_OUT)/ViewSample.exe + +.PHONY : Info +ifeq "$(OS)" "WIN" +Info : SpreadsheetSample GeneralTableSample ViewSample + @echo ------------------------------------------------------------------------------- + @echo Please use one of the following commands to execute the examples! + @echo - + @echo $(MAKE) GeneralTableSample.run + @echo $(MAKE) SpreadsheetSample.run + @echo $(MAKE) ViewSample.run + @echo ------------------------------------------------------------------------------- +else +Info : + @echo -------------------------------------------------------------------------------- + @echo This example works only under Windows! + @echo -------------------------------------------------------------------------------- +endif + +%.run : $(SPREADSHEET_OUT)/%.exe + @echo ------------------------------------------------------------------------------- + @echo Executing sample $@ $(UNORC) $(CONNECT_STRING)... + @echo ------------------------------------------------------------------------------- + cd $(subst /,\\,$(SPREADSHEET_OUT)) && $(basename $@).exe + +.PHONY: clean +clean : + -$(DELRECURSIVE) $(subst /,$(PS),$(OUT_BIN)/CSharp_SpreadsheetExamples) + + diff --git a/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetDocHelper.cs b/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetDocHelper.cs new file mode 100644 index 000000000..d8ddd6beb --- /dev/null +++ b/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetDocHelper.cs @@ -0,0 +1,361 @@ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * This file incorporates work covered by the following license notice: + * + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed + * with this work for additional information regarding copyright + * ownership. The ASF licenses this file to you under the Apache + * License, Version 2.0 (the "License"); you may not use this file + * except in compliance with the License. You may obtain a copy of + * the License at http://www.apache.org/licenses/LICENSE-2.0 . + */ + +using System; +using unoidl.com.sun.star.lang; +using unoidl.com.sun.star.uno; +using unoidl.com.sun.star.bridge; +using unoidl.com.sun.star.frame; + +// __________ implementation ____________________________________ + +/** This is a helper class for the spreadsheet and table samples. + It connects to a running office and creates a spreadsheet document. + Additionally it contains various helper functions. + */ +public class SpreadsheetDocHelper : System.IDisposable +{ + +// __ private members ___________________________________________ + + private const String msDataSheetName = "Data"; + + private unoidl.com.sun.star.uno.XComponentContext m_xContext; + private unoidl.com.sun.star.lang.XMultiServiceFactory mxMSFactory; + private unoidl.com.sun.star.sheet.XSpreadsheetDocument mxDocument; + + + + public SpreadsheetDocHelper( String[] args ) + { + // Connect to a running office and get the service manager + mxMSFactory = connect( args ); + // Create a new spreadsheet document + mxDocument = initDocument(); + } + +// __ helper methods ____________________________________________ + + /** Returns the service manager. + @return XMultiServiceFactory interface of the service manager. */ + public unoidl.com.sun.star.lang.XMultiServiceFactory getServiceManager() + { + return mxMSFactory; + } + + /** Returns the whole spreadsheet document. + @return XSpreadsheetDocument interface of the document. */ + public unoidl.com.sun.star.sheet.XSpreadsheetDocument getDocument() + { + return mxDocument; + } + + /** Returns the spreadsheet with the specified index (0-based). + @param nIndex The index of the sheet. + @return XSpreadsheet interface of the sheet. */ + public unoidl.com.sun.star.sheet.XSpreadsheet getSpreadsheet( int nIndex ) + { + // Collection of sheets + unoidl.com.sun.star.sheet.XSpreadsheets xSheets = + mxDocument.getSheets(); + + unoidl.com.sun.star.container.XIndexAccess xSheetsIA = + (unoidl.com.sun.star.container.XIndexAccess) xSheets; + + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = + (unoidl.com.sun.star.sheet.XSpreadsheet) + xSheetsIA.getByIndex( nIndex ).Value; + + return xSheet; + } + + /** Inserts a new empty spreadsheet with the specified name. + @param aName The name of the new sheet. + @param nIndex The insertion index. + @return The XSpreadsheet interface of the new sheet. */ + public unoidl.com.sun.star.sheet.XSpreadsheet insertSpreadsheet( + String aName, short nIndex ) + { + // Collection of sheets + unoidl.com.sun.star.sheet.XSpreadsheets xSheets = + mxDocument.getSheets(); + + xSheets.insertNewByName( aName, nIndex ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = + (unoidl.com.sun.star.sheet.XSpreadsheet) + xSheets.getByName( aName ).Value; + + return xSheet; + } + + +// Methods to fill values into cells. + + /** Writes a double value into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param fValue The value to write into the cell. */ + public void setValue( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + double fValue ) + { + xSheet.getCellRangeByName( aCellName ).getCellByPosition( + 0, 0 ).setValue( fValue ); + } + + /** Writes a formula into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param aFormula The formula to write into the cell. */ + public void setFormula( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + String aFormula ) + { + xSheet.getCellRangeByName( aCellName ).getCellByPosition( + 0, 0 ).setFormula( aFormula ); + } + + /** Writes a date with standard date format into a spreadsheet. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCellName The address of the cell (or a named range). + @param nDay The day of the date. + @param nMonth The month of the date. + @param nYear The year of the date. */ + public void setDate( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, + String aCellName, + int nDay, int nMonth, int nYear ) + { + // Set the date value. + unoidl.com.sun.star.table.XCell xCell = + xSheet.getCellRangeByName( aCellName ).getCellByPosition( 0, 0 ); + String aDateStr = nMonth + "/" + nDay + "/" + nYear; + xCell.setFormula( aDateStr ); + + // Set standard date format. + unoidl.com.sun.star.util.XNumberFormatsSupplier xFormatsSupplier = + (unoidl.com.sun.star.util.XNumberFormatsSupplier) getDocument(); + unoidl.com.sun.star.util.XNumberFormatTypes xFormatTypes = + (unoidl.com.sun.star.util.XNumberFormatTypes) + xFormatsSupplier.getNumberFormats(); + int nFormat = xFormatTypes.getStandardFormat( + unoidl.com.sun.star.util.NumberFormat.DATE, + new unoidl.com.sun.star.lang.Locale() ); + + unoidl.com.sun.star.beans.XPropertySet xPropSet = + (unoidl.com.sun.star.beans.XPropertySet) xCell; + xPropSet.setPropertyValue( + "NumberFormat", + new uno.Any( (Int32) nFormat ) ); + } + + /** 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 ) ); + } + + +// Methods to create cell addresses and range addresses. + + /** Creates a unoidl.com.sun.star.table.CellAddress and initializes it + with the given range. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aCell The address of the cell (or a named cell). */ + public unoidl.com.sun.star.table.CellAddress createCellAddress( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, + String aCell ) + { + unoidl.com.sun.star.sheet.XCellAddressable xAddr = + (unoidl.com.sun.star.sheet.XCellAddressable) + xSheet.getCellRangeByName( aCell ).getCellByPosition( 0, 0 ); + return xAddr.getCellAddress(); + } + + /** Creates a unoidl.com.sun.star.table.CellRangeAddress and initializes + it with the given range. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aRange The address of the cell range (or a named range). */ + public unoidl.com.sun.star.table.CellRangeAddress createCellRangeAddress( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) + { + unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = + (unoidl.com.sun.star.sheet.XCellRangeAddressable) + xSheet.getCellRangeByName( aRange ); + return xAddr.getRangeAddress(); + } + + +// Methods to convert cell addresses and range addresses to strings. + + /** Returns the text address of the cell. + @param nColumn The column index. + @param nRow The row index. + @return A string containing the cell address. */ + public String getCellAddressString( int nColumn, int nRow ) + { + String aStr = ""; + if (nColumn > 25) + aStr += (char) ('A' + nColumn / 26 - 1); + aStr += (char) ('A' + nColumn % 26); + aStr += (nRow + 1); + return aStr; + } + + /** 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 ); + } + + /** Returns the text address of the cell range. + @param xCellRange The XSheetCellRange interface of the cell range. + @param bWithSheet true = Include sheet name. + @return A string containing the cell range address. */ + public String getCellRangeAddressString( + unoidl.com.sun.star.sheet.XSheetCellRange xCellRange, bool bWithSheet ) + { + String aStr = ""; + if (bWithSheet) + { + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = + xCellRange.getSpreadsheet(); + unoidl.com.sun.star.container.XNamed xNamed = + (unoidl.com.sun.star.container.XNamed) xSheet; + aStr += xNamed.getName() + "."; + } + unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr = + (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange; + aStr += getCellRangeAddressString( xAddr.getRangeAddress() ); + return aStr; + } + + /** Returns a list of addresses of all cell ranges contained in the + collection. + + @param xRangesIA The XIndexAccess interface of the collection. + @return A string containing the cell range address list. */ + public String getCellRangeListString( + unoidl.com.sun.star.container.XIndexAccess xRangesIA ) + { + String aStr = ""; + int nCount = xRangesIA.getCount(); + for (int nIndex = 0; nIndex < nCount; ++nIndex) + { + if (nIndex > 0) + aStr += " "; + uno.Any aRangeObj = xRangesIA.getByIndex( nIndex ); + unoidl.com.sun.star.sheet.XSheetCellRange xCellRange = + (unoidl.com.sun.star.sheet.XSheetCellRange) aRangeObj.Value; + aStr += getCellRangeAddressString( xCellRange, false ); + } + return aStr; + } + + + + /** Connect to a running office that is accepting connections. + @return The ServiceManager to instantiate office components. */ + private XMultiServiceFactory connect( String [] args ) + { + + m_xContext = uno.util.Bootstrap.bootstrap(); + + return (XMultiServiceFactory) m_xContext.getServiceManager(); + } + + public void Dispose() + { + + } + + /** Creates an empty spreadsheet document. + @return The XSpreadsheetDocument interface of the document. */ + private unoidl.com.sun.star.sheet.XSpreadsheetDocument initDocument() + { + XComponentLoader aLoader = (XComponentLoader) + mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); + + XComponent xComponent = aLoader.loadComponentFromURL( + "private:factory/scalc", "_blank", 0, + new unoidl.com.sun.star.beans.PropertyValue[0] ); + + return (unoidl.com.sun.star.sheet.XSpreadsheetDocument) xComponent; + } + + +} diff --git a/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetSample.cs b/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetSample.cs new file mode 100644 index 000000000..1dd2f8a03 --- /dev/null +++ b/odk/examples/CLI/CSharp/Spreadsheet/SpreadsheetSample.cs @@ -0,0 +1,1495 @@ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * This file incorporates work covered by the following license notice: + * + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed + * with this work for additional information regarding copyright + * ownership. The ASF licenses this file to you under the Apache + * License, Version 2.0 (the "License"); you may not use this file + * except in compliance with the License. You may obtain a copy of + * the License at http://www.apache.org/licenses/LICENSE-2.0 . + */ + +using System; + +// __________ implementation ____________________________________ + +/** Create and modify a spreadsheet document. + */ +public class SpreadsheetSample : SpreadsheetDocHelper +{ + + public static void Main( String [] args ) + { + try + { + using ( SpreadsheetSample aSample = new SpreadsheetSample( args ) ) + { + aSample.doSampleFunctions(); + } + Console.WriteLine( "\nSamples done." ); + } + catch (Exception ex) + { + Console.WriteLine( "Sample caught exception! " + ex ); + } + } + + public SpreadsheetSample( String[] args ) + : base( args ) + { + } + + /** This sample function performs all changes on the document. */ + public void doSampleFunctions() + { + doCellSamples(); + doCellRangeSamples(); + doCellRangesSamples(); + doCellCursorSamples(); + doFormattingSamples(); + doDocumentSamples(); + doDatabaseSamples(); + doDataPilotSamples(); + doNamedRangesSamples(); + doFunctionAccessSamples(); + doApplicationSettingsSamples(); + } + + + + /** All samples regarding the service com.sun.star.sheet.SheetCell. */ + private void doCellSamples() + { + Console.WriteLine( "\n*** Samples for service sheet.SheetCell ***\n" ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + unoidl.com.sun.star.table.XCell xCell = null; + unoidl.com.sun.star.beans.XPropertySet xPropSet = null; + String aText; + prepareRange( xSheet, "A1:C7", "Cells and Cell Ranges" ); + + // --- Get cell B3 by position - (column, row) --- + xCell = xSheet.getCellByPosition( 1, 2 ); + + // --- Insert two text paragraphs into the cell. --- + unoidl.com.sun.star.text.XText xText = + (unoidl.com.sun.star.text.XText) xCell; + unoidl.com.sun.star.text.XTextCursor xTextCursor = + xText.createTextCursor(); + + xText.insertString( xTextCursor, "Text in first line.", false ); + xText.insertControlCharacter( xTextCursor, + unoidl.com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false ); + xText.insertString( xTextCursor, "And a ", false ); + + // create a hyperlink + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceMan = + (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument(); + Object aHyperlinkObj = + xServiceMan.createInstance( "com.sun.star.text.TextField.URL" ); + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aHyperlinkObj; + xPropSet.setPropertyValue( + "URL", new uno.Any( "http://www.example.org" ) ); + xPropSet.setPropertyValue( + "Representation", new uno.Any( "hyperlink" ) ); + // ... and insert + unoidl.com.sun.star.text.XTextContent xContent = + (unoidl.com.sun.star.text.XTextContent) aHyperlinkObj; + xText.insertTextContent( xTextCursor, xContent, false ); + + // --- Query the separate paragraphs. --- + unoidl.com.sun.star.container.XEnumerationAccess xParaEA = + (unoidl.com.sun.star.container.XEnumerationAccess) xCell; + unoidl.com.sun.star.container.XEnumeration xParaEnum = + xParaEA.createEnumeration(); + // Go through the paragraphs + while( xParaEnum.hasMoreElements() ) + { + uno.Any aPortionObj = xParaEnum.nextElement(); + unoidl.com.sun.star.container.XEnumerationAccess xPortionEA = + (unoidl.com.sun.star.container.XEnumerationAccess) + aPortionObj.Value; + unoidl.com.sun.star.container.XEnumeration xPortionEnum = + xPortionEA.createEnumeration(); + aText = ""; + // Go through all text portions of a paragraph and construct string. + while( xPortionEnum.hasMoreElements() ) + { + unoidl.com.sun.star.text.XTextRange xRange = + (unoidl.com.sun.star.text.XTextRange) + xPortionEnum.nextElement().Value; + aText += xRange.getString(); + } + Console.WriteLine( "Paragraph text: " + aText ); + } + + + // --- Change cell properties. --- + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell; + // from styles.CharacterProperties + xPropSet.setPropertyValue( + "CharColor", new uno.Any( (Int32) 0x003399 ) ); + xPropSet.setPropertyValue( + "CharHeight", new uno.Any( (Single) 20.0 ) ); + // from styles.ParagraphProperties + xPropSet.setPropertyValue( + "ParaLeftMargin", new uno.Any( (Int32) 500 ) ); + // from table.CellProperties + xPropSet.setPropertyValue( + "IsCellBackgroundTransparent", new uno.Any( false ) ); + xPropSet.setPropertyValue( + "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) ); + + + // --- Get cell address. --- + unoidl.com.sun.star.sheet.XCellAddressable xCellAddr = + (unoidl.com.sun.star.sheet.XCellAddressable) xCell; + unoidl.com.sun.star.table.CellAddress aAddress = + xCellAddr.getCellAddress(); + aText = "Address of this cell: Column=" + aAddress.Column; + aText += "; Row=" + aAddress.Row; + aText += "; Sheet=" + aAddress.Sheet; + Console.WriteLine( aText ); + + + // --- Insert an annotation --- + unoidl.com.sun.star.sheet.XSheetAnnotationsSupplier xAnnotationsSupp = + (unoidl.com.sun.star.sheet.XSheetAnnotationsSupplier) xSheet; + unoidl.com.sun.star.sheet.XSheetAnnotations xAnnotations = + xAnnotationsSupp.getAnnotations(); + xAnnotations.insertNew( aAddress, "This is an annotation" ); + + unoidl.com.sun.star.sheet.XSheetAnnotationAnchor xAnnotAnchor = + (unoidl.com.sun.star.sheet.XSheetAnnotationAnchor) xCell; + unoidl.com.sun.star.sheet.XSheetAnnotation xAnnotation = + xAnnotAnchor.getAnnotation(); + xAnnotation.setIsVisible( true ); + } + + + + /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */ + private void doCellRangeSamples() + { + Console.WriteLine( + "\n*** Samples for service sheet.SheetCellRange ***\n" ); + 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 ); + Console.WriteLine( "Search text replaced " + nCount + " times." ); + + + // --- 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 ); + + + // --- Change indentation. --- +/* does not work (bug in XIndent implementation) + prepareRange( xSheet, "I20:I23", "XIndent" ); + setValue( xSheet, "I21", 1 ); + setValue( xSheet, "I22", 1 ); + setValue( xSheet, "I23", 1 ); + + xCellRange = xSheet.getCellRangeByName( "I21:I22" ); + unoidl.com.sun.star.util.XIndent xIndent = + (unoidl.com.sun.star.util.XIndent) xCellRange; + xIndent.incrementIndent(); + + xCellRange = xSheet.getCellRangeByName( "I22:I23" ); + xIndent = (unoidl.com.sun.star.util.XIndent) xCellRange; + xIndent.incrementIndent(); +*/ + + + // --- 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; + Console.WriteLine( + "The name of the wide column is " + xNamed.getName() + "." ); + + + // --- 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(); + Console.WriteLine( + "Address of this range: Sheet=" + aRangeAddress.Sheet ); + Console.WriteLine( + "Start column=" + aRangeAddress.StartColumn + "; Start row=" + + aRangeAddress.StartRow ); + Console.WriteLine( + "End column =" + aRangeAddress.EndColumn + "; End row =" + + aRangeAddress.EndRow ); + + + // --- 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 ); + Console.WriteLine( + "Average value of the data table A10:C30: " + fResult ); + + + // --- 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 ); + + unoidl.com.sun.star.sheet.XCellSeries xSeries = null; + // Fill 2 rows linear with end value + // -> 2nd series is not filled completely + xSeries = getCellSeries( xSheet, "E10:I11" ); + xSeries.fillSeries( + unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, + unoidl.com.sun.star.sheet.FillMode.LINEAR, + unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9 ); + // Add months to a date + xSeries = getCellSeries( xSheet, "E12:I12" ); + xSeries.fillSeries( + unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, + unoidl.com.sun.star.sheet.FillMode.DATE, + unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH, + 1, 0x7FFFFFFF ); + // Fill right to left with a text containing a value + xSeries = getCellSeries( xSheet, "E13:I13" ); + xSeries.fillSeries( + unoidl.com.sun.star.sheet.FillDirection.TO_LEFT, + unoidl.com.sun.star.sheet.FillMode.LINEAR, + unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, + 10, 0x7FFFFFFF ); + // Fill with an user defined list + xSeries = getCellSeries( xSheet, "E14:I14" ); + xSeries.fillSeries( + unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, + unoidl.com.sun.star.sheet.FillMode.AUTO, + unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, + 1, 0x7FFFFFFF ); + // Fill bottom to top with a geometric series + xSeries = getCellSeries( xSheet, "K10:K14" ); + xSeries.fillSeries( + unoidl.com.sun.star.sheet.FillDirection.TO_TOP, + unoidl.com.sun.star.sheet.FillMode.GROWTH, + unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, + 2, 0x7FFFFFFF ); + // Auto fill + xSeries = getCellSeries( xSheet, "E16:K18" ); + xSeries.fillAuto( + unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 2 ); + // Fill series copies cell formats -> draw border here + prepareRange( xSheet, "E9:K18", "XCellSeries" ); + + + // --- Array formulas --- + xCellRange = xSheet.getCellRangeByName( "E21:G23" ); + prepareRange( xSheet, "E20:G23", "XArrayFormulaRange" ); + unoidl.com.sun.star.sheet.XArrayFormulaRange xArrayFormula = + (unoidl.com.sun.star.sheet.XArrayFormulaRange) xCellRange; + // Insert a 3x3 unit matrix. + xArrayFormula.setArrayFormula( "=A10:C12" ); + Console.WriteLine( + "Array formula is: " + xArrayFormula.getArrayFormula() ); + + + // --- Multiple operations --- + setFormula( xSheet, "E26", "=E27^F26" ); + setValue( xSheet, "E27", 1 ); + setValue( xSheet, "F26", 1 ); + getCellSeries( xSheet, "E27:E31" ).fillAuto( + unoidl.com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 ); + getCellSeries( xSheet, "F26:J26" ).fillAuto( + unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 1 ); + setFormula( xSheet, "F33", "=SIN(E33)" ); + setFormula( xSheet, "G33", "=COS(E33)" ); + setFormula( xSheet, "H33", "=TAN(E33)" ); + setValue( xSheet, "E34", 0 ); + setValue( xSheet, "E35", 0.2 ); + getCellSeries( xSheet, "E34:E38" ).fillAuto( + unoidl.com.sun.star.sheet.FillDirection.TO_BOTTOM, 2 ); + prepareRange( xSheet, "E25:J38", "XMultipleOperation" ); + + unoidl.com.sun.star.table.CellRangeAddress aFormulaRange = + createCellRangeAddress( xSheet, "E26" ); + unoidl.com.sun.star.table.CellAddress aColCell = + createCellAddress( xSheet, "E27" ); + unoidl.com.sun.star.table.CellAddress aRowCell = + createCellAddress( xSheet, "F26" ); + + xCellRange = xSheet.getCellRangeByName( "E26:J31" ); + unoidl.com.sun.star.sheet.XMultipleOperation xMultOp = + (unoidl.com.sun.star.sheet.XMultipleOperation) xCellRange; + xMultOp.setTableOperation( + aFormulaRange, unoidl.com.sun.star.sheet.TableOperationMode.BOTH, + aColCell, aRowCell ); + + aFormulaRange = createCellRangeAddress( xSheet, "F33:H33" ); + aColCell = createCellAddress( xSheet, "E33" ); + // Row cell not needed + + xCellRange = xSheet.getCellRangeByName( "E34:H38" ); + xMultOp = (unoidl.com.sun.star.sheet.XMultipleOperation) xCellRange; + xMultOp.setTableOperation( + aFormulaRange, unoidl.com.sun.star.sheet.TableOperationMode.COLUMN, + aColCell, aRowCell ); + + + // --- Cell Ranges Query --- + xCellRange = xSheet.getCellRangeByName( "A10:C30" ); + unoidl.com.sun.star.sheet.XCellRangesQuery xRangesQuery = + (unoidl.com.sun.star.sheet.XCellRangesQuery) xCellRange; + unoidl.com.sun.star.sheet.XSheetCellRanges xCellRanges = + xRangesQuery.queryContentCells( + (short) unoidl.com.sun.star.sheet.CellFlags.STRING ); + Console.WriteLine( + "Cells in A10:C30 containing text: " + + xCellRanges.getRangeAddressesAsString() ); + } + + /** Returns the XCellSeries interface of a cell range. + @param xSheet The spreadsheet containing the cell range. + @param aRange The address of the cell range. + @return The XCellSeries interface. */ + private unoidl.com.sun.star.sheet.XCellSeries getCellSeries( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange ) + { + return (unoidl.com.sun.star.sheet.XCellSeries) + xSheet.getCellRangeByName( aRange ); + } + + + + /** All samples regarding cell range collections. */ + private void doCellRangesSamples() + { + Console.WriteLine( "\n*** Samples for cell range collections ***\n" ); + + // Create a new cell range container + unoidl.com.sun.star.lang.XMultiServiceFactory xDocFactory = + (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument(); + unoidl.com.sun.star.sheet.XSheetCellRangeContainer xRangeCont = + (unoidl.com.sun.star.sheet.XSheetCellRangeContainer) + xDocFactory.createInstance( + "com.sun.star.sheet.SheetCellRanges" ); + + + // --- Insert ranges --- + insertRange( xRangeCont, 0, 0, 0, 0, 0, false ); // A1:A1 + insertRange( xRangeCont, 0, 0, 1, 0, 2, true ); // A2:A3 + insertRange( xRangeCont, 0, 1, 0, 1, 2, false ); // B1:B3 + + + // --- Query the list of filled cells --- + Console.WriteLine( "All filled cells: " ); + unoidl.com.sun.star.container.XEnumerationAccess xCellsEA = + xRangeCont.getCells(); + unoidl.com.sun.star.container.XEnumeration xEnum = + xCellsEA.createEnumeration(); + while( xEnum.hasMoreElements() ) + { + uno.Any aCellObj = xEnum.nextElement(); + unoidl.com.sun.star.sheet.XCellAddressable xAddr = + (unoidl.com.sun.star.sheet.XCellAddressable) aCellObj.Value; + unoidl.com.sun.star.table.CellAddress aAddr = + xAddr.getCellAddress(); + Console.WriteLine( + getCellAddressString( aAddr.Column, aAddr.Row ) + " " ); + } + Console.WriteLine(); + } + + /** 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() ); + } + + + + /** All samples regarding cell cursors. */ + private void doCellCursorSamples() + { + Console.WriteLine( "\n*** Samples for cell cursor ***\n" ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + + + // --- Find the array formula using a cell cursor --- + unoidl.com.sun.star.table.XCellRange xRange = + xSheet.getCellRangeByName( "F22" ); + unoidl.com.sun.star.sheet.XSheetCellRange xCellRange = + (unoidl.com.sun.star.sheet.XSheetCellRange) xRange; + unoidl.com.sun.star.sheet.XSheetCellCursor xCursor = + xSheet.createCursorByRange( xCellRange ); + + xCursor.collapseToCurrentArray(); + unoidl.com.sun.star.sheet.XArrayFormulaRange xArray = + (unoidl.com.sun.star.sheet.XArrayFormulaRange) xCursor; + Console.WriteLine( + "Array formula in " + getCellRangeAddressString( xCursor, false ) + + " contains formula " + xArray.getArrayFormula() ); + + + // --- Find the used area --- + unoidl.com.sun.star.sheet.XUsedAreaCursor xUsedCursor = + (unoidl.com.sun.star.sheet.XUsedAreaCursor) xCursor; + xUsedCursor.gotoStartOfUsedArea( false ); + xUsedCursor.gotoEndOfUsedArea( true ); + // xUsedCursor and xCursor are interfaces of the same object - + // so modifying xUsedCursor takes effect on xCursor: + Console.WriteLine( + "The used area is: " + getCellRangeAddressString( xCursor, true ) ); + } + + + + /** All samples regarding the formatting of cells and ranges. */ + private void doFormattingSamples() + { + Console.WriteLine( "\n*** Formatting samples ***\n" ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 ); + unoidl.com.sun.star.table.XCellRange xCellRange; + unoidl.com.sun.star.beans.XPropertySet xPropSet = null; + unoidl.com.sun.star.container.XIndexAccess xRangeIA = null; + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager; + + + // --- Cell styles --- + // get the cell style container + unoidl.com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier = + (unoidl.com.sun.star.style.XStyleFamiliesSupplier) getDocument(); + unoidl.com.sun.star.container.XNameAccess xFamiliesNA = + xFamiliesSupplier.getStyleFamilies(); + uno.Any aCellStylesObj = xFamiliesNA.getByName( "CellStyles" ); + unoidl.com.sun.star.container.XNameContainer xCellStylesNA = + (unoidl.com.sun.star.container.XNameContainer) aCellStylesObj.Value; + + // create a new cell style + xServiceManager = + (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument(); + Object aCellStyle = xServiceManager.createInstance( + "com.sun.star.style.CellStyle" ); + String aStyleName = "MyNewCellStyle"; + xCellStylesNA.insertByName( + aStyleName, new uno.Any( typeof (Object), aCellStyle ) ); + + // modify properties of the new style + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aCellStyle; + xPropSet.setPropertyValue( + "CellBackColor", new uno.Any( (Int32) 0x888888 ) ); + xPropSet.setPropertyValue( + "IsCellBackgroundTransparent", new uno.Any( false ) ); + + + + // --- Query equal-formatted cell ranges --- + // prepare example, use the new cell style + xCellRange = xSheet.getCellRangeByName( "D2:F2" ); + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; + xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) ); + + xCellRange = xSheet.getCellRangeByName( "A3:G3" ); + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; + xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) ); + + // All ranges in one container + xCellRange = xSheet.getCellRangeByName( "A1:G3" ); + Console.WriteLine( "Service CellFormatRanges:" ); + unoidl.com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp = + (unoidl.com.sun.star.sheet.XCellFormatRangesSupplier) xCellRange; + xRangeIA = xFormatSupp.getCellFormatRanges(); + Console.WriteLine( getCellRangeListString( xRangeIA ) ); + + // Ranges sorted in SheetCellRanges containers + Console.WriteLine( "\nService UniqueCellFormatRanges:" ); + unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier + xUniqueFormatSupp = + (unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier) + xCellRange; + unoidl.com.sun.star.container.XIndexAccess xRangesIA = + xUniqueFormatSupp.getUniqueCellFormatRanges(); + int nCount = xRangesIA.getCount(); + for (int nIndex = 0; nIndex < nCount; ++nIndex) + { + uno.Any aRangesObj = xRangesIA.getByIndex( nIndex ); + xRangeIA = + (unoidl.com.sun.star.container.XIndexAccess) aRangesObj.Value; + Console.WriteLine( + "Container " + (nIndex + 1) + ": " + + getCellRangeListString( xRangeIA ) ); + } + + + // --- Table auto formats --- + // get the global collection of table auto formats, + // use global service manager + xServiceManager = getServiceManager(); + Object aAutoFormatsObj = xServiceManager.createInstance( + "com.sun.star.sheet.TableAutoFormats" ); + unoidl.com.sun.star.container.XNameContainer xAutoFormatsNA = + (unoidl.com.sun.star.container.XNameContainer) aAutoFormatsObj; + + // create a new table auto format and insert into the container + String aAutoFormatName = "Temp_Example"; + bool bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName ); + uno.Any aAutoFormatObj; + if (bExistsAlready) + // auto format already exists -> use it + aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName ); + else + { + // create a new auto format (with document service manager!) + xServiceManager = + (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument(); + aAutoFormatObj = new uno.Any( + typeof (Object), + xServiceManager.createInstance( + "com.sun.star.sheet.TableAutoFormat" ) ); + xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj ); + } + // index access to the auto format fields + unoidl.com.sun.star.container.XIndexAccess xAutoFormatIA = + (unoidl.com.sun.star.container.XIndexAccess) aAutoFormatObj.Value; + + // set properties of all auto format fields + for (int nRow = 0; nRow < 4; ++nRow) + { + int nRowColor = 0; + switch (nRow) + { + case 0: nRowColor = 0x999999; break; + case 1: nRowColor = 0xFFFFCC; break; + case 2: nRowColor = 0xEEEEEE; break; + case 3: nRowColor = 0x999999; break; + } + + for (int nColumn = 0; nColumn < 4; ++nColumn) + { + int nColor = nRowColor; + if ((nColumn == 0) || (nColumn == 3)) + nColor -= 0x333300; + + // get the auto format field and apply properties + uno.Any aFieldObj = xAutoFormatIA.getByIndex( + 4 * nRow + nColumn ); + xPropSet = + (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value; + xPropSet.setPropertyValue( + "CellBackColor", new uno.Any( (Int32) nColor ) ); + } + } + + // set the auto format to the spreadsheet + xCellRange = xSheet.getCellRangeByName( "A5:H25" ); + unoidl.com.sun.star.table.XAutoFormattable xAutoForm = + (unoidl.com.sun.star.table.XAutoFormattable) xCellRange; + xAutoForm.autoFormat( aAutoFormatName ); + + // remove the auto format + if (!bExistsAlready) + xAutoFormatsNA.removeByName( aAutoFormatName ); + + + // --- Conditional formats --- + xSheet = getSpreadsheet( 0 ); + prepareRange( xSheet, "K20:K23", "Cond. Format" ); + setValue( xSheet, "K21", 1 ); + setValue( xSheet, "K22", 2 ); + setValue( xSheet, "K23", 3 ); + + // get the conditional format object of the cell range + xCellRange = xSheet.getCellRangeByName( "K21:K23" ); + xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange; + unoidl.com.sun.star.sheet.XSheetConditionalEntries xEntries = + (unoidl.com.sun.star.sheet.XSheetConditionalEntries) + xPropSet.getPropertyValue( "ConditionalFormat" ).Value; + + // create a condition and apply it to the range + unoidl.com.sun.star.beans.PropertyValue[] aCondition = + new unoidl.com.sun.star.beans.PropertyValue[3]; + aCondition[0] = new unoidl.com.sun.star.beans.PropertyValue(); + aCondition[0].Name = "Operator"; + aCondition[0].Value = + new uno.Any( + typeof (unoidl.com.sun.star.sheet.ConditionOperator), + unoidl.com.sun.star.sheet.ConditionOperator.GREATER ); + aCondition[1] = new unoidl.com.sun.star.beans.PropertyValue(); + aCondition[1].Name = "Formula1"; + aCondition[1].Value = new uno.Any( "1" ); + aCondition[2] = new unoidl.com.sun.star.beans.PropertyValue(); + aCondition[2].Name = "StyleName"; + aCondition[2].Value = new uno.Any( aStyleName ); + xEntries.addNew( aCondition ); + xPropSet.setPropertyValue( + "ConditionalFormat", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.XSheetConditionalEntries), + xEntries ) ); + } + + + + /** All samples regarding the spreadsheet document. */ + private void doDocumentSamples() + { + Console.WriteLine( "\n*** Samples for spreadsheet document ***\n" ); + + + // --- Insert a new spreadsheet --- + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = + insertSpreadsheet( "A new sheet", (short) 0x7FFF ); + + + // --- Copy a cell range --- + prepareRange( xSheet, "A1:B3", "Copy from" ); + prepareRange( xSheet, "D1:E3", "To" ); + setValue( xSheet, "A2", 123 ); + setValue( xSheet, "B2", 345 ); + setFormula( xSheet, "A3", "=SUM(A2:B2)" ); + setFormula( xSheet, "B3", "=FORMULA(A3)" ); + + unoidl.com.sun.star.sheet.XCellRangeMovement xMovement = + (unoidl.com.sun.star.sheet.XCellRangeMovement) xSheet; + unoidl.com.sun.star.table.CellRangeAddress aSourceRange = + createCellRangeAddress( xSheet, "A2:B3" ); + unoidl.com.sun.star.table.CellAddress aDestCell = + createCellAddress( xSheet, "D2" ); + xMovement.copyRange( aDestCell, aSourceRange ); + + + // --- Print automatic column page breaks --- + unoidl.com.sun.star.sheet.XSheetPageBreak xPageBreak = + (unoidl.com.sun.star.sheet.XSheetPageBreak) xSheet; + unoidl.com.sun.star.sheet.TablePageBreakData[] aPageBreakArray = + xPageBreak.getColumnPageBreaks(); + + Console.Write( "Automatic column page breaks:" ); + for (int nIndex = 0; nIndex < aPageBreakArray.Length; ++nIndex) + if (!aPageBreakArray[nIndex].ManualBreak) + Console.Write( " " + aPageBreakArray[nIndex].Position ); + Console.WriteLine(); + + + // --- Document properties --- + unoidl.com.sun.star.beans.XPropertySet xPropSet = + (unoidl.com.sun.star.beans.XPropertySet) getDocument(); + + String aText = "Value of property IsIterationEnabled: "; + aText += + (Boolean) xPropSet.getPropertyValue( "IsIterationEnabled" ).Value; + Console.WriteLine( aText ); + aText = "Value of property IterationCount: "; + aText += (Int32) xPropSet.getPropertyValue( "IterationCount" ).Value; + Console.WriteLine( aText ); + aText = "Value of property NullDate: "; + unoidl.com.sun.star.util.Date aDate = (unoidl.com.sun.star.util.Date) + xPropSet.getPropertyValue( "NullDate" ).Value; + aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day; + Console.WriteLine( aText ); + + + // --- Data validation --- + prepareRange( xSheet, "A5:C7", "Validation" ); + setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" ); + + unoidl.com.sun.star.table.XCellRange xCellRange = + xSheet.getCellRangeByName( "A7:C7" ); + unoidl.com.sun.star.beans.XPropertySet xCellPropSet = + (unoidl.com.sun.star.beans.XPropertySet) xCellRange; + // validation properties + unoidl.com.sun.star.beans.XPropertySet xValidPropSet = + (unoidl.com.sun.star.beans.XPropertySet) + xCellPropSet.getPropertyValue( "Validation" ).Value; + xValidPropSet.setPropertyValue( + "Type", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.ValidationType), + unoidl.com.sun.star.sheet.ValidationType.DECIMAL ) ); + xValidPropSet.setPropertyValue( + "ShowErrorMessage", new uno.Any( true ) ); + xValidPropSet.setPropertyValue( + "ErrorMessage", new uno.Any( "This is an invalid value!" ) ); + xValidPropSet.setPropertyValue( + "ErrorAlertStyle", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.ValidationAlertStyle), + unoidl.com.sun.star.sheet.ValidationAlertStyle.STOP ) ); + // condition + unoidl.com.sun.star.sheet.XSheetCondition xCondition = + (unoidl.com.sun.star.sheet.XSheetCondition) xValidPropSet; + xCondition.setOperator( + unoidl.com.sun.star.sheet.ConditionOperator.BETWEEN ); + xCondition.setFormula1( "0.0" ); + xCondition.setFormula2( "5.0" ); + // apply on cell range + xCellPropSet.setPropertyValue( + "Validation", + new uno.Any( + typeof (unoidl.com.sun.star.beans.XPropertySet), + xValidPropSet ) ); + + + // --- Scenarios --- + uno.Any [][] aValues = { + new uno.Any [] { uno.Any.VOID, uno.Any.VOID }, + new uno.Any [] { uno.Any.VOID, uno.Any.VOID } + }; + + aValues[ 0 ][ 0 ] = new uno.Any( (Double) 11 ); + aValues[ 0 ][ 1 ] = new uno.Any( (Double) 12 ); + aValues[ 1 ][ 0 ] = new uno.Any( "Test13" ); + aValues[ 1 ][ 1 ] = new uno.Any( "Test14" ); + insertScenario( + xSheet, "B10:C11", aValues, + "First Scenario", "The first scenario." ); + + aValues[ 0 ][ 0 ] = new uno.Any( "Test21" ); + aValues[ 0 ][ 1 ] = new uno.Any( "Test22" ); + aValues[ 1 ][ 0 ] = new uno.Any( (Double) 23 ); + aValues[ 1 ][ 1 ] = new uno.Any( (Double) 24 ); + insertScenario( + xSheet, "B10:C11", aValues, + "Second Scenario", "The visible scenario." ); + + aValues[ 0 ][ 0 ] = new uno.Any( (Double) 31 ); + aValues[ 0 ][ 1 ] = new uno.Any( (Double) 32 ); + aValues[ 1 ][ 0 ] = new uno.Any( "Test33" ); + aValues[ 1 ][ 1 ] = new uno.Any( "Test34" ); + insertScenario( + xSheet, "B10:C11", aValues, + "Third Scenario", "The last scenario." ); + + // show second scenario + showScenario( xSheet, "Second Scenario" ); + } + + /** 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 ); + } + + /** Activates a scenario. + @param xSheet The XSpreadsheet interface of the spreadsheet. + @param aScenarioName The name of the scenario. */ + private void showScenario( + unoidl.com.sun.star.sheet.XSpreadsheet xSheet, + String aScenarioName ) + { + // get the scenario set + unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp = + (unoidl.com.sun.star.sheet.XScenariosSupplier) xSheet; + unoidl.com.sun.star.sheet.XScenarios xScenarios = + xScenSupp.getScenarios(); + + // get the scenario and activate it + uno.Any aScenarioObj = xScenarios.getByName( aScenarioName ); + unoidl.com.sun.star.sheet.XScenario xScenario = + (unoidl.com.sun.star.sheet.XScenario) aScenarioObj.Value; + xScenario.apply(); + } + + + + 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" ); + } + + + + /** Helper for doDatabaseSamples: get name of first database. */ + private String getFirstDatabaseName() + { + String aDatabase = null; + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager = + getServiceManager(); + unoidl.com.sun.star.container.XNameAccess xContext = + (unoidl.com.sun.star.container.XNameAccess) + xServiceManager.createInstance( + "com.sun.star.sdb.DatabaseContext" ); + String[] aNames = xContext.getElementNames(); + if ( aNames.Length > 0 ) + aDatabase = aNames[0]; + return aDatabase; + } + + /** Helper for doDatabaseSamples: get name of first table in a database. */ + private String getFirstTableName( String aDatabase ) + { + if ( aDatabase == null ) + return null; + + String aTable = null; + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager = + getServiceManager(); + unoidl.com.sun.star.container.XNameAccess xContext = + (unoidl.com.sun.star.container.XNameAccess) + xServiceManager.createInstance( + "com.sun.star.sdb.DatabaseContext" ); + unoidl.com.sun.star.sdb.XCompletedConnection xSource = + (unoidl.com.sun.star.sdb.XCompletedConnection) + xContext.getByName( aDatabase ).Value; + unoidl.com.sun.star.task.XInteractionHandler xHandler = + (unoidl.com.sun.star.task.XInteractionHandler) + xServiceManager.createInstance( + "com.sun.star.task.InteractionHandler" ); + unoidl.com.sun.star.sdbcx.XTablesSupplier xSupplier = + (unoidl.com.sun.star.sdbcx.XTablesSupplier) + xSource.connectWithCompletion( xHandler ); + unoidl.com.sun.star.container.XNameAccess xTables = + xSupplier.getTables(); + String[] aNames = xTables.getElementNames(); + if ( aNames.Length > 0 ) + aTable = aNames[0]; + return aTable; + } + + private void doDatabaseSamples() + { + Console.WriteLine( "\n*** Samples for database operations ***\n" ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 2 ); + + + // --- put some example data into the sheet --- + unoidl.com.sun.star.table.XCellRange xRange = + xSheet.getCellRangeByName( "B3:D24" ); + unoidl.com.sun.star.sheet.XCellRangeData xData = + (unoidl.com.sun.star.sheet.XCellRangeData) xRange; + uno.Any [][] aValues = + { + new uno.Any [] { new uno.Any( "Name" ), + new uno.Any( "Year" ), + new uno.Any( "Sales" ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 2001 ), + new uno.Any( (Double) 4.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 1997 ), + new uno.Any( (Double) 3.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 1998 ), + new uno.Any( (Double) 8.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 1997 ), + new uno.Any( (Double) 8.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 2002 ), + new uno.Any( (Double) 9.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 1999 ), + new uno.Any( (Double) 7.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 1996 ), + new uno.Any( (Double) 3.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 2000 ), + new uno.Any( (Double) 1.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 1999 ), + new uno.Any( (Double) 5.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 2002 ), + new uno.Any( (Double) 1.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 2001 ), + new uno.Any( (Double) 5.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 2000 ), + new uno.Any( (Double) 1.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 1996 ), + new uno.Any( (Double) 8.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 1996 ), + new uno.Any( (Double) 7.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 1997 ), + new uno.Any( (Double) 3.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 2000 ), + new uno.Any( (Double) 9.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 1998 ), + new uno.Any( (Double) 1.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 1999 ), + new uno.Any( (Double) 6.0 ) }, + new uno.Any [] { new uno.Any( "Carol" ), + new uno.Any( (Double) 2002 ), + new uno.Any( (Double) 8.0 ) }, + new uno.Any [] { new uno.Any( "Alice" ), + new uno.Any( (Double) 1998 ), + new uno.Any( (Double) 5.0 ) }, + new uno.Any [] { new uno.Any( "Bob" ), + new uno.Any( (Double) 2001 ), + new uno.Any( (Double) 6.0 ) } + }; + xData.setDataArray( aValues ); + + + // --- filter for second column >= 1998 --- + unoidl.com.sun.star.sheet.XSheetFilterable xFilter = + (unoidl.com.sun.star.sheet.XSheetFilterable) xRange; + unoidl.com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc = + xFilter.createFilterDescriptor( true ); + unoidl.com.sun.star.sheet.TableFilterField[] aFilterFields = + new unoidl.com.sun.star.sheet.TableFilterField[1]; + aFilterFields[0] = new unoidl.com.sun.star.sheet.TableFilterField(); + aFilterFields[0].Field = 1; + aFilterFields[0].IsNumeric = true; + aFilterFields[0].Operator = + unoidl.com.sun.star.sheet.FilterOperator.GREATER_EQUAL; + aFilterFields[0].NumericValue = 1998; + xFilterDesc.setFilterFields( aFilterFields ); + unoidl.com.sun.star.beans.XPropertySet xFilterProp = + (unoidl.com.sun.star.beans.XPropertySet) xFilterDesc; + xFilterProp.setPropertyValue( + "ContainsHeader", new uno.Any( true ) ); + xFilter.filter( xFilterDesc ); + + + // --- do the same filter as above, using criteria from a cell range --- + unoidl.com.sun.star.table.XCellRange xCritRange = + xSheet.getCellRangeByName( "B27:B28" ); + unoidl.com.sun.star.sheet.XCellRangeData xCritData = + (unoidl.com.sun.star.sheet.XCellRangeData) xCritRange; + uno.Any [][] aCritValues = + { + new uno.Any [] { new uno.Any( "Year" ) }, + new uno.Any [] { new uno.Any( ">= 1998" ) } + }; + xCritData.setDataArray( aCritValues ); + unoidl.com.sun.star.sheet.XSheetFilterableEx xCriteria = + (unoidl.com.sun.star.sheet.XSheetFilterableEx) xCritRange; + xFilterDesc = xCriteria.createFilterDescriptorByObject( xFilter ); + if ( xFilterDesc != null ) + xFilter.filter( xFilterDesc ); + + + // --- sort by second column, ascending --- + unoidl.com.sun.star.util.SortField[] aSortFields = + new unoidl.com.sun.star.util.SortField[1]; + aSortFields[0] = new unoidl.com.sun.star.util.SortField(); + aSortFields[0].Field = 1; + aSortFields[0].SortAscending = true; + + unoidl.com.sun.star.beans.PropertyValue[] aSortDesc = + new unoidl.com.sun.star.beans.PropertyValue[2]; + aSortDesc[0] = new unoidl.com.sun.star.beans.PropertyValue(); + aSortDesc[0].Name = "SortFields"; + aSortDesc[0].Value = + new uno.Any( + typeof (unoidl.com.sun.star.util.SortField []), + aSortFields ); + aSortDesc[1] = new unoidl.com.sun.star.beans.PropertyValue(); + aSortDesc[1].Name = "ContainsHeader"; + aSortDesc[1].Value = new uno.Any( true ); + + unoidl.com.sun.star.util.XSortable xSort = + (unoidl.com.sun.star.util.XSortable) xRange; + xSort.sort( aSortDesc ); + + + // --- insert subtotals --- + unoidl.com.sun.star.sheet.XSubTotalCalculatable xSub = + (unoidl.com.sun.star.sheet.XSubTotalCalculatable) xRange; + unoidl.com.sun.star.sheet.XSubTotalDescriptor xSubDesc = + xSub.createSubTotalDescriptor( true ); + unoidl.com.sun.star.sheet.SubTotalColumn[] aColumns = + new unoidl.com.sun.star.sheet.SubTotalColumn[1]; + // calculate sum of third column + aColumns[0] = new unoidl.com.sun.star.sheet.SubTotalColumn(); + aColumns[0].Column = 2; + aColumns[0].Function = unoidl.com.sun.star.sheet.GeneralFunction.SUM; + // group by first column + xSubDesc.addNew( aColumns, 0 ); + xSub.applySubTotals( xSubDesc, true ); + + String aDatabase = getFirstDatabaseName(); + String aTableName = getFirstTableName( aDatabase ); + if ( aDatabase != null && aTableName != null ) + { + // --- import from database --- + unoidl.com.sun.star.beans.PropertyValue[] aImportDesc = + new unoidl.com.sun.star.beans.PropertyValue[3]; + aImportDesc[0] = new unoidl.com.sun.star.beans.PropertyValue(); + aImportDesc[0].Name = "DatabaseName"; + aImportDesc[0].Value = new uno.Any( aDatabase ); + aImportDesc[1] = new unoidl.com.sun.star.beans.PropertyValue(); + aImportDesc[1].Name = "SourceType"; + aImportDesc[1].Value = + new uno.Any( + typeof (unoidl.com.sun.star.sheet.DataImportMode), + unoidl.com.sun.star.sheet.DataImportMode.TABLE ); + aImportDesc[2] = new unoidl.com.sun.star.beans.PropertyValue(); + aImportDesc[2].Name = "SourceObject"; + aImportDesc[2].Value = new uno.Any( aTableName ); + + unoidl.com.sun.star.table.XCellRange xImportRange = + xSheet.getCellRangeByName( "B35:B35" ); + unoidl.com.sun.star.util.XImportable xImport = + (unoidl.com.sun.star.util.XImportable) xImportRange; + xImport.doImport( aImportDesc ); + + + // --- use the temporary database range to find the + // imported data's size --- + unoidl.com.sun.star.beans.XPropertySet xDocProp = + (unoidl.com.sun.star.beans.XPropertySet) getDocument(); + uno.Any aRangesObj = xDocProp.getPropertyValue( "DatabaseRanges" ); + unoidl.com.sun.star.container.XNameAccess xRanges = + (unoidl.com.sun.star.container.XNameAccess) aRangesObj.Value; + String[] aNames = xRanges.getElementNames(); + for ( int i=0; i<aNames.Length; i++ ) + { + uno.Any aRangeObj = xRanges.getByName( aNames[i] ); + unoidl.com.sun.star.beans.XPropertySet xRangeProp = + (unoidl.com.sun.star.beans.XPropertySet) aRangeObj.Value; + bool bUser = (Boolean) + xRangeProp.getPropertyValue( "IsUserDefined" ).Value; + if ( !bUser ) + { + // this is the temporary database range - + // get the cell range and format it + unoidl.com.sun.star.sheet.XCellRangeReferrer xRef = + (unoidl.com.sun.star.sheet.XCellRangeReferrer) + aRangeObj.Value; + unoidl.com.sun.star.table.XCellRange xResultRange = + xRef.getReferredCells(); + unoidl.com.sun.star.beans.XPropertySet xResultProp = + (unoidl.com.sun.star.beans.XPropertySet) xResultRange; + xResultProp.setPropertyValue( + "IsCellBackgroundTransparent", new uno.Any( false ) ); + xResultProp.setPropertyValue( + "CellBackColor", new uno.Any( (Int32) 0xFFFFCC ) ); + } + } + } + else + Console.WriteLine("can't get database"); + } + + + + private void doDataPilotSamples() + { + Console.WriteLine( "\n*** Samples for Data Pilot ***\n" ); + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 ); + + + // --- Create a new DataPilot table --- + prepareRange( xSheet, "A38:C38", "Data Pilot" ); + unoidl.com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp = + (unoidl.com.sun.star.sheet.XDataPilotTablesSupplier) xSheet; + unoidl.com.sun.star.sheet.XDataPilotTables xDPTables = + xDPSupp.getDataPilotTables(); + unoidl.com.sun.star.sheet.XDataPilotDescriptor xDPDesc = + xDPTables.createDataPilotDescriptor(); + // set source range (use data range from CellRange test) + unoidl.com.sun.star.table.CellRangeAddress aSourceAddress = + createCellRangeAddress( xSheet, "A10:C30" ); + xDPDesc.setSourceRange( aSourceAddress ); + // settings for fields + unoidl.com.sun.star.container.XIndexAccess xFields = + xDPDesc.getDataPilotFields(); + uno.Any aFieldObj; + unoidl.com.sun.star.beans.XPropertySet xFieldProp; + // use first column as column field + aFieldObj = xFields.getByIndex(0); + xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value; + xFieldProp.setPropertyValue( + "Orientation", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation), + unoidl.com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) ); + // use second column as row field + aFieldObj = xFields.getByIndex(1); + xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value; + xFieldProp.setPropertyValue( + "Orientation", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation), + unoidl.com.sun.star.sheet.DataPilotFieldOrientation.ROW ) ); + // use third column as data field, calculating the sum + aFieldObj = xFields.getByIndex(2); + xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value; + xFieldProp.setPropertyValue( + "Orientation", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation), + unoidl.com.sun.star.sheet.DataPilotFieldOrientation.DATA ) ); + xFieldProp.setPropertyValue( + "Function", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.GeneralFunction), + unoidl.com.sun.star.sheet.GeneralFunction.SUM ) ); + // select output position + unoidl.com.sun.star.table.CellAddress aDestAddress = + createCellAddress( xSheet, "A40" ); + xDPTables.insertNewByName( "DataPilotExample", aDestAddress, xDPDesc ); + + + // --- Modify the DataPilot table --- + uno.Any aDPTableObj = xDPTables.getByName( "DataPilotExample" ); + xDPDesc = + (unoidl.com.sun.star.sheet.XDataPilotDescriptor) aDPTableObj.Value; + xFields = xDPDesc.getDataPilotFields(); + // add a second data field from the third column, + // calculating the average + aFieldObj = xFields.getByIndex(2); + xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value; + xFieldProp.setPropertyValue( + "Orientation", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation), + unoidl.com.sun.star.sheet.DataPilotFieldOrientation.DATA ) ); + xFieldProp.setPropertyValue( + "Function", + new uno.Any( + typeof (unoidl.com.sun.star.sheet.GeneralFunction), + unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE ) ); + } + + + + private void doFunctionAccessSamples() + { + Console.WriteLine( "\n*** Samples for function handling ***\n" ); + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager = + getServiceManager(); + + + // --- Calculate a function --- + Object aFuncInst = xServiceManager.createInstance( + "com.sun.star.sheet.FunctionAccess" ); + unoidl.com.sun.star.sheet.XFunctionAccess xFuncAcc = + (unoidl.com.sun.star.sheet.XFunctionAccess) aFuncInst; + // put the data in a two-dimensional array + Double [][] aData = { new Double [] { 1.0, 2.0, 3.0 } }; + // construct the array of function arguments + uno.Any [] aArgs = new uno.Any [2]; + aArgs[0] = new uno.Any( typeof (Double [][]), aData ); + aArgs[1] = new uno.Any( (Double) 2.0 ); + uno.Any aResult = xFuncAcc.callFunction( "ZTEST", aArgs ); + Console.WriteLine( + "ZTEST result for data {1,2,3} and value 2 is " + aResult.Value ); + + + // --- Get the list of recently used functions --- + Object aRecInst = xServiceManager.createInstance( + "com.sun.star.sheet.RecentFunctions" ); + unoidl.com.sun.star.sheet.XRecentFunctions xRecFunc = + (unoidl.com.sun.star.sheet.XRecentFunctions) aRecInst; + int[] nRecentIds = xRecFunc.getRecentFunctionIds(); + + + // --- Get the names for these functions --- + Object aDescInst = xServiceManager.createInstance( + "com.sun.star.sheet.FunctionDescriptions" ); + unoidl.com.sun.star.sheet.XFunctionDescriptions xFuncDesc = + (unoidl.com.sun.star.sheet.XFunctionDescriptions) aDescInst; + Console.Write("Recently used functions: "); + for (int nFunction=0; nFunction<nRecentIds.Length; nFunction++) + { + unoidl.com.sun.star.beans.PropertyValue[] aProperties = + xFuncDesc.getById( nRecentIds[nFunction] ); + for (int nProp=0; nProp<aProperties.Length; nProp++) + if ( aProperties[nProp].Name.Equals( "Name" ) ) + Console.Write( aProperties[nProp].Value + " " ); + } + Console.WriteLine(); + } + + + + private void doApplicationSettingsSamples() + { + Console.WriteLine( "\n*** Samples for application settings ***\n" ); + unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager = + getServiceManager(); + + + // --- Get the user defined sort lists --- + Object aSettings = xServiceManager.createInstance( + "com.sun.star.sheet.GlobalSheetSettings" ); + unoidl.com.sun.star.beans.XPropertySet xPropSet = + (unoidl.com.sun.star.beans.XPropertySet) aSettings; + String[] aEntries = (String []) + xPropSet.getPropertyValue( "UserLists" ).Value; + Console.WriteLine("User defined sort lists:"); + for ( int i=0; i<aEntries.Length; i++ ) + Console.WriteLine( aEntries[i] ); + } + + + +} diff --git a/odk/examples/CLI/CSharp/Spreadsheet/ViewSample.cs b/odk/examples/CLI/CSharp/Spreadsheet/ViewSample.cs new file mode 100644 index 000000000..b5d0543c1 --- /dev/null +++ b/odk/examples/CLI/CSharp/Spreadsheet/ViewSample.cs @@ -0,0 +1,182 @@ +/* + * This file is part of the LibreOffice project. + * + * This Source Code Form is subject to the terms of the Mozilla Public + * License, v. 2.0. If a copy of the MPL was not distributed with this + * file, You can obtain one at http://mozilla.org/MPL/2.0/. + * + * This file incorporates work covered by the following license notice: + * + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed + * with this work for additional information regarding copyright + * ownership. The ASF licenses this file to you under the Apache + * License, Version 2.0 (the "License"); you may not use this file + * except in compliance with the License. You may obtain a copy of + * the License at http://www.apache.org/licenses/LICENSE-2.0 . + */ + +using System; +using System.Threading; + +// __________ implementation ____________________________________ + +/** Create and modify a spreadsheet view. + */ +public class ViewSample : SpreadsheetDocHelper +{ + + public static void Main( String [] args ) + { + try + { + using ( ViewSample aSample = new ViewSample( args ) ) + { + aSample.doSampleFunction(); + } + Console.WriteLine( "\nSamples done." ); + } + catch (Exception ex) + { + Console.WriteLine( "Sample caught exception! " + ex ); + } + } + + + + public ViewSample( String[] args ) + : base( args ) + { + } + + + + /** This sample function performs all changes on the view. */ + public void doSampleFunction() + { + unoidl.com.sun.star.sheet.XSpreadsheetDocument xDoc = getDocument(); + unoidl.com.sun.star.frame.XModel xModel = + (unoidl.com.sun.star.frame.XModel) xDoc; + unoidl.com.sun.star.frame.XController xController = + xModel.getCurrentController(); + + // --- Spreadsheet view --- + // freeze the first column and first two rows + unoidl.com.sun.star.sheet.XViewFreezable xFreeze = + (unoidl.com.sun.star.sheet.XViewFreezable) xController; + if ( null != xFreeze ) + Console.WriteLine( "got xFreeze" ); + xFreeze.freezeAtPosition( 1, 2 ); + + // --- View pane --- + // get the cell range shown in the second pane and assign + // a cell background to them + unoidl.com.sun.star.container.XIndexAccess xIndex = + (unoidl.com.sun.star.container.XIndexAccess) xController; + uno.Any aPane = xIndex.getByIndex(1); + unoidl.com.sun.star.sheet.XCellRangeReferrer xRefer = + (unoidl.com.sun.star.sheet.XCellRangeReferrer) aPane.Value; + unoidl.com.sun.star.table.XCellRange xRange = xRefer.getReferredCells(); + unoidl.com.sun.star.beans.XPropertySet xRangeProp = + (unoidl.com.sun.star.beans.XPropertySet) xRange; + xRangeProp.setPropertyValue( + "IsCellBackgroundTransparent", new uno.Any( false ) ); + xRangeProp.setPropertyValue( + "CellBackColor", new uno.Any( (Int32) 0xFFFFCC ) ); + + // --- View settings --- + // change the view to display green grid lines + unoidl.com.sun.star.beans.XPropertySet xProp = + (unoidl.com.sun.star.beans.XPropertySet) xController; + xProp.setPropertyValue( + "ShowGrid", new uno.Any( true ) ); + xProp.setPropertyValue( + "GridColor", new uno.Any( (Int32) 0x00CC00 ) ); + + // --- Range selection --- + // let the user select a range and use it as the view's selection + unoidl.com.sun.star.sheet.XRangeSelection xRngSel = + (unoidl.com.sun.star.sheet.XRangeSelection) xController; + ExampleRangeListener aListener = new ExampleRangeListener(); + xRngSel.addRangeSelectionListener( aListener ); + unoidl.com.sun.star.beans.PropertyValue[] aArguments = + new unoidl.com.sun.star.beans.PropertyValue[2]; + aArguments[0] = new unoidl.com.sun.star.beans.PropertyValue(); + aArguments[0].Name = "Title"; + aArguments[0].Value = new uno.Any( "Please select a range" ); + aArguments[1] = new unoidl.com.sun.star.beans.PropertyValue(); + aArguments[1].Name = "CloseOnMouseRelease"; + aArguments[1].Value = new uno.Any( true ); + xRngSel.startRangeSelection( aArguments ); + Monitor.Enter( aListener ); + try + { + Monitor.Wait( aListener ); // wait until the selection is done + } + finally + { + Monitor.Exit( aListener ); + } + xRngSel.removeRangeSelectionListener( aListener ); + if ( aListener.aResult != null && aListener.aResult.Length != 0 ) + { + unoidl.com.sun.star.view.XSelectionSupplier xSel = + (unoidl.com.sun.star.view.XSelectionSupplier) xController; + unoidl.com.sun.star.sheet.XSpreadsheetView xView = + (unoidl.com.sun.star.sheet.XSpreadsheetView) xController; + unoidl.com.sun.star.sheet.XSpreadsheet xSheet = + xView.getActiveSheet(); + unoidl.com.sun.star.table.XCellRange xResultRange = + xSheet.getCellRangeByName( aListener.aResult ); + xSel.select( + new uno.Any( + typeof (unoidl.com.sun.star.table.XCellRange), + xResultRange ) ); + } + } + + + + // listener to react on finished selection + + private class ExampleRangeListener + : unoidl.com.sun.star.sheet.XRangeSelectionListener + { + public String aResult; + + public void done( unoidl.com.sun.star.sheet.RangeSelectionEvent aEvent ) + { + aResult = aEvent.RangeDescriptor; + Monitor.Enter( this ); + try + { + Monitor.Pulse( this ); + } + finally + { + Monitor.Exit( this ); + } + } + + public void aborted( + unoidl.com.sun.star.sheet.RangeSelectionEvent aEvent ) + { + Monitor.Enter( this ); + try + { + Monitor.Pulse( this ); + } + finally + { + Monitor.Exit( this ); + } + } + + public void disposing( unoidl.com.sun.star.lang.EventObject aObj ) + { + } + } + + + +} |