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