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 //***************************************************************************
25 // comment: Step 1: get the remote component context from the office
26 //          Step 2: open an empty calc document
27 //          Step 3: create cell styles
28 //          Step 4: get the sheet an insert some data
29 //          Step 5: apply the created cell syles
30 //          Step 6: insert a 3D Chart
31 //***************************************************************************
32 
33 import com.sun.star.awt.Rectangle;
34 
35 import com.sun.star.beans.PropertyValue;
36 import com.sun.star.beans.XPropertySet;
37 
38 import com.sun.star.chart.XDiagram;
39 import com.sun.star.chart.XChartDocument;
40 
41 import com.sun.star.container.XIndexAccess;
42 import com.sun.star.container.XNameAccess;
43 import com.sun.star.container.XNameContainer;
44 
45 import com.sun.star.document.XEmbeddedObjectSupplier;
46 
47 import com.sun.star.frame.XDesktop;
48 import com.sun.star.frame.XComponentLoader;
49 
50 import com.sun.star.lang.XComponent;
51 import com.sun.star.lang.XMultiServiceFactory;
52 import com.sun.star.lang.XMultiComponentFactory;
53 
54 import com.sun.star.uno.UnoRuntime;
55 import com.sun.star.uno.XInterface;
56 import com.sun.star.uno.XComponentContext;
57 
58 import com.sun.star.sheet.XCellRangeAddressable;
59 import com.sun.star.sheet.XSpreadsheet;
60 import com.sun.star.sheet.XSpreadsheets;
61 import com.sun.star.sheet.XSpreadsheetDocument;
62 
63 import com.sun.star.style.XStyleFamiliesSupplier;
64 
65 import com.sun.star.table.CellRangeAddress;
66 import com.sun.star.table.XCell;
67 import com.sun.star.table.XCellRange;
68 import com.sun.star.table.XTableChart;
69 import com.sun.star.table.XTableCharts;
70 import com.sun.star.table.XTableChartsSupplier;
71 
72 
73 public class SCalc  {
74 
main(String args[])75     public static void main(String args[]) {
76 
77         //oooooooooooooooooooooooooooStep 1oooooooooooooooooooooooooooooooooooooooooo
78         // call UNO bootstrap method and get the remote component context form
79         // the a running office (office will be started if necessary)
80         //***************************************************************************
81         XComponentContext xContext = null;
82 
83         // get the remote office component context
84         try {
85             xContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
86             System.out.println("Connected to a running office ...");
87         } catch( Exception e) {
88             e.printStackTrace(System.err);
89             System.exit(1);
90         }
91 
92         //oooooooooooooooooooooooooooStep 2oooooooooooooooooooooooooooooooooooooooooo
93         // open an empty document. In this case it's a calc document.
94         // For this purpose an instance of com.sun.star.frame.Desktop
95         // is created. The desktop provides the XComponentLoader interface,
96         // which is used to open the document via loadComponentFromURL
97         //***************************************************************************
98 
99         //Open document
100 
101         //Calc
102         XSpreadsheetDocument myDoc = null;
103 //        XCell oCell = null;
104 
105         System.out.println("Opening an empty Calc document");
106         myDoc = openCalc(xContext);
107 
108         //***************************************************************************
109 
110 
111         //oooooooooooooooooooooooooooStep 3oooooooooooooooooooooooooooooooooooooooooo
112         // create cell styles.
113         // For this purpose get the StyleFamiliesSupplier and the the familiy
114         // CellStyle. Create an instance of com.sun.star.style.CellStyle and
115         // add it to the family. Now change some properties
116         //***************************************************************************
117 
118         try {
119             XStyleFamiliesSupplier xSFS = (XStyleFamiliesSupplier)
120                 UnoRuntime.queryInterface(XStyleFamiliesSupplier.class, myDoc);
121             XNameAccess xSF = (XNameAccess) xSFS.getStyleFamilies();
122             XNameAccess xCS = (XNameAccess) UnoRuntime.queryInterface(
123                 XNameAccess.class, xSF.getByName("CellStyles"));
124             XMultiServiceFactory oDocMSF = (XMultiServiceFactory)
125                 UnoRuntime.queryInterface(XMultiServiceFactory.class, myDoc );
126             XNameContainer oStyleFamilyNameContainer = (XNameContainer)
127                 UnoRuntime.queryInterface(
128                 XNameContainer.class, xCS);
129             XInterface oInt1 = (XInterface) oDocMSF.createInstance(
130                 "com.sun.star.style.CellStyle");
131             oStyleFamilyNameContainer.insertByName("My Style", oInt1);
132             XPropertySet oCPS1 = (XPropertySet)UnoRuntime.queryInterface(
133                 XPropertySet.class, oInt1 );
134             oCPS1.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));
135             oCPS1.setPropertyValue("CellBackColor",new Integer(6710932));
136             oCPS1.setPropertyValue("CharColor",new Integer(16777215));
137             XInterface oInt2 = (XInterface) oDocMSF.createInstance(
138                 "com.sun.star.style.CellStyle");
139             oStyleFamilyNameContainer.insertByName("My Style2", oInt2);
140             XPropertySet oCPS2 = (XPropertySet)UnoRuntime.queryInterface(
141                 XPropertySet.class, oInt2 );
142             oCPS2.setPropertyValue("IsCellBackgroundTransparent", new Boolean(false));
143             oCPS2.setPropertyValue("CellBackColor",new Integer(13421823));
144         } catch (Exception e) {
145             e.printStackTrace(System.err);
146         }
147 
148         //***************************************************************************
149 
150         //oooooooooooooooooooooooooooStep 4oooooooooooooooooooooooooooooooooooooooooo
151         // get the sheet an insert some data.
152         // Get the sheets from the document and then the first from this container.
153         // Now some data can be inserted. For this purpose get a Cell via
154         // getCellByPosition and insert into this cell via setValue() (for floats)
155         // or setFormula() for formulas and Strings
156         //***************************************************************************
157 
158 
159         XSpreadsheet xSheet=null;
160 
161         try {
162             System.out.println("Getting spreadsheet") ;
163             XSpreadsheets xSheets = myDoc.getSheets() ;
164             XIndexAccess oIndexSheets = (XIndexAccess) UnoRuntime.queryInterface(
165                 XIndexAccess.class, xSheets);
166             xSheet = (XSpreadsheet) UnoRuntime.queryInterface(
167                 XSpreadsheet.class, oIndexSheets.getByIndex(0));
168 
169         } catch (Exception e) {
170             System.out.println("Couldn't get Sheet " +e);
171             e.printStackTrace(System.err);
172         }
173 
174 
175 
176         System.out.println("Creating the Header") ;
177 
178         insertIntoCell(1,0,"JAN",xSheet,"");
179         insertIntoCell(2,0,"FEB",xSheet,"");
180         insertIntoCell(3,0,"MAR",xSheet,"");
181         insertIntoCell(4,0,"APR",xSheet,"");
182         insertIntoCell(5,0,"MAI",xSheet,"");
183         insertIntoCell(6,0,"JUN",xSheet,"");
184         insertIntoCell(7,0,"JUL",xSheet,"");
185         insertIntoCell(8,0,"AUG",xSheet,"");
186         insertIntoCell(9,0,"SEP",xSheet,"");
187         insertIntoCell(10,0,"OCT",xSheet,"");
188         insertIntoCell(11,0,"NOV",xSheet,"");
189         insertIntoCell(12,0,"DEC",xSheet,"");
190         insertIntoCell(13,0,"SUM",xSheet,"");
191 
192 
193         System.out.println("Fill the lines");
194 
195         insertIntoCell(0,1,"Smith",xSheet,"");
196         insertIntoCell(1,1,"42",xSheet,"V");
197         insertIntoCell(2,1,"58.9",xSheet,"V");
198         insertIntoCell(3,1,"-66.5",xSheet,"V");
199         insertIntoCell(4,1,"43.4",xSheet,"V");
200         insertIntoCell(5,1,"44.5",xSheet,"V");
201         insertIntoCell(6,1,"45.3",xSheet,"V");
202         insertIntoCell(7,1,"-67.3",xSheet,"V");
203         insertIntoCell(8,1,"30.5",xSheet,"V");
204         insertIntoCell(9,1,"23.2",xSheet,"V");
205         insertIntoCell(10,1,"-97.3",xSheet,"V");
206         insertIntoCell(11,1,"22.4",xSheet,"V");
207         insertIntoCell(12,1,"23.5",xSheet,"V");
208         insertIntoCell(13,1,"=SUM(B2:M2)",xSheet,"");
209 
210 
211         insertIntoCell(0,2,"Jones",xSheet,"");
212         insertIntoCell(1,2,"21",xSheet,"V");
213         insertIntoCell(2,2,"40.9",xSheet,"V");
214         insertIntoCell(3,2,"-57.5",xSheet,"V");
215         insertIntoCell(4,2,"-23.4",xSheet,"V");
216         insertIntoCell(5,2,"34.5",xSheet,"V");
217         insertIntoCell(6,2,"59.3",xSheet,"V");
218         insertIntoCell(7,2,"27.3",xSheet,"V");
219         insertIntoCell(8,2,"-38.5",xSheet,"V");
220         insertIntoCell(9,2,"43.2",xSheet,"V");
221         insertIntoCell(10,2,"57.3",xSheet,"V");
222         insertIntoCell(11,2,"25.4",xSheet,"V");
223         insertIntoCell(12,2,"28.5",xSheet,"V");
224         insertIntoCell(13,2,"=SUM(B3:M3)",xSheet,"");
225 
226         insertIntoCell(0,3,"Brown",xSheet,"");
227         insertIntoCell(1,3,"31.45",xSheet,"V");
228         insertIntoCell(2,3,"-20.9",xSheet,"V");
229         insertIntoCell(3,3,"-117.5",xSheet,"V");
230         insertIntoCell(4,3,"23.4",xSheet,"V");
231         insertIntoCell(5,3,"-114.5",xSheet,"V");
232         insertIntoCell(6,3,"115.3",xSheet,"V");
233         insertIntoCell(7,3,"-171.3",xSheet,"V");
234         insertIntoCell(8,3,"89.5",xSheet,"V");
235         insertIntoCell(9,3,"41.2",xSheet,"V");
236         insertIntoCell(10,3,"71.3",xSheet,"V");
237         insertIntoCell(11,3,"25.4",xSheet,"V");
238         insertIntoCell(12,3,"38.5",xSheet,"V");
239         insertIntoCell(13,3,"=SUM(A4:L4)",xSheet,"");
240 
241         //***************************************************************************
242 
243         //oooooooooooooooooooooooooooStep 5oooooooooooooooooooooooooooooooooooooooooo
244         // apply the created cell style.
245         // For this purpose get the PropertySet of the Cell and change the
246         // property CellStyle to the appropriate value.
247         //***************************************************************************
248 
249         // change backcolor
250         chgbColor( 1 , 0, 13, 0, "My Style", xSheet );
251         chgbColor( 0 , 1, 0, 3, "My Style", xSheet );
252         chgbColor( 1 , 1, 13, 3, "My Style2", xSheet );
253 
254         //***************************************************************************
255 
256         //oooooooooooooooooooooooooooStep 6oooooooooooooooooooooooooooooooooooooooooo
257         // insert a 3D chart.
258         // get the CellRange which holds the data for the chart and its RangeAddress
259         // get the TableChartSupplier from the sheet and then the TableCharts from it.
260         // add a new chart based on the data to the TableCharts.
261         // get the ChartDocument, which provide the Diagramm. Change the properties
262         // Dim3D (3 dimension) and String (the title) of the diagramm.
263         //***************************************************************************
264 
265         // insert a chart
266 
267         Rectangle oRect = new Rectangle();
268         oRect.X = 500;
269         oRect.Y = 3000;
270         oRect.Width = 25000;
271         oRect.Height = 11000;
272 
273         XCellRange oRange = (XCellRange)UnoRuntime.queryInterface(
274             XCellRange.class, xSheet);
275         XCellRange myRange = oRange.getCellRangeByName("A1:N4");
276         XCellRangeAddressable oRangeAddr = (XCellRangeAddressable)
277             UnoRuntime.queryInterface(XCellRangeAddressable.class, myRange);
278         CellRangeAddress myAddr = oRangeAddr.getRangeAddress();
279 
280         CellRangeAddress[] oAddr = new CellRangeAddress[1];
281         oAddr[0] = myAddr;
282         XTableChartsSupplier oSupp = (XTableChartsSupplier)UnoRuntime.queryInterface(
283             XTableChartsSupplier.class, xSheet);
284 
285         XTableChart oChart = null;
286 
287         System.out.println("Insert Chart");
288 
289         XTableCharts oCharts = oSupp.getCharts();
290         oCharts.addNewByName("Example", oRect, oAddr, true, true);
291 
292         // get the diagramm and Change some of the properties
293 
294         try {
295             oChart = (XTableChart) (UnoRuntime.queryInterface(
296                 XTableChart.class, ((XNameAccess)UnoRuntime.queryInterface(
297                             XNameAccess.class, oCharts)).getByName("Example")));
298             XEmbeddedObjectSupplier oEOS = (XEmbeddedObjectSupplier)
299                 UnoRuntime.queryInterface(XEmbeddedObjectSupplier.class, oChart);
300             XInterface oInt = oEOS.getEmbeddedObject();
301             XChartDocument xChart = (XChartDocument) UnoRuntime.queryInterface(
302                 XChartDocument.class,oInt);
303             XDiagram oDiag = (XDiagram) xChart.getDiagram();
304             System.out.println("Change Diagramm to 3D");
305             XPropertySet oCPS = (XPropertySet)UnoRuntime.queryInterface(
306                 XPropertySet.class, oDiag );
307             oCPS.setPropertyValue("Dim3D", new Boolean(true));
308             System.out.println("Change the title");
309             Thread.sleep(200);
310             XPropertySet oTPS = (XPropertySet)UnoRuntime.queryInterface(
311                 XPropertySet.class, xChart.getTitle() );
312             oTPS.setPropertyValue("String","The new title");
313             //oDiag.Dim3D();
314         } catch (Exception e){
315             System.err.println("Changin Properties failed "+e);
316             e.printStackTrace(System.err);
317         }
318 
319         System.out.println("done");
320         System.exit(0);
321     }
322 
openCalc(XComponentContext xContext)323     public static XSpreadsheetDocument openCalc(XComponentContext xContext)
324     {
325         //define variables
326         XMultiComponentFactory xMCF = null;
327         XComponentLoader xCLoader;
328         XSpreadsheetDocument xSpreadSheetDoc = null;
329         XComponent xComp = null;
330 
331         try {
332             // get the servie manager rom the office
333             xMCF = xContext.getServiceManager();
334 
335             // create a new instance of the the desktop
336             Object oDesktop = xMCF.createInstanceWithContext(
337                 "com.sun.star.frame.Desktop", xContext );
338 
339             // query the desktop object for the XComponentLoader
340             xCLoader = ( XComponentLoader ) UnoRuntime.queryInterface(
341                 XComponentLoader.class, oDesktop );
342 
343             PropertyValue [] szEmptyArgs = new PropertyValue [0];
344             String strDoc = "private:factory/scalc";
345 
346             xComp = xCLoader.loadComponentFromURL(strDoc, "_blank", 0, szEmptyArgs );
347             xSpreadSheetDoc = (XSpreadsheetDocument) UnoRuntime.queryInterface(
348                 XSpreadsheetDocument.class, xComp);
349 
350         } catch(Exception e){
351             System.err.println(" Exception " + e);
352             e.printStackTrace(System.err);
353         }
354 
355         return xSpreadSheetDoc;
356     }
357 
358 
insertIntoCell(int CellX, int CellY, String theValue, XSpreadsheet TT1, String flag)359     public static void insertIntoCell(int CellX, int CellY, String theValue,
360                                       XSpreadsheet TT1, String flag)
361     {
362         XCell xCell = null;
363 
364         try {
365             xCell = TT1.getCellByPosition(CellX, CellY);
366         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
367             System.err.println("Could not get Cell");
368             ex.printStackTrace(System.err);
369         }
370 
371         if (flag.equals("V")) {
372             xCell.setValue((new Float(theValue)).floatValue());
373         } else {
374             xCell.setFormula(theValue);
375         }
376 
377     }
378 
chgbColor( int x1, int y1, int x2, int y2, String template, XSpreadsheet TT )379     public static void chgbColor( int x1, int y1, int x2, int y2,
380                                   String template, XSpreadsheet TT )
381     {
382         XCellRange xCR = null;
383         try {
384             xCR = TT.getCellRangeByPosition(x1,y1,x2,y2);
385         } catch (com.sun.star.lang.IndexOutOfBoundsException ex) {
386             System.err.println("Could not get CellRange");
387             ex.printStackTrace(System.err);
388         }
389 
390         XPropertySet xCPS = (XPropertySet)UnoRuntime.queryInterface(
391             XPropertySet.class, xCR );
392 
393         try {
394             xCPS.setPropertyValue("CellStyle", template);
395         } catch (Exception e) {
396             System.err.println("Can't change colors chgbColor" + e);
397             e.printStackTrace(System.err);
398         }
399     }
400 
401 }
402