odk/examples/CLI/CSharp/Spreadsheet/GeneralTableSample.cs

   1 /*
   2  * This file is part of the LibreOffice project.
   3  *
   4  * This Source Code Form is subject to the terms of the Mozilla Public
   5  * License, v. 2.0. If a copy of the MPL was not distributed with this
   6  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   7  *
   8  * This file incorporates work covered by the following license notice:
   9  *
  10  *   Licensed to the Apache Software Foundation (ASF) under one or more
  11  *   contributor license agreements. See the NOTICE file distributed
  12  *   with this work for additional information regarding copyright
  13  *   ownership. The ASF licenses this file to you under the Apache
  14  *   License, Version 2.0 (the "License"); you may not use this file
  15  *   except in compliance with the License. You may obtain a copy of
  16  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  17  */
  18
  19 using System;
  20
  21 // __________  implementation  ____________________________________
  22
  23 /** Create a spreadsheet document and provide access to table contents.
  24  */
  25 public class GeneralTableSample : SpreadsheetDocHelper
  26 {
  27
  28     public static void Main( String [] args )
  29     {
  30         try
  31         {
  32             using ( GeneralTableSample aSample =
  33                       new GeneralTableSample( args ) )
  34             {
  35                 aSample.doSampleFunction();
  36             }
  37             Console.WriteLine( "Sample done." );
  38         }
  39         catch (Exception ex)
  40         {
  41             Console.WriteLine( "Sample caught exception! " + ex );
  42         }
  43     }
  44
  45
  46
  47     public GeneralTableSample( String[] args ) : base( args )
  48     {
  49     }
  50
  51
  52
  53     /// This sample function modifies cells and cell ranges.
  54     public void doSampleFunction()
  55     {
  56         // for common usage
  57         unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
  58         unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
  59         unoidl.com.sun.star.table.XCell xCell = null;
  60         unoidl.com.sun.star.table.XCellRange xCellRange = null;
  61
  62         // *** Access and modify a VALUE CELL ***
  63         Console.WriteLine( "*** Sample for service table.Cell ***" );
  64
  65         xCell = xSheet.getCellByPosition( 0, 0 );
  66         // Set cell value.
  67         xCell.setValue( 1234 );
  68
  69         // Get cell value.
  70         double nDblValue = xCell.getValue() * 2;
  71         xSheet.getCellByPosition( 0, 1 ).setValue( nDblValue );
  72
  73         // *** Create a FORMULA CELL and query error type ***
  74         xCell = xSheet.getCellByPosition( 0, 2 );
  75         // Set formula string.
  76         xCell.setFormula( "=1/0" );
  77
  78         // Get error type.
  79         bool bValid = (xCell.getError() == 0);
  80         // Get formula string.
  81         String aText = "The formula " + xCell.getFormula() + " is ";
  82         aText += bValid ? "valid." : "erroneous.";
  83
  84         // *** Insert a TEXT CELL using the XText interface ***
  85         xCell = xSheet.getCellByPosition( 0, 3 );
  86         unoidl.com.sun.star.text.XText xCellText =
  87             (unoidl.com.sun.star.text.XText) xCell;
  88         unoidl.com.sun.star.text.XTextCursor xTextCursor =
  89             xCellText.createTextCursor();
  90         xCellText.insertString( xTextCursor, aText, false );
  91
  92         // *** Change cell properties ***
  93         int nValue = bValid ? 0x00FF00 : 0xFF4040;
  94         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell;
  95         xPropSet.setPropertyValue(
  96             "CellBackColor", new uno.Any( (Int32) nValue ) );
  97
  98
  99         // *** Accessing a CELL RANGE ***
 100         Console.WriteLine( "*** Sample for service table.CellRange ***" );
 101
 102         // Accessing a cell range over its position.
 103         xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 );
 104
 105         // Change properties of the range.
 106         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
 107         xPropSet.setPropertyValue(
 108             "CellBackColor", new uno.Any( (Int32) 0x8080FF ) );
 109
 110         // Accessing a cell range over its name.
 111         xCellRange = xSheet.getCellRangeByName( "C4:D5" );
 112
 113         // Change properties of the range.
 114         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
 115         xPropSet.setPropertyValue(
 116             "CellBackColor", new uno.Any( (Int32) 0xFFFF80 ) );
 117
 118
 119         // *** Using the CELL CURSOR to add some data below of
 120         // the filled area ***
 121         Console.WriteLine( "*** Sample for service table.CellCursor ***" );
 122
 123         // Create a cursor using the XSpreadsheet method createCursorByRange()
 124         xCellRange = xSheet.getCellRangeByName( "A1" );
 125         unoidl.com.sun.star.sheet.XSheetCellRange xSheetCellRange =
 126             (unoidl.com.sun.star.sheet.XSheetCellRange) xCellRange;
 127
 128         unoidl.com.sun.star.sheet.XSheetCellCursor xSheetCellCursor =
 129             xSheet.createCursorByRange( xSheetCellRange );
 130         unoidl.com.sun.star.table.XCellCursor xCursor =
 131             (unoidl.com.sun.star.table.XCellCursor) xSheetCellCursor;
 132
 133         // Move to the last filled cell.
 134         xCursor.gotoEnd();
 135         // Move one row down.
 136         xCursor.gotoOffset( 0, 1 );
 137         xCursor.getCellByPosition( 0, 0 ).setFormula(
 138             "Beyond of the last filled cell." );
 139
 140
 141         // *** Modifying COLUMNS and ROWS ***
 142         Console.WriteLine( "*** Sample for services table.TableRows and " +
 143                            "table.TableColumns ***" );
 144
 145         unoidl.com.sun.star.table.XColumnRowRange xCRRange =
 146             (unoidl.com.sun.star.table.XColumnRowRange) xSheet;
 147         unoidl.com.sun.star.table.XTableColumns xColumns =
 148             xCRRange.getColumns();
 149         unoidl.com.sun.star.table.XTableRows xRows = xCRRange.getRows();
 150
 151         // Get column C by index (interface XIndexAccess).
 152         uno.Any aColumnObj = xColumns.getByIndex( 2 );
 153         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value;
 154         xPropSet.setPropertyValue( "Width", new uno.Any( (Int32) 5000 ) );
 155
 156         // Get the name of the column.
 157         unoidl.com.sun.star.container.XNamed xNamed =
 158             (unoidl.com.sun.star.container.XNamed) aColumnObj.Value;
 159         aText = "The name of this column is " + xNamed.getName() + ".";
 160         xSheet.getCellByPosition( 2, 2 ).setFormula( aText );
 161
 162         // Get column D by name (interface XNameAccess).
 163         unoidl.com.sun.star.container.XNameAccess xColumnsName =
 164             (unoidl.com.sun.star.container.XNameAccess) xColumns;
 165
 166         aColumnObj = xColumnsName.getByName( "D" );
 167         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value;
 168         xPropSet.setPropertyValue(
 169             "IsVisible", new uno.Any( (Boolean) false ) );
 170
 171         // Get row 7 by index (interface XIndexAccess)
 172         uno.Any aRowObj = xRows.getByIndex( 6 );
 173         xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aRowObj.Value;
 174         xPropSet.setPropertyValue( "Height", new uno.Any( (Int32) 5000 ) );
 175
 176         xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." );
 177
 178         // Create a cell series with the values 1 ... 7.
 179         for (int nRow = 8; nRow < 15; ++nRow)
 180             xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 );
 181         // Insert a row between 1 and 2
 182         xRows.insertByIndex( 9, 1 );
 183         // Delete the rows with the values 3 and 4.
 184         xRows.removeByIndex( 11, 2 );
 185
 186         // *** Inserting CHARTS ***
 187         Console.WriteLine( "*** Sample for service table.TableCharts ***" );
 188
 189         unoidl.com.sun.star.table.XTableChartsSupplier xChartsSupp =
 190             (unoidl.com.sun.star.table.XTableChartsSupplier) xSheet;
 191         unoidl.com.sun.star.table.XTableCharts xCharts =
 192             xChartsSupp.getCharts();
 193
 194         // The chart will base on the last cell series, initializing all values.
 195         String aName = "newChart";
 196         unoidl.com.sun.star.awt.Rectangle aRect =
 197             new unoidl.com.sun.star.awt.Rectangle();
 198         aRect.X = 10000;
 199         aRect.Y = 3000;
 200         aRect.Width = aRect.Height = 5000;
 201         unoidl.com.sun.star.table.CellRangeAddress[] aRanges =
 202             new unoidl.com.sun.star.table.CellRangeAddress[1];
 203         aRanges[0] = createCellRangeAddress( xSheet, "A9:A14" );
 204
 205         // Create the chart.
 206         xCharts.addNewByName( aName, aRect, aRanges, false, false );
 207
 208         // Get the chart by name.
 209         uno.Any aChartObj = xCharts.getByName( aName );
 210         unoidl.com.sun.star.table.XTableChart xChart =
 211             (unoidl.com.sun.star.table.XTableChart) aChartObj.Value;
 212
 213         // Query the state of row and column headers.
 214         aText = "Chart has column headers: ";
 215         aText += xChart.getHasColumnHeaders() ? "yes" : "no";
 216         xSheet.getCellByPosition( 2, 8 ).setFormula( aText );
 217         aText = "Chart has row headers: ";
 218         aText += xChart.getHasRowHeaders() ? "yes" : "no";
 219         xSheet.getCellByPosition( 2, 9 ).setFormula( aText );
 220     }
 221
 222 }