Difference between revisions of "Documentation/DevGuide/FirstSteps/Example: Working with a Spreadsheet Document"

From Apache OpenOffice Wiki
Jump to: navigation, search
(add link to NB client application project type)
(17 intermediate revisions by 6 users not shown)
Line 1: Line 1:
−
{{Documentation/APIGuide/FirstSteps/FirstSteps|FirstSteps=block|FirstSteps2b=block|PrevNext=block|Prev=Documentation/APIGuide/FirstSteps/Using Services|Next=Documentation/APIGuide/FirstSteps/Common Types}}
+
{{Documentation/DevGuide/FirstStepsTOC
 +
|FirstSteps2b=block
 +
|ShowPrevNext=block
 +
|PrevPage=Documentation/DevGuide/FirstSteps/Using Services
 +
|NextPage=Documentation/DevGuide/FirstSteps/Common Types
 +
}}
 +
{{Documentation/DevGuideLanguages|Documentation/DevGuide/FirstSteps/{{SUBPAGENAME}}}}
 
{{DISPLAYTITLE:Example: Working with a Spreadsheet Document}}
 
{{DISPLAYTITLE:Example: Working with a Spreadsheet Document}}
−
In this example, we will ask the remote service manager to give us the remote <code>Desktop</code> object and use its <code>loadComponentFromURL()</code> method to create a new spreadsheet document. From the document we get its sheets container where we insert and access a new sheet by name. In the new sheet, we enter values into A1 and A2 and summarize them in A3. The cell style of the summarizing cell gets the cell style Result, so that it appears in italics, bold and underlined. Finally, we make our new sheet the active sheet, so that the user can see it.
+
In this example, we will use the simple bootstrap mechanism ([http://api.openoffice.org/docs/java/ref/com/sun/star/comp/helper/Bootstrap.html#bootstrap() com.sun.star.comp.helper.Bootstrap.boostrap()]) to get the remote office context of a running office instance and ask the remote service manager to give us the remote <code><idls>com.sun.star.frame.Desktop</idls></code> object and use its <code><idlm>com.sun.star.frame.XComponentLoader:loadComponentFromURL</idlm>()</code> method to create a new spreadsheet document. From the document we get its sheets container where we insert and access a new sheet by name. In the new sheet, we enter values into A1 and A2 and summarize them in A3. The cell style of the summarizing cell gets the cell style Result, so that it appears in italics, bold and underlined. Finally, we make our new sheet the active sheet, so that the user can see it.
−
Add these import lines to the FirstConnection example above: <!--[SOURCE:FirstSteps/FirstLoadComponent.java]-->
+
Add the following code in a new generated [http://wiki.services.openoffice.org/wiki/OpenOffice_Simple_UNO_Client_Application_Project_Type NetBeans UNO client application] project FirstLoadComponent: <!--[SOURCE:FirstSteps/FirstLoadComponent.java]-->
  
−
   import com.sun.star.beans.PropertyValue;
+
   <source lang="java">
−
  import com.sun.star.lang.XComponent;
+
package ooo.sdk.devguide.firststeps;
−
  import com.sun.star.sheet.XSpreadsheetDocument;
+
−
  import com.sun.star.sheet.XSpreadsheets;
+
−
  import com.sun.star.sheet.XSpreadsheet;
+
−
  import com.sun.star.sheet.XSpreadsheetView;
+
−
  import com.sun.star.table.XCell;
+
−
  import com.sun.star.frame.XModel;
+
−
  import com.sun.star.frame.XController;
+
−
  import com.sun.star.frame.XComponentLoader;
+
  
−
Edit the <code>useConnection</code> method as follows:
+
import com.sun.star.beans.PropertyValue;
 +
import com.sun.star.beans.XPropertySet;
 +
import com.sun.star.uno.XComponentContext;
 +
import com.sun.star.comp.helper.Bootstrap;
 +
import com.sun.star.container.XEnumeration;
 +
import com.sun.star.container.XEnumerationAccess;
 +
import com.sun.star.frame.XComponentLoader;
 +
import com.sun.star.frame.XController;
 +
import com.sun.star.frame.XModel;
 +
import com.sun.star.lang.XComponent;
 +
import com.sun.star.lang.XMultiComponentFactory;
 +
import com.sun.star.sheet.XCellAddressable;
 +
import com.sun.star.sheet.XCellRangesQuery;
 +
import com.sun.star.sheet.XSheetCellRanges;
 +
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;
  
−
  protected void useConnection() throws java.lang.Exception {
+
public class FirstLoadComponent {
 +
   
 +
    /** Creates a new instance of FirstLoadComponent */
 +
    public FirstLoadComponent() {
 +
    }
 +
   
 +
    /**
 +
    * @param args the command line arguments
 +
    */
 +
    public static void main(String[] args) {
 
         try {
 
         try {
 
             // get the remote office component context
 
             // get the remote office component context
−
             xRemoteContext = com.sun.star.comp.helper.Bootstrap.bootstrap();
+
             XComponentContext xRemoteContext = Bootstrap.bootstrap();
−
             System.out.println("Connected to a running office ...");
+
            if (xRemoteContext == null) {
−
                  
+
                System.err.println("ERROR: Could not bootstrap default Office.");
−
             xRemoteServiceManager = xRemoteContext.getServiceManager();
+
            }
 +
 
 +
            XMultiComponentFactory xRemoteServiceManager = xRemoteContext.getServiceManager();
 +
 
 +
            Object desktop = xRemoteServiceManager.createInstanceWithContext(
 +
                "com.sun.star.frame.Desktop", xRemoteContext);
 +
            XComponentLoader xComponentLoader = (XComponentLoader)
 +
                UnoRuntime.queryInterface(XComponentLoader.class, desktop);
 +
 
 +
            PropertyValue[] loadProps = new PropertyValue[0];
 +
            XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0, loadProps);
 +
 
 +
            XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)
 +
                UnoRuntime.queryInterface(XSpreadsheetDocument.class,
 +
                                          xSpreadsheetComponent);
 +
 
 +
            XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
 +
            xSpreadsheets.insertNewByName("MySheet", (short)0);
 +
            com.sun.star.uno.Type elemType = xSpreadsheets.getElementType();
 +
 
 +
             System.out.println(elemType.getTypeName());
 +
            Object sheet = xSpreadsheets.getByName("MySheet");
 +
            XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(
 +
                XSpreadsheet.class, sheet);
 +
 
 +
            XCell xCell = xSpreadsheet.getCellByPosition(0, 0);
 +
            xCell.setValue(21);
 +
            xCell = xSpreadsheet.getCellByPosition(0, 1);
 +
            xCell.setValue(21);
 +
            xCell = xSpreadsheet.getCellByPosition(0, 2);
 +
            xCell.setFormula("=sum(A1:A2)");
 +
 
 +
            XPropertySet xCellProps = (XPropertySet)UnoRuntime.queryInterface(
 +
                XPropertySet.class, xCell);
 +
            xCellProps.setPropertyValue("CellStyle", "Result");
 +
 
 +
            XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
 +
                XModel.class, xSpreadsheetComponent);
 +
            XController xSpreadsheetController = xSpreadsheetModel.getCurrentController();
 +
            XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)
 +
                UnoRuntime.queryInterface(XSpreadsheetView.class,
 +
                                          xSpreadsheetController);
 +
            xSpreadsheetView.setActiveSheet(xSpreadsheet);
 +
 
 +
            // *********************************************************
 +
            // example for use of enum types
 +
            xCellProps.setPropertyValue("VertJustify",
 +
                                        com.sun.star.table.CellVertJustify.TOP);
 +
 
 +
 
 +
            // *********************************************************
 +
            // example for a sequence of PropertyValue structs
 +
            // create an array with one PropertyValue struct, it contains
 +
            // references only
 +
            loadProps = new PropertyValue[1];
 +
 
 +
            // instantiate PropertyValue struct and set its member fields
 +
            PropertyValue asTemplate = new PropertyValue();
 +
            asTemplate.Name = "AsTemplate";
 +
            asTemplate.Value = new Boolean(true);
 +
 
 +
            // assign PropertyValue struct to array of references for PropertyValue
 +
            // structs
 +
            loadProps[0] = asTemplate;
 +
 
 +
            // load calc file as template
 +
            //xSpreadsheetComponent = xComponentLoader.loadComponentFromURL(
 +
            //    "file:///c:/temp/DataAnalysys.ods", "_blank", 0, loadProps);
 +
 
 +
            // *********************************************************
 +
            // example for use of XEnumerationAccess
 +
            XCellRangesQuery xCellQuery = (XCellRangesQuery)
 +
                 UnoRuntime.queryInterface(XCellRangesQuery.class, sheet);
 +
             XSheetCellRanges xFormulaCells = xCellQuery.queryContentCells(
 +
                (short)com.sun.star.sheet.CellFlags.FORMULA);
 +
            XEnumerationAccess xFormulas = xFormulaCells.getCells();
 +
            XEnumeration xFormulaEnum = xFormulas.createEnumeration();
 +
 
 +
            while (xFormulaEnum.hasMoreElements()) {
 +
                Object formulaCell = xFormulaEnum.nextElement();
 +
                xCell = (XCell)UnoRuntime.queryInterface(XCell.class, formulaCell);
 +
                XCellAddressable xCellAddress = (XCellAddressable)
 +
                    UnoRuntime.queryInterface(XCellAddressable.class, xCell);
 +
                System.out.println("Formula cell in column " +
 +
                                  xCellAddress.getCellAddress().Column
 +
                                  + ", row " + xCellAddress.getCellAddress().Row
 +
                                  + " contains " + xCell.getFormula());
 +
            }
 +
 
 
         }
 
         }
−
         catch( Exception e) {
+
         catch (java.lang.Exception e){
 
             e.printStackTrace();
 
             e.printStackTrace();
−
            System.exit(1);
 
 
         }
 
         }
−
 
+
        finally {
−
  try {
+
             System.exit( 0 );
−
        // get the Desktop, we need its XComponentLoader interface to load a new document
+
         }
−
        Object desktop = xRemoteServiceManager.createInstanceWithContext(
+
−
             "com.sun.star.frame.Desktop", xRemoteContext);
+
−
       
+
−
        // query the XComponentLoader interface from the desktop
+
−
        XComponentLoader xComponentLoader = (XComponentLoader)UnoRuntime.queryInterface(
+
−
            XComponentLoader.class, desktop);
+
−
 
+
−
        // create empty array of PropertyValue structs, needed for loadComponentFromURL
+
−
        PropertyValue[] loadProps = new PropertyValue[0];
+
−
       
+
−
        // load new calc file
+
−
        XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL(
+
−
            "private:factory/scalc", "_blank", 0, loadProps);
+
−
 
+
−
         // query its XSpreadsheetDocument interface, we want to use getSheets()
+
−
        XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(
+
−
            XSpreadsheetDocument.class, xSpreadsheetComponent);
+
−
 
+
−
        // use getSheets to get spreadsheets container
+
−
        XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
+
−
 
+
−
        //insert new sheet at position 0 and get it by name, then query its XSpreadsheet interface
+
−
        xSpreadsheets.insertNewByName("MySheet", (short)0);
+
−
        Object sheet = xSpreadsheets.getByName("MySheet");
+
−
        XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(
+
−
            XSpreadsheet.class, sheet);
+
−
 
+
−
        // use XSpreadsheet interface to get the cell A1 at position 0,0 and enter 21 as value
+
−
        XCell xCell = xSpreadsheet.getCellByPosition(0, 0);
+
−
        xCell.setValue(21);
+
−
 
+
−
        // enter another value into the cell A2 at position 0,1
+
−
        xCell = xSpreadsheet.getCellByPosition(0, 1);
+
−
        xCell.setValue(21);
+
−
 
+
−
        // sum up the two cells
+
−
        xCell = xSpreadsheet.getCellByPosition(0, 2);
+
−
        xCell.setFormula("=sum(A1:A2)");
+
−
 
+
−
        // we want to access the cell property CellStyle, so query the cell's XPropertySet interface
+
−
        XPropertySet xCellProps = (XPropertySet)UnoRuntime.queryInterface(
+
−
            XPropertySet.class, xCell);
+
−
 
+
−
        // assign the cell style "Result" to our formula, which is available out of the box
+
−
        xCellProps.setPropertyValue("CellStyle", "Result");
+
−
 
+
−
        // we want to make our new sheet the current sheet, so we need to ask the model
+
−
        // for the controller: first query the XModel interface from our spreadsheet component
+
−
        XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
+
−
          XModel.class, xSpreadsheetComponent);
+
−
       
+
−
        // then get the current controller from the model
+
−
        XController xSpreadsheetController = xSpreadsheetModel.getCurrentController();
+
−
 
+
−
        // get the XSpreadsheetView interface from the controller, we want to call its method
+
−
        // setActiveSheet
+
−
        XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)UnoRuntime.queryInterface(
+
−
          XSpreadsheetView.class, xSpreadsheetController);
+
−
 
+
−
        // make our newly inserted sheet the active sheet using setActiveSheet
+
−
        xSpreadsheetView.setActiveSheet(xSpreadsheet);   
+
 
     }
 
     }
−
     catch( com.sun.star.lang.DisposedException e ) { //works from Patch 1
+
      
−
        xRemoteContext = null;
+
}
−
        throw e;
+
</source>
−
    }         
+
 
−
  }
+
 
Alternatively, you can add ''FirstLoadComponent.java'' from the samples directory to your current project, it contains the changes shown above.
 
Alternatively, you can add ''FirstLoadComponent.java'' from the samples directory to your current project, it contains the changes shown above.
  
 
{{PDL1}}
 
{{PDL1}}
−
[[Category: Developer's Guide]]
+
 
−
[[Category: API]]
+
[[Category:Documentation/Developer's Guide/First Steps]]

Revision as of 11:22, 27 May 2009



In this example, we will use the simple bootstrap mechanism (com.sun.star.comp.helper.Bootstrap.boostrap()) to get the remote office context of a running office instance and ask the remote service manager to give us the remote Desktop object and use its loadComponentFromURL() method to create a new spreadsheet document. From the document we get its sheets container where we insert and access a new sheet by name. In the new sheet, we enter values into A1 and A2 and summarize them in A3. The cell style of the summarizing cell gets the cell style Result, so that it appears in italics, bold and underlined. Finally, we make our new sheet the active sheet, so that the user can see it. Add the following code in a new generated NetBeans UNO client application project FirstLoadComponent: 

package ooo.sdk.devguide.firststeps;
 
import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.XPropertySet;
import com.sun.star.uno.XComponentContext;
import com.sun.star.comp.helper.Bootstrap;
import com.sun.star.container.XEnumeration;
import com.sun.star.container.XEnumerationAccess;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.frame.XController;
import com.sun.star.frame.XModel;
import com.sun.star.lang.XComponent;
import com.sun.star.lang.XMultiComponentFactory;
import com.sun.star.sheet.XCellAddressable;
import com.sun.star.sheet.XCellRangesQuery;
import com.sun.star.sheet.XSheetCellRanges;
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;
 
public class FirstLoadComponent {
 
    /** Creates a new instance of FirstLoadComponent */
    public FirstLoadComponent() {
    }
 
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            // get the remote office component context
            XComponentContext xRemoteContext = Bootstrap.bootstrap();
            if (xRemoteContext == null) {
                System.err.println("ERROR: Could not bootstrap default Office.");
            }
 
            XMultiComponentFactory xRemoteServiceManager = xRemoteContext.getServiceManager();
 
            Object desktop = xRemoteServiceManager.createInstanceWithContext(
                "com.sun.star.frame.Desktop", xRemoteContext);
            XComponentLoader xComponentLoader = (XComponentLoader)
                UnoRuntime.queryInterface(XComponentLoader.class, desktop);
 
            PropertyValue[] loadProps = new PropertyValue[0];
            XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0, loadProps);
 
            XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)
                UnoRuntime.queryInterface(XSpreadsheetDocument.class,
                                          xSpreadsheetComponent);
 
            XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
            xSpreadsheets.insertNewByName("MySheet", (short)0);
            com.sun.star.uno.Type elemType = xSpreadsheets.getElementType();
 
            System.out.println(elemType.getTypeName());
            Object sheet = xSpreadsheets.getByName("MySheet");
            XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(
                XSpreadsheet.class, sheet);
 
            XCell xCell = xSpreadsheet.getCellByPosition(0, 0);
            xCell.setValue(21);
            xCell = xSpreadsheet.getCellByPosition(0, 1);
            xCell.setValue(21);
            xCell = xSpreadsheet.getCellByPosition(0, 2);
            xCell.setFormula("=sum(A1:A2)");
 
            XPropertySet xCellProps = (XPropertySet)UnoRuntime.queryInterface(
                XPropertySet.class, xCell);
            xCellProps.setPropertyValue("CellStyle", "Result");
 
            XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
                XModel.class, xSpreadsheetComponent);
            XController xSpreadsheetController = xSpreadsheetModel.getCurrentController();
            XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)
                UnoRuntime.queryInterface(XSpreadsheetView.class,
                                          xSpreadsheetController);
            xSpreadsheetView.setActiveSheet(xSpreadsheet);
 
            // *********************************************************
            // example for use of enum types
            xCellProps.setPropertyValue("VertJustify",
                                        com.sun.star.table.CellVertJustify.TOP);
 
 
            // *********************************************************
            // example for a sequence of PropertyValue structs
            // create an array with one PropertyValue struct, it contains
            // references only
            loadProps = new PropertyValue[1];
 
            // instantiate PropertyValue struct and set its member fields
            PropertyValue asTemplate = new PropertyValue();
            asTemplate.Name = "AsTemplate";
            asTemplate.Value = new Boolean(true);
 
            // assign PropertyValue struct to array of references for PropertyValue
            // structs
            loadProps[0] = asTemplate;
 
            // load calc file as template
            //xSpreadsheetComponent = xComponentLoader.loadComponentFromURL(
            //    "file:///c:/temp/DataAnalysys.ods", "_blank", 0, loadProps);
 
            // *********************************************************
            // example for use of XEnumerationAccess
            XCellRangesQuery xCellQuery = (XCellRangesQuery)
                UnoRuntime.queryInterface(XCellRangesQuery.class, sheet);
            XSheetCellRanges xFormulaCells = xCellQuery.queryContentCells(
                (short)com.sun.star.sheet.CellFlags.FORMULA);
            XEnumerationAccess xFormulas = xFormulaCells.getCells();
            XEnumeration xFormulaEnum = xFormulas.createEnumeration();
 
            while (xFormulaEnum.hasMoreElements()) {
                Object formulaCell = xFormulaEnum.nextElement();
                xCell = (XCell)UnoRuntime.queryInterface(XCell.class, formulaCell);
                XCellAddressable xCellAddress = (XCellAddressable)
                    UnoRuntime.queryInterface(XCellAddressable.class, xCell);
                System.out.println("Formula cell in column " +
                                   xCellAddress.getCellAddress().Column
                                   + ", row " + xCellAddress.getCellAddress().Row
                                   + " contains " + xCell.getFormula());
            }
 
        }
        catch (java.lang.Exception e){
            e.printStackTrace();
        }
        finally {
            System.exit( 0 );
        }
    }
 
}

Alternatively, you can add FirstLoadComponent.java from the samples directory to your current project, it contains the changes shown above.

Content on this page is licensed under the Public Documentation License (PDL).
Retrieved from "https://wiki.openoffice.org/w/index.php?title=Documentation/DevGuide/FirstSteps/Example:_Working_with_a_Spreadsheet_Document&oldid=129749"
Personal tools
In other languages