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 
24 import com.sun.star.uno.UnoRuntime;
25 import com.sun.star.uno.RuntimeException;
26 
27 
28 // __________  implementation  ____________________________________
29 
30 /** Create a spreadsheet document and provide access to table contents.
31  */
32 public class GeneralTableSample extends SpreadsheetDocHelper
33 {
34 
35 // ________________________________________________________________
36 
main( String args[] )37     public static void main( String args[] )
38     {
39         try
40         {
41             GeneralTableSample aSample = new GeneralTableSample( args );
42             aSample.doSampleFunction();
43         }
44         catch (Exception ex)
45         {
46             System.out.println( "Error: Sample caught exception!\nException Message = "
47                                 + ex.getMessage());
48             ex.printStackTrace();
49             System.exit( 1 );
50         }
51 
52         System.out.println( "Sample done." );
53         System.exit( 0 );
54     }
55 
56 // ________________________________________________________________
57 
58     /// This sample function modifies cells and cell ranges.
doSampleFunction()59     public void doSampleFunction() throws RuntimeException, Exception
60     {
61         // for common usage
62         com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
63         com.sun.star.beans.XPropertySet xPropSet = null;
64         com.sun.star.table.XCell xCell = null;
65         com.sun.star.table.XCellRange xCellRange = null;
66 
67     // *** Access and modify a VALUE CELL ***
68         System.out.println( "*** 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         boolean 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         com.sun.star.text.XText xCellText = (com.sun.star.text.XText)
92             UnoRuntime.queryInterface( com.sun.star.text.XText.class, xCell );
93         com.sun.star.text.XTextCursor xTextCursor = xCellText.createTextCursor();
94         xCellText.insertString( xTextCursor, aText, false );
95 
96     // *** Change cell properties ***
97         int nValue = bValid ? 0x00FF00 : 0xFF4040;
98         xPropSet = (com.sun.star.beans.XPropertySet)
99             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCell );
100         xPropSet.setPropertyValue( "CellBackColor", new Integer( nValue ) );
101 
102 
103     // *** Accessing a CELL RANGE ***
104         System.out.println( "*** Sample for service table.CellRange ***" );
105 
106         // Accessing a cell range over its position.
107         xCellRange = xSheet.getCellRangeByPosition( 2, 0, 3, 1 );
108 
109         // Change properties of the range.
110         xPropSet = (com.sun.star.beans.XPropertySet)
111             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
112         xPropSet.setPropertyValue( "CellBackColor", new Integer( 0x8080FF ) );
113 
114         // Accessing a cell range over its name.
115         xCellRange = xSheet.getCellRangeByName( "C4:D5" );
116 
117         // Change properties of the range.
118         xPropSet = (com.sun.star.beans.XPropertySet)
119             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, xCellRange );
120         xPropSet.setPropertyValue( "CellBackColor", new Integer( 0xFFFF80 ) );
121 
122 
123     // *** Using the CELL CURSOR to add some data below of the filled area ***
124         System.out.println( "*** Sample for service table.CellCursor ***" );
125 
126         // Create a cursor using the XSpreadsheet method createCursorByRange()
127         xCellRange = xSheet.getCellRangeByName( "A1" );
128         com.sun.star.sheet.XSheetCellRange xSheetCellRange = (com.sun.star.sheet.XSheetCellRange)
129             UnoRuntime.queryInterface( com.sun.star.sheet.XSheetCellRange.class, xCellRange );
130 
131         com.sun.star.sheet.XSheetCellCursor xSheetCellCursor =
132             xSheet.createCursorByRange( xSheetCellRange );
133         com.sun.star.table.XCellCursor xCursor = (com.sun.star.table.XCellCursor)
134             UnoRuntime.queryInterface( com.sun.star.table.XCellCursor.class, xSheetCellCursor );
135 
136         // Move to the last filled cell.
137         xCursor.gotoEnd();
138         // Move one row down.
139         xCursor.gotoOffset( 0, 1 );
140         xCursor.getCellByPosition( 0, 0 ).setFormula( "Beyond of the last filled cell." );
141 
142 
143     // *** Modifying COLUMNS and ROWS ***
144         System.out.println( "*** Sample for services table.TableRows and table.TableColumns ***" );
145 
146         com.sun.star.table.XColumnRowRange xCRRange = (com.sun.star.table.XColumnRowRange)
147             UnoRuntime.queryInterface( com.sun.star.table.XColumnRowRange.class, xSheet );
148         com.sun.star.table.XTableColumns xColumns = xCRRange.getColumns();
149         com.sun.star.table.XTableRows xRows = xCRRange.getRows();
150 
151         // Get column C by index (interface XIndexAccess).
152         Object aColumnObj = xColumns.getByIndex( 2 );
153         xPropSet = (com.sun.star.beans.XPropertySet)
154             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj );
155         xPropSet.setPropertyValue( "Width", new Integer( 5000 ) );
156 
157         // Get the name of the column.
158         com.sun.star.container.XNamed xNamed = (com.sun.star.container.XNamed)
159             UnoRuntime.queryInterface( com.sun.star.container.XNamed.class, aColumnObj );
160         aText = "The name of this column is " + xNamed.getName() + ".";
161         xSheet.getCellByPosition( 2, 2 ).setFormula( aText );
162 
163         // Get column D by name (interface XNameAccess).
164         com.sun.star.container.XNameAccess xColumnsName = (com.sun.star.container.XNameAccess)
165             UnoRuntime.queryInterface( com.sun.star.container.XNameAccess.class, xColumns );
166 
167         aColumnObj = xColumnsName.getByName( "D" );
168         xPropSet = (com.sun.star.beans.XPropertySet)
169             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aColumnObj );
170         xPropSet.setPropertyValue( "IsVisible", new Boolean( false ) );
171 
172         // Get row 7 by index (interface XIndexAccess)
173         Object aRowObj = xRows.getByIndex( 6 );
174         xPropSet = (com.sun.star.beans.XPropertySet)
175             UnoRuntime.queryInterface( com.sun.star.beans.XPropertySet.class, aRowObj );
176         xPropSet.setPropertyValue( "Height", new Integer( 5000 ) );
177 
178         xSheet.getCellByPosition( 2, 6 ).setFormula( "What a big cell." );
179 
180         // Create a cell series with the values 1 ... 7.
181         for (int nRow = 8; nRow < 15; ++nRow)
182             xSheet.getCellByPosition( 0, nRow ).setValue( nRow - 7 );
183         // Insert a row between 1 and 2
184         xRows.insertByIndex( 9, 1 );
185         // Delete the rows with the values 3 and 4.
186         xRows.removeByIndex( 11, 2 );
187 
188     // *** Inserting CHARTS ***
189         System.out.println( "*** Sample for service table.TableCharts ***" );
190 
191         com.sun.star.table.XTableChartsSupplier xChartsSupp =
192             (com.sun.star.table.XTableChartsSupplier) UnoRuntime.queryInterface(
193                 com.sun.star.table.XTableChartsSupplier.class, xSheet );
194         com.sun.star.table.XTableCharts xCharts = xChartsSupp.getCharts();
195 
196         // The chart will base on the last cell series, initializing all values.
197         String aName = "newChart";
198         com.sun.star.awt.Rectangle aRect = new com.sun.star.awt.Rectangle();
199         aRect.X = 10000;
200         aRect.Y = 3000;
201         aRect.Width = aRect.Height = 5000;
202         com.sun.star.table.CellRangeAddress[] aRanges = new 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         Object aChartObj = xCharts.getByName( aName );
210         com.sun.star.table.XTableChart xChart = (com.sun.star.table.XTableChart)
211             UnoRuntime.queryInterface( com.sun.star.table.XTableChart.class, aChartObj );
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 // ________________________________________________________________
223 
GeneralTableSample( String[] args )224     public GeneralTableSample( String[] args )
225     {
226         super( args );
227     }
228 
229 // ________________________________________________________________
230 }
231