Attachment #79059 for issue #120655

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




 * 
 *************************************************************/


package testlib.uno;

import java.util.HashMap;


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;

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 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 {
		XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
		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,...
	 * @return
	 * @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));

		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);
		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);
		XTableColumns xTableColumns = xColumnRowRange.getColumns();

		return xTableColumns;
	}

	/**
	 * 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 {
		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 {
		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
		XText xText = (XText) UnoRuntime.queryInterface(XText.class, xCell);
		xText.setString(text);


	/**
	 * 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 {
		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 {
		XCell xCell = xSpreadsheet.getCellByPosition(column, row);
		double cellValue = xCell.getValue();

		return cellValue;
	}

	/**
	 * Get text from specific cell
	 * 
	 * @param xSpreadsheet
	 * @param column
	 * @param row

	 * @return
	 * @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 {
		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

	 * @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);
		XCell xCell = null;
		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

	 * @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);
		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++) {
				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

	 * @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);
		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++) {
				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

	 * @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);
		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++) {
				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

	/**
	 * 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);
		XController xController = xModel.getCurrentController();
		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);
		XController xController = xModel.getCurrentController();
		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")
	 * @throws Exception
	 */
	public static void saveFileAs(XComponent scComponent, String fileName,
			String extName) throws Exception {

		initFilterName();

		String storeUrl = Testspace
				.getUrl("output/" + fileName + "." + extName);

		PropertyValue[] storeProps = new PropertyValue[2];
		storeProps[0] = new PropertyValue();
		storeProps[0].Name = "FilterName";

		storeProps[1] = new PropertyValue();
		storeProps[1].Name = "Overwrite";
		storeProps[1].Value = new Boolean(true);

		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);
		xCloseable.close(false);
	}

	/**
	 * 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")
	 * @return
	 * @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));

		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");

Lines 28-35 Link Here

(-)C:/Users/IBM_ADMIN/workspace/testuno/source/testcase/uno/sc/sheet/SheetBasicTest.java (-132 / +241 lines)
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;
33	import com.sun.star.container.XIndexAccess;	37	import com.sun.star.container.XIndexAccess;
34	import com.sun.star.container.XNamed;	38	import com.sun.star.container.XNamed;
35	import com.sun.star.frame.XModel;	39	import com.sun.star.frame.XModel;
Lines 38-43 Link Here
38	import com.sun.star.lang.IndexOutOfBoundsException;	42	import com.sun.star.lang.IndexOutOfBoundsException;
39	import com.sun.star.lang.WrappedTargetException;	43	import com.sun.star.lang.WrappedTargetException;
40	import com.sun.star.lang.XComponent;	44	import com.sun.star.lang.XComponent;
		45	import com.sun.star.sheet.SheetLinkMode;
		46	import com.sun.star.sheet.XExternalDocLink;
		47	import com.sun.star.sheet.XExternalDocLinks;
		48	import com.sun.star.sheet.XSheetLinkable;
41	import com.sun.star.sheet.XSpreadsheet;	49	import com.sun.star.sheet.XSpreadsheet;
42	import com.sun.star.sheet.XSpreadsheetDocument;	50	import com.sun.star.sheet.XSpreadsheetDocument;
43	import com.sun.star.sheet.XSpreadsheetView;	51	import com.sun.star.sheet.XSpreadsheetView;
Lines 77-278 Link Here
77	public void insertRenameDeleteSheet() throws Exception {	85	public void insertRenameDeleteSheet() throws Exception {
78	// Insert a sheet named aa after first sheet	86	// Insert a sheet named aa after first sheet
79	String sheetname = "aa";	87	String sheetname = "aa";
80	scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(	88	scDocument = SCUtil.getSCDocument(scComponent);
81	XSpreadsheetDocument.class, scComponent);
82	XSpreadsheets spreadsheets = scDocument.getSheets();	89	XSpreadsheets spreadsheets = scDocument.getSheets();
83	spreadsheets.insertNewByName(sheetname, (short) 1);	90	spreadsheets.insertNewByName(sheetname, (short) 1);
84		91
85	// active the sheet second sheet aa	92	// active the sheet second sheet aa
86	XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime	93	XSpreadsheet newSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
87	.queryInterface(XIndexAccess.class, spreadsheets);	94	(short) 1);
88	XSpreadsheet newSpreadSheet = (XSpreadsheet) UnoRuntime.queryInterface(	95	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		96
97	// get the new speadsheet name	97	// get the new speadsheet name
98	XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class,	98	assertEquals("actual should equals aa", sheetname,
99	newSpreadSheet);	99	SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
100	assertEquals("actual should equals aa", sheetname, xsheetname.getName());
101		100
102	// Change the Spreadsheet name	101	// Change the Spreadsheet name
103	String changedname = "SpeadsheetAfterChange";	102	String changedname = "SpeadsheetAfterChange";
104	xsheetname.setName(changedname);	103	SCUtil.setSCSheetNameByIndex(scDocument, (short) 1, changedname);
105		104
106	// Save and reload document	105	// Save and reload document
107	reloadSpreadsheet("TestSpreadsheet.xls");	106	SCUtil.saveFileAs(scComponent, "TestSpreadsheet", "ods");
		107	XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
		108	scDocument, "TestSpreadsheet.ods");
108		109
		110	scDocument = scDocumentTemp;
		111	String sheetnameaftermove = SCUtil.getSCSheetNameByIndex(scDocument,
		112	(short) 1);
		113
109	// Verify the changed Spreadsheet name	114	// Verify the changed Spreadsheet name
110	assertEquals("actual should equals SpeadsheetAfterChange", changedname,	115	assertEquals("actual should equals SpeadsheetAfterChange", changedname,
111	xsheetname.getName());	116	sheetnameaftermove);
112		117
113	spreadsheets.removeByName(changedname);	118	scDocument.getSheets().removeByName(changedname);
114		119
115	assertFalse("actual should equals false",	120	assertFalse("actual should equals false",
116	spreadsheets.hasByName(changedname));	121	spreadsheets.hasByName(changedname));
117		122	SCUtil.save(scDocumentTemp);
118	}	123	}
119		124
120	@Test	125	@Test
121	public void copypastesheet() throws Exception {	126	public void copypastesheet() throws Exception {
122	// Insert some value into cells	127	// Insert some value into cells
123	scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(	128	scDocument = SCUtil.getSCDocument(scComponent);
124	XSpreadsheetDocument.class, scComponent);	129	String souceSheetName = "sourcesheet";
125	XSpreadsheets spreadsheets = scDocument.getSheets();	130	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" },	131	String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
134	{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },	132	{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
135	{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };	133	{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
		134	XSpreadsheet sourceSpreadSheet = SCUtil.getSCSheetByName(scDocument,
		135	souceSheetName);
136	// input strings into sheet1	136	// input strings into sheet1
137	for (int intY = 0; intY < stringValues.length; intY++) {	137	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	138	// copy the sheet from sourcesheet to copysheet
146	String newcopysheet = "copysheet";	139	String newcopysheet = "copysheet";
147	spreadsheets.copyByName(xsheetname.getName(), newcopysheet, (short) 2);	140	XSpreadsheets spreadsheets = scDocument.getSheets();
		141	spreadsheets.copyByName(souceSheetName, newcopysheet, (short) 2);
148		142
149	// Save and reload document	143	// Save and reload document
150	reloadSpreadsheet("TestCopysheet.xls");	144	SCUtil.saveFileAs(scComponent, "TestCopysheet", "xls");
151	XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(	145	XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
152	XModel.class, scDocument);	146	scDocument, "TestCopysheet.xls");
153	XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime	147	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		148
		149	XSpreadsheet copysheet = SCUtil
		150	.getSCSheetByIndex(scDocument, (short) 2);
		151	String[][] CopystringValues = SCUtil.getTextFromCellRange(copysheet, 0,
		152	0, 5, 2);
		153	assertArrayEquals("Expect string value should be stringValues",
		154	stringValues, CopystringValues);
		155
166	}	156	}
167		157
168	@Test	158	@Test
169	public void movesheet() throws Exception {	159	public void movesheet() throws Exception {
170		160
171	// new sc document	161	// new sc document
172	scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(	162	scDocument = SCUtil.getSCDocument(scComponent);
173	XSpreadsheetDocument.class, scComponent);	163	XSpreadsheets spreadsheets = scDocument.getSheets();
174		164
175	// change the first sheet name and input same value into the sheet cell	165	// 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";	166	String sheetname = "sourcesheet";
184	xsheetname.setName(sheetname);	167	SCUtil.setSCSheetNameByIndex(scDocument, (short) 0, sheetname);
185	String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },	168	String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" },
186	{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },	169	{ "Profit", "12.3", "43.2", "5.1", "76", "56.8" },
187	{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };	170	{ "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, };
		171	XSpreadsheet movesheet = SCUtil
		172	.getSCSheetByIndex(scDocument, (short) 0);
		173	SCUtil.setTextToCellRange(movesheet, 0, 0, 5, 2, stringValues);
188		174
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	175	// Before move, get the 2nd sheet name
198	XSpreadsheet secondSheetBeforeMove = (XSpreadsheet) UnoRuntime	176	String secondSheetNameBeforeMove = SCUtil.getSCSheetNameByIndex(
199	.queryInterface(XSpreadsheet.class,	177	scDocument, (short) 1);
200	xspreadsheetIndex.getByIndex(1));
201	XNamed secondSheetNameBeforeMove = (XNamed) UnoRuntime.queryInterface(
202	XNamed.class, secondSheetBeforeMove);
203		178
204	// move the first sheet	179	// move the first sheet
205	spreadsheets.moveByName(sheetname, (short) 2);	180	spreadsheets.moveByName(sheetname, (short) 2);
206		181
207	// Save and reload document	182	// Save and reload document
208	reloadSpreadsheet("Testmovesheet.xls");	183	SCUtil.saveFileAs(scComponent, "Testmovesheet", "xls");
		184	XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
		185	scDocument, "Testmovesheet.xls");
		186	scDocument = scDocumentTemp;
209		187
210	// After move, get the first sheet name, and verify it same as 2nd sheet	188	// After move, get the first sheet name, and verify it same as 2nd sheet
211	// name before move	189	// name before move
212	XSpreadsheet firstSheetAfterMove = (XSpreadsheet) UnoRuntime	190	String firstsheetnameAfterMove = SCUtil.getSCSheetNameByIndex(
213	.queryInterface(XSpreadsheet.class,	191	scDocument, (short) 0);
214	xspreadsheetIndex.getByIndex(0));
215	XNamed xfirstsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface(
216	XNamed.class, firstSheetAfterMove);
217	assertEquals("Expect result should be Sheet2",	192	assertEquals("Expect result should be Sheet2",
218	secondSheetNameBeforeMove.getName(),	193	secondSheetNameBeforeMove, firstsheetnameAfterMove);
219	xfirstsheetnameAfterMove.getName());
220		194
221	// Get the target sheet name after move	195	// Get the target sheet name after move
222	XSpreadsheet sheetAfterMove = (XSpreadsheet) UnoRuntime.queryInterface(	196	String sheetnameAfterMove = SCUtil.getSCSheetNameByIndex(scDocument,
223	XSpreadsheet.class, xspreadsheetIndex.getByIndex(1));	197	(short) 1);
224	XNamed xsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface(
225	XNamed.class, sheetAfterMove);
226	assertEquals("Expect result should be sourcesheet", sheetname,	198	assertEquals("Expect result should be sourcesheet", sheetname,
227	xsheetnameAfterMove.getName());	199	sheetnameAfterMove);
228		200
229	// Check the cell value after move	201	// Check the cell value after move
230	XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface(	202	XSpreadsheet sheetaftermove = SCUtil.getSCSheetByIndex(scDocument,
231	XModel.class, scDocument);	203	(short) 1);
232	XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime	204	String[][] stringValuesaftermove = SCUtil.getTextFromCellRange(
233	.queryInterface(XSpreadsheetView.class,	205	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		206
		207	assertArrayEquals("Expect result should be stringValues", stringValues,
		208	stringValuesaftermove);
		209
245	}	210	}
246		211
247	// input value into sheet cell	212	@Test
248	public static void insertIntoCell(int intX, int intY, String stringValue,	213	public void hideShowSheet() throws Exception {
249	XSpreadsheet xspreadsheet, String stringFlag)	214	// Insert a sheet named hide sheet after first sheet
250	throws IndexOutOfBoundsException {	215	String sheetname = "hide sheet";
251	XCell xcell = null;	216	scDocument = SCUtil.getSCDocument(scComponent);
252	xcell = xspreadsheet.getCellByPosition(intX, intY);	217	XSpreadsheets spreadsheets = scDocument.getSheets();
253	if (stringFlag.equals("V")) {	218	spreadsheets.insertNewByName(sheetname, (short) 1);
254	xcell.setValue((new Float(stringValue)).floatValue());	219
255	} else {	220	// active the sheet second sheet "hide sheet"
256	xcell.setFormula(stringValue);	221	XSpreadsheet secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
257	}	222	(short) 1);
		223	SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
		224	// get second sheet name and verify it should be "hide sheet"
		225	assertEquals("expect active sheet name will be hide sheet", sheetname,
		226	SCUtil.getSCSheetNameByIndex(scDocument, (short) 1));
		227
		228	// hide the sheet you insert
		229	XPropertySet sheetPropertySet = (XPropertySet) UnoRuntime
		230	.queryInterface(XPropertySet.class, secondSpreadSheet);
		231	boolean isvisiable = false;
		232	sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
		233
		234	// Save and reload document
		235	SCUtil.saveFileAs(scComponent, "Testhideshowsheet", "xls");
		236	XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
		237	scDocument, "Testhideshowsheet.xls");
		238	scDocument = scDocumentTemp;
		239
		240	// get the active sheet name after hide sheet, it should be Sheet2
		241	String sheet2Name = SCUtil.getSCSheetNameByIndex(scDocument, (short) 2);
		242	String activesheetname = SCUtil.getSCActiveSheetName(scDocument);
		243	assertEquals("Expect sheet name should be Sheet2", sheet2Name,
		244	activesheetname);
		245
		246	// show sheet "hide sheet"
		247	sheetPropertySet = (XPropertySet) UnoRuntime.queryInterface(
		248	XPropertySet.class,
		249	SCUtil.getSCSheetByIndex(scDocument, (short) 1));
		250	isvisiable = true;
		251	sheetPropertySet.setPropertyValue("IsVisible", isvisiable);
		252
		253	// active sheet "hide sheet"
		254	secondSpreadSheet = SCUtil.getSCSheetByIndex(scDocument, (short) 1);
		255	SCUtil.setCurrentSheet(scDocument, secondSpreadSheet);
		256
		257	// Get current active sheet name, verify it same as "hide sheet"
		258	String currentactivesheetname = SCUtil.getSCActiveSheetName(scDocument);
		259	assertEquals("Expect active sheet name is hidesheet", sheetname,
		260	currentactivesheetname);
		261	SCUtil.save(scDocument);
258	}	262	}
259		263
260	// Save and load the document	264	@Test
261	public XSpreadsheetDocument reloadSpreadsheet(String spreadSheetname)	265	public void sheetColor() throws Exception {
262	throws Exception {	266	// get first sheet propertyset
263	String filePath = Testspace.getPath("output/" + spreadSheetname);	267	scDocument = SCUtil.getSCDocument(scComponent);
264	XStorable xStorable = (XStorable) UnoRuntime.queryInterface(	268	XSpreadsheets spreadsheets = scDocument.getSheets();
265	XStorable.class, scDocument);	269	XSpreadsheet firstSpreadSheet = SCUtil.getSCSheetByIndex(scDocument,
266	PropertyValue[] aStoreProperties = new PropertyValue[2];	270	(short) 0);
267	aStoreProperties[0] = new PropertyValue();	271	XPropertySet sheet1PropertySet = (XPropertySet) UnoRuntime
268	aStoreProperties[1] = new PropertyValue();	272	.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		273
275	return UnoRuntime.queryInterface(XSpreadsheetDocument.class,	274	// Set tabcolor to 111
276	unoApp.loadDocument(filePath));	275	sheet1PropertySet.setPropertyValue("TabColor", 111);
		276
		277	// copy the color sheet to new sheet
		278	spreadsheets.copyByName(
		279	SCUtil.getSCSheetNameByIndex(scDocument, (short) 0),
		280	"newsheet", (short) 3);
		281
		282	// Save and reopen the document
		283	SCUtil.saveFileAs(scComponent, "Testcolorsheet", "ods");
		284	XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
		285	scDocument, "Testcolorsheet.ods");
		286	scDocument = scDocumentTemp;
		287
		288	// Get first sheet color
		289	sheet1PropertySet = (XPropertySet) UnoRuntime.queryInterface(
		290	XPropertySet.class,
		291	SCUtil.getSCSheetByIndex(scDocument, (short) 0));
		292	int firstSheetcolorid = (int) sheet1PropertySet
		293	.getPropertyValue("TabColor");
		294
		295	// Get the copyed sheet color
		296	XPropertySet newsheetPropertySet = (XPropertySet) UnoRuntime
		297	.queryInterface(XPropertySet.class,
		298	SCUtil.getSCSheetByIndex(scDocument, (short) 3));
		299	int copySheetcolorid = (int) newsheetPropertySet
		300	.getPropertyValue("TabColor");
		301
		302	// Verify first sheet color changed successfully
		303	assertEquals("Expect color should be 111", 111, firstSheetcolorid);
		304
		305	// Verify first sheet color same as copy sheet color
		306	assertEquals("Expect color should be 111", firstSheetcolorid,
		307	copySheetcolorid);
277	}	308	}
		309
		310	@Test
		311	public void insertSheetFromfile() throws Exception {
		312	// New a document source.xls, add value to 3 sheet
		313	scDocument = SCUtil.getSCDocument(scComponent);
		314	XSpreadsheets spreadsheets = scDocument.getSheets();
		315	XSpreadsheet firstSheet = SCUtil.getSCSheetByIndex(scDocument,
		316	(short) 0);
		317	XSpreadsheet secondSheet = SCUtil.getSCSheetByIndex(scDocument,
		318	(short) 1);
		319	SCUtil.setFormulaToCell(firstSheet, 1, 2, "=2*2");
		320	SCUtil.setFormulaToCell(secondSheet, 1, 2, "=2*2");
		321
		322	// Save and close this document
		323	SCUtil.saveFileAs(scComponent, "source", "xls");
		324	SCUtil.closeFile(scDocument);
		325
		326	// get source document URL
		327	String SourcestoreUrl = Testspace.getUrl("output/" + "source" + "."
		328	+ "xls");
		329
		330	// New a document
		331	scComponent = unoApp.newDocument("scalc");
		332	scDocument = SCUtil.getSCDocument(scComponent);
		333	spreadsheets = scDocument.getSheets();
		334	// Insert firstexternalsheet sheet, link with Sheet1 in source document
		335	// and the link mode is NORMAL
		336	spreadsheets.insertNewByName("firstexternalsheet", (short) 3);
		337	XSpreadsheet firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
		338	(short) 3);
		339	XSheetLinkable xfirstSheetLineable = (XSheetLinkable) UnoRuntime
		340	.queryInterface(XSheetLinkable.class, firstexternalsheet);
		341	xfirstSheetLineable.link(SourcestoreUrl, "", "MS Excel 97", "",
		342	SheetLinkMode.NORMAL);
		343
		344	// Insert secondexternalsheet sheet, link with Sheet2 in source document
		345	// and the link mode is NONE
		346	spreadsheets.insertNewByName("secondexternalsheet", (short) 4);
		347	XSpreadsheet secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument,
		348	(short) 4);
		349	XSheetLinkable xsecondSheetLineable = (XSheetLinkable) UnoRuntime
		350	.queryInterface(XSheetLinkable.class, secondexternalsheet);
		351	xsecondSheetLineable.link(SourcestoreUrl, "Sheet2", "MS Excel 97", "",
		352	SheetLinkMode.VALUE);
		353
		354	// Verify firstexternalsheet
		355	assertEquals("Expect formula should be =22", "=22",
		356	SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
		357	assertEquals("Expect formula result should be 4", "4",
		358	SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
		359
		360	// Verify secondexternalsheet
		361	assertEquals("Expect formula should be 4", "4",
		362	SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
		363	assertEquals("Expect formula result should be 4", "4",
		364	SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
		365
		366	// save document and verify the linked sheet again
		367	SCUtil.saveFileAs(scComponent, "linked", "ods");
		368	XSpreadsheetDocument tempscDocument = SCUtil.reloadFile(unoApp,
		369	scDocument, "linked.ods");
		370	scDocument = tempscDocument;
		371	firstexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 3);
		372	secondexternalsheet = SCUtil.getSCSheetByIndex(scDocument, (short) 4);
373
374	// Verify firstexternalsheet
375	assertEquals("Expect formula should be =22", "=22",
376	SCUtil.getFormulaFromCell(firstexternalsheet, 1, 2));
377	assertEquals("Expect formula result should be 4", "4",
378	SCUtil.getTextFromCell(firstexternalsheet, 1, 2));
379
380	// Verify secondexternalsheet
381	assertEquals("Expect formula should be 4", "4",
382	SCUtil.getFormulaFromCell(secondexternalsheet, 1, 2));
383	assertEquals("Expect formula result should be 4", "4",
384	SCUtil.getTextFromCell(secondexternalsheet, 1, 2));
385	}
386
278	}	387	}

Return to issue 120655