Index: C:/Users/IBM_ADMIN/workspace/testuno/source/testlib/uno/SCUtil.java =================================================================== --- C:/Users/IBM_ADMIN/workspace/testuno/source/testlib/uno/SCUtil.java (revision 1375410) +++ C:/Users/IBM_ADMIN/workspace/testuno/source/testlib/uno/SCUtil.java (working copy) @@ -19,7 +19,6 @@ * *************************************************************/ - package testlib.uno; import java.util.HashMap; @@ -29,6 +28,7 @@ import com.sun.star.beans.PropertyValue; import com.sun.star.container.XIndexAccess; +import com.sun.star.container.XNamed; import com.sun.star.frame.XController; import com.sun.star.frame.XModel; import com.sun.star.frame.XStorable; @@ -46,116 +46,215 @@ import com.sun.star.uno.UnoRuntime; import com.sun.star.util.XCloseable; - /** * Utilities of Spreadsheet + * * @author test - * + * */ public class SCUtil { - - private static HashMap filterName = new HashMap(); - + + private static HashMap filterName = new HashMap(); + private SCUtil() { - + } - + /** * Get spreadsheet document object + * * @param xSpreadsheetComponent * @return * @throws Exception */ - public static XSpreadsheetDocument getSCDocument(XComponent xSpreadsheetComponent) throws Exception { - XSpreadsheetDocument xSpreadsheetDocument = - (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, xSpreadsheetComponent); - - return xSpreadsheetDocument; - } - - /** - * Get sheet object by sheet name - * @param xSpreadsheetDocument - * @param sheetName - * @return - * @throws Exception - */ - public static XSpreadsheet getSCSheetByName(XSpreadsheetDocument xSpreadsheetDocument, String sheetName) throws Exception { + public static XSpreadsheetDocument getSCDocument( + XComponent xSpreadsheetComponent) throws Exception { + XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument) UnoRuntime + .queryInterface(XSpreadsheetDocument.class, + xSpreadsheetComponent); + + return xSpreadsheetDocument; + } + + /** + * Get sheet object by sheet name + * + * @param xSpreadsheetDocument + * @param sheetName + * @return + * @throws Exception + */ + public static XSpreadsheet getSCSheetByName( + XSpreadsheetDocument xSpreadsheetDocument, String sheetName) + throws Exception { XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); - XSpreadsheet xSpreadsheet = - (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xSpreadsheets.getByName(sheetName)); - + XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( + XSpreadsheet.class, xSpreadsheets.getByName(sheetName)); + return xSpreadsheet; } - + /** * Get sheet object by sheet index + * * @param xSpreadsheetDocument - * @param index (Short) 0,1,2,... + * @param index + * (Short) 0,1,2,... * @return * @throws Exception */ - public static XSpreadsheet getSCSheetByIndex(XSpreadsheetDocument xSpreadsheetDocument, short index) throws Exception { + public static XSpreadsheet getSCSheetByIndex( + XSpreadsheetDocument xSpreadsheetDocument, short index) + throws Exception { XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); - XIndexAccess xIndexAccess = - (XIndexAccess) UnoRuntime.queryInterface(XIndexAccess.class, xSpreadsheets); - XSpreadsheet xSpreadsheet = - (XSpreadsheet) UnoRuntime.queryInterface(XSpreadsheet.class, xIndexAccess.getByIndex(index)); - + XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( + XIndexAccess.class, xSpreadsheets); + XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( + XSpreadsheet.class, xIndexAccess.getByIndex(index)); + return xSpreadsheet; } - + /** + * Get sheet object by sheet index + * + * @param xSpreadsheetDocument + * @return + * @throws Exception + */ + public static XSpreadsheet getSCActiveSheet( + XSpreadsheetDocument xSpreadsheetDocument) throws Exception { + XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( + XModel.class, xSpreadsheetDocument); + XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime + .queryInterface(XSpreadsheetView.class, + xSpreadsheetModel.getCurrentController()); + return xSpeadsheetView.getActiveSheet(); + } + + /** + * Get sheet object by sheet index + * + * @param xSpreadsheetDocument + * @return + * @throws Exception + */ + public static String getSCActiveSheetName( + XSpreadsheetDocument xSpreadsheetDocument) throws Exception { + XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( + XModel.class, xSpreadsheetDocument); + XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime + .queryInterface(XSpreadsheetView.class, + xSpreadsheetModel.getCurrentController()); + XSpreadsheet activesheet = xSpeadsheetView.getActiveSheet(); + XNamed activesheetName = (XNamed) UnoRuntime.queryInterface( + XNamed.class, activesheet); + return activesheetName.getName(); + } + + /** + * Get sheet name by sheet index + * + * @param xSpreadsheetDocument + * @param index + * (Short) 0,1,2,... + * @return + * @throws Exception + */ + public static String getSCSheetNameByIndex( + XSpreadsheetDocument xSpreadsheetDocument, short index) + throws Exception { + XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); + XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( + XIndexAccess.class, xSpreadsheets); + XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( + XSpreadsheet.class, xIndexAccess.getByIndex(index)); + XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, + xSpreadsheet); + return xsheetname.getName(); + } + + /** + * Set sheet name by sheet index + * + * @param xSpreadsheetDocument + * @param index + * (Short) 0,1,2,... + * @return + * @throws Exception + */ + public static void setSCSheetNameByIndex( + XSpreadsheetDocument xSpreadsheetDocument, short index, + String sheetname) throws Exception { + XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets(); + XIndexAccess xIndexAccess = (XIndexAccess) UnoRuntime.queryInterface( + XIndexAccess.class, xSpreadsheets); + XSpreadsheet xSpreadsheet = (XSpreadsheet) UnoRuntime.queryInterface( + XSpreadsheet.class, xIndexAccess.getByIndex(index)); + XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, + xSpreadsheet); + xsheetname.setName(sheetname); + } + + /** * Get rows object + * * @param xSpreadsheet * @return * @throws Exception */ - public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) throws Exception { - XColumnRowRange xColumnRowRange = - (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); + public static XTableRows getSCRows(XSpreadsheet xSpreadsheet) + throws Exception { + XColumnRowRange xColumnRowRange = (XColumnRowRange) UnoRuntime + .queryInterface(XColumnRowRange.class, xSpreadsheet); XTableRows xTableRows = xColumnRowRange.getRows(); - + return xTableRows; } - + /** * Get columns object + * * @param xSpreadsheet * @return * @throws Exception */ - public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) throws Exception { - XColumnRowRange xColumnRowRange = - (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xSpreadsheet); + public static XTableColumns getSCColumns(XSpreadsheet xSpreadsheet) + throws Exception { + XColumnRowRange xColumnRowRange = (XColumnRowRange) UnoRuntime + .queryInterface(XColumnRowRange.class, xSpreadsheet); XTableColumns xTableColumns = xColumnRowRange.getColumns(); - + return xTableColumns; } - + /** - * Set floating number into specific cell + * Set floating number into specific cell + * * @param xSpreadsheet * @param column * @param row * @param value * @throws Exception */ - public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, int row, double value) throws Exception { + public static void setValueToCell(XSpreadsheet xSpreadsheet, int column, + int row, double value) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); xCell.setValue(value); } - + /** * Set text into specific cell + * * @param xSpreadsheet * @param column * @param row * @param text * @throws Exception */ - public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, int row, String text) throws Exception { + public static void setTextToCell(XSpreadsheet xSpreadsheet, int column, + int row, String text) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); xText.setString(text); @@ -163,34 +262,39 @@ /** * Set formula into specific cell + * * @param xSpreadsheet * @param column * @param row * @param formula * @throws Exception */ - public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, int row, String formula) throws Exception { + public static void setFormulaToCell(XSpreadsheet xSpreadsheet, int column, + int row, String formula) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); xCell.setFormula(formula); } - + /** * Get value from specific cell + * * @param xSpreadsheet * @param column * @param row * @return * @throws Exception */ - public static double getValueFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { + public static double getValueFromCell(XSpreadsheet xSpreadsheet, + int column, int row) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); double cellValue = xCell.getValue(); - + return cellValue; } - + /** * Get text from specific cell + * * @param xSpreadsheet * @param column * @param row @@ -198,30 +302,34 @@ * @return * @throws Exception */ - public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { + public static String getTextFromCell(XSpreadsheet xSpreadsheet, int column, + int row) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); - + return xText.getString(); } - + /** * Get formula string from specific cell + * * @param xSpreadsheet * @param column * @param row * @return * @throws Exception */ - public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, int column, int row) throws Exception { + public static String getFormulaFromCell(XSpreadsheet xSpreadsheet, + int column, int row) throws Exception { XCell xCell = xSpreadsheet.getCellByPosition(column, row); String cellFormula = xCell.getFormula(); - + return cellFormula; } - + /** * Set numbers into a cell range + * * @param xSpreadsheet * @param start_col * @param start_row @@ -230,19 +338,23 @@ * @param values * @throws Exception */ - public static void setValueToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, double[][] values) throws Exception { - XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); + public static void setValueToCellRange(XSpreadsheet xSpreadsheet, + int start_col, int start_row, int end_col, int end_row, + double[][] values) throws Exception { + XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, + start_row, end_col, end_row); XCell xCell = null; - for (int i = 0; i <= (end_row - start_row); i++ ) { - for(int j = 0; j <= (end_col - start_col); j++) { + for (int i = 0; i <= (end_row - start_row); i++) { + for (int j = 0; j <= (end_col - start_col); j++) { xCell = xCellRange.getCellByPosition(j, i); xCell.setValue(values[i][j]); } } } - + /** * Set text into a cell range + * * @param xSpreadsheet * @param start_col * @param start_row @@ -251,21 +363,25 @@ * @param texts * @throws Exception */ - public static void setTextToCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row, String[][] texts) throws Exception { - XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); + public static void setTextToCellRange(XSpreadsheet xSpreadsheet, + int start_col, int start_row, int end_col, int end_row, + String[][] texts) throws Exception { + XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, + start_row, end_col, end_row); XCell xCell = null; XText xText = null; - for (int i = 0; i <= (end_row - start_row); i++ ) { - for(int j = 0; j <= (end_col - start_col); j++) { + for (int i = 0; i <= (end_row - start_row); i++) { + for (int j = 0; j <= (end_col - start_col); j++) { xCell = xCellRange.getCellByPosition(j, i); xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); xText.setString(texts[i][j]); } } } - + /** * Get number content from a cell range + * * @param xSpreadsheet * @param start_col * @param start_row @@ -274,23 +390,28 @@ * @return * @throws Exception */ - public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { - XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); + public static double[][] getValueFromCellRange(XSpreadsheet xSpreadsheet, + int start_col, int start_row, int end_col, int end_row) + throws Exception { + XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, + start_row, end_col, end_row); XCell xCell = null; - double[][] cellValues = new double[end_row - start_row+1][end_col - start_col +1]; - - for (int i = 0; i <= (end_row - start_row); i++ ) { - for(int j = 0; j <= (end_col - start_col); j++) { + double[][] cellValues = new double[end_row - start_row + 1][end_col + - start_col + 1]; + + for (int i = 0; i <= (end_row - start_row); i++) { + for (int j = 0; j <= (end_col - start_col); j++) { xCell = xCellRange.getCellByPosition(j, i); cellValues[i][j] = xCell.getValue(); } } - + return cellValues; } - + /** * Get text content from a cell range + * * @param xSpreadsheet * @param start_col * @param start_row @@ -299,64 +420,97 @@ * @return * @throws Exception */ - public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, int start_col, int start_row, int end_col, int end_row) throws Exception { - XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, start_row, end_col, end_row); + public static String[][] getTextFromCellRange(XSpreadsheet xSpreadsheet, + int start_col, int start_row, int end_col, int end_row) + throws Exception { + XCellRange xCellRange = xSpreadsheet.getCellRangeByPosition(start_col, + start_row, end_col, end_row); XCell xCell = null; XText xText = null; - String[][] cellTexts = new String[end_row - start_row+1][end_col - start_col +1]; - - for (int i = 0; i <= (end_row - start_row); i++ ) { - for(int j = 0; j <= (end_col - start_col); j++) { + String[][] cellTexts = new String[end_row - start_row + 1][end_col + - start_col + 1]; + + for (int i = 0; i <= (end_row - start_row); i++) { + for (int j = 0; j <= (end_col - start_col); j++) { xCell = xCellRange.getCellByPosition(j, i); xText = (XText) UnoRuntime.queryInterface(XText.class, xCell); cellTexts[i][j] = xText.getString(); } } - + return cellTexts; } - - //TODO ZS - public static String[][] getAllFromCellRange - + + // TODO ZS - public static String[][] getAllFromCellRange + /** * Switch to specific sheet + * * @param xSpreadsheetDocument * @param xSpreadsheet */ - public static void setCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) throws Exception { - XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); + public static void setCurrentSheet( + XSpreadsheetDocument xSpreadsheetDocument, XSpreadsheet xSpreadsheet) + throws Exception { + XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, + xSpreadsheetDocument); XController xController = xModel.getCurrentController(); - XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); + XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime + .queryInterface(XSpreadsheetView.class, xController); xSpreadsheetView.setActiveSheet(xSpreadsheet); } - + /** * Get sheet object of current active sheet + * * @param xSpreadsheetDocument * @return */ - public static XSpreadsheet getCurrentSheet(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { - XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, xSpreadsheetDocument); + public static XSpreadsheet getCurrentSheet( + XSpreadsheetDocument xSpreadsheetDocument) throws Exception { + XModel xModel = (XModel) UnoRuntime.queryInterface(XModel.class, + xSpreadsheetDocument); XController xController = xModel.getCurrentController(); - XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, xController); + XSpreadsheetView xSpreadsheetView = (XSpreadsheetView) UnoRuntime + .queryInterface(XSpreadsheetView.class, xController); XSpreadsheet xSpreadsheet = xSpreadsheetView.getActiveSheet(); - + return xSpreadsheet; } - + /** + * Save file after open file. + * + * @param xSpreadsheetDocument + * @throws Exception + */ + public static void save(XSpreadsheetDocument xSpreadsheetDocument) + throws Exception { + + XStorable scStorable = (XStorable) UnoRuntime.queryInterface( + XStorable.class, xSpreadsheetDocument); + scStorable.store(); + + } + + /** * Save file as specific file format into testspace/output folder. + * * @param scComponent - * @param fileName File name string without extension name (e.g. "sampleFile") - * @param extName ("ods", "ots", "xls", "xlt", "csv") + * @param fileName + * File name string without extension name (e.g. "sampleFile") + * @param extName + * ("ods", "ots", "xls", "xlt", "csv") * @throws Exception */ - public static void saveFileAs(XComponent scComponent, String fileName, String extName) throws Exception { - + public static void saveFileAs(XComponent scComponent, String fileName, + String extName) throws Exception { + initFilterName(); - String storeUrl = Testspace.getUrl("output/" + fileName + "." + extName); - + String storeUrl = Testspace + .getUrl("output/" + fileName + "." + extName); + PropertyValue[] storeProps = new PropertyValue[2]; storeProps[0] = new PropertyValue(); storeProps[0].Name = "FilterName"; @@ -364,48 +518,58 @@ storeProps[1] = new PropertyValue(); storeProps[1].Name = "Overwrite"; storeProps[1].Value = new Boolean(true); - - XStorable scStorable = - (XStorable) UnoRuntime.queryInterface(XStorable.class, scComponent); + + XStorable scStorable = (XStorable) UnoRuntime.queryInterface( + XStorable.class, scComponent); scStorable.storeAsURL(storeUrl, storeProps); } - + /** * Close specific opening spreadsheet file which has been saved + * * @param xSpreadsheetDocument * @throws Exception */ - public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) throws Exception { - XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface(XCloseable.class, xSpreadsheetDocument); + public static void closeFile(XSpreadsheetDocument xSpreadsheetDocument) + throws Exception { + XCloseable xCloseable = (XCloseable) UnoRuntime.queryInterface( + XCloseable.class, xSpreadsheetDocument); xCloseable.close(false); } - + /** - * Close a opening file saved in testspace/output direction and reopen it in Spreadsheet. For save&reload test scenario only. + * Close a opening file saved in testspace/output direction and reopen it in + * Spreadsheet. For save&reload test scenario only. + * * @param unoApp * @param xSpreadsheetDocument - * @param fullFileName File name with the extension name. (e.g. "sc.ods") + * @param fullFileName + * File name with the extension name. (e.g. "sc.ods") * @return * @throws Exception */ - public static XSpreadsheetDocument reloadFile(UnoApp unoApp, XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) throws Exception { + public static XSpreadsheetDocument reloadFile(UnoApp unoApp, + XSpreadsheetDocument xSpreadsheetDocument, String fullFileName) + throws Exception { closeFile(xSpreadsheetDocument); - + String filePath = Testspace.getPath("output/" + fullFileName); - XSpreadsheetDocument xScDocument = UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath)); - + XSpreadsheetDocument xScDocument = UnoRuntime.queryInterface( + XSpreadsheetDocument.class, unoApp.loadDocument(filePath)); + return xScDocument; } - + /** * Initial the filter name list + * * @throws Exception */ private static void initFilterName() throws Exception { if (filterName.size() > 0) { return; } - + filterName.put("ods", "calc8"); filterName.put("ots", "calc8_template"); filterName.put("xls", "MS Excel 97"); Index: C:/Users/IBM_ADMIN/workspace/testuno/source/testcase/uno/sc/sheet/SheetBasicTest.java =================================================================== --- C:/Users/IBM_ADMIN/workspace/testuno/source/testcase/uno/sc/sheet/SheetBasicTest.java (revision 1375410) +++ C:/Users/IBM_ADMIN/workspace/testuno/source/testcase/uno/sc/sheet/SheetBasicTest.java (working copy) @@ -28,8 +28,13 @@ import org.openoffice.test.common.FileUtil; import org.openoffice.test.common.Testspace; import org.openoffice.test.uno.UnoApp; - +import testlib.uno.SCUtil; +import com.sun.star.beans.Property; +import com.sun.star.beans.PropertyAttribute; import com.sun.star.beans.PropertyValue; +import com.sun.star.beans.XPropertySet; +import com.sun.star.beans.XPropertySetInfo; +import com.sun.star.container.XEnumerationAccess; import com.sun.star.container.XIndexAccess; import com.sun.star.container.XNamed; import com.sun.star.frame.XModel; @@ -38,12 +43,18 @@ import com.sun.star.lang.IndexOutOfBoundsException; import com.sun.star.lang.WrappedTargetException; import com.sun.star.lang.XComponent; +import com.sun.star.sheet.SheetLinkMode; +import com.sun.star.sheet.XCalculatable; +import com.sun.star.sheet.XExternalDocLink; +import com.sun.star.sheet.XExternalDocLinks; +import com.sun.star.sheet.XSheetLinkable; import com.sun.star.sheet.XSpreadsheet; import com.sun.star.sheet.XSpreadsheetDocument; import com.sun.star.sheet.XSpreadsheetView; import com.sun.star.sheet.XSpreadsheets; import com.sun.star.table.XCell; import com.sun.star.uno.UnoRuntime; +import com.sun.star.util.XRefreshable; public class SheetBasicTest { UnoApp unoApp = new UnoApp(); @@ -77,202 +88,392 @@ public void insertRenameDeleteSheet() throws Exception { // Insert a sheet named aa after first sheet String sheetname = "aa"; - scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( - XSpreadsheetDocument.class, scComponent); + scDocument = SCUtil.getSCDocument(scComponent); XSpreadsheets spreadsheets = scDocument.getSheets(); spreadsheets.insertNewByName(sheetname, (short) 1); // active the sheet second sheet aa - XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime - .queryInterface(XIndexAccess.class, spreadsheets); - XSpreadsheet newSpreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); - XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( - XModel.class, scDocument); - XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime - .queryInterface(XSpreadsheetView.class, - xSpreadsheetModel.getCurrentController()); - xSpeadsheetView.setActiveSheet(newSpreadSheet); + XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 1); + SCUtil.setCurrentSheet(scDocument, newSpreadSheet); // get the new speadsheet name - XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, - newSpreadSheet); - assertEquals("actual should equals aa", sheetname, xsheetname.getName()); + assertEquals("actual should equals aa", sheetname, + SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); // Change the Spreadsheet name String changedname = "SpeadsheetAfterChange"; - xsheetname.setName(changedname); + SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname); // Save and reload document - reloadSpreadsheet("TestSpreadsheet.xls"); + SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods"); + XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, + scDocument, "TestSpreadsheet.ods"); + scDocument = scDocumentTemp; + String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument, + (short) 1); + // Verify the changed Spreadsheet name assertEquals("actual should equals SpeadsheetAfterChange", changedname, - xsheetname.getName()); + sheetnameaftermove); - spreadsheets.removeByName(changedname); + scDocument.getSheets().removeByName(changedname); assertFalse("actual should equals false", spreadsheets.hasByName(changedname)); - + SCUtil.save(scDocumentTemp); } @Test public void copypastesheet() throws Exception { // Insert some value into cells - scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( - XSpreadsheetDocument.class, scComponent); - XSpreadsheets spreadsheets = scDocument.getSheets(); - XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime - .queryInterface(XIndexAccess.class, spreadsheets); - XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(0)); - XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, - spreadSheet); - xsheetname.setName("sourcesheet"); + scDocument = SCUtil.getSCDocument(scComponent); + String souceSheetName = "sourcesheet"; + SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, souceSheetName); String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; + XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument, + souceSheetName); // input strings into sheet1 - for (int intY = 0; intY < stringValues.length; intY++) { - for (int intX = 0; intX < stringValues[intY].length; intX++) { - // Insert the value to the cell, specified by intY and intX. - this.insertIntoCell(intY, intX, stringValues[intY][intX], - spreadSheet, ""); - } - } - + SCUtil.setTextToCellRange(sourceSpreadSheet, 0, 0, 5, 2, stringValues); // copy the sheet from sourcesheet to copysheet String newcopysheet = "copysheet"; - spreadsheets.copyByName(xsheetname.getName(), newcopysheet, (short) 2); + XSpreadsheets spreadsheets = scDocument.getSheets(); + spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2); // Save and reload document - reloadSpreadsheet("TestCopysheet.xls"); - XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( - XModel.class, scDocument); - XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime - .queryInterface(XSpreadsheetView.class, - xSpreadsheetModel.getCurrentController()); - XSpreadsheet copysheet = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(2)); - for (int intY = 0; intY < stringValues.length; intY++) { - for (int intX = 0; intX < stringValues[intY].length; intX++) { - XCell xcell = null; - xcell = copysheet.getCellByPosition(intY, intX); - assertEquals(stringValues[intY][intX], xcell.getFormula()); - } - } + SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls"); + XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, + scDocument, "TestCopysheet.xls"); + scDocument = scDocumentTemp; + XSpreadsheet copysheet = SCUtil + .getSCSheetByIndex(scDocument, (short) 2); + String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0, + 0, 5, 2); + assertArrayEquals("Expect string value should be stringValues", + stringValues, CopystringValues); + } @Test public void movesheet() throws Exception { // new sc document - scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( - XSpreadsheetDocument.class, scComponent); + scDocument = SCUtil.getSCDocument(scComponent); + XSpreadsheets spreadsheets = scDocument.getSheets(); // change the first sheet name and input same value into the sheet cell - XSpreadsheets spreadsheets = scDocument.getSheets(); - XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime - .queryInterface(XIndexAccess.class, spreadsheets); - XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(0)); - XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, - spreadSheet); String sheetname = "sourcesheet"; - xsheetname.setName(sheetname); + SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname); String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; + XSpreadsheet movesheet = SCUtil + .getSCSheetByIndex(scDocument, (short) 0); + SCUtil.setTextToCellRange(movesheet, 0, 0, 5, 2, stringValues); - for (int intY = 0; intY < stringValues.length; intY++) { - for (int intX = 0; intX < stringValues[intY].length; intX++) { - // Insert the value to the cell, specified by intY and intX. - this.insertIntoCell(intY, intX, stringValues[intY][intX], - spreadSheet, ""); - } - } - // Before move, get the 2nd sheet name - XSpreadsheet secondSheetBeforeMove = (XSpreadsheet) UnoRuntime - .queryInterface(XSpreadsheet.class, - xspreadsheetIndex.getByIndex(1)); - XNamed secondSheetNameBeforeMove = (XNamed) UnoRuntime.queryInterface( - XNamed.class, secondSheetBeforeMove); + String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex( + scDocument, (short) 1); // move the first sheet spreadsheets.moveByName(sheetname, (short) 2); // Save and reload document - reloadSpreadsheet("Testmovesheet.xls"); + SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls"); + XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, + scDocument, "Testmovesheet.xls"); + scDocument = scDocumentTemp; // After move, get the first sheet name, and verify it same as 2nd sheet // name before move - XSpreadsheet firstSheetAfterMove = (XSpreadsheet) UnoRuntime - .queryInterface(XSpreadsheet.class, - xspreadsheetIndex.getByIndex(0)); - XNamed xfirstsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface( - XNamed.class, firstSheetAfterMove); + String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex( + scDocument, (short) 0); assertEquals("Expect result should be Sheet2", - secondSheetNameBeforeMove.getName(), - xfirstsheetnameAfterMove.getName()); + secondSheetNameBeforeMove, firstsheetnameAfterMove); // Get the target sheet name after move - XSpreadsheet sheetAfterMove = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); - XNamed xsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface( - XNamed.class, sheetAfterMove); + String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument, + (short) 1); assertEquals("Expect result should be sourcesheet", sheetname, - xsheetnameAfterMove.getName()); + sheetnameAfterMove); // Check the cell value after move - XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( - XModel.class, scDocument); - XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime - .queryInterface(XSpreadsheetView.class, - xSpreadsheetModel.getCurrentController()); - XSpreadsheet movesheet = (XSpreadsheet) UnoRuntime.queryInterface( - XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); - for (int intY = 0; intY < stringValues.length; intY++) { - for (int intX = 0; intX < stringValues[intY].length; intX++) { - XCell xcell = null; - xcell = movesheet.getCellByPosition(intY, intX); - assertEquals(stringValues[intY][intX], xcell.getFormula()); - } - } + XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument, + (short) 1); + String[][] stringValuesaftermove = SCUtil.getTextFromCellRange( + sheetaftermove, 0, 0, 5, 2); + assertArrayEquals("Expect result should be stringValues", stringValues, + stringValuesaftermove); + } - // input value into sheet cell - public static void insertIntoCell(int intX, int intY, String stringValue, - XSpreadsheet xspreadsheet, String stringFlag) - throws IndexOutOfBoundsException { - XCell xcell = null; - xcell = xspreadsheet.getCellByPosition(intX, intY); - if (stringFlag.equals("V")) { - xcell.setValue((new Float(stringValue)).floatValue()); - } else { - xcell.setFormula(stringValue); - } + @Test + public void hideShowSheet() throws Exception { + // Insert a sheet named hide sheet after first sheet + String sheetname = "hide sheet"; + scDocument = SCUtil.getSCDocument(scComponent); + XSpreadsheets spreadsheets = scDocument.getSheets(); + spreadsheets.insertNewByName(sheetname, (short) 1); + + // active the sheet second sheet "hide sheet" + XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 1); + SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); + // get second sheet name and verify it should be "hide sheet" + assertEquals("expect active sheet name will be hide sheet", sheetname, + SCUtil.getSCSheetNameByIndex(scDocument, (short) 1)); + + // hide the sheet you insert + XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime + .queryInterface(XPropertySet.class, secondSpreadSheet); + boolean isvisiable = false; + sheetPropertySet.setPropertyValue("IsVisible", isvisiable); + + // Save and reload document + SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls"); + XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, + scDocument, "Testhideshowsheet.xls"); + scDocument = scDocumentTemp; + + // get the active sheet name after hide sheet, it should be Sheet2 + String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2); + String activesheetname = SCUtil.getSCActiveSheetName(scDocument); + assertEquals("Expect sheet name should be Sheet2", sheet2Name, + activesheetname); + + // show sheet "hide sheet" + sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface( + XPropertySet.class, + SCUtil.getSCSheetByIndex(scDocument, (short) 1)); + isvisiable = true; + sheetPropertySet.setPropertyValue("IsVisible", isvisiable); + + // active sheet "hide sheet" + secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1); + SCUtil.setCurrentSheet(scDocument, secondSpreadSheet); + + // Get current active sheet name, verify it same as "hide sheet" + String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument); + assertEquals("Expect active sheet name is hidesheet", sheetname, + currentactivesheetname); + SCUtil.save(scDocument); } - // Save and load the document - public XSpreadsheetDocument reloadSpreadsheet(String spreadSheetname) - throws Exception { - String filePath = Testspace.getPath("output/" + spreadSheetname); - XStorable xStorable = (XStorable) UnoRuntime.queryInterface( - XStorable.class, scDocument); - PropertyValue[] aStoreProperties = new PropertyValue[2]; - aStoreProperties[0] = new PropertyValue(); - aStoreProperties[1] = new PropertyValue(); - aStoreProperties[0].Name = "Override"; - aStoreProperties[0].Value = true; - aStoreProperties[1].Name = "FilterName"; - aStoreProperties[1].Value = "MS Excel 97"; - xStorable.storeAsURL(FileUtil.getUrl(filePath), aStoreProperties); + @Test + public void sheetColor() throws Exception { + // get first sheet propertyset + scDocument = SCUtil.getSCDocument(scComponent); + XSpreadsheets spreadsheets = scDocument.getSheets(); + XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 0); + XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime + .queryInterface(XPropertySet.class, firstSpreadSheet); - return UnoRuntime.queryInterface(XSpreadsheetDocument.class, - unoApp.loadDocument(filePath)); + // Set tabcolor to 111 + sheet1PropertySet.setPropertyValue("TabColor", 111); + + // copy the color sheet to new sheet + spreadsheets.copyByName( + SCUtil.getSCSheetNameByIndex(scDocument, (short) 0), + "newsheet", (short) 3); + + // Save and reopen the document + SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods"); + XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, + scDocument, "Testcolorsheet.ods"); + scDocument = scDocumentTemp; + + // Get first sheet color + sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface( + XPropertySet.class, + SCUtil.getSCSheetByIndex(scDocument, (short) 0)); + int firstSheetcolorid = (int) sheet1PropertySet + .getPropertyValue("TabColor"); + + // Get the copyed sheet color + XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime + .queryInterface(XPropertySet.class, + SCUtil.getSCSheetByIndex(scDocument, (short) 3)); + int copySheetcolorid = (int) newsheetPropertySet + .getPropertyValue("TabColor"); + + // Verify first sheet color changed successfully + assertEquals("Expect color should be 111", 111, firstSheetcolorid); + + // Verify first sheet color same as copy sheet color + assertEquals("Expect color should be 111", firstSheetcolorid, + copySheetcolorid); } + + @Test + public void insertSheetFromfile() throws Exception { + // New a document source.xls, add value to 3 sheet + scDocument = SCUtil.getSCDocument(scComponent); + XSpreadsheets spreadsheets = scDocument.getSheets(); + XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 0); + XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 1); + XSpreadsheet thirdSheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 2); + SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2"); + SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2"); + SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=2*2"); + + // Save and close this document + SCUtil.saveFileAs(scComponent, "source", "xls"); + SCUtil.closeFile(scDocument); + + // get source document URL + String SourcestoreUrl = Testspace.getUrl("output/" + "source" + "." + + "xls"); + + // New a document + scComponent = unoApp.newDocument("scalc"); + scDocument = SCUtil.getSCDocument(scComponent); + spreadsheets = scDocument.getSheets(); + // Insert firstexternalsheet sheet, link with Sheet1 in source document + // and the link mode is NORMAL + spreadsheets.insertNewByName("firstexternalsheet", (short) 3); + XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 3); + XSheetLinkable xfirstSheetLinkable = (XSheetLinkable) UnoRuntime + .queryInterface(XSheetLinkable.class, firstexternalsheet); + xfirstSheetLinkable.link(SourcestoreUrl, "", "MS Excel 97", "", + SheetLinkMode.NORMAL); + + // Insert secondexternalsheet sheet, link with Sheet2 in source document + // and the link mode is VALUE + spreadsheets.insertNewByName("secondexternalsheet", (short) 4); + XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 4); + XSheetLinkable xsecondSheetLinkable = (XSheetLinkable) UnoRuntime + .queryInterface(XSheetLinkable.class, secondexternalsheet); + xsecondSheetLinkable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "", + SheetLinkMode.VALUE); + + // Insert secondexternalsheet sheet, link with Sheet2 in source document + // and the link mode is NONE + spreadsheets.insertNewByName("thirdexternalsheet", (short) 5); + XSpreadsheet thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, + (short) 5); + XSheetLinkable xthirdSheetLinkable = (XSheetLinkable) UnoRuntime + .queryInterface(XSheetLinkable.class, thirdexternalsheet); + xthirdSheetLinkable.link(SourcestoreUrl, "Sheet3", "MS Excel 97", "", + SheetLinkMode.NONE); + + // Verify firstexternalsheet + assertEquals("Expect formula should be =2*2", "=2*2", + SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 4", "4", + SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); + + // Verify secondexternalsheet + assertEquals("Expect formula should be 4", "4", + SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 4", "4", + SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); + + // Verify thirdexternalsheet + assertEquals("Expect formula should be blank", "", + SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); + assertEquals("Expect formula result should be blank", "", + SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); + + // save document and verify the linked sheet again + SCUtil.saveFileAs(scComponent, "linked", "ods"); + XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp, + scDocument, "linked.ods"); + scDocument = tempscDocument; + firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); + secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); + thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); + + // Verify firstexternalsheet + assertEquals("Expect formula should be =2*2", "=2*2", + SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 4", "4", + SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); + + // Verify secondexternalsheet + assertEquals("Expect formula should be 4", "4", + SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 4", "4", + SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); + + // Verify thirdexternalsheet + assertEquals("Expect formula should be blank", "", + SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); + assertEquals("Expect formula result should be blank", "", + SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); + + //save and close document + SCUtil.save(scDocument); + SCUtil.closeFile(scDocument); + + //Open souce document and change the value in souce document + XSpreadsheetDocument sourcescDocument = SCUtil.reloadFile(unoApp, + scDocument, "source.xls"); + firstSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 0); + secondSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 1); + thirdSheet = SCUtil.getSCSheetByIndex(sourcescDocument, (short) 2); + SCUtil.setFormulaToCell(firstSheet, 1, 2, "=3*3"); + SCUtil.setFormulaToCell(secondSheet, 1, 2, "=3*3"); + SCUtil.setFormulaToCell(thirdSheet, 1, 2, "=3*3"); + SCUtil.save(sourcescDocument); + SCUtil.closeFile(sourcescDocument); + + //Open link document + tempscDocument = SCUtil.reloadFile(unoApp, scDocument, "linked.ods"); + scDocument = tempscDocument; + spreadsheets = scDocument.getSheets(); + + firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3); + secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4); + thirdexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 5); + + //get Object SheetLinks for document + XPropertySet sheetpropertyset = (XPropertySet) UnoRuntime + .queryInterface(XPropertySet.class, scDocument); + Object sheetLinks = sheetpropertyset.getPropertyValue("SheetLinks"); + + XIndexAccess xsheetlinks = (XIndexAccess) UnoRuntime.queryInterface( + XIndexAccess.class, sheetLinks); + + //Refresh all links + for (int i = 0; i < xsheetlinks.getCount(); i++) { + Object sheetlink = xsheetlinks.getByIndex(i); + XRefreshable xsheetRefreshable = (XRefreshable) UnoRuntime + .queryInterface(XRefreshable.class, sheetlink); + xsheetRefreshable.refresh(); + } + + // Verify firstexternalsheet + assertEquals("Expect formula should be =3*3", "=3*3", + SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 9", "9", + SCUtil.getTextFromCell(firstexternalsheet, 1, 2)); + + // Verify secondexternalsheet + assertEquals("Expect formula should be 9", "9", + SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2)); + assertEquals("Expect formula result should be 9", "9", + SCUtil.getTextFromCell(secondexternalsheet, 1, 2)); + + // Verify thirdexternalsheet + assertEquals("Expect formula should be blank", "", + SCUtil.getFormulaFromCell(thirdexternalsheet, 1, 2)); + assertEquals("Expect formula result should be blank", "", + SCUtil.getTextFromCell(thirdexternalsheet, 1, 2)); + + //Save the document before close + SCUtil.save(scDocument); + + } + }