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 |
} |