/**************************************************************
 *
 * 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.
            }
        }
    }

// ________________________________________________________________
}

}