1 package fvt.uno.sc.cell; 2 3 import static org.junit.Assert.*; 4 5 import org.junit.After; 6 import org.junit.Before; 7 import org.junit.Test; 8 9 import org.openoffice.test.uno.UnoApp; 10 11 import com.sun.star.container.XIndexAccess; 12 import com.sun.star.lang.XComponent; 13 import com.sun.star.sheet.CellDeleteMode; 14 import com.sun.star.sheet.CellInsertMode; 15 import com.sun.star.sheet.XCellRangeAddressable; 16 import com.sun.star.sheet.XSpreadsheet; 17 import com.sun.star.sheet.XSpreadsheetDocument; 18 import com.sun.star.sheet.XSpreadsheets; 19 import com.sun.star.table.XCell; 20 import com.sun.star.uno.UnoRuntime; 21 import com.sun.star.table.XCellRange; 22 import com.sun.star.table.CellRangeAddress; 23 import com.sun.star.sheet.XCellRangeMovement; 24 25 /** 26 * Test insert or delete cells 27 * @author BinGuo 8/30/2012 28 * 29 */ 30 31 public class InsertDeleteCells { 32 33 UnoApp unoApp = new UnoApp(); 34 XSpreadsheetDocument scDocument = null; 35 XComponent scComponent = null; 36 37 @Before 38 public void setUp() throws Exception { 39 unoApp.start(); 40 } 41 42 @After 43 public void tearDown() throws Exception { 44 unoApp.closeDocument(scComponent); 45 unoApp.close(); 46 } 47 48 /** 49 * New spreadsheet 50 * Create 3x3 cell range A2:C4 51 * Execute insert empty A2 & B2 cells shift other existing cells in Column A & B down 52 * Execute insert empty A2 & B2 cells shift other existing cells in row 2 move right 53 * Execute insert entire empty Row 2 make the whole existing cell range moves down 54 * Execute insert entire empty Columns A & B make the whole existing cell range moves right 55 * Verify results after insert cells 56 */ 57 58 @Test 59 public void testInsertCells() throws Exception { 60 61 scComponent = unoApp.newDocument("scalc"); 62 scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, scComponent); 63 XSpreadsheets xSpreadsheets = scDocument.getSheets(); 64 65 // Gets the first sheet in the document. 66 XIndexAccess xSheetsIA = (XIndexAccess)UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); 67 Object sheetObj = (XSpreadsheet)UnoRuntime.queryInterface(XSpreadsheet.class, xSheetsIA.getByIndex(0)); 68 XSpreadsheet xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, sheetObj); 69 70 // Create a 3x3 cell range "A2:C4" with the values 0 ... 8. 71 int nCol = 0; 72 int nValue = 0; 73 74 for (int n = 1; n < 4; ++n){ 75 int nRow = 1; 76 for (int i = 1; i < 4; ++i) { 77 xSheet.getCellByPosition( nCol, nRow ).setValue( nValue ); 78 nRow += 1; 79 nValue += 1; 80 } 81 nCol += 1; 82 } 83 84 //Insert 2 cells in A2:B2 and shift other existing cells in Column A & B down 85 86 // Get cell range A2:B2 by position - (column, row, column, row) 87 XCellRange xCellRange = xSheet.getCellRangeByPosition( 0, 1, 1, 1 ); 88 XCellRangeMovement xCellRangeMovement = (XCellRangeMovement) 89 UnoRuntime.queryInterface(XCellRangeMovement.class, xSheet); 90 91 // Gets the selected range's address/location. 92 XCellRangeAddressable xCellRangeAddr = (XCellRangeAddressable) 93 UnoRuntime.queryInterface( XCellRangeAddressable.class, xCellRange ); 94 CellRangeAddress address = xCellRangeAddr.getRangeAddress(); 95 96 //Execute Insert cells in A2:B2 and shift other existing cells in Column A & B down 97 xCellRangeMovement.insertCells(address, CellInsertMode.DOWN); 98 99 //Get value of cell A2, B2 and C2 100 XCell cellA2 = xSheet.getCellByPosition(0, 1); 101 XCell cellB2 = xSheet.getCellByPosition(1, 1); 102 XCell cellC2 = xSheet.getCellByPosition(2, 1); 103 double expectValueA2 = 0.0; 104 double expectValueB2 = 0.0; 105 double expectValueC2 = 6; 106 107 //Verify results after execute Insert cells in A2:B2 and shift other existing cells in Column A & B down 108 assertEquals("Verify value of A2 after execute Insert cells in A2:B2 and shift cells down.", 109 expectValueA2, cellA2.getValue(),0); 110 assertEquals("Verify value of B2 after execute Insert cells in A2:B2 and shift cells down.", 111 expectValueB2, cellB2.getValue(),0); 112 assertEquals("Verify value of C2 after execute Insert cells in A2:B2 and shift cells down.", 113 expectValueC2, cellC2.getValue(),0); 114 115 //Execute Insert cells in A2:B2 and shift other existing cells in row 2 move right 116 xCellRangeMovement.insertCells(address, CellInsertMode.RIGHT); 117 118 //Get value of cell C2, D2, E2 and C3 119 cellC2 = xSheet.getCellByPosition(2, 1); 120 XCell cellD2 = xSheet.getCellByPosition(3, 1); 121 XCell cellE2 = xSheet.getCellByPosition(4, 1); 122 XCell cellC3 = xSheet.getCellByPosition(2, 2); 123 double expectValueC2right = 0.0; 124 double expectValueD2 = 0.0; 125 double expectValueE2 = 6; 126 double expectValueC3 = 7; 127 128 //Verify results after execute Insert cells in A2:B2 and shift other existing cells in row 2 move right 129 assertEquals("Verify value of C2 after execute Insert cells in A2:B2 and shift cells Right.", 130 expectValueC2right, cellC2.getValue(),0); 131 assertEquals("Verify value of D2 after execute Insert cells in A2:B2 and shift cells Right.", 132 expectValueD2, cellD2.getValue(),0); 133 assertEquals("Verify value of E2 after execute Insert cells in A2:B2 and shift cells Right.", 134 expectValueE2, cellE2.getValue(),0); 135 assertEquals("Verify value of C3 after execute Insert cells in A2:B2 and shift cells Right.", 136 expectValueC3, cellC3.getValue(),0); 137 138 //Execute Insert Entire Row 2 make the whole existing cell range moves down 139 xCellRangeMovement.insertCells(address, CellInsertMode.ROWS); 140 141 //Get value of cell E2, E3 and C3 142 cellE2 = xSheet.getCellByPosition(4, 1); 143 XCell cellE3 = xSheet.getCellByPosition(4, 2); 144 cellC3 = xSheet.getCellByPosition(2, 2); 145 double expectValueE2rows = 0.0; 146 double expectValueE3 = 6; 147 double expectValueC3rows = 0.0; 148 149 //Verify results after execute Insert Entire Row 2 make the whole existing cell range moves down 150 assertEquals("Verify value of E2 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 151 expectValueE2rows, cellE2.getValue(),0); 152 assertEquals("Verify value of E3 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 153 expectValueE3, cellE3.getValue(),0); 154 assertEquals("Verify value of C3 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 155 expectValueC3rows, cellC3.getValue(),0); 156 157 //Execute Insert Entire Columns make the whole existing cell range moves right 158 xCellRangeMovement.insertCells(address, CellInsertMode.COLUMNS); 159 160 //Get value of cell C4, C5 and C6 161 XCell cellC4 = xSheet.getCellByPosition(2, 3); 162 XCell cellC5 = xSheet.getCellByPosition(2, 4); 163 XCell cellC6 = xSheet.getCellByPosition(2, 5); 164 double expectValueC4 = 0.0; 165 double expectValueC5 = 1; 166 double expectValueC6 = 2; 167 168 //Verify results after execute Insert Entire Columns make the whole existing cell range moves right 169 assertEquals("Verify value of E2 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 170 expectValueC4, cellC4.getValue(),0); 171 assertEquals("Verify value of E3 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 172 expectValueC5, cellC5.getValue(),0); 173 assertEquals("Verify value of C3 after execute Insert Entire Row 2 make the whole existing cell range moves down.", 174 expectValueC6, cellC6.getValue(),0); 175 176 } 177 178 /** 179 * New spreadsheet 180 * Create 3x3 cell range A2:C4 181 * Execute delete cells A2 & B2 shift other existing cells in column A & B move up 182 * Execute delete cells A2 & B2 shift other existing cells in row 2 move left 183 * Execute delete entire Row 2 make the whole existing cell range moves up 184 * Execute delete entire Columns A & B make the whole existing cell range moves left 185 * Verify results after delete cells 186 */ 187 188 @Test 189 public void testDeleteCells() throws Exception { 190 191 scComponent = unoApp.newDocument("scalc"); 192 scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, scComponent); 193 XSpreadsheets xSpreadsheets = scDocument.getSheets(); 194 195 // Gets the first sheet in the document. 196 XIndexAccess xSheetsIA = (XIndexAccess)UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); 197 Object sheetObj = (XSpreadsheet)UnoRuntime.queryInterface(XSpreadsheet.class, xSheetsIA.getByIndex(0)); 198 XSpreadsheet xSheet = (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, sheetObj); 199 200 // Create a 3x3 cell range "A2:C4" with the values 0 ... 8. 201 int nCol = 0; 202 int nValue = 0; 203 204 for (int n = 1; n < 4; ++n){ 205 int nRow = 1; 206 for (int i = 1; i < 4; ++i) { 207 xSheet.getCellByPosition( nCol, nRow ).setValue( nValue ); 208 nRow += 1; 209 nValue += 1; 210 } 211 nCol += 1; 212 } 213 214 //Insert 2 cells in A2:B2 and shift cells up 215 216 // Get cell range A2:B2 by position - (column, row, column, row) 217 XCellRange xCellRange = xSheet.getCellRangeByPosition( 0, 1, 1, 1 ); 218 XCellRangeMovement xCellRangeMovement = (XCellRangeMovement) 219 UnoRuntime.queryInterface(XCellRangeMovement.class, xSheet); 220 221 // Gets the selected range's address/location. 222 XCellRangeAddressable xCellRangeAddr = (XCellRangeAddressable) 223 UnoRuntime.queryInterface( XCellRangeAddressable.class, xCellRange ); 224 CellRangeAddress address = xCellRangeAddr.getRangeAddress(); 225 226 //Execute delete cells in A2:B2 and shift cells in column A & B move up 227 xCellRangeMovement.removeRange(address,CellDeleteMode.UP); 228 229 //Get value of cell A2, B2 and C2 230 XCell cellA2 = xSheet.getCellByPosition(0, 1); 231 XCell cellB2 = xSheet.getCellByPosition(1, 1); 232 XCell cellC2 = xSheet.getCellByPosition(2, 1); 233 double expectValueA2up = 1; 234 double expectValueB2up = 4; 235 double expectValueC2up = 6; 236 237 //Verify results after execute delete cells in A2:B2 and shift cells in column A & B move up 238 assertEquals("Verify value of A2 after execute delete cells in A2:B2 and shift cells up.", 239 expectValueA2up, cellA2.getValue(),0); 240 assertEquals("Verify value of B2 after execute delete cells in A2:B2 and shift cells up.", 241 expectValueB2up, cellB2.getValue(),0); 242 assertEquals("Verify value of C2 after execute delete cells in A2:B2 and shift cells up.", 243 expectValueC2up, cellC2.getValue(),0); 244 245 //Execute delete cells in A2:B2 and shift other existing cells in row 2 move left 246 xCellRangeMovement.removeRange(address,CellDeleteMode.LEFT); 247 248 //Get value of cell A2, B2 and C2 249 cellA2 = xSheet.getCellByPosition(0, 1); 250 cellB2 = xSheet.getCellByPosition(1, 1); 251 cellC2 = xSheet.getCellByPosition(2, 1); 252 double expectValueA2left = 6; 253 double expectValueB2left = 0.0; 254 double expectValueC2left = 0.0; 255 256 //Verify results after execute delete cells in A2:B2 and shift other existing cells in row 2 move left 257 assertEquals("Verify value of A2 after execute delete cells in A2:B2 and shift cells left.", 258 expectValueA2left, cellA2.getValue(),0); 259 assertEquals("Verify value of B2 after execute delete cells in A2:B2 and shift cells left.", 260 expectValueB2left, cellB2.getValue(),0); 261 assertEquals("Verify value of C2 after execute delete cells in A2:B2 and shift cells left.", 262 expectValueC2left, cellC2.getValue(),0); 263 264 //Execute delete Entire Row 2 make the whole existing cell range moves up 265 xCellRangeMovement.removeRange(address,CellDeleteMode.ROWS); 266 267 //Get value of cell A2, B2 and C2 268 cellA2 = xSheet.getCellByPosition(0, 1); 269 cellB2 = xSheet.getCellByPosition(1, 1); 270 cellC2 = xSheet.getCellByPosition(2, 1); 271 double expectValueA2rows = 2; 272 double expectValueB2rows = 5; 273 double expectValueC2rows = 7; 274 275 //Verify results after delete Entire Row 2 make the whole existing cell range moves up 276 assertEquals("Verify value of A2 after delete Entire Row 2 make the whole existing cell range moves up.", 277 expectValueA2rows, cellA2.getValue(),0); 278 assertEquals("Verify value of B2 after delete Entire Row 2 make the whole existing cell range moves up.", 279 expectValueB2rows, cellB2.getValue(),0); 280 assertEquals("Verify value of C2 after delete Entire Row 2 make the whole existing cell range moves up.", 281 expectValueC2rows, cellC2.getValue(),0); 282 283 //Execute delete Entire Columns make the whole existing cell range moves left 284 xCellRangeMovement.removeRange(address,CellDeleteMode.COLUMNS); 285 286 //Get value of cell A2, B2 and C2 287 cellA2 = xSheet.getCellByPosition(0, 1); 288 cellB2 = xSheet.getCellByPosition(1, 1); 289 cellC2 = xSheet.getCellByPosition(2, 1); 290 double expectValueA2columns = 7; 291 double expectValueB2columns = 0.0; 292 double expectValueC2columns = 0.0; 293 294 //Verify results after execute delete Entire Columns make the whole existing cell range moves left 295 assertEquals("Verify value of A2 after delete Entire Columns make the whole existing cell range moves left.", 296 expectValueA2columns, cellA2.getValue(),0); 297 assertEquals("Verify value of B2 after delete Entire Columns make the whole existing cell range moves left.", 298 expectValueB2columns, cellB2.getValue(),0); 299 assertEquals("Verify value of C2 after delete Entire Columns make the whole existing cell range moves left.", 300 expectValueC2columns, cellC2.getValue(),0); 301 302 } 303 304 } 305 306 307