1 /* 2 * CellBinding.java 3 * 4 * Created on 12. Mai 2004, 15:15 5 */ 6 7 package integration.forms; 8 9 import com.sun.star.uno.*; 10 import com.sun.star.util.*; 11 import com.sun.star.lang.*; 12 import com.sun.star.beans.*; 13 import com.sun.star.form.binding.*; 14 import com.sun.star.accessibility.*; 15 import com.sun.star.awt.XListBox; 16 import com.sun.star.table.CellAddress; 17 import com.sun.star.table.XCell; 18 import com.sun.star.sheet.XCellRangeData; 19 import com.sun.star.sheet.XCellRangeFormula; 20 import com.sun.star.table.CellRangeAddress; 21 import com.sun.star.text.XTextRange; 22 23 /** 24 * 25 * @author fs93730 26 */ 27 public class CellBinding extends complexlib.ComplexTestCase 28 { 29 /** the test document our form layer lives in */ 30 private SpreadsheetDocument m_document; 31 /** our form layer */ 32 private FormLayer m_formLayer; 33 /** our service factory */ 34 private XMultiServiceFactory m_orb; 35 36 /** Creates a new instance of CellBinding */ 37 public CellBinding() 38 { 39 } 40 41 public String[] getTestMethodNames() 42 { 43 return new String[] { 44 "checkTextFieldBinding", 45 "checkBooleanRadioBinding", 46 "checkStringRadioBinding", 47 "checkBooleanCheckBoxBinding", 48 "checkStringCheckBoxBinding", 49 "checkListBoxBinding", 50 "checkListBoxIndexBinding" 51 }; 52 } 53 54 public String getTestObjectName() 55 { 56 return "Form Control Spreadsheet Cell Binding Test"; 57 } 58 59 /* ------------------------------------------------------------------ */ 60 /** closes our document, if we have an open one 61 */ 62 private void closeDocument() 63 { 64 try 65 { 66 // close our document 67 if ( m_document != null ) 68 { 69 XCloseable closeDoc = (XCloseable)UnoRuntime.queryInterface( XCloseable.class, 70 m_document.getDocument() ); 71 closeDoc.close( true ); 72 } 73 } 74 catch ( com.sun.star.uno.Exception e ) 75 { 76 } 77 } 78 79 /* ------------------------------------------------------------------ */ 80 public void before() throws com.sun.star.uno.Exception, java.lang.Exception 81 { 82 m_orb = (XMultiServiceFactory)param.getMSF(); 83 m_document = new SpreadsheetDocument( m_orb ); 84 m_formLayer = new FormLayer( m_document ); 85 } 86 87 /* ------------------------------------------------------------------ */ 88 public void after() throws com.sun.star.uno.Exception, java.lang.Exception 89 { 90 closeDocument(); 91 } 92 93 /* ------------------------------------------------------------------ */ 94 public void checkTextFieldBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 95 { 96 final short col = 0; 97 final short row = 2; 98 final String text = new String( "content" ); 99 final String otherText = new String( "something else" ); 100 final String yetAnotherText = new String( "yet another text" ); 101 102 // cretae a normal text control 103 XPropertySet controlModel = m_formLayer.createControlAndShape( "DatabaseTextField", 30, 9, 30, 6 ); 104 105 // bind it to cell A1 106 bindToCell( controlModel, col, row ); 107 108 // switch to alive mode 109 m_document.getCurrentView().toggleFormDesignMode(); 110 111 // test the data transfer control -> cell 112 simulateUserTextInput( controlModel, text ); 113 verifyStringCellContent( col, row, text, "A text field does not forward its user input to the cell." ); 114 115 // the same, but this time changing the control value programmatically 116 controlModel.setPropertyValue( "Text", otherText ); 117 verifyStringCellContent( col, row, otherText, "A text field does not forward programmatic changes to the cell." ); 118 119 // the other way round: cell->control 120 setCellText( col, row, yetAnotherText ); 121 String controlText = (String)controlModel.getPropertyValue( "Text" ); 122 if ( !controlText.equals( yetAnotherText ) ) 123 failed( "Changes in the cell are not forwarded to the text field." ); 124 } 125 /* ------------------------------------------------------------------ */ 126 public void checkBooleanRadioBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 127 { 128 // two radio buttons 129 XPropertySet primaryRadio = createRadio( 28, "radio button no. 1", "radio group", "primary" ); 130 XPropertySet secondaryRadio = createRadio( 33, "radio button no. 2", "radio group", "secodary" ); 131 132 // bind them 133 short col = (short)0; 134 short row1 = (short)6; 135 short row2 = (short)7; 136 bindToCell( primaryRadio, col, row1 ); 137 bindToCell( secondaryRadio, col, row2 ); 138 139 // check the first button 140 simulateUserRadioCheck( primaryRadio ); 141 // check the cell content 142 verifyNumericCellContent( col, row1, 1, "Radio buttons do not forward their (boolean) values to cells (1)." ); 143 verifyNumericCellContent( col, row2, 0, "Radio buttons do not forward their (boolean) values to cells (2)." ); 144 // check the second button 145 simulateUserRadioCheck( secondaryRadio ); 146 // check the cell content 147 verifyNumericCellContent( col, row1, 0, "Radio buttons do not forward their (boolean) values to cells (3)." ); 148 verifyNumericCellContent( col, row2, 1, "Radio buttons do not forward their (boolean) values to cells (4)." ); 149 150 // the other way round: writing values into the cell 151 setCellValue( col, row1, 1.0 ); 152 // setting this should have checked the primary radio, which should have unchecked the secondary radio, 153 // which should have been propagated to the second cell 154 verifyNumericCellContent( col, row2, 0, "Changing primary cell is not propagated to the secondary cell (via the radio buttons)." ); 155 156 // setting an empty cell should result in the radio being unchecked 157 setCellEmpty( col, row1 ); 158 if ( ((Short)primaryRadio.getPropertyValue( "State" )).shortValue() != 0 ) 159 failed( "Setting a cell to 'empty' does not reset the bound radio button." ); 160 } 161 162 /* ------------------------------------------------------------------ */ 163 public void checkStringRadioBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 164 { 165 // two radio buttons 166 XPropertySet primaryRadio = createRadio( 46, "radio button A", "radio ref group", "primary" ); 167 XPropertySet secondaryRadio = createRadio( 51, "radio button B", "radio ref group", "secodary" ); 168 169 // give the ref values 170 String refValueA = new String( "ref value A" ); 171 String refValueB = new String( "ref value B" ); 172 primaryRadio.setPropertyValue( "RefValue", refValueA ); 173 secondaryRadio.setPropertyValue( "RefValue", refValueB ); 174 175 // bind them to the same cell 176 short col = (short)0; 177 short row = (short)10; 178 bindToCell( primaryRadio, col, row ); 179 bindToCell( secondaryRadio, col, row ); 180 181 // checking a radio should set the respective ref value at the cell 182 simulateUserRadioCheck( primaryRadio ); 183 verifyStringCellContent( col, row, refValueA, "A bound radio button with a reference value does not pass this value to the cell upon checking (1)." ); 184 simulateUserRadioCheck( secondaryRadio ); 185 verifyStringCellContent( col, row, refValueB, "A bound radio button with a reference value does not pass this value to the cell upon checking (2)." ); 186 187 // changing the cell should check the buttons if the cell text equals the ref value 188 setCellText( col, row, "no ref value" ); 189 verifyRadioStates( primaryRadio, secondaryRadio, (short)0, (short)0, "Radio button not unchecked, though the bound cell value does not equal ref value." ); 190 191 setCellText( col, row, refValueA ); 192 verifyRadioStates( primaryRadio, secondaryRadio, (short)1, (short)0, "Radio button not properly un/checked according to the cell and ref value (1)." ); 193 194 setCellText( col, row, refValueB ); 195 verifyRadioStates( primaryRadio, secondaryRadio, (short)0, (short)1, "Radio button not properly un/checked according to the cell and ref value (2)." ); 196 } 197 198 /* ------------------------------------------------------------------ */ 199 public void checkBooleanCheckBoxBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 200 { 201 XPropertySet checkBox = m_formLayer.createControlAndShape( "DatabaseCheckBox", 30, 59, 40, 4 ); 202 checkBox.setPropertyValue( "Label", "check box" ); 203 checkBox.setPropertyValue( "TriState", new Boolean( true ) ); 204 205 short col = (short)0; 206 short row = (short)13; 207 bindToCell( checkBox, col, row ); 208 209 // initialize with "not checked" 210 checkBox.setPropertyValue( "State", new Short( (short)0 ) ); 211 verifyNumericCellContent( col, row, 0, "programmatically unchecking the check box is not propagated to the cell." ); 212 213 // first click: "not checked" -> "checked" 214 simulateUserCheckBoxCheck( checkBox, (short)1 ); 215 verifyNumericCellContent( col, row, 1, "moving the check box state to 'checked' is not propagated to the cell." ); 216 217 // second click: "checked" -> "indetermined" 218 simulateUserCheckBoxCheck( checkBox, (short)2 ); 219 verifyVoidCell( col, row, "propagating the 'indetermined' state to the cell does not work." ); 220 221 // third click: "indetermined" -> "not checked" 222 simulateUserCheckBoxCheck( checkBox, (short)0 ); 223 verifyNumericCellContent( col, row, 0, "unchecking a check box via UI is not propagated to the cell." ); 224 } 225 226 /* ------------------------------------------------------------------ */ 227 public void checkStringCheckBoxBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 228 { 229 String refValue = new String( "checked " ); 230 231 XPropertySet checkBox = m_formLayer.createControlAndShape( "DatabaseCheckBox", 30, 68, 40, 4 ); 232 checkBox.setPropertyValue( "Label", "check box with ref value" ); 233 checkBox.setPropertyValue( "TriState", new Boolean( true ) ); 234 checkBox.setPropertyValue( "RefValue", refValue ); 235 236 short col = (short)0; 237 short row = (short)15; 238 bindToCell( checkBox, col, row ); 239 240 // initialize with "not checked" 241 checkBox.setPropertyValue( "State", new Short( (short)0 ) ); 242 verifyNumericCellContent( col, row, 0, "programmatically unchecking the check box is not propagated to the cell." ); 243 244 // first click: "not checked" -> "checked" 245 simulateUserCheckBoxCheck( checkBox, (short)1 ); 246 verifyStringCellContent( col, row, refValue, "moving the check box state to 'checked' does not propagated the ref value to the cell." ); 247 248 // second click: "checked" -> "indetermined" 249 simulateUserCheckBoxCheck( checkBox, (short)2 ); 250 verifyVoidCell( col, row, "propagating the 'indetermined' state to the cell does not work, when exchanging ref values." ); 251 252 // third click: "indetermined" -> "not checked" 253 simulateUserCheckBoxCheck( checkBox, (short)0 ); 254 verifyStringCellContent( col, row, "", "unchecking a check box via UI does not propagated the ref value to the cell." ); 255 } 256 257 /* ------------------------------------------------------------------ */ 258 /** verifies that a list box, which is bound via an ordinary value binding, 259 * works as expected 260 */ 261 public void checkListBoxBinding( ) throws com.sun.star.uno.Exception, java.lang.Exception 262 { 263 XPropertySet listBox = m_formLayer.createControlAndShape( "DatabaseListBox", 30, 80, 40, 6 ); 264 listBox.setPropertyValue( "Dropdown", new Boolean( true ) ); 265 listBox.setPropertyValue( "StringItemList", new String[] { "Apples", "Oranges", "Peaches" } ); 266 267 short col = (short)0; 268 short row = (short)18; 269 270 // ............................................................... 271 // add a list entry source which fills the list boxes list from cells in the 272 // spreadsheet 273 short sourceCol = (short)4; 274 setCellText( sourceCol, (short)( row - 1 ), "Apples" ); 275 setCellText( sourceCol, (short)( row + 0 ), "Oranges" ); 276 setCellText( sourceCol, (short)( row + 1 ), "Peaches" ); 277 278 //setListSource( listBox, sourceCol, row, (short)( row + 2 ) ); 279 // TODO: this is currently prone to deadlocks 280 281 // ............................................................... 282 // bind to a cell 283 bindToCell( listBox, col, row ); 284 285 // ............................................................... 286 // do the tests 287 listBox.setPropertyValue( "SelectedItems", new short[] { (short)0 } ); 288 verifyStringCellContent( col, row, "Apples", "programmatically selecting a list entry is not propagated to the cell." ); 289 290 simulateUserListBoxSelection( listBox, "Oranges" ); 291 verifyStringCellContent( col, row, "Oranges", "UI-selecting a list entry is not propagated to the cell." ); 292 293 setCellText( col, row, "Peaches" ); 294 short[] selectedItems = (short[])listBox.getPropertyValue( "SelectedItems" ); 295 assureEquals( "changes in the cell bound to a list box are not propagated to the list box selection", 296 2, selectedItems[0] ); 297 } 298 299 /* ------------------------------------------------------------------ */ 300 /** verifies that a list box, which is bound via a value binding exchanging the <b>index</b> 301 * of the selected entry, works as expected 302 */ 303 public void checkListBoxIndexBinding() throws com.sun.star.uno.Exception, java.lang.Exception 304 { 305 XPropertySet listBox = m_formLayer.createControlAndShape( "DatabaseListBox", 30, 94, 40, 6 ); 306 listBox.setPropertyValue( "Dropdown", new Boolean( true ) ); 307 listBox.setPropertyValue( "StringItemList", new String[] { "Pears", "Bananas", "Strawberries" } ); 308 309 short col = (short)0; 310 short row = (short)21; 311 312 // ............................................................... 313 // add a list entry source which fills the list boxes list from cells in the 314 // spreadsheet 315 short sourceCol = (short)4; 316 setCellText( sourceCol, (short)( row - 1 ), "Pears" ); 317 setCellText( sourceCol, (short)( row + 0 ), "Bananas" ); 318 setCellText( sourceCol, (short)( row + 1 ), "Strawberries" ); 319 320 //setListSource( listBox, sourceCol, row, (short)( row + 2 ) ); 321 // TODO: this is currently prone to deadlocks 322 323 // ............................................................... 324 // bind to a cell 325 bindToCell( listBox, col, row, "com.sun.star.table.ListPositionCellBinding" ); 326 327 // ............................................................... 328 // do the tests 329 listBox.setPropertyValue( "SelectedItems", new short[] { (short)0 } ); 330 verifyNumericCellContent( col, row, 1, "programmatically selecting a list entry is not propagated (as index) to the cell." ); 331 332 simulateUserListBoxSelection( listBox, "Bananas" ); 333 verifyNumericCellContent( col, row, 2, "UI-selecting a list entry is not propagated (as index) to the cell." ); 334 335 setCellValue( col, row, 3 ); 336 short[] selectedItems = (short[])listBox.getPropertyValue( "SelectedItems" ); 337 assureEquals( "changes in the cell bound to a list box via list index are not propagated to the list box selection", 338 2, selectedItems[0] ); 339 } 340 341 /* ------------------------------------------------------------------ */ 342 /** verifies that the content of a given cell equals a given string 343 */ 344 private XPropertySet createRadio( int yPos, String label, String name, String tag ) throws com.sun.star.uno.Exception, java.lang.Exception 345 { 346 XPropertySet radio = m_formLayer.createControlAndShape( "DatabaseRadioButton", 30, yPos, 40, 4 ); 347 radio.setPropertyValue( "Label", label ); 348 radio.setPropertyValue( "Name", name ); 349 radio.setPropertyValue( "Tag", tag ); 350 return radio; 351 } 352 353 /* ------------------------------------------------------------------ */ 354 /** verifies the states of two radio button 355 */ 356 private boolean verifyRadioStates( XPropertySet radio1, XPropertySet radio2, short value1, short value2, 357 String errorMessage ) throws com.sun.star.uno.Exception, java.lang.Exception 358 { 359 if ( ( ((Short)radio1.getPropertyValue( "State" )).shortValue() != value1 ) 360 || ( ((Short)radio2.getPropertyValue( "State" )).shortValue() != value2 ) 361 ) 362 { 363 failed( errorMessage ); 364 return false; 365 } 366 return true; 367 } 368 369 /* ------------------------------------------------------------------ */ 370 /** verifies that the content of a given cell equals a given string 371 */ 372 private boolean verifyVoidCell( short col, short row, String failErrorMessage ) throws com.sun.star.uno.Exception 373 { 374 XCellRangeData cell = (XCellRangeData)UnoRuntime.queryInterface( XCellRangeData.class, 375 m_document.getSheet( 0 ).getCellByPosition( col, row ) 376 ); 377 Object cellContent = cell.getDataArray()[0][0]; 378 if ( ((com.sun.star.uno.Any)cellContent).getType().getTypeClass() != com.sun.star.uno.TypeClass.VOID ) 379 { 380 failed( failErrorMessage ); 381 return false; 382 } 383 return true; 384 } 385 386 /* ------------------------------------------------------------------ */ 387 /** verifies that the content of a given cell equals a given string 388 */ 389 private boolean verifyNumericCellContent( short col, short row, double value, String failErrorMessage ) throws com.sun.star.uno.Exception 390 { 391 XCell cell = (XCell)UnoRuntime.queryInterface( XCell.class, 392 m_document.getSheet( 0 ).getCellByPosition( col, row ) 393 ); 394 if ( cell.getValue() != value ) 395 { 396 failed( failErrorMessage ); 397 return false; 398 } 399 return true; 400 } 401 402 /* ------------------------------------------------------------------ */ 403 /** verifies that the content of a given cell equals a given string 404 */ 405 private boolean verifyStringCellContent( short col, short row, String text, String failErrorMessage ) throws com.sun.star.uno.Exception 406 { 407 XTextRange cell = (XTextRange)UnoRuntime.queryInterface( XTextRange.class, 408 m_document.getSheet( 0 ).getCellByPosition( col, row ) 409 ); 410 if ( !cell.getString().equals( text ) ) 411 { 412 failed( failErrorMessage ); 413 return false; 414 } 415 return true; 416 } 417 418 /* ------------------------------------------------------------------ */ 419 /** sets the text of a given cell to a given string 420 */ 421 private void setCellText( short col, short row, String text ) throws com.sun.star.uno.Exception 422 { 423 XTextRange cell = (XTextRange)UnoRuntime.queryInterface( XTextRange.class, 424 m_document.getSheet( 0 ).getCellByPosition( col, row ) 425 ); 426 cell.setString( text ); 427 } 428 429 /* ------------------------------------------------------------------ */ 430 /** sets a numeric value in a given cell 431 */ 432 private void setCellValue( short col, short row, double value ) throws com.sun.star.uno.Exception 433 { 434 XCell cell = (XCell)UnoRuntime.queryInterface( XCell.class, 435 m_document.getSheet( 0 ).getCellByPosition( col, row ) 436 ); 437 cell.setValue( value ); 438 } 439 440 /* ------------------------------------------------------------------ */ 441 /** sets a numeric value in a given cell 442 */ 443 private void setCellEmpty( short col, short row ) throws com.sun.star.uno.Exception 444 { 445 // as long as #i29130# is not fixed, we do not set the cell to "empty", but to 446 // an invalid formular, which serves well for our purpose 447 XCellRangeFormula cell = (XCellRangeFormula)UnoRuntime.queryInterface( XCellRangeFormula.class, 448 m_document.getSheet( 0 ).getCellByPosition( col, row ) 449 ); 450 String[][] args = new String[][] { new String[] { "=INVALID_FUNCTION()" } }; 451 cell.setFormulaArray( args ); 452 } 453 454 /* ------------------------------------------------------------------ */ 455 /** binds the given control model to the given cell in the first sheet, 456 * using the given service name for the binding 457 */ 458 private void bindToCell( XPropertySet controlModel, short column, short row, String _bindingServiceName ) throws com.sun.star.uno.Exception 459 { 460 XBindableValue bindableModel = (XBindableValue)UnoRuntime.queryInterface( XBindableValue.class, 461 controlModel 462 ); 463 464 CellAddress address = new CellAddress(); 465 address.Column = column; 466 address.Row = row; 467 address.Sheet = 0; 468 469 NamedValue[] parameters = new NamedValue[] { new NamedValue() }; 470 parameters[0].Name = "BoundCell"; 471 parameters[0].Value = address; 472 473 XValueBinding cellBinding = (XValueBinding)UnoRuntime.queryInterface( XValueBinding.class, 474 m_document.createInstanceWithArguments( _bindingServiceName, parameters ) 475 ); 476 477 bindableModel.setValueBinding( cellBinding ); 478 } 479 480 /* ------------------------------------------------------------------ */ 481 /** binds the given control model to the given cell in the first sheet 482 */ 483 private void bindToCell( XPropertySet _controlModel, short _column, short _row ) throws com.sun.star.uno.Exception 484 { 485 bindToCell( _controlModel, _column, _row, "com.sun.star.table.CellValueBinding" ); 486 } 487 488 /* ------------------------------------------------------------------ */ 489 /** sets the given cell range as list entry source for the given control 490 */ 491 private void setListSource( XPropertySet _listSink, short _sourceCol, short _rowStart, short _rowEnd ) throws com.sun.star.uno.Exception 492 { 493 CellRangeAddress listSourceAddress = new CellRangeAddress( (short)0, (int)_sourceCol, (int)_rowStart, (int)_sourceCol, (int)_rowEnd ); 494 NamedValue addressParameter = new NamedValue( "CellRange", listSourceAddress ); 495 496 XListEntrySource listSource = (XListEntrySource)UnoRuntime.queryInterface( XListEntrySource.class, 497 m_document.createInstanceWithArguments( "com.sun.star.table.CellRangeListSource", new NamedValue[]{ addressParameter } ) 498 ); 499 XListEntrySink listSink = (XListEntrySink)UnoRuntime.queryInterface( XListEntrySink.class, 500 _listSink ); 501 listSink.setListEntrySource( listSource ); 502 } 503 504 /* ------------------------------------------------------------------ */ 505 /** simulates a user action to check a radio button 506 */ 507 private void simulateUserRadioCheck( XPropertySet radioModel ) throws com.sun.star.uno.Exception 508 { 509 XAccessible accessible = (XAccessible)UnoRuntime.queryInterface( 510 XAccessible.class, m_document.getCurrentView().getControl( radioModel ) ); 511 512 XAccessibleValue xValue = (XAccessibleValue)UnoRuntime.queryInterface( 513 XAccessibleValue.class, accessible.getAccessibleContext() ); 514 515 Integer newValue = new Integer( 1 ); 516 xValue.setCurrentValue( newValue ); 517 } 518 519 /* ------------------------------------------------------------------ */ 520 /** simulates a user action to check a radio button 521 */ 522 private void simulateUserCheckBoxCheck( XPropertySet checkBox, short state ) throws com.sun.star.uno.Exception 523 { 524 XAccessible accessible = (XAccessible)UnoRuntime.queryInterface( 525 XAccessible.class, m_document.getCurrentView().getControl( checkBox ) ); 526 527 XAccessibleValue xValue = (XAccessibleValue)UnoRuntime.queryInterface( 528 XAccessibleValue.class, accessible.getAccessibleContext() ); 529 530 xValue.setCurrentValue( new Short( state ) ); 531 } 532 533 /* ------------------------------------------------------------------ */ 534 /** simulates a user selecting an entry in a list box 535 */ 536 private void simulateUserListBoxSelection( XPropertySet _listBox, String _selectEntry ) throws com.sun.star.uno.Exception 537 { 538 XListBox listBoxControl = (XListBox)UnoRuntime.queryInterface( 539 XListBox.class, m_document.getCurrentView().getControl( _listBox ) ); 540 listBoxControl.selectItem( _selectEntry, true ); 541 } 542 543 /* ------------------------------------------------------------------ */ 544 /** simulates text input into the control belonging to the given model 545 */ 546 private void simulateUserTextInput( XPropertySet controlModel, String text ) throws com.sun.star.uno.Exception 547 { 548 XAccessible accessible = (XAccessible)UnoRuntime.queryInterface( 549 XAccessible.class, m_document.getCurrentView().getControl( controlModel ) ); 550 551 XAccessibleContext context = accessible.getAccessibleContext(); 552 XServiceInfo si = (XServiceInfo)UnoRuntime.queryInterface( XServiceInfo.class, 553 accessible.getAccessibleContext() ); 554 555 XAccessibleEditableText textAccess = (XAccessibleEditableText)UnoRuntime.queryInterface( 556 XAccessibleEditableText.class, accessible.getAccessibleContext() ); 557 558 textAccess.setText( text ); 559 } 560 } 561