View | Details | Raw Unified | Return to issue 120655
Collapse All | Expand All

(-)testuno/source/testlib/uno/SCUtil.java (+81 lines)
Lines 31-36 Link Here
31
import com.sun.star.beans.PropertyValue;
31
import com.sun.star.beans.PropertyValue;
32
import com.sun.star.beans.XPropertySet;
32
import com.sun.star.beans.XPropertySet;
33
import com.sun.star.container.XIndexAccess;
33
import com.sun.star.container.XIndexAccess;
34
import com.sun.star.container.XNamed;
34
import com.sun.star.frame.XController;
35
import com.sun.star.frame.XController;
35
import com.sun.star.frame.XModel;
36
import com.sun.star.frame.XModel;
36
import com.sun.star.frame.XStorable;
37
import com.sun.star.frame.XStorable;
Lines 109-114 Link Here
109
	}
110
	}
110
	
111
	
111
	/**
112
	/**
113
	 * Get sheet name by sheet index
114
	 * 
115
	 * @param xSpreadsheetDocument
116
	 * @param index
117
	 *            (Short) 0,1,2,...
118
	 * @return
119
	 * @throws Exception
120
	 */
121
	public static String getSCSheetNameByIndex(
122
			XSpreadsheetDocument xSpreadsheetDocument, short index)
123
			throws Exception {
124
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
125
		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
126
				XIndexAccess.class, xSpreadsheets);
127
		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
128
				XSpreadsheet.class, xIndexAccess.getByIndex(index));
129
		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
130
				xSpreadsheet);
131
		return xsheetname.getName();
132
	}
133
134
	/**
135
	 * Set sheet name by sheet index
136
	 * 
137
	 * @param xSpreadsheetDocument
138
	 * @param index
139
	 *            (Short) 0,1,2,...
140
	 * @return
141
	 * @throws Exception
142
	 */
143
	public static void setSCSheetNameByIndex(
144
			XSpreadsheetDocument xSpreadsheetDocument, short index,
145
			String sheetname) throws Exception {
146
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
147
		XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface(
148
				XIndexAccess.class, xSpreadsheets);
149
		XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface(
150
				XSpreadsheet.class, xIndexAccess.getByIndex(index));
151
		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
152
				xSpreadsheet);
153
		xsheetname.setName(sheetname);
154
	}
155
	
156
	/**
112
	 * Get rows object
157
	 * Get rows object
113
	 * @param xSpreadsheet
158
	 * @param xSpreadsheet
114
	 * @return
159
	 * @return
Lines 358-363 Link Here
358
	}
403
	}
359
	
404
	
360
	/**
405
	/**
406
	 * Get sheet object by sheet index
407
	 * 
408
	 * @param xSpreadsheetDocument
409
	 * @return
410
	 * @throws Exception
411
	 */
412
	public static String getSCActiveSheetName(
413
			XSpreadsheetDocument xSpreadsheetDocument) throws Exception {
414
		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
415
				XModel.class, xSpreadsheetDocument);
416
		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
417
				.queryInterface(XSpreadsheetView.class,
418
						xSpreadsheetModel.getCurrentController());
419
		XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet();
420
		XNamed activesheetName = (XNamed) UnoRuntime.queryInterface(
421
				XNamed.class, activesheet);
422
		return activesheetName.getName();
423
	}
424
	
425
	/**
361
	 * Set value of specific property from a cell
426
	 * Set value of specific property from a cell
362
	 * @param xCell
427
	 * @param xCell
363
	 * @param propName
428
	 * @param propName
Lines 418-423 Link Here
418
	}
483
	}
419
	
484
	
420
	/**
485
	/**
486
	 * Save file after open file.
487
	 * 
488
	 * @param xSpreadsheetDocument
489
	 * @throws Exception
490
	 */
491
	public static void save(XSpreadsheetDocument xSpreadsheetDocument)
492
			throws Exception {
493
494
		XStorable scStorable = (XStorable) UnoRuntime.queryInterface(
495
				XStorable.class, xSpreadsheetDocument);
496
		scStorable.store();
497
498
	}
499
500
	
501
	/**
421
	 * Close specific opening spreadsheet file which has been saved
502
	 * Close specific opening spreadsheet file which has been saved
422
	 * @param xSpreadsheetDocument
503
	 * @param xSpreadsheetDocument
423
	 * @throws Exception
504
	 * @throws Exception
(-)testuno/source/testcase/uno/sc/sheet/SheetBasicTest.java (-132 / +333 lines)
Lines 28-35 Link Here
28
import org.openoffice.test.common.FileUtil;
28
import org.openoffice.test.common.FileUtil;
29
import org.openoffice.test.common.Testspace;
29
import org.openoffice.test.common.Testspace;
30
import org.openoffice.test.uno.UnoApp;
30
import org.openoffice.test.uno.UnoApp;
31
31
import testlib.uno.SCUtil;
32
import com.sun.star.beans.Property;
33
import com.sun.star.beans.PropertyAttribute;
32
import com.sun.star.beans.PropertyValue;
34
import com.sun.star.beans.PropertyValue;
35
import com.sun.star.beans.XPropertySet;
36
import com.sun.star.beans.XPropertySetInfo;
37
import com.sun.star.container.XEnumerationAccess;
33
import com.sun.star.container.XIndexAccess;
38
import com.sun.star.container.XIndexAccess;
34
import com.sun.star.container.XNamed;
39
import com.sun.star.container.XNamed;
35
import com.sun.star.frame.XModel;
40
import com.sun.star.frame.XModel;
Lines 38-49 Link Here
38
import com.sun.star.lang.IndexOutOfBoundsException;
43
import com.sun.star.lang.IndexOutOfBoundsException;
39
import com.sun.star.lang.WrappedTargetException;
44
import com.sun.star.lang.WrappedTargetException;
40
import com.sun.star.lang.XComponent;
45
import com.sun.star.lang.XComponent;
46
import com.sun.star.sheet.SheetLinkMode;
47
import com.sun.star.sheet.XCalculatable;
48
import com.sun.star.sheet.XExternalDocLink;
49
import com.sun.star.sheet.XExternalDocLinks;
50
import com.sun.star.sheet.XSheetLinkable;
41
import com.sun.star.sheet.XSpreadsheet;
51
import com.sun.star.sheet.XSpreadsheet;
42
import com.sun.star.sheet.XSpreadsheetDocument;
52
import com.sun.star.sheet.XSpreadsheetDocument;
43
import com.sun.star.sheet.XSpreadsheetView;
53
import com.sun.star.sheet.XSpreadsheetView;
44
import com.sun.star.sheet.XSpreadsheets;
54
import com.sun.star.sheet.XSpreadsheets;
45
import com.sun.star.table.XCell;
55
import com.sun.star.table.XCell;
46
import com.sun.star.uno.UnoRuntime;
56
import com.sun.star.uno.UnoRuntime;
57
import com.sun.star.util.XRefreshable;
47
58
48
public class SheetBasicTest {
59
public class SheetBasicTest {
49
	UnoApp unoApp = new UnoApp();
60
	UnoApp unoApp = new UnoApp();
Lines 77-278 Link Here
77
	public void insertRenameDeleteSheet() throws Exception {
88
	public void insertRenameDeleteSheet() throws Exception {
78
		// Insert a sheet named aa after first sheet
89
		// Insert a sheet named aa after first sheet
79
		String sheetname = "aa";
90
		String sheetname = "aa";
80
		scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(
91
		scDocument = SCUtil.getSCDocument(scComponent);
81
				XSpreadsheetDocument.class, scComponent);
82
		XSpreadsheets spreadsheets = scDocument.getSheets();
92
		XSpreadsheets spreadsheets = scDocument.getSheets();
83
		spreadsheets.insertNewByName(sheetname, (short) 1);
93
		spreadsheets.insertNewByName(sheetname, (short) 1);
84
94
85
		// active the sheet second sheet aa
95
		// active the sheet second sheet aa
86
		XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime
96
		XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
87
				.queryInterface(XIndexAccess.class, spreadsheets);
97
				(short) 1);
88
		XSpreadsheet newSpreadSheet = (XSpreadsheet) UnoRuntime.queryInterface(
98
		SCUtil.setCurrentSheet(scDocument, newSpreadSheet);
89
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(1));
90
		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
91
				XModel.class, scDocument);
92
		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
93
				.queryInterface(XSpreadsheetView.class,
94
						xSpreadsheetModel.getCurrentController());
95
		xSpeadsheetView.setActiveSheet(newSpreadSheet);
96
99
97
		// get the new speadsheet name
100
		// get the new speadsheet name
98
		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
101
		assertEquals("actual should equals aa", sheetname,
99
				newSpreadSheet);
102
				SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
100
		assertEquals("actual should equals aa", sheetname, xsheetname.getName());
101
103
102
		// Change the Spreadsheet name
104
		// Change the Spreadsheet name
103
		String changedname = "SpeadsheetAfterChange";
105
		String changedname = "SpeadsheetAfterChange";
104
		xsheetname.setName(changedname);
106
		SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname);
105
107
106
		// Save and reload document
108
		// Save and reload document
107
		reloadSpreadsheet("TestSpreadsheet.xls");
109
		SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods");
110
		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
111
				scDocument, "TestSpreadsheet.ods");
108
112
113
		scDocument = scDocumentTemp;
114
		String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument,
115
				(short) 1);
116
109
		// Verify the changed Spreadsheet name
117
		// Verify the changed Spreadsheet name
110
		assertEquals("actual should equals SpeadsheetAfterChange", changedname,
118
		assertEquals("actual should equals SpeadsheetAfterChange", changedname,
111
				xsheetname.getName());
119
				sheetnameaftermove);
112
120
113
		spreadsheets.removeByName(changedname);
121
		scDocument.getSheets().removeByName(changedname);
114
122
115
		assertFalse("actual should equals false",
123
		assertFalse("actual should equals false",
116
				spreadsheets.hasByName(changedname));
124
				spreadsheets.hasByName(changedname));
117
125
		SCUtil.save(scDocumentTemp);
118
	}
126
	}
119
127
120
	@Test
128
	@Test
121
	public void copypastesheet() throws Exception {
129
	public void copypastesheet() throws Exception {
122
		// Insert some value into cells
130
		// Insert some value into cells
123
		scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(
131
		scDocument = SCUtil.getSCDocument(scComponent);
124
				XSpreadsheetDocument.class, scComponent);
132
		String souceSheetName = "sourcesheet";
125
		XSpreadsheets spreadsheets = scDocument.getSheets();
133
		SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName);
126
		XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime
127
				.queryInterface(XIndexAccess.class, spreadsheets);
128
		XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface(
129
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(0));
130
		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
131
				spreadSheet);
132
		xsheetname.setName("sourcesheet");
133
		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
134
		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
134
				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
135
				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
135
				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
136
				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
137
		XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument,
138
				souceSheetName);
136
		// input strings into sheet1
139
		// input strings into sheet1
137
		for (int intY = 0; intY < stringValues.length; intY++) {
140
		SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, 5, 2, stringValues);
138
			for (int intX = 0; intX < stringValues[intY].length; intX++) {
139
				// Insert the value to the cell, specified by intY and intX.
140
				this.insertIntoCell(intY, intX, stringValues[intY][intX],
141
						spreadSheet, "");
142
			}
143
		}
144
145
		// copy the sheet from sourcesheet to copysheet
141
		// copy the sheet from sourcesheet to copysheet
146
		String newcopysheet = "copysheet";
142
		String newcopysheet = "copysheet";
147
		spreadsheets.copyByName(xsheetname.getName(), newcopysheet, (short) 2);
143
		XSpreadsheets spreadsheets = scDocument.getSheets();
144
		spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2);
148
145
149
		// Save and reload document
146
		// Save and reload document
150
		reloadSpreadsheet("TestCopysheet.xls");
147
		SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls");
151
		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
148
		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
152
				XModel.class, scDocument);
149
				scDocument, "TestCopysheet.xls");
153
		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
150
		scDocument = scDocumentTemp;
154
				.queryInterface(XSpreadsheetView.class,
155
						xSpreadsheetModel.getCurrentController());
156
		XSpreadsheet copysheet = (XSpreadsheet) UnoRuntime.queryInterface(
157
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(2));
158
		for (int intY = 0; intY < stringValues.length; intY++) {
159
			for (int intX = 0; intX < stringValues[intY].length; intX++) {
160
				XCell xcell = null;
161
				xcell = copysheet.getCellByPosition(intY, intX);
162
				assertEquals(stringValues[intY][intX], xcell.getFormula());
163
			}
164
		}
165
151
152
		XSpreadsheet copysheet = SCUtil
153
				.getSCSheetByIndex(scDocument, (short) 2);
154
		String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0,
155
				0, 5, 2);
156
		assertArrayEquals("Expect string value should be stringValues",
157
				stringValues, CopystringValues);
158
166
	}
159
	}
167
160
168
	@Test
161
	@Test
169
	public void movesheet() throws Exception {
162
	public void movesheet() throws Exception {
170
163
171
		// new sc document
164
		// new sc document
172
		scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(
165
		scDocument = SCUtil.getSCDocument(scComponent);
173
				XSpreadsheetDocument.class, scComponent);
166
		XSpreadsheets spreadsheets = scDocument.getSheets();
174
167
175
		// change the first sheet name and input same value into the sheet cell
168
		// change the first sheet name and input same value into the sheet cell
176
		XSpreadsheets spreadsheets = scDocument.getSheets();
177
		XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime
178
				.queryInterface(XIndexAccess.class, spreadsheets);
179
		XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface(
180
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(0));
181
		XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,
182
				spreadSheet);
183
		String sheetname = "sourcesheet";
169
		String sheetname = "sourcesheet";
184
		xsheetname.setName(sheetname);
170
		SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname);
185
		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
171
		String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
186
				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
172
				{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
187
				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
173
				{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
174
		XSpreadsheet movesheet = SCUtil
175
				.getSCSheetByIndex(scDocument, (short) 0);
176
		SCUtil.setTextToCellRange(movesheet, 0, 0, 5, 2, stringValues);
188
177
189
		for (int intY = 0; intY < stringValues.length; intY++) {
190
			for (int intX = 0; intX < stringValues[intY].length; intX++) {
191
				// Insert the value to the cell, specified by intY and intX.
192
				this.insertIntoCell(intY, intX, stringValues[intY][intX],
193
						spreadSheet, "");
194
			}
195
		}
196
197
		// Before move, get the 2nd sheet name
178
		// Before move, get the 2nd sheet name
198
		XSpreadsheet secondSheetBeforeMove = (XSpreadsheet) UnoRuntime
179
		String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex(
199
				.queryInterface(XSpreadsheet.class,
180
				scDocument, (short) 1);
200
						xspreadsheetIndex.getByIndex(1));
201
		XNamed secondSheetNameBeforeMove = (XNamed) UnoRuntime.queryInterface(
202
				XNamed.class, secondSheetBeforeMove);
203
181
204
		// move the first sheet
182
		// move the first sheet
205
		spreadsheets.moveByName(sheetname, (short) 2);
183
		spreadsheets.moveByName(sheetname, (short) 2);
206
184
207
		// Save and reload document
185
		// Save and reload document
208
		reloadSpreadsheet("Testmovesheet.xls");
186
		SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls");
187
		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
188
				scDocument, "Testmovesheet.xls");
189
		scDocument = scDocumentTemp;
209
190
210
		// After move, get the first sheet name, and verify it same as 2nd sheet
191
		// After move, get the first sheet name, and verify it same as 2nd sheet
211
		// name before move
192
		// name before move
212
		XSpreadsheet firstSheetAfterMove = (XSpreadsheet) UnoRuntime
193
		String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex(
213
				.queryInterface(XSpreadsheet.class,
194
				scDocument, (short) 0);
214
						xspreadsheetIndex.getByIndex(0));
215
		XNamed xfirstsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface(
216
				XNamed.class, firstSheetAfterMove);
217
		assertEquals("Expect result should be Sheet2",
195
		assertEquals("Expect result should be Sheet2",
218
				secondSheetNameBeforeMove.getName(),
196
				secondSheetNameBeforeMove, firstsheetnameAfterMove);
219
				xfirstsheetnameAfterMove.getName());
220
197
221
		// Get the target sheet name after move
198
		// Get the target sheet name after move
222
		XSpreadsheet sheetAfterMove = (XSpreadsheet) UnoRuntime.queryInterface(
199
		String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument,
223
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(1));
200
				(short) 1);
224
		XNamed xsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface(
225
				XNamed.class, sheetAfterMove);
226
		assertEquals("Expect result should be sourcesheet", sheetname,
201
		assertEquals("Expect result should be sourcesheet", sheetname,
227
				xsheetnameAfterMove.getName());
202
				sheetnameAfterMove);
228
203
229
		// Check the cell value after move
204
		// Check the cell value after move
230
		XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(
205
		XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument,
231
				XModel.class, scDocument);
206
				(short) 1);
232
		XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime
207
		String[][] stringValuesaftermove = SCUtil.getTextFromCellRange(
233
				.queryInterface(XSpreadsheetView.class,
208
				sheetaftermove, 0, 0, 5, 2);
234
						xSpreadsheetModel.getCurrentController());
235
		XSpreadsheet movesheet = (XSpreadsheet) UnoRuntime.queryInterface(
236
				XSpreadsheet.class, xspreadsheetIndex.getByIndex(1));
237
		for (int intY = 0; intY < stringValues.length; intY++) {
238
			for (int intX = 0; intX < stringValues[intY].length; intX++) {
239
				XCell xcell = null;
240
				xcell = movesheet.getCellByPosition(intY, intX);
241
				assertEquals(stringValues[intY][intX], xcell.getFormula());
242
			}
243
		}
244
209
210
		assertArrayEquals("Expect result should be stringValues", stringValues,
211
				stringValuesaftermove);
212
245
	}
213
	}
246
214
247
	// input value into sheet cell
215
	@Test
248
	public static void insertIntoCell(int intX, int intY, String stringValue,
216
	public void hideShowSheet() throws Exception {
249
			XSpreadsheet xspreadsheet, String stringFlag)
217
		// Insert a sheet named hide sheet after first sheet
250
			throws IndexOutOfBoundsException {
218
		String sheetname = "hide sheet";
251
		XCell xcell = null;
219
		scDocument = SCUtil.getSCDocument(scComponent);
252
		xcell = xspreadsheet.getCellByPosition(intX, intY);
220
		XSpreadsheets spreadsheets = scDocument.getSheets();
253
		if (stringFlag.equals("V")) {
221
		spreadsheets.insertNewByName(sheetname, (short) 1);
254
			xcell.setValue((new Float(stringValue)).floatValue());
222
255
		} else {
223
		// active the sheet second sheet "hide sheet"
256
			xcell.setFormula(stringValue);
224
		XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
257
		}
225
				(short) 1);
226
		SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
227
		// get second sheet name and verify it should be "hide sheet"
228
		assertEquals("expect active sheet name will be hide sheet", sheetname,
229
				SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
230
231
		// hide the sheet you insert
232
		XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime
233
				.queryInterface(XPropertySet.class, secondSpreadSheet);
234
		boolean isvisiable = false;
235
		sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
236
237
		// Save and reload document
238
		SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls");
239
		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
240
				scDocument, "Testhideshowsheet.xls");
241
		scDocument = scDocumentTemp;
242
243
		// get the active sheet name after hide sheet, it should be Sheet2
244
		String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2);
245
		String activesheetname = SCUtil.getSCActiveSheetName(scDocument);
246
		assertEquals("Expect sheet name should be Sheet2", sheet2Name,
247
				activesheetname);
248
249
		// show sheet "hide sheet"
250
		sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface(
251
				XPropertySet.class,
252
				SCUtil.getSCSheetByIndex(scDocument, (short) 1));
253
		isvisiable = true;
254
		sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
255
256
		// active sheet "hide sheet"
257
		secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1);
258
		SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
259
260
		// Get current active sheet name, verify it same as "hide sheet"
261
		String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument);
262
		assertEquals("Expect active sheet name is hidesheet", sheetname,
263
				currentactivesheetname);
264
		SCUtil.save(scDocument);
258
	}
265
	}
259
266
260
	// Save and load the document
267
	@Test
261
	public XSpreadsheetDocument reloadSpreadsheet(String spreadSheetname)
268
	public void sheetColor() throws Exception {
262
			throws Exception {
269
		// get first sheet propertyset
263
		String filePath = Testspace.getPath("output/" + spreadSheetname);
270
		scDocument = SCUtil.getSCDocument(scComponent);
264
		XStorable xStorable = (XStorable) UnoRuntime.queryInterface(
271
		XSpreadsheets spreadsheets = scDocument.getSheets();
265
				XStorable.class, scDocument);
272
		XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
266
		PropertyValue[] aStoreProperties = new PropertyValue[2];
273
				(short) 0);
267
		aStoreProperties[0] = new PropertyValue();
274
		XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime
268
		aStoreProperties[1] = new PropertyValue();
275
				.queryInterface(XPropertySet.class, firstSpreadSheet);
269
		aStoreProperties[0].Name = "Override";
270
		aStoreProperties[0].Value = true;
271
		aStoreProperties[1].Name = "FilterName";
272
		aStoreProperties[1].Value = "MS Excel 97";
273
		xStorable.storeAsURL(FileUtil.getUrl(filePath), aStoreProperties);
274
276
275
		return UnoRuntime.queryInterface(XSpreadsheetDocument.class,
277
		// Set tabcolor to 111
276
				unoApp.loadDocument(filePath));
278
		sheet1PropertySet.setPropertyValue("TabColor", 111);
279
280
		// copy the color sheet to new sheet
281
		spreadsheets.copyByName(
282
				SCUtil.getSCSheetNameByIndex(scDocument, (short) 0),
283
				"newsheet", (short) 3);
284
285
		// Save and reopen the document
286
		SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods");
287
		XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
288
				scDocument, "Testcolorsheet.ods");
289
		scDocument = scDocumentTemp;
290
291
		// Get first sheet color
292
		sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface(
293
				XPropertySet.class,
294
				SCUtil.getSCSheetByIndex(scDocument, (short) 0));
295
		int firstSheetcolorid = (int) sheet1PropertySet
296
				.getPropertyValue("TabColor");
297
298
		// Get the copyed sheet color
299
		XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime
300
				.queryInterface(XPropertySet.class,
301
						SCUtil.getSCSheetByIndex(scDocument, (short) 3));
302
		int copySheetcolorid = (int) newsheetPropertySet
303
				.getPropertyValue("TabColor");
304
305
		// Verify first sheet color changed successfully
306
		assertEquals("Expect color should be 111", 111, firstSheetcolorid);
307
308
		// Verify first sheet color same as copy sheet color
309
		assertEquals("Expect color should be 111", firstSheetcolorid,
310
				copySheetcolorid);
277
	}
311
	}
312
313
	@Test
314
	public void insertSheetFromfile() throws Exception {
315
		// New a document source.xls, add value to 3 sheet
316
		scDocument = SCUtil.getSCDocument(scComponent);
317
		XSpreadsheets spreadsheets = scDocument.getSheets();
318
		XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument,
319
				(short) 0);
320
		XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument,
321
				(short) 1);
322
		XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument,
323
				(short) 2);
324
		SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2");
325
		SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2");
326
		SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2");
327
328
		// Save and close this document
329
		SCUtil.saveFileAs(scComponent, "source", "xls");
330
		SCUtil.closeFile(scDocument);
331
332
		// get source document URL
333
		String SourcestoreUrl = Testspace.getUrl("output/" + "source" + "."
334
				+ "xls");
335
336
		// New a document
337
		scComponent = unoApp.newDocument("scalc");
338
		scDocument = SCUtil.getSCDocument(scComponent);
339
		spreadsheets = scDocument.getSheets();
340
		// Insert firstexternalsheet sheet, link with Sheet1 in source document
341
		// and the link mode is NORMAL
342
		spreadsheets.insertNewByName("firstexternalsheet", (short) 3);
343
		XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
344
				(short) 3);
345
		XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime
346
				.queryInterface(XSheetLinkable.class, firstexternalsheet);
347
		xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "",
348
				SheetLinkMode.NORMAL);
349
350
		// Insert secondexternalsheet sheet, link with Sheet2 in source document
351
		// and the link mode is VALUE
352
		spreadsheets.insertNewByName("secondexternalsheet", (short) 4);
353
		XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
354
				(short) 4);
355
		XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime
356
				.queryInterface(XSheetLinkable.class, secondexternalsheet);
357
		xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "",
358
				SheetLinkMode.VALUE);
359
		
360
		// Insert secondexternalsheet sheet, link with Sheet2 in source document
361
		// and the link mode is NONE
362
		spreadsheets.insertNewByName("thirdexternalsheet", (short) 5);
363
		XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
364
				(short) 5);
365
		XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime
366
				.queryInterface(XSheetLinkable.class, thirdexternalsheet);
367
		xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "",
368
				SheetLinkMode.NONE);
369
370
		// Verify firstexternalsheet
371
		assertEquals("Expect formula should be =2*2", "=2*2",
372
				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
373
		assertEquals("Expect formula result should be 4", "4",
374
				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
375
376
		// Verify secondexternalsheet
377
		assertEquals("Expect formula should be 4", "4",
378
				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
379
		assertEquals("Expect formula result should be 4", "4",
380
				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
381
		
382
		// Verify thirdexternalsheet
383
		assertEquals("Expect formula should be blank", "",
384
				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
385
		assertEquals("Expect formula result should be blank", "",
386
				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
387
388
		// save document and verify the linked sheet again
389
		SCUtil.saveFileAs(scComponent, "linked", "ods");
390
		XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp,
391
				scDocument, "linked.ods");
392
		scDocument = tempscDocument;
393
		firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3);
394
		secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4);
395
		thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5);
396
397
		// Verify firstexternalsheet
398
		assertEquals("Expect formula should be =2*2", "=2*2",
399
				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
400
		assertEquals("Expect formula result should be 4", "4",
401
				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
402
403
		// Verify secondexternalsheet
404
		assertEquals("Expect formula should be 4", "4",
405
				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
406
		assertEquals("Expect formula result should be 4", "4",
407
				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
408
		
409
		// Verify thirdexternalsheet
410
		assertEquals("Expect formula should be blank", "",
411
				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
412
		assertEquals("Expect formula result should be blank", "",
413
				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
414
		
415
		//save and close document
416
		SCUtil.save(scDocument);
417
		SCUtil.closeFile(scDocument);
418
		
419
		//Open souce document and change the value in souce document
420
		XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp,
421
				scDocument, "source.xls");
422
		firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0);
423
		secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1);
424
		thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2);
425
		SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3");
426
		SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3");
427
		SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3");
428
		SCUtil.save(sourcescDocument);
429
		SCUtil.closeFile(sourcescDocument);
430
431
		//Open link document
432
		tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods");
433
		scDocument = tempscDocument;
434
		spreadsheets = scDocument.getSheets();
435
436
		firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3);
437
		secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4);
438
		thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5);
439
		
440
		//get Object SheetLinks for document
441
		XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime
442
				.queryInterface(XPropertySet.class, scDocument);
443
		Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks");
444
445
		XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface(
446
				XIndexAccess.class, sheetLinks);
447
		
448
		//Refresh all links
449
		for (int i = 0; i < xsheetlinks.getCount(); i++) {
450
			Object sheetlink = xsheetlinks.getByIndex(i);
451
			XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime
452
					.queryInterface(XRefreshable.class, sheetlink);
453
			xsheetRefreshable.refresh();
454
		}
455
456
		// Verify firstexternalsheet
457
		assertEquals("Expect formula should be =3*3", "=3*3",
458
				SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
459
		assertEquals("Expect formula result should be 9", "9",
460
				SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
461
462
		// Verify secondexternalsheet
463
		assertEquals("Expect formula should be 9", "9",
464
				SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
465
		assertEquals("Expect formula result should be 9", "9",
466
				SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
467
		
468
		// Verify thirdexternalsheet
469
		assertEquals("Expect formula should be blank", "",
470
				SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2));
471
		assertEquals("Expect formula result should be blank", "",
472
				SCUtil.getTextFromCell(thirdexternalsheet, 1, 2));
473
		
474
		//Save the document before close
475
		SCUtil.save(scDocument);
476
477
	}
478
278
}
479
}

Return to issue 120655