?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
cid#1607171 Data race condition
[LibreOffice.git] / odk / examples / CLI / CSharp / Spreadsheet / SpreadsheetSample.cs
blob812121043e9f1794b2dc42acd293152774bc1895
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 and modify a spreadsheet document.
24 */
25 public class SpreadsheetSample : SpreadsheetDocHelper
26 {
27
28 public static void Main( String [] args )
29 {
30 try
31 {
32 using ( SpreadsheetSample aSample = new SpreadsheetSample( args ) )
33 {
34 aSample.doSampleFunctions();
35 }
36 Console.WriteLine( "\nSamples done." );
37 }
38 catch (Exception ex)
39 {
40 Console.WriteLine( "Sample caught exception! " + ex );
41 }
42 }
43
44 public SpreadsheetSample( String[] args )
45 : base( args )
46 {
47 }
48
49 /** This sample function performs all changes on the document. */
50 public void doSampleFunctions()
51 {
52 doCellSamples();
53 doCellRangeSamples();
54 doCellRangesSamples();
55 doCellCursorSamples();
56 doFormattingSamples();
57 doDocumentSamples();
58 doDatabaseSamples();
59 doDataPilotSamples();
60 doNamedRangesSamples();
61 doFunctionAccessSamples();
62 doApplicationSettingsSamples();
63 }
64
65
66
67 /** All samples regarding the service com.sun.star.sheet.SheetCell. */
68 private void doCellSamples()
69 {
70 Console.WriteLine( "\n*** Samples for service sheet.SheetCell ***\n" );
71 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
72 unoidl.com.sun.star.table.XCell xCell = null;
73 unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
74 String aText;
75 prepareRange( xSheet, "A1:C7", "Cells and Cell Ranges" );
76
77 // --- Get cell B3 by position - (column, row) ---
78 xCell = xSheet.getCellByPosition( 1, 2 );
79
80 // --- Insert two text paragraphs into the cell. ---
81 unoidl.com.sun.star.text.XText xText =
82 (unoidl.com.sun.star.text.XText) xCell;
83 unoidl.com.sun.star.text.XTextCursor xTextCursor =
84 xText.createTextCursor();
85
86 xText.insertString( xTextCursor, "Text in first line.", false );
87 xText.insertControlCharacter( xTextCursor,
88 unoidl.com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK, false );
89 xText.insertString( xTextCursor, "And a ", false );
90
91 // create a hyperlink
92 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceMan =
93 (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
94 Object aHyperlinkObj =
95 xServiceMan.createInstance( "com.sun.star.text.TextField.URL" );
96 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aHyperlinkObj;
97 xPropSet.setPropertyValue(
98 "URL", new uno.Any( "http://www.example.org" ) );
99 xPropSet.setPropertyValue(
100 "Representation", new uno.Any( "hyperlink" ) );
101 // ... and insert
102 unoidl.com.sun.star.text.XTextContent xContent =
103 (unoidl.com.sun.star.text.XTextContent) aHyperlinkObj;
104 xText.insertTextContent( xTextCursor, xContent, false );
105
106 // --- Query the separate paragraphs. ---
107 unoidl.com.sun.star.container.XEnumerationAccess xParaEA =
108 (unoidl.com.sun.star.container.XEnumerationAccess) xCell;
109 unoidl.com.sun.star.container.XEnumeration xParaEnum =
110 xParaEA.createEnumeration();
111 // Go through the paragraphs
112 while( xParaEnum.hasMoreElements() )
113 {
114 uno.Any aPortionObj = xParaEnum.nextElement();
115 unoidl.com.sun.star.container.XEnumerationAccess xPortionEA =
116 (unoidl.com.sun.star.container.XEnumerationAccess)
117 aPortionObj.Value;
118 unoidl.com.sun.star.container.XEnumeration xPortionEnum =
119 xPortionEA.createEnumeration();
120 aText = "";
121 // Go through all text portions of a paragraph and construct string.
122 while( xPortionEnum.hasMoreElements() )
123 {
124 unoidl.com.sun.star.text.XTextRange xRange =
125 (unoidl.com.sun.star.text.XTextRange)
126 xPortionEnum.nextElement().Value;
127 aText += xRange.getString();
128 }
129 Console.WriteLine( "Paragraph text: " + aText );
130 }
131
132
133 // --- Change cell properties. ---
134 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCell;
135 // from styles.CharacterProperties
136 xPropSet.setPropertyValue(
137 "CharColor", new uno.Any( (Int32) 0x003399 ) );
138 xPropSet.setPropertyValue(
139 "CharHeight", new uno.Any( (Single) 20.0 ) );
140 // from styles.ParagraphProperties
141 xPropSet.setPropertyValue(
142 "ParaLeftMargin", new uno.Any( (Int32) 500 ) );
143 // from table.CellProperties
144 xPropSet.setPropertyValue(
145 "IsCellBackgroundTransparent", new uno.Any( false ) );
146 xPropSet.setPropertyValue(
147 "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) );
148
149
150 // --- Get cell address. ---
151 unoidl.com.sun.star.sheet.XCellAddressable xCellAddr =
152 (unoidl.com.sun.star.sheet.XCellAddressable) xCell;
153 unoidl.com.sun.star.table.CellAddress aAddress =
154 xCellAddr.getCellAddress();
155 aText = "Address of this cell: Column=" + aAddress.Column;
156 aText += "; Row=" + aAddress.Row;
157 aText += "; Sheet=" + aAddress.Sheet;
158 Console.WriteLine( aText );
159
160
161 // --- Insert an annotation ---
162 unoidl.com.sun.star.sheet.XSheetAnnotationsSupplier xAnnotationsSupp =
163 (unoidl.com.sun.star.sheet.XSheetAnnotationsSupplier) xSheet;
164 unoidl.com.sun.star.sheet.XSheetAnnotations xAnnotations =
165 xAnnotationsSupp.getAnnotations();
166 xAnnotations.insertNew( aAddress, "This is an annotation" );
167
168 unoidl.com.sun.star.sheet.XSheetAnnotationAnchor xAnnotAnchor =
169 (unoidl.com.sun.star.sheet.XSheetAnnotationAnchor) xCell;
170 unoidl.com.sun.star.sheet.XSheetAnnotation xAnnotation =
171 xAnnotAnchor.getAnnotation();
172 xAnnotation.setIsVisible( true );
173 }
174
175
176
177 /** All samples regarding the service com.sun.star.sheet.SheetCellRange. */
178 private void doCellRangeSamples()
179 {
180 Console.WriteLine(
181 "\n*** Samples for service sheet.SheetCellRange ***\n" );
182 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
183 unoidl.com.sun.star.table.XCellRange xCellRange = null;
184 unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
185 unoidl.com.sun.star.table.CellRangeAddress aRangeAddress = null;
186
187 // Preparation
188 setFormula( xSheet, "B5", "First cell" );
189 setFormula( xSheet, "B6", "Second cell" );
190 // Get cell range B5:B6 by position - (column, row, column, row)
191 xCellRange = xSheet.getCellRangeByPosition( 1, 4, 1, 5 );
192
193
194 // --- Change cell range properties. ---
195 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
196 // from com.sun.star.styles.CharacterProperties
197 xPropSet.setPropertyValue(
198 "CharColor", new uno.Any( (Int32) 0x003399 ) );
199 xPropSet.setPropertyValue(
200 "CharHeight", new uno.Any( (Single) 20.0 ) );
201 // from com.sun.star.styles.ParagraphProperties
202 xPropSet.setPropertyValue(
203 "ParaLeftMargin", new uno.Any( (Int32) 500 ) );
204 // from com.sun.star.table.CellProperties
205 xPropSet.setPropertyValue(
206 "IsCellBackgroundTransparent", new uno.Any( false ) );
207 xPropSet.setPropertyValue(
208 "CellBackColor", new uno.Any( (Int32) 0x99CCFF ) );
209
210
211 // --- Replace text in all cells. ---
212 unoidl.com.sun.star.util.XReplaceable xReplace =
213 (unoidl.com.sun.star.util.XReplaceable) xCellRange;
214 unoidl.com.sun.star.util.XReplaceDescriptor xReplaceDesc =
215 xReplace.createReplaceDescriptor();
216 xReplaceDesc.setSearchString( "cell" );
217 xReplaceDesc.setReplaceString( "text" );
218 // property SearchWords searches for whole cells!
219 xReplaceDesc.setPropertyValue( "SearchWords", new uno.Any( false ) );
220 int nCount = xReplace.replaceAll( xReplaceDesc );
221 Console.WriteLine( "Search text replaced " + nCount + " times." );
222
223
224 // --- Merge cells. ---
225 xCellRange = xSheet.getCellRangeByName( "F3:G6" );
226 prepareRange( xSheet, "E1:H7", "XMergeable" );
227 unoidl.com.sun.star.util.XMergeable xMerge =
228 (unoidl.com.sun.star.util.XMergeable) xCellRange;
229 xMerge.merge( true );
230
231
232 // --- Change indentation. ---
233 /* does not work (bug in XIndent implementation)
234 prepareRange( xSheet, "I20:I23", "XIndent" );
235 setValue( xSheet, "I21", 1 );
236 setValue( xSheet, "I22", 1 );
237 setValue( xSheet, "I23", 1 );
238
239 xCellRange = xSheet.getCellRangeByName( "I21:I22" );
240 unoidl.com.sun.star.util.XIndent xIndent =
241 (unoidl.com.sun.star.util.XIndent) xCellRange;
242 xIndent.incrementIndent();
243
244 xCellRange = xSheet.getCellRangeByName( "I22:I23" );
245 xIndent = (unoidl.com.sun.star.util.XIndent) xCellRange;
246 xIndent.incrementIndent();
247 */
248
249
250 // --- Column properties. ---
251 xCellRange = xSheet.getCellRangeByName( "B1" );
252 unoidl.com.sun.star.table.XColumnRowRange xColRowRange =
253 (unoidl.com.sun.star.table.XColumnRowRange) xCellRange;
254 unoidl.com.sun.star.table.XTableColumns xColumns =
255 xColRowRange.getColumns();
256
257 uno.Any aColumnObj = xColumns.getByIndex( 0 );
258 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aColumnObj.Value;
259 xPropSet.setPropertyValue( "Width", new uno.Any( (Int32) 6000 ) );
260
261 unoidl.com.sun.star.container.XNamed xNamed =
262 (unoidl.com.sun.star.container.XNamed) aColumnObj.Value;
263 Console.WriteLine(
264 "The name of the wide column is " + xNamed.getName() + "." );
265
266
267 // --- Cell range data ---
268 prepareRange( xSheet, "A9:C30", "XCellRangeData" );
269
270 xCellRange = xSheet.getCellRangeByName( "A10:C30" );
271 unoidl.com.sun.star.sheet.XCellRangeData xData =
272 (unoidl.com.sun.star.sheet.XCellRangeData) xCellRange;
273 uno.Any [][] aValues =
274 {
275 new uno.Any [] { new uno.Any( "Name" ),
276 new uno.Any( "Fruit" ),
277 new uno.Any( "Quantity" ) },
278 new uno.Any [] { new uno.Any( "Alice" ),
279 new uno.Any( "Apples" ),
280 new uno.Any( (Double) 3.0 ) },
281 new uno.Any [] { new uno.Any( "Alice" ),
282 new uno.Any( "Oranges" ),
283 new uno.Any( (Double) 7.0 ) },
284 new uno.Any [] { new uno.Any( "Bob" ),
285 new uno.Any( "Apples" ),
286 new uno.Any( (Double) 3.0 ) },
287 new uno.Any [] { new uno.Any( "Alice" ),
288 new uno.Any( "Apples" ),
289 new uno.Any( (Double) 9.0 ) },
290 new uno.Any [] { new uno.Any( "Bob" ),
291 new uno.Any( "Apples" ),
292 new uno.Any( (Double) 5.0 ) },
293 new uno.Any [] { new uno.Any( "Bob" ),
294 new uno.Any( "Oranges" ),
295 new uno.Any( (Double) 6.0 ) },
296 new uno.Any [] { new uno.Any( "Alice" ),
297 new uno.Any( "Oranges" ),
298 new uno.Any( (Double) 3.0 ) },
299 new uno.Any [] { new uno.Any( "Alice" ),
300 new uno.Any( "Apples" ),
301 new uno.Any( (Double) 8.0 ) },
302 new uno.Any [] { new uno.Any( "Alice" ),
303 new uno.Any( "Oranges" ),
304 new uno.Any( (Double) 1.0 ) },
305 new uno.Any [] { new uno.Any( "Bob" ),
306 new uno.Any( "Oranges" ),
307 new uno.Any( (Double) 2.0 ) },
308 new uno.Any [] { new uno.Any( "Bob" ),
309 new uno.Any( "Oranges" ),
310 new uno.Any( (Double) 7.0 ) },
311 new uno.Any [] { new uno.Any( "Bob" ),
312 new uno.Any( "Apples" ),
313 new uno.Any( (Double) 1.0 ) },
314 new uno.Any [] { new uno.Any( "Alice" ),
315 new uno.Any( "Apples" ),
316 new uno.Any( (Double) 8.0 ) },
317 new uno.Any [] { new uno.Any( "Alice" ),
318 new uno.Any( "Oranges" ),
319 new uno.Any( (Double) 8.0 ) },
320 new uno.Any [] { new uno.Any( "Alice" ),
321 new uno.Any( "Apples" ),
322 new uno.Any( (Double) 7.0 ) },
323 new uno.Any [] { new uno.Any( "Bob" ),
324 new uno.Any( "Apples" ),
325 new uno.Any( (Double) 1.0 ) },
326 new uno.Any [] { new uno.Any( "Bob" ),
327 new uno.Any( "Oranges" ),
328 new uno.Any( (Double) 9.0 ) },
329 new uno.Any [] { new uno.Any( "Bob" ),
330 new uno.Any( "Oranges" ),
331 new uno.Any( (Double) 3.0 ) },
332 new uno.Any [] { new uno.Any( "Alice" ),
333 new uno.Any( "Oranges" ),
334 new uno.Any( (Double) 4.0 ) },
335 new uno.Any [] { new uno.Any( "Alice" ),
336 new uno.Any( "Apples" ),
337 new uno.Any( (Double) 9.0 ) }
338 };
339 xData.setDataArray( aValues );
340
341
342 // --- Get cell range address. ---
343 unoidl.com.sun.star.sheet.XCellRangeAddressable xRangeAddr =
344 (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange;
345 aRangeAddress = xRangeAddr.getRangeAddress();
346 Console.WriteLine(
347 "Address of this range: Sheet=" + aRangeAddress.Sheet );
348 Console.WriteLine(
349 "Start column=" + aRangeAddress.StartColumn + "; Start row=" +
350 aRangeAddress.StartRow );
351 Console.WriteLine(
352 "End column =" + aRangeAddress.EndColumn + "; End row =" +
353 aRangeAddress.EndRow );
354
355
356 // --- Sheet operation. ---
357 // uses the range filled with XCellRangeData
358 unoidl.com.sun.star.sheet.XSheetOperation xSheetOp =
359 (unoidl.com.sun.star.sheet.XSheetOperation) xData;
360 double fResult = xSheetOp.computeFunction(
361 unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE );
362 Console.WriteLine(
363 "Average value of the data table A10:C30: " + fResult );
364
365
366 // --- Fill series ---
367 // Prepare the example
368 setValue( xSheet, "E10", 1 );
369 setValue( xSheet, "E11", 4 );
370 setDate( xSheet, "E12", 30, 1, 2002 );
371 setFormula( xSheet, "I13", "Text 10" );
372 setFormula( xSheet, "E14", "Jan" );
373 setValue( xSheet, "K14", 10 );
374 setValue( xSheet, "E16", 1 );
375 setValue( xSheet, "F16", 2 );
376 setDate( xSheet, "E17", 28, 2, 2002 );
377 setDate( xSheet, "F17", 28, 1, 2002 );
378 setValue( xSheet, "E18", 6 );
379 setValue( xSheet, "F18", 4 );
380
381 unoidl.com.sun.star.sheet.XCellSeries xSeries = null;
382 // Fill 2 rows linear with end value
383 // -> 2nd series is not filled completely
384 xSeries = getCellSeries( xSheet, "E10:I11" );
385 xSeries.fillSeries(
386 unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
387 unoidl.com.sun.star.sheet.FillMode.LINEAR,
388 unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY, 2, 9 );
389 // Add months to a date
390 xSeries = getCellSeries( xSheet, "E12:I12" );
391 xSeries.fillSeries(
392 unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
393 unoidl.com.sun.star.sheet.FillMode.DATE,
394 unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_MONTH,
395 1, 0x7FFFFFFF );
396 // Fill right to left with a text containing a value
397 xSeries = getCellSeries( xSheet, "E13:I13" );
398 xSeries.fillSeries(
399 unoidl.com.sun.star.sheet.FillDirection.TO_LEFT,
400 unoidl.com.sun.star.sheet.FillMode.LINEAR,
401 unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY,
402 10, 0x7FFFFFFF );
403 // Fill with an user defined list
404 xSeries = getCellSeries( xSheet, "E14:I14" );
405 xSeries.fillSeries(
406 unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT,
407 unoidl.com.sun.star.sheet.FillMode.AUTO,
408 unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY,
409 1, 0x7FFFFFFF );
410 // Fill bottom to top with a geometric series
411 xSeries = getCellSeries( xSheet, "K10:K14" );
412 xSeries.fillSeries(
413 unoidl.com.sun.star.sheet.FillDirection.TO_TOP,
414 unoidl.com.sun.star.sheet.FillMode.GROWTH,
415 unoidl.com.sun.star.sheet.FillDateMode.FILL_DATE_DAY,
416 2, 0x7FFFFFFF );
417 // Auto fill
418 xSeries = getCellSeries( xSheet, "E16:K18" );
419 xSeries.fillAuto(
420 unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 2 );
421 // Fill series copies cell formats -> draw border here
422 prepareRange( xSheet, "E9:K18", "XCellSeries" );
423
424
425 // --- Array formulas ---
426 xCellRange = xSheet.getCellRangeByName( "E21:G23" );
427 prepareRange( xSheet, "E20:G23", "XArrayFormulaRange" );
428 unoidl.com.sun.star.sheet.XArrayFormulaRange xArrayFormula =
429 (unoidl.com.sun.star.sheet.XArrayFormulaRange) xCellRange;
430 // Insert a 3x3 unit matrix.
431 xArrayFormula.setArrayFormula( "=A10:C12" );
432 Console.WriteLine(
433 "Array formula is: " + xArrayFormula.getArrayFormula() );
434
435
436 // --- Multiple operations ---
437 setFormula( xSheet, "E26", "=E27^F26" );
438 setValue( xSheet, "E27", 1 );
439 setValue( xSheet, "F26", 1 );
440 getCellSeries( xSheet, "E27:E31" ).fillAuto(
441 unoidl.com.sun.star.sheet.FillDirection.TO_BOTTOM, 1 );
442 getCellSeries( xSheet, "F26:J26" ).fillAuto(
443 unoidl.com.sun.star.sheet.FillDirection.TO_RIGHT, 1 );
444 setFormula( xSheet, "F33", "=SIN(E33)" );
445 setFormula( xSheet, "G33", "=COS(E33)" );
446 setFormula( xSheet, "H33", "=TAN(E33)" );
447 setValue( xSheet, "E34", 0 );
448 setValue( xSheet, "E35", 0.2 );
449 getCellSeries( xSheet, "E34:E38" ).fillAuto(
450 unoidl.com.sun.star.sheet.FillDirection.TO_BOTTOM, 2 );
451 prepareRange( xSheet, "E25:J38", "XMultipleOperation" );
452
453 unoidl.com.sun.star.table.CellRangeAddress aFormulaRange =
454 createCellRangeAddress( xSheet, "E26" );
455 unoidl.com.sun.star.table.CellAddress aColCell =
456 createCellAddress( xSheet, "E27" );
457 unoidl.com.sun.star.table.CellAddress aRowCell =
458 createCellAddress( xSheet, "F26" );
459
460 xCellRange = xSheet.getCellRangeByName( "E26:J31" );
461 unoidl.com.sun.star.sheet.XMultipleOperation xMultOp =
462 (unoidl.com.sun.star.sheet.XMultipleOperation) xCellRange;
463 xMultOp.setTableOperation(
464 aFormulaRange, unoidl.com.sun.star.sheet.TableOperationMode.BOTH,
465 aColCell, aRowCell );
466
467 aFormulaRange = createCellRangeAddress( xSheet, "F33:H33" );
468 aColCell = createCellAddress( xSheet, "E33" );
469 // Row cell not needed
470
471 xCellRange = xSheet.getCellRangeByName( "E34:H38" );
472 xMultOp = (unoidl.com.sun.star.sheet.XMultipleOperation) xCellRange;
473 xMultOp.setTableOperation(
474 aFormulaRange, unoidl.com.sun.star.sheet.TableOperationMode.COLUMN,
475 aColCell, aRowCell );
476
477
478 // --- Cell Ranges Query ---
479 xCellRange = xSheet.getCellRangeByName( "A10:C30" );
480 unoidl.com.sun.star.sheet.XCellRangesQuery xRangesQuery =
481 (unoidl.com.sun.star.sheet.XCellRangesQuery) xCellRange;
482 unoidl.com.sun.star.sheet.XSheetCellRanges xCellRanges =
483 xRangesQuery.queryContentCells(
484 (short) unoidl.com.sun.star.sheet.CellFlags.STRING );
485 Console.WriteLine(
486 "Cells in A10:C30 containing text: "
487 + xCellRanges.getRangeAddressesAsString() );
488 }
489
490 /** Returns the XCellSeries interface of a cell range.
491 @param xSheet The spreadsheet containing the cell range.
492 @param aRange The address of the cell range.
493 @return The XCellSeries interface. */
494 private unoidl.com.sun.star.sheet.XCellSeries getCellSeries(
495 unoidl.com.sun.star.sheet.XSpreadsheet xSheet, String aRange )
496 {
497 return (unoidl.com.sun.star.sheet.XCellSeries)
498 xSheet.getCellRangeByName( aRange );
499 }
500
501
502
503 /** All samples regarding cell range collections. */
504 private void doCellRangesSamples()
505 {
506 Console.WriteLine( "\n*** Samples for cell range collections ***\n" );
507
508 // Create a new cell range container
509 unoidl.com.sun.star.lang.XMultiServiceFactory xDocFactory =
510 (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
511 unoidl.com.sun.star.sheet.XSheetCellRangeContainer xRangeCont =
512 (unoidl.com.sun.star.sheet.XSheetCellRangeContainer)
513 xDocFactory.createInstance(
514 "com.sun.star.sheet.SheetCellRanges" );
515
516
517 // --- Insert ranges ---
518 insertRange( xRangeCont, 0, 0, 0, 0, 0, false ); // A1:A1
519 insertRange( xRangeCont, 0, 0, 1, 0, 2, true ); // A2:A3
520 insertRange( xRangeCont, 0, 1, 0, 1, 2, false ); // B1:B3
521
522
523 // --- Query the list of filled cells ---
524 Console.WriteLine( "All filled cells: " );
525 unoidl.com.sun.star.container.XEnumerationAccess xCellsEA =
526 xRangeCont.getCells();
527 unoidl.com.sun.star.container.XEnumeration xEnum =
528 xCellsEA.createEnumeration();
529 while( xEnum.hasMoreElements() )
530 {
531 uno.Any aCellObj = xEnum.nextElement();
532 unoidl.com.sun.star.sheet.XCellAddressable xAddr =
533 (unoidl.com.sun.star.sheet.XCellAddressable) aCellObj.Value;
534 unoidl.com.sun.star.table.CellAddress aAddr =
535 xAddr.getCellAddress();
536 Console.WriteLine(
537 getCellAddressString( aAddr.Column, aAddr.Row ) + " " );
538 }
539 Console.WriteLine();
540 }
541
542 /** Inserts a cell range address into a cell range container and prints
543 a message.
544 @param xContainer unoidl.com.sun.star.sheet.XSheetCellRangeContainer
545 interface of the container.
546 @param nSheet Index of sheet of the range.
547 @param nStartCol Index of first column of the range.
548 @param nStartRow Index of first row of the range.
549 @param nEndCol Index of last column of the range.
550 @param nEndRow Index of last row of the range.
551 @param bMerge Determines whether the new range should be merged
552 with the existing ranges.
553 */
554 private void insertRange(
555 unoidl.com.sun.star.sheet.XSheetCellRangeContainer xContainer,
556 int nSheet, int nStartCol, int nStartRow, int nEndCol, int nEndRow,
557 bool bMerge )
558 {
559 unoidl.com.sun.star.table.CellRangeAddress aAddress =
560 new unoidl.com.sun.star.table.CellRangeAddress();
561 aAddress.Sheet = (short)nSheet;
562 aAddress.StartColumn = nStartCol;
563 aAddress.StartRow = nStartRow;
564 aAddress.EndColumn = nEndCol;
565 aAddress.EndRow = nEndRow;
566 xContainer.addRangeAddress( aAddress, bMerge );
567 Console.WriteLine(
568 "Inserting " + getCellRangeAddressString( aAddress )
569 + " " + (bMerge ? " with" : "without") + " merge,"
570 + " resulting list: " + xContainer.getRangeAddressesAsString() );
571 }
572
573
574
575 /** All samples regarding cell cursors. */
576 private void doCellCursorSamples()
577 {
578 Console.WriteLine( "\n*** Samples for cell cursor ***\n" );
579 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
580
581
582 // --- Find the array formula using a cell cursor ---
583 unoidl.com.sun.star.table.XCellRange xRange =
584 xSheet.getCellRangeByName( "F22" );
585 unoidl.com.sun.star.sheet.XSheetCellRange xCellRange =
586 (unoidl.com.sun.star.sheet.XSheetCellRange) xRange;
587 unoidl.com.sun.star.sheet.XSheetCellCursor xCursor =
588 xSheet.createCursorByRange( xCellRange );
589
590 xCursor.collapseToCurrentArray();
591 unoidl.com.sun.star.sheet.XArrayFormulaRange xArray =
592 (unoidl.com.sun.star.sheet.XArrayFormulaRange) xCursor;
593 Console.WriteLine(
594 "Array formula in " + getCellRangeAddressString( xCursor, false )
595 + " contains formula " + xArray.getArrayFormula() );
596
597
598 // --- Find the used area ---
599 unoidl.com.sun.star.sheet.XUsedAreaCursor xUsedCursor =
600 (unoidl.com.sun.star.sheet.XUsedAreaCursor) xCursor;
601 xUsedCursor.gotoStartOfUsedArea( false );
602 xUsedCursor.gotoEndOfUsedArea( true );
603 // xUsedCursor and xCursor are interfaces of the same object -
604 // so modifying xUsedCursor takes effect on xCursor:
605 Console.WriteLine(
606 "The used area is: " + getCellRangeAddressString( xCursor, true ) );
607 }
608
609
610
611 /** All samples regarding the formatting of cells and ranges. */
612 private void doFormattingSamples()
613 {
614 Console.WriteLine( "\n*** Formatting samples ***\n" );
615 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 1 );
616 unoidl.com.sun.star.table.XCellRange xCellRange;
617 unoidl.com.sun.star.beans.XPropertySet xPropSet = null;
618 unoidl.com.sun.star.container.XIndexAccess xRangeIA = null;
619 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager;
620
621
622 // --- Cell styles ---
623 // get the cell style container
624 unoidl.com.sun.star.style.XStyleFamiliesSupplier xFamiliesSupplier =
625 (unoidl.com.sun.star.style.XStyleFamiliesSupplier) getDocument();
626 unoidl.com.sun.star.container.XNameAccess xFamiliesNA =
627 xFamiliesSupplier.getStyleFamilies();
628 uno.Any aCellStylesObj = xFamiliesNA.getByName( "CellStyles" );
629 unoidl.com.sun.star.container.XNameContainer xCellStylesNA =
630 (unoidl.com.sun.star.container.XNameContainer) aCellStylesObj.Value;
631
632 // create a new cell style
633 xServiceManager =
634 (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
635 Object aCellStyle = xServiceManager.createInstance(
636 "com.sun.star.style.CellStyle" );
637 String aStyleName = "MyNewCellStyle";
638 xCellStylesNA.insertByName(
639 aStyleName, new uno.Any( typeof (Object), aCellStyle ) );
640
641 // modify properties of the new style
642 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) aCellStyle;
643 xPropSet.setPropertyValue(
644 "CellBackColor", new uno.Any( (Int32) 0x888888 ) );
645 xPropSet.setPropertyValue(
646 "IsCellBackgroundTransparent", new uno.Any( false ) );
647
648
649
650 // --- Query equal-formatted cell ranges ---
651 // prepare example, use the new cell style
652 xCellRange = xSheet.getCellRangeByName( "D2:F2" );
653 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
654 xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) );
655
656 xCellRange = xSheet.getCellRangeByName( "A3:G3" );
657 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
658 xPropSet.setPropertyValue( "CellStyle", new uno.Any( aStyleName ) );
659
660 // All ranges in one container
661 xCellRange = xSheet.getCellRangeByName( "A1:G3" );
662 Console.WriteLine( "Service CellFormatRanges:" );
663 unoidl.com.sun.star.sheet.XCellFormatRangesSupplier xFormatSupp =
664 (unoidl.com.sun.star.sheet.XCellFormatRangesSupplier) xCellRange;
665 xRangeIA = xFormatSupp.getCellFormatRanges();
666 Console.WriteLine( getCellRangeListString( xRangeIA ) );
667
668 // Ranges sorted in SheetCellRanges containers
669 Console.WriteLine( "\nService UniqueCellFormatRanges:" );
670 unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier
671 xUniqueFormatSupp =
672 (unoidl.com.sun.star.sheet.XUniqueCellFormatRangesSupplier)
673 xCellRange;
674 unoidl.com.sun.star.container.XIndexAccess xRangesIA =
675 xUniqueFormatSupp.getUniqueCellFormatRanges();
676 int nCount = xRangesIA.getCount();
677 for (int nIndex = 0; nIndex < nCount; ++nIndex)
678 {
679 uno.Any aRangesObj = xRangesIA.getByIndex( nIndex );
680 xRangeIA =
681 (unoidl.com.sun.star.container.XIndexAccess) aRangesObj.Value;
682 Console.WriteLine(
683 "Container " + (nIndex + 1) + ": " +
684 getCellRangeListString( xRangeIA ) );
685 }
686
687
688 // --- Table auto formats ---
689 // get the global collection of table auto formats,
690 // use global service manager
691 xServiceManager = getServiceManager();
692 Object aAutoFormatsObj = xServiceManager.createInstance(
693 "com.sun.star.sheet.TableAutoFormats" );
694 unoidl.com.sun.star.container.XNameContainer xAutoFormatsNA =
695 (unoidl.com.sun.star.container.XNameContainer) aAutoFormatsObj;
696
697 // create a new table auto format and insert into the container
698 String aAutoFormatName = "Temp_Example";
699 bool bExistsAlready = xAutoFormatsNA.hasByName( aAutoFormatName );
700 uno.Any aAutoFormatObj;
701 if (bExistsAlready)
702 // auto format already exists -> use it
703 aAutoFormatObj = xAutoFormatsNA.getByName( aAutoFormatName );
704 else
705 {
706 // create a new auto format (with document service manager!)
707 xServiceManager =
708 (unoidl.com.sun.star.lang.XMultiServiceFactory) getDocument();
709 aAutoFormatObj = new uno.Any(
710 typeof (Object),
711 xServiceManager.createInstance(
712 "com.sun.star.sheet.TableAutoFormat" ) );
713 xAutoFormatsNA.insertByName( aAutoFormatName, aAutoFormatObj );
714 }
715 // index access to the auto format fields
716 unoidl.com.sun.star.container.XIndexAccess xAutoFormatIA =
717 (unoidl.com.sun.star.container.XIndexAccess) aAutoFormatObj.Value;
718
719 // set properties of all auto format fields
720 for (int nRow = 0; nRow < 4; ++nRow)
721 {
722 int nRowColor = 0;
723 switch (nRow)
724 {
725 case 0: nRowColor = 0x999999; break;
726 case 1: nRowColor = 0xFFFFCC; break;
727 case 2: nRowColor = 0xEEEEEE; break;
728 case 3: nRowColor = 0x999999; break;
729 }
730
731 for (int nColumn = 0; nColumn < 4; ++nColumn)
732 {
733 int nColor = nRowColor;
734 if ((nColumn == 0) || (nColumn == 3))
735 nColor -= 0x333300;
736
737 // get the auto format field and apply properties
738 uno.Any aFieldObj = xAutoFormatIA.getByIndex(
739 4 * nRow + nColumn );
740 xPropSet =
741 (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
742 xPropSet.setPropertyValue(
743 "CellBackColor", new uno.Any( (Int32) nColor ) );
744 }
745 }
746
747 // set the auto format to the spreadsheet
748 xCellRange = xSheet.getCellRangeByName( "A5:H25" );
749 unoidl.com.sun.star.table.XAutoFormattable xAutoForm =
750 (unoidl.com.sun.star.table.XAutoFormattable) xCellRange;
751 xAutoForm.autoFormat( aAutoFormatName );
752
753 // remove the auto format
754 if (!bExistsAlready)
755 xAutoFormatsNA.removeByName( aAutoFormatName );
756
757
758 // --- Conditional formats ---
759 xSheet = getSpreadsheet( 0 );
760 prepareRange( xSheet, "K20:K23", "Cond. Format" );
761 setValue( xSheet, "K21", 1 );
762 setValue( xSheet, "K22", 2 );
763 setValue( xSheet, "K23", 3 );
764
765 // get the conditional format object of the cell range
766 xCellRange = xSheet.getCellRangeByName( "K21:K23" );
767 xPropSet = (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
768 unoidl.com.sun.star.sheet.XSheetConditionalEntries xEntries =
769 (unoidl.com.sun.star.sheet.XSheetConditionalEntries)
770 xPropSet.getPropertyValue( "ConditionalFormat" ).Value;
771
772 // create a condition and apply it to the range
773 unoidl.com.sun.star.beans.PropertyValue[] aCondition =
774 new unoidl.com.sun.star.beans.PropertyValue[3];
775 aCondition[0] = new unoidl.com.sun.star.beans.PropertyValue();
776 aCondition[0].Name = "Operator";
777 aCondition[0].Value =
778 new uno.Any(
779 typeof (unoidl.com.sun.star.sheet.ConditionOperator),
780 unoidl.com.sun.star.sheet.ConditionOperator.GREATER );
781 aCondition[1] = new unoidl.com.sun.star.beans.PropertyValue();
782 aCondition[1].Name = "Formula1";
783 aCondition[1].Value = new uno.Any( "1" );
784 aCondition[2] = new unoidl.com.sun.star.beans.PropertyValue();
785 aCondition[2].Name = "StyleName";
786 aCondition[2].Value = new uno.Any( aStyleName );
787 xEntries.addNew( aCondition );
788 xPropSet.setPropertyValue(
789 "ConditionalFormat",
790 new uno.Any(
791 typeof (unoidl.com.sun.star.sheet.XSheetConditionalEntries),
792 xEntries ) );
793 }
794
795
796
797 /** All samples regarding the spreadsheet document. */
798 private void doDocumentSamples()
799 {
800 Console.WriteLine( "\n*** Samples for spreadsheet document ***\n" );
801
802
803 // --- Insert a new spreadsheet ---
804 unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
805 insertSpreadsheet( "A new sheet", (short) 0x7FFF );
806
807
808 // --- Copy a cell range ---
809 prepareRange( xSheet, "A1:B3", "Copy from" );
810 prepareRange( xSheet, "D1:E3", "To" );
811 setValue( xSheet, "A2", 123 );
812 setValue( xSheet, "B2", 345 );
813 setFormula( xSheet, "A3", "=SUM(A2:B2)" );
814 setFormula( xSheet, "B3", "=FORMULA(A3)" );
815
816 unoidl.com.sun.star.sheet.XCellRangeMovement xMovement =
817 (unoidl.com.sun.star.sheet.XCellRangeMovement) xSheet;
818 unoidl.com.sun.star.table.CellRangeAddress aSourceRange =
819 createCellRangeAddress( xSheet, "A2:B3" );
820 unoidl.com.sun.star.table.CellAddress aDestCell =
821 createCellAddress( xSheet, "D2" );
822 xMovement.copyRange( aDestCell, aSourceRange );
823
824
825 // --- Print automatic column page breaks ---
826 unoidl.com.sun.star.sheet.XSheetPageBreak xPageBreak =
827 (unoidl.com.sun.star.sheet.XSheetPageBreak) xSheet;
828 unoidl.com.sun.star.sheet.TablePageBreakData[] aPageBreakArray =
829 xPageBreak.getColumnPageBreaks();
830
831 Console.Write( "Automatic column page breaks:" );
832 for (int nIndex = 0; nIndex < aPageBreakArray.Length; ++nIndex)
833 if (!aPageBreakArray[nIndex].ManualBreak)
834 Console.Write( " " + aPageBreakArray[nIndex].Position );
835 Console.WriteLine();
836
837
838 // --- Document properties ---
839 unoidl.com.sun.star.beans.XPropertySet xPropSet =
840 (unoidl.com.sun.star.beans.XPropertySet) getDocument();
841
842 String aText = "Value of property IsIterationEnabled: ";
843 aText +=
844 (Boolean) xPropSet.getPropertyValue( "IsIterationEnabled" ).Value;
845 Console.WriteLine( aText );
846 aText = "Value of property IterationCount: ";
847 aText += (Int32) xPropSet.getPropertyValue( "IterationCount" ).Value;
848 Console.WriteLine( aText );
849 aText = "Value of property NullDate: ";
850 unoidl.com.sun.star.util.Date aDate = (unoidl.com.sun.star.util.Date)
851 xPropSet.getPropertyValue( "NullDate" ).Value;
852 aText += aDate.Year + "-" + aDate.Month + "-" + aDate.Day;
853 Console.WriteLine( aText );
854
855
856 // --- Data validation ---
857 prepareRange( xSheet, "A5:C7", "Validation" );
858 setFormula( xSheet, "A6", "Insert values between 0.0 and 5.0 below:" );
859
860 unoidl.com.sun.star.table.XCellRange xCellRange =
861 xSheet.getCellRangeByName( "A7:C7" );
862 unoidl.com.sun.star.beans.XPropertySet xCellPropSet =
863 (unoidl.com.sun.star.beans.XPropertySet) xCellRange;
864 // validation properties
865 unoidl.com.sun.star.beans.XPropertySet xValidPropSet =
866 (unoidl.com.sun.star.beans.XPropertySet)
867 xCellPropSet.getPropertyValue( "Validation" ).Value;
868 xValidPropSet.setPropertyValue(
869 "Type",
870 new uno.Any(
871 typeof (unoidl.com.sun.star.sheet.ValidationType),
872 unoidl.com.sun.star.sheet.ValidationType.DECIMAL ) );
873 xValidPropSet.setPropertyValue(
874 "ShowErrorMessage", new uno.Any( true ) );
875 xValidPropSet.setPropertyValue(
876 "ErrorMessage", new uno.Any( "This is an invalid value!" ) );
877 xValidPropSet.setPropertyValue(
878 "ErrorAlertStyle",
879 new uno.Any(
880 typeof (unoidl.com.sun.star.sheet.ValidationAlertStyle),
881 unoidl.com.sun.star.sheet.ValidationAlertStyle.STOP ) );
882 // condition
883 unoidl.com.sun.star.sheet.XSheetCondition xCondition =
884 (unoidl.com.sun.star.sheet.XSheetCondition) xValidPropSet;
885 xCondition.setOperator(
886 unoidl.com.sun.star.sheet.ConditionOperator.BETWEEN );
887 xCondition.setFormula1( "0.0" );
888 xCondition.setFormula2( "5.0" );
889 // apply on cell range
890 xCellPropSet.setPropertyValue(
891 "Validation",
892 new uno.Any(
893 typeof (unoidl.com.sun.star.beans.XPropertySet),
894 xValidPropSet ) );
895
896
897 // --- Scenarios ---
898 uno.Any [][] aValues = {
899 new uno.Any [] { uno.Any.VOID, uno.Any.VOID },
900 new uno.Any [] { uno.Any.VOID, uno.Any.VOID }
901 };
902
903 aValues[ 0 ][ 0 ] = new uno.Any( (Double) 11 );
904 aValues[ 0 ][ 1 ] = new uno.Any( (Double) 12 );
905 aValues[ 1 ][ 0 ] = new uno.Any( "Test13" );
906 aValues[ 1 ][ 1 ] = new uno.Any( "Test14" );
907 insertScenario(
908 xSheet, "B10:C11", aValues,
909 "First Scenario", "The first scenario." );
910
911 aValues[ 0 ][ 0 ] = new uno.Any( "Test21" );
912 aValues[ 0 ][ 1 ] = new uno.Any( "Test22" );
913 aValues[ 1 ][ 0 ] = new uno.Any( (Double) 23 );
914 aValues[ 1 ][ 1 ] = new uno.Any( (Double) 24 );
915 insertScenario(
916 xSheet, "B10:C11", aValues,
917 "Second Scenario", "The visible scenario." );
918
919 aValues[ 0 ][ 0 ] = new uno.Any( (Double) 31 );
920 aValues[ 0 ][ 1 ] = new uno.Any( (Double) 32 );
921 aValues[ 1 ][ 0 ] = new uno.Any( "Test33" );
922 aValues[ 1 ][ 1 ] = new uno.Any( "Test34" );
923 insertScenario(
924 xSheet, "B10:C11", aValues,
925 "Third Scenario", "The last scenario." );
926
927 // show second scenario
928 showScenario( xSheet, "Second Scenario" );
929 }
930
931 /** Inserts a scenario containing one cell range into a sheet and
932 applies the value array.
933 @param xSheet The XSpreadsheet interface of the spreadsheet.
934 @param aRange The range address for the scenario.
935 @param aValueArray The array of cell contents.
936 @param aScenarioName The name of the new scenario.
937 @param aScenarioComment The user comment for the scenario. */
938 private void insertScenario(
939 unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
940 String aRange,
941 uno.Any [][] aValueArray,
942 String aScenarioName,
943 String aScenarioComment )
944 {
945 // get the cell range with the given address
946 unoidl.com.sun.star.table.XCellRange xCellRange =
947 xSheet.getCellRangeByName( aRange );
948
949 // create the range address sequence
950 unoidl.com.sun.star.sheet.XCellRangeAddressable xAddr =
951 (unoidl.com.sun.star.sheet.XCellRangeAddressable) xCellRange;
952 unoidl.com.sun.star.table.CellRangeAddress[] aRangesSeq =
953 new unoidl.com.sun.star.table.CellRangeAddress[1];
954 aRangesSeq[0] = xAddr.getRangeAddress();
955
956 // create the scenario
957 unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp =
958 (unoidl.com.sun.star.sheet.XScenariosSupplier) xSheet;
959 unoidl.com.sun.star.sheet.XScenarios xScenarios =
960 xScenSupp.getScenarios();
961 xScenarios.addNewByName( aScenarioName, aRangesSeq, aScenarioComment );
962
963 // insert the values into the range
964 unoidl.com.sun.star.sheet.XCellRangeData xData =
965 (unoidl.com.sun.star.sheet.XCellRangeData) xCellRange;
966 xData.setDataArray( aValueArray );
967 }
968
969 /** Activates a scenario.
970 @param xSheet The XSpreadsheet interface of the spreadsheet.
971 @param aScenarioName The name of the scenario. */
972 private void showScenario(
973 unoidl.com.sun.star.sheet.XSpreadsheet xSheet,
974 String aScenarioName )
975 {
976 // get the scenario set
977 unoidl.com.sun.star.sheet.XScenariosSupplier xScenSupp =
978 (unoidl.com.sun.star.sheet.XScenariosSupplier) xSheet;
979 unoidl.com.sun.star.sheet.XScenarios xScenarios =
980 xScenSupp.getScenarios();
981
982 // get the scenario and activate it
983 uno.Any aScenarioObj = xScenarios.getByName( aScenarioName );
984 unoidl.com.sun.star.sheet.XScenario xScenario =
985 (unoidl.com.sun.star.sheet.XScenario) aScenarioObj.Value;
986 xScenario.apply();
987 }
988
989
990
991 private void doNamedRangesSamples()
992 {
993 Console.WriteLine( "\n*** Samples for named ranges ***\n" );
994 unoidl.com.sun.star.sheet.XSpreadsheetDocument xDocument =
995 getDocument();
996 unoidl.com.sun.star.sheet.XSpreadsheet xSheet =
997 getSpreadsheet( 0 );
998
999
1000 // --- Named ranges ---
1001 prepareRange( xSheet, "G42:H45", "Named ranges" );
1002 xSheet.getCellByPosition( 6, 42 ).setValue( 1 );
1003 xSheet.getCellByPosition( 6, 43 ).setValue( 2 );
1004 xSheet.getCellByPosition( 7, 42 ).setValue( 3 );
1005 xSheet.getCellByPosition( 7, 43 ).setValue( 4 );
1006
1007 // insert a named range
1008 unoidl.com.sun.star.beans.XPropertySet xDocProp =
1009 (unoidl.com.sun.star.beans.XPropertySet) xDocument;
1010 uno.Any aRangesObj = xDocProp.getPropertyValue( "NamedRanges" );
1011 unoidl.com.sun.star.sheet.XNamedRanges xNamedRanges =
1012 (unoidl.com.sun.star.sheet.XNamedRanges) aRangesObj.Value;
1013 unoidl.com.sun.star.table.CellAddress aRefPos =
1014 new unoidl.com.sun.star.table.CellAddress();
1015 aRefPos.Sheet = 0;
1016 aRefPos.Column = 6;
1017 aRefPos.Row = 44;
1018 xNamedRanges.addNewByName( "ExampleName", "SUM(G43:G44)", aRefPos, 0 );
1019
1020 // use the named range in formulas
1021 xSheet.getCellByPosition( 6, 44 ).setFormula( "=ExampleName" );
1022 xSheet.getCellByPosition( 7, 44 ).setFormula( "=ExampleName" );
1023
1024
1025 // --- Label ranges ---
1026 prepareRange( xSheet, "G47:I50", "Label ranges" );
1027 unoidl.com.sun.star.table.XCellRange xRange =
1028 xSheet.getCellRangeByPosition( 6, 47, 7, 49 );
1029 unoidl.com.sun.star.sheet.XCellRangeData xData =
1030 ( unoidl.com.sun.star.sheet.XCellRangeData ) xRange;
1031 uno.Any [][] aValues =
1032 {
1033 new uno.Any [] { new uno.Any( "Apples" ),
1034 new uno.Any( "Oranges" ) },
1035 new uno.Any [] { new uno.Any( (Double) 5 ),
1036 new uno.Any( (Double) 7 ) },
1037 new uno.Any [] { new uno.Any( (Double) 6 ),
1038 new uno.Any( (Double) 8 ) }
1039 };
1040 xData.setDataArray( aValues );
1041
1042 // insert a column label range
1043 uno.Any aLabelsObj = xDocProp.getPropertyValue( "ColumnLabelRanges" );
1044 unoidl.com.sun.star.sheet.XLabelRanges xLabelRanges =
1045 (unoidl.com.sun.star.sheet.XLabelRanges) aLabelsObj.Value;
1046 unoidl.com.sun.star.table.CellRangeAddress aLabelArea =
1047 new unoidl.com.sun.star.table.CellRangeAddress();
1048 aLabelArea.Sheet = 0;
1049 aLabelArea.StartColumn = 6;
1050 aLabelArea.StartRow = 47;
1051 aLabelArea.EndColumn = 7;
1052 aLabelArea.EndRow = 47;
1053 unoidl.com.sun.star.table.CellRangeAddress aDataArea =
1054 new unoidl.com.sun.star.table.CellRangeAddress();
1055 aDataArea.Sheet = 0;
1056 aDataArea.StartColumn = 6;
1057 aDataArea.StartRow = 48;
1058 aDataArea.EndColumn = 7;
1059 aDataArea.EndRow = 49;
1060 xLabelRanges.addNew( aLabelArea, aDataArea );
1061
1062 // use the label range in formulas
1063 xSheet.getCellByPosition( 8, 48 ).setFormula( "=Apples+Oranges" );
1064 xSheet.getCellByPosition( 8, 49 ).setFormula( "=Apples+Oranges" );
1065 }
1066
1067
1068
1069 /** Helper for doDatabaseSamples: get name of first database. */
1070 private String getFirstDatabaseName()
1071 {
1072 String aDatabase = null;
1073 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager =
1074 getServiceManager();
1075 unoidl.com.sun.star.container.XNameAccess xContext =
1076 (unoidl.com.sun.star.container.XNameAccess)
1077 xServiceManager.createInstance(
1078 "com.sun.star.sdb.DatabaseContext" );
1079 String[] aNames = xContext.getElementNames();
1080 if ( aNames.Length > 0 )
1081 aDatabase = aNames[0];
1082 return aDatabase;
1083 }
1084
1085 /** Helper for doDatabaseSamples: get name of first table in a database. */
1086 private String getFirstTableName( String aDatabase )
1087 {
1088 if ( aDatabase == null )
1089 return null;
1090
1091 String aTable = null;
1092 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager =
1093 getServiceManager();
1094 unoidl.com.sun.star.container.XNameAccess xContext =
1095 (unoidl.com.sun.star.container.XNameAccess)
1096 xServiceManager.createInstance(
1097 "com.sun.star.sdb.DatabaseContext" );
1098 unoidl.com.sun.star.sdb.XCompletedConnection xSource =
1099 (unoidl.com.sun.star.sdb.XCompletedConnection)
1100 xContext.getByName( aDatabase ).Value;
1101 unoidl.com.sun.star.task.XInteractionHandler xHandler =
1102 (unoidl.com.sun.star.task.XInteractionHandler)
1103 xServiceManager.createInstance(
1104 "com.sun.star.task.InteractionHandler" );
1105 unoidl.com.sun.star.sdbcx.XTablesSupplier xSupplier =
1106 (unoidl.com.sun.star.sdbcx.XTablesSupplier)
1107 xSource.connectWithCompletion( xHandler );
1108 unoidl.com.sun.star.container.XNameAccess xTables =
1109 xSupplier.getTables();
1110 String[] aNames = xTables.getElementNames();
1111 if ( aNames.Length > 0 )
1112 aTable = aNames[0];
1113 return aTable;
1114 }
1115
1116 private void doDatabaseSamples()
1117 {
1118 Console.WriteLine( "\n*** Samples for database operations ***\n" );
1119 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 2 );
1120
1121
1122 // --- put some example data into the sheet ---
1123 unoidl.com.sun.star.table.XCellRange xRange =
1124 xSheet.getCellRangeByName( "B3:D24" );
1125 unoidl.com.sun.star.sheet.XCellRangeData xData =
1126 (unoidl.com.sun.star.sheet.XCellRangeData) xRange;
1127 uno.Any [][] aValues =
1128 {
1129 new uno.Any [] { new uno.Any( "Name" ),
1130 new uno.Any( "Year" ),
1131 new uno.Any( "Sales" ) },
1132 new uno.Any [] { new uno.Any( "Alice" ),
1133 new uno.Any( (Double) 2001 ),
1134 new uno.Any( (Double) 4.0 ) },
1135 new uno.Any [] { new uno.Any( "Carol" ),
1136 new uno.Any( (Double) 1997 ),
1137 new uno.Any( (Double) 3.0 ) },
1138 new uno.Any [] { new uno.Any( "Carol" ),
1139 new uno.Any( (Double) 1998 ),
1140 new uno.Any( (Double) 8.0 ) },
1141 new uno.Any [] { new uno.Any( "Bob" ),
1142 new uno.Any( (Double) 1997 ),
1143 new uno.Any( (Double) 8.0 ) },
1144 new uno.Any [] { new uno.Any( "Alice" ),
1145 new uno.Any( (Double) 2002 ),
1146 new uno.Any( (Double) 9.0 ) },
1147 new uno.Any [] { new uno.Any( "Alice" ),
1148 new uno.Any( (Double) 1999 ),
1149 new uno.Any( (Double) 7.0 ) },
1150 new uno.Any [] { new uno.Any( "Alice" ),
1151 new uno.Any( (Double) 1996 ),
1152 new uno.Any( (Double) 3.0 ) },
1153 new uno.Any [] { new uno.Any( "Bob" ),
1154 new uno.Any( (Double) 2000 ),
1155 new uno.Any( (Double) 1.0 ) },
1156 new uno.Any [] { new uno.Any( "Carol" ),
1157 new uno.Any( (Double) 1999 ),
1158 new uno.Any( (Double) 5.0 ) },
1159 new uno.Any [] { new uno.Any( "Bob" ),
1160 new uno.Any( (Double) 2002 ),
1161 new uno.Any( (Double) 1.0 ) },
1162 new uno.Any [] { new uno.Any( "Carol" ),
1163 new uno.Any( (Double) 2001 ),
1164 new uno.Any( (Double) 5.0 ) },
1165 new uno.Any [] { new uno.Any( "Carol" ),
1166 new uno.Any( (Double) 2000 ),
1167 new uno.Any( (Double) 1.0 ) },
1168 new uno.Any [] { new uno.Any( "Carol" ),
1169 new uno.Any( (Double) 1996 ),
1170 new uno.Any( (Double) 8.0 ) },
1171 new uno.Any [] { new uno.Any( "Bob" ),
1172 new uno.Any( (Double) 1996 ),
1173 new uno.Any( (Double) 7.0 ) },
1174 new uno.Any [] { new uno.Any( "Alice" ),
1175 new uno.Any( (Double) 1997 ),
1176 new uno.Any( (Double) 3.0 ) },
1177 new uno.Any [] { new uno.Any( "Alice" ),
1178 new uno.Any( (Double) 2000 ),
1179 new uno.Any( (Double) 9.0 ) },
1180 new uno.Any [] { new uno.Any( "Bob" ),
1181 new uno.Any( (Double) 1998 ),
1182 new uno.Any( (Double) 1.0 ) },
1183 new uno.Any [] { new uno.Any( "Bob" ),
1184 new uno.Any( (Double) 1999 ),
1185 new uno.Any( (Double) 6.0 ) },
1186 new uno.Any [] { new uno.Any( "Carol" ),
1187 new uno.Any( (Double) 2002 ),
1188 new uno.Any( (Double) 8.0 ) },
1189 new uno.Any [] { new uno.Any( "Alice" ),
1190 new uno.Any( (Double) 1998 ),
1191 new uno.Any( (Double) 5.0 ) },
1192 new uno.Any [] { new uno.Any( "Bob" ),
1193 new uno.Any( (Double) 2001 ),
1194 new uno.Any( (Double) 6.0 ) }
1195 };
1196 xData.setDataArray( aValues );
1197
1198
1199 // --- filter for second column >= 1998 ---
1200 unoidl.com.sun.star.sheet.XSheetFilterable xFilter =
1201 (unoidl.com.sun.star.sheet.XSheetFilterable) xRange;
1202 unoidl.com.sun.star.sheet.XSheetFilterDescriptor xFilterDesc =
1203 xFilter.createFilterDescriptor( true );
1204 unoidl.com.sun.star.sheet.TableFilterField[] aFilterFields =
1205 new unoidl.com.sun.star.sheet.TableFilterField[1];
1206 aFilterFields[0] = new unoidl.com.sun.star.sheet.TableFilterField();
1207 aFilterFields[0].Field = 1;
1208 aFilterFields[0].IsNumeric = true;
1209 aFilterFields[0].Operator =
1210 unoidl.com.sun.star.sheet.FilterOperator.GREATER_EQUAL;
1211 aFilterFields[0].NumericValue = 1998;
1212 xFilterDesc.setFilterFields( aFilterFields );
1213 unoidl.com.sun.star.beans.XPropertySet xFilterProp =
1214 (unoidl.com.sun.star.beans.XPropertySet) xFilterDesc;
1215 xFilterProp.setPropertyValue(
1216 "ContainsHeader", new uno.Any( true ) );
1217 xFilter.filter( xFilterDesc );
1218
1219
1220 // --- do the same filter as above, using criteria from a cell range ---
1221 unoidl.com.sun.star.table.XCellRange xCritRange =
1222 xSheet.getCellRangeByName( "B27:B28" );
1223 unoidl.com.sun.star.sheet.XCellRangeData xCritData =
1224 (unoidl.com.sun.star.sheet.XCellRangeData) xCritRange;
1225 uno.Any [][] aCritValues =
1226 {
1227 new uno.Any [] { new uno.Any( "Year" ) },
1228 new uno.Any [] { new uno.Any( ">= 1998" ) }
1229 };
1230 xCritData.setDataArray( aCritValues );
1231 unoidl.com.sun.star.sheet.XSheetFilterableEx xCriteria =
1232 (unoidl.com.sun.star.sheet.XSheetFilterableEx) xCritRange;
1233 xFilterDesc = xCriteria.createFilterDescriptorByObject( xFilter );
1234 if ( xFilterDesc != null )
1235 xFilter.filter( xFilterDesc );
1236
1237
1238 // --- sort by second column, ascending ---
1239 unoidl.com.sun.star.util.SortField[] aSortFields =
1240 new unoidl.com.sun.star.util.SortField[1];
1241 aSortFields[0] = new unoidl.com.sun.star.util.SortField();
1242 aSortFields[0].Field = 1;
1243 aSortFields[0].SortAscending = true;
1244
1245 unoidl.com.sun.star.beans.PropertyValue[] aSortDesc =
1246 new unoidl.com.sun.star.beans.PropertyValue[2];
1247 aSortDesc[0] = new unoidl.com.sun.star.beans.PropertyValue();
1248 aSortDesc[0].Name = "SortFields";
1249 aSortDesc[0].Value =
1250 new uno.Any(
1251 typeof (unoidl.com.sun.star.util.SortField []),
1252 aSortFields );
1253 aSortDesc[1] = new unoidl.com.sun.star.beans.PropertyValue();
1254 aSortDesc[1].Name = "ContainsHeader";
1255 aSortDesc[1].Value = new uno.Any( true );
1256
1257 unoidl.com.sun.star.util.XSortable xSort =
1258 (unoidl.com.sun.star.util.XSortable) xRange;
1259 xSort.sort( aSortDesc );
1260
1261
1262 // --- insert subtotals ---
1263 unoidl.com.sun.star.sheet.XSubTotalCalculatable xSub =
1264 (unoidl.com.sun.star.sheet.XSubTotalCalculatable) xRange;
1265 unoidl.com.sun.star.sheet.XSubTotalDescriptor xSubDesc =
1266 xSub.createSubTotalDescriptor( true );
1267 unoidl.com.sun.star.sheet.SubTotalColumn[] aColumns =
1268 new unoidl.com.sun.star.sheet.SubTotalColumn[1];
1269 // calculate sum of third column
1270 aColumns[0] = new unoidl.com.sun.star.sheet.SubTotalColumn();
1271 aColumns[0].Column = 2;
1272 aColumns[0].Function = unoidl.com.sun.star.sheet.GeneralFunction.SUM;
1273 // group by first column
1274 xSubDesc.addNew( aColumns, 0 );
1275 xSub.applySubTotals( xSubDesc, true );
1276
1277 String aDatabase = getFirstDatabaseName();
1278 String aTableName = getFirstTableName( aDatabase );
1279 if ( aDatabase != null && aTableName != null )
1280 {
1281 // --- import from database ---
1282 unoidl.com.sun.star.beans.PropertyValue[] aImportDesc =
1283 new unoidl.com.sun.star.beans.PropertyValue[3];
1284 aImportDesc[0] = new unoidl.com.sun.star.beans.PropertyValue();
1285 aImportDesc[0].Name = "DatabaseName";
1286 aImportDesc[0].Value = new uno.Any( aDatabase );
1287 aImportDesc[1] = new unoidl.com.sun.star.beans.PropertyValue();
1288 aImportDesc[1].Name = "SourceType";
1289 aImportDesc[1].Value =
1290 new uno.Any(
1291 typeof (unoidl.com.sun.star.sheet.DataImportMode),
1292 unoidl.com.sun.star.sheet.DataImportMode.TABLE );
1293 aImportDesc[2] = new unoidl.com.sun.star.beans.PropertyValue();
1294 aImportDesc[2].Name = "SourceObject";
1295 aImportDesc[2].Value = new uno.Any( aTableName );
1296
1297 unoidl.com.sun.star.table.XCellRange xImportRange =
1298 xSheet.getCellRangeByName( "B35:B35" );
1299 unoidl.com.sun.star.util.XImportable xImport =
1300 (unoidl.com.sun.star.util.XImportable) xImportRange;
1301 xImport.doImport( aImportDesc );
1302
1303
1304 // --- use the temporary database range to find the
1305 // imported data's size ---
1306 unoidl.com.sun.star.beans.XPropertySet xDocProp =
1307 (unoidl.com.sun.star.beans.XPropertySet) getDocument();
1308 uno.Any aRangesObj = xDocProp.getPropertyValue( "DatabaseRanges" );
1309 unoidl.com.sun.star.container.XNameAccess xRanges =
1310 (unoidl.com.sun.star.container.XNameAccess) aRangesObj.Value;
1311 String[] aNames = xRanges.getElementNames();
1312 for ( int i=0; i<aNames.Length; i++ )
1313 {
1314 uno.Any aRangeObj = xRanges.getByName( aNames[i] );
1315 unoidl.com.sun.star.beans.XPropertySet xRangeProp =
1316 (unoidl.com.sun.star.beans.XPropertySet) aRangeObj.Value;
1317 bool bUser = (Boolean)
1318 xRangeProp.getPropertyValue( "IsUserDefined" ).Value;
1319 if ( !bUser )
1320 {
1321 // this is the temporary database range -
1322 // get the cell range and format it
1323 unoidl.com.sun.star.sheet.XCellRangeReferrer xRef =
1324 (unoidl.com.sun.star.sheet.XCellRangeReferrer)
1325 aRangeObj.Value;
1326 unoidl.com.sun.star.table.XCellRange xResultRange =
1327 xRef.getReferredCells();
1328 unoidl.com.sun.star.beans.XPropertySet xResultProp =
1329 (unoidl.com.sun.star.beans.XPropertySet) xResultRange;
1330 xResultProp.setPropertyValue(
1331 "IsCellBackgroundTransparent", new uno.Any( false ) );
1332 xResultProp.setPropertyValue(
1333 "CellBackColor", new uno.Any( (Int32) 0xFFFFCC ) );
1334 }
1335 }
1336 }
1337 else
1338 Console.WriteLine("can't get database");
1339 }
1340
1341
1342
1343 private void doDataPilotSamples()
1344 {
1345 Console.WriteLine( "\n*** Samples for Data Pilot ***\n" );
1346 unoidl.com.sun.star.sheet.XSpreadsheet xSheet = getSpreadsheet( 0 );
1347
1348
1349 // --- Create a new DataPilot table ---
1350 prepareRange( xSheet, "A38:C38", "Data Pilot" );
1351 unoidl.com.sun.star.sheet.XDataPilotTablesSupplier xDPSupp =
1352 (unoidl.com.sun.star.sheet.XDataPilotTablesSupplier) xSheet;
1353 unoidl.com.sun.star.sheet.XDataPilotTables xDPTables =
1354 xDPSupp.getDataPilotTables();
1355 unoidl.com.sun.star.sheet.XDataPilotDescriptor xDPDesc =
1356 xDPTables.createDataPilotDescriptor();
1357 // set source range (use data range from CellRange test)
1358 unoidl.com.sun.star.table.CellRangeAddress aSourceAddress =
1359 createCellRangeAddress( xSheet, "A10:C30" );
1360 xDPDesc.setSourceRange( aSourceAddress );
1361 // settings for fields
1362 unoidl.com.sun.star.container.XIndexAccess xFields =
1363 xDPDesc.getDataPilotFields();
1364 uno.Any aFieldObj;
1365 unoidl.com.sun.star.beans.XPropertySet xFieldProp;
1366 // use first column as column field
1367 aFieldObj = xFields.getByIndex(0);
1368 xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
1369 xFieldProp.setPropertyValue(
1370 "Orientation",
1371 new uno.Any(
1372 typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation),
1373 unoidl.com.sun.star.sheet.DataPilotFieldOrientation.COLUMN ) );
1374 // use second column as row field
1375 aFieldObj = xFields.getByIndex(1);
1376 xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
1377 xFieldProp.setPropertyValue(
1378 "Orientation",
1379 new uno.Any(
1380 typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation),
1381 unoidl.com.sun.star.sheet.DataPilotFieldOrientation.ROW ) );
1382 // use third column as data field, calculating the sum
1383 aFieldObj = xFields.getByIndex(2);
1384 xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
1385 xFieldProp.setPropertyValue(
1386 "Orientation",
1387 new uno.Any(
1388 typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation),
1389 unoidl.com.sun.star.sheet.DataPilotFieldOrientation.DATA ) );
1390 xFieldProp.setPropertyValue(
1391 "Function",
1392 new uno.Any(
1393 typeof (unoidl.com.sun.star.sheet.GeneralFunction),
1394 unoidl.com.sun.star.sheet.GeneralFunction.SUM ) );
1395 // select output position
1396 unoidl.com.sun.star.table.CellAddress aDestAddress =
1397 createCellAddress( xSheet, "A40" );
1398 xDPTables.insertNewByName( "DataPilotExample", aDestAddress, xDPDesc );
1399
1400
1401 // --- Modify the DataPilot table ---
1402 uno.Any aDPTableObj = xDPTables.getByName( "DataPilotExample" );
1403 xDPDesc =
1404 (unoidl.com.sun.star.sheet.XDataPilotDescriptor) aDPTableObj.Value;
1405 xFields = xDPDesc.getDataPilotFields();
1406 // add a second data field from the third column,
1407 // calculating the average
1408 aFieldObj = xFields.getByIndex(2);
1409 xFieldProp = (unoidl.com.sun.star.beans.XPropertySet) aFieldObj.Value;
1410 xFieldProp.setPropertyValue(
1411 "Orientation",
1412 new uno.Any(
1413 typeof (unoidl.com.sun.star.sheet.DataPilotFieldOrientation),
1414 unoidl.com.sun.star.sheet.DataPilotFieldOrientation.DATA ) );
1415 xFieldProp.setPropertyValue(
1416 "Function",
1417 new uno.Any(
1418 typeof (unoidl.com.sun.star.sheet.GeneralFunction),
1419 unoidl.com.sun.star.sheet.GeneralFunction.AVERAGE ) );
1420 }
1421
1422
1423
1424 private void doFunctionAccessSamples()
1425 {
1426 Console.WriteLine( "\n*** Samples for function handling ***\n" );
1427 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager =
1428 getServiceManager();
1429
1430
1431 // --- Calculate a function ---
1432 Object aFuncInst = xServiceManager.createInstance(
1433 "com.sun.star.sheet.FunctionAccess" );
1434 unoidl.com.sun.star.sheet.XFunctionAccess xFuncAcc =
1435 (unoidl.com.sun.star.sheet.XFunctionAccess) aFuncInst;
1436 // put the data in a two-dimensional array
1437 Double [][] aData = { Double.valueOf [] { 1.0, 2.0, 3.0 } };
1438 // construct the array of function arguments
1439 uno.Any [] aArgs = new uno.Any [2];
1440 aArgs[0] = new uno.Any( typeof (Double [][]), aData );
1441 aArgs[1] = new uno.Any( (Double) 2.0 );
1442 uno.Any aResult = xFuncAcc.callFunction( "ZTEST", aArgs );
1443 Console.WriteLine(
1444 "ZTEST result for data {1,2,3} and value 2 is " + aResult.Value );
1445
1446
1447 // --- Get the list of recently used functions ---
1448 Object aRecInst = xServiceManager.createInstance(
1449 "com.sun.star.sheet.RecentFunctions" );
1450 unoidl.com.sun.star.sheet.XRecentFunctions xRecFunc =
1451 (unoidl.com.sun.star.sheet.XRecentFunctions) aRecInst;
1452 int[] nRecentIds = xRecFunc.getRecentFunctionIds();
1453
1454
1455 // --- Get the names for these functions ---
1456 Object aDescInst = xServiceManager.createInstance(
1457 "com.sun.star.sheet.FunctionDescriptions" );
1458 unoidl.com.sun.star.sheet.XFunctionDescriptions xFuncDesc =
1459 (unoidl.com.sun.star.sheet.XFunctionDescriptions) aDescInst;
1460 Console.Write("Recently used functions: ");
1461 for (int nFunction=0; nFunction<nRecentIds.Length; nFunction++)
1462 {
1463 unoidl.com.sun.star.beans.PropertyValue[] aProperties =
1464 xFuncDesc.getById( nRecentIds[nFunction] );
1465 for (int nProp=0; nProp<aProperties.Length; nProp++)
1466 if ( aProperties[nProp].Name.Equals( "Name" ) )
1467 Console.Write( aProperties[nProp].Value + " " );
1468 }
1469 Console.WriteLine();
1470 }
1471
1472
1473
1474 private void doApplicationSettingsSamples()
1475 {
1476 Console.WriteLine( "\n*** Samples for application settings ***\n" );
1477 unoidl.com.sun.star.lang.XMultiServiceFactory xServiceManager =
1478 getServiceManager();
1479
1480
1481 // --- Get the user defined sort lists ---
1482 Object aSettings = xServiceManager.createInstance(
1483 "com.sun.star.sheet.GlobalSheetSettings" );
1484 unoidl.com.sun.star.beans.XPropertySet xPropSet =
1485 (unoidl.com.sun.star.beans.XPropertySet) aSettings;
1486 String[] aEntries = (String [])
1487 xPropSet.getPropertyValue( "UserLists" ).Value;
1488 Console.WriteLine("User defined sort lists:");
1489 for ( int i=0; i<aEntries.Length; i++ )
1490 Console.WriteLine( aEntries[i] );
1491 }
1492
1493
1494
1495 }
FREE OFFICE SUITE