xref: /AOO41X/test/testuno/source/testlib/uno/SCUtil.java (revision cebb507a3778fe3ad6e9619f5c9b011689597537)

/**************************************************************
 *
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 *
 *************************************************************/


package testlib.uno;

import java.util.HashMap;

import org.openoffice.test.common.FileUtil;
import org.openoffice.test.common.Testspace;
import org.openoffice.test.uno.UnoApp;

import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.XPropertySet;
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.lang.XComponent;
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.table.XCellRange;
import com.sun.star.table.XColumnRowRange;
import com.sun.star.table.XTableColumns;
import com.sun.star.table.XTableRows;
import com.sun.star.text.XText;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.util.XCloseable;


/**
 * Utilities of Spreadsheet
 *
 */

public class SCUtil {

    private static final String scTempDir = "output/sc/"; //Spreadsheet temp file directory
    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 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 text into specific cell
     * @param xCell
     * @param text
     * @throws Exception
     */
    public static void setTextToCell(XCell xCell, String text) throws Exception {
        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 end_col
     * @param end_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 end_col
     * @param end_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
     * @param end_col
     * @param end_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
     * @param end_col
     * @param end_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;
    }

    /**
     * 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();
    }

    /**
     * Set value of specific property from a cell
     * @param xCell
     * @param propName
     * @param value
     * @throws Exception
     */
    public static void setCellProperties(XCell xCell, String propName, Object value) throws Exception {

        XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell);
        xPropertySet.setPropertyValue(propName, value);
    }

    /**
     * Get value of specific property from a cell
     * @param xCell
     * @param propName
     * @return
     * @throws Exception
     */
    public static Object getCellProperties(XCell xCell, String propName) throws Exception {
        XPropertySet xPropertySet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xCell);
        Object value = xPropertySet.getPropertyValue(propName);

        return value;
    }

    /**
     * Clear temp file directory
     */
    public static void clearTempDir() {
        FileUtil.deleteFile(Testspace.getFile(Testspace.getPath(scTempDir)));
    }

    /**
     * Save file as specific file format into spreadsheet temp file 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(scTempDir + fileName + "." + extName);

        PropertyValue[] storeProps = new PropertyValue[2];
        storeProps[0] = new PropertyValue();
        storeProps[0].Name = "FilterName";
        storeProps[0].Value = filterName.get(extName);
        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);
    }

    /**
     * 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();

    }


    /**
     * 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 spreadsheet temp file 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(scTempDir + fullFileName);
        XSpreadsheetDocument xScDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, unoApp.loadDocument(filePath));

        return xScDocument;
    }

    /**
     * open file in Spreadsheet.
     * @param unoApp
     * @param filtpath   File path with the extension name. (e.g. "testcase/uno/sc/data/sample.xls")
     * @return
     * @throws Exception
     */
    public static XSpreadsheetDocument openFile(String filePath, UnoApp app) throws Exception {
        return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, app.loadDocument(filePath));
    }

    /**
     * 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");
        filterName.put("xlt", "MS Excel 97 Vorlage/Template");
        filterName.put("csv", "Text - txt - csv (StarCalc)");
    }

}