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