/************************************************************** * * 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 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. * *************************************************************/ using System; using unoidl.com.sun.star.lang; using unoidl.com.sun.star.uno; using unoidl.com.sun.star.frame; using unoidl.com.sun.star.util; namespace cliversion { public class Version { public Version() { try { // System.Diagnostics.Debugger.Launch(); //link with cli_ure.dll uno.util.WeakBase wb = new uno.util.WeakBase(); using ( SpreadsheetSample aSample = new SpreadsheetSample() ) { aSample.doCellRangeSamples(); aSample.terminate(); } } catch (System.Exception ) { //This exception is thrown if we link with a library which is not //available throw; } } } class SpreadsheetSample: SpreadsheetDocHelper { public SpreadsheetSample() { } /** 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 ); } /** 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 ); } } /** 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. */ 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() { // System.Diagnostics.Debugger.Launch(); // Connect to a running office and get the service manager mxMSFactory = connect(); // 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() { 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; } public void terminate() { XModifiable xMod = (XModifiable) mxDocument; if (xMod != null) xMod.setModified(false); XDesktop aDesktop = (XDesktop) mxMSFactory.createInstance( "com.sun.star.frame.Desktop" ); if (aDesktop != null) { try { aDesktop.terminate(); } catch (DisposedException d) { //This exception may be thrown because shutting down OOo using //XDesktop terminate does not really work. In the case of the //Exception OOo will still terminate. } } } // ________________________________________________________________ } }