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

From Apache OpenOffice Wiki
Jump to: navigation, search
(New page: <maintab>First Steps||Programming||Applications||Get Started||Get Objects||[[Work with Objects|Wor...)
 
(add link to NB client application project type)
(30 intermediate revisions by 6 users not shown)
Line 1: Line 1:
−
<maintab>[[Development Concepts|First Steps]]||[[Programming|Programming]]||[[Applications|Applications]]||[[Get Started|Get Started]]||[[Get Objects|Get Objects]]||[[Work with Objects|Work with Objects]]||[[Types|Types]]||[[Example|Example]]</maintab>
+
{{Documentation/DevGuide/FirstStepsTOC
−
<subtab>[[Objects, Interfaces, and Services|Objects, Interfaces, and Services]]||[[Using Services|Using Services]]||[[Example Spreadsheet|Example Spreadsheet]]||[[Common Types|Types]]||[[Struct|Struct]]||[[Any|Any]]||[[Sequence|Sequence]]||[[Elements|Elements]]</subtab>
+
|FirstSteps2b=block
−
In this example, we will 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.
+
|ShowPrevNext=block
−
Add these import lines to the FirstConnection example above: [SOURCE:FirstSteps/FirstLoadComponent.java]
+
|PrevPage=Documentation/DevGuide/FirstSteps/Using Services
−
  import com.sun.star.beans.PropertyValue;
+
|NextPage=Documentation/DevGuide/FirstSteps/Common Types
−
   import com.sun.star.lang.XComponent;
+
}}
−
  import com.sun.star.sheet.XSpreadsheetDocument;
+
{{Documentation/DevGuideLanguages|Documentation/DevGuide/FirstSteps/{{SUBPAGENAME}}}}
−
  import com.sun.star.sheet.XSpreadsheets;
+
{{DISPLAYTITLE:Example: Working with a Spreadsheet Document}}
−
  import com.sun.star.sheet.XSpreadsheet;
+
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.
−
  import com.sun.star.sheet.XSpreadsheetView;
+
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.table.XCell;
+
 
−
  import com.sun.star.frame.XModel;
+
   <source lang="java">
−
  import com.sun.star.frame.XController;
+
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.XComponentLoader;
−
Edit the useConnection method as follows:
+
import com.sun.star.frame.XController;
−
protected void useConnection() throws java.lang.Exception {
+
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 {
 
         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();
+
             }
−
        }
+
−
        catch( Exception e) {
+
−
            e.printStackTrace();
+
−
            System.exit(1);
+
−
        }
+
  
−
try {
+
            XMultiComponentFactory xRemoteServiceManager = xRemoteContext.getServiceManager();
  
−
        // get the Desktop, we need its XComponentLoader interface to load a new document
+
            Object desktop = xRemoteServiceManager.createInstanceWithContext(
−
        Object desktop = xRemoteServiceManager.createInstanceWithContext(
+
                "com.sun.star.frame.Desktop", xRemoteContext);
−
            "com.sun.star.frame.Desktop", xRemoteContext);
+
            XComponentLoader xComponentLoader = (XComponentLoader)
−
       
+
                UnoRuntime.queryInterface(XComponentLoader.class, desktop);
−
        // 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];
−
        PropertyValue[] loadProps = new PropertyValue[0];
+
            XComponent xSpreadsheetComponent = xComponentLoader.loadComponentFromURL("private:factory/scalc", "_blank", 0, loadProps);
−
       
+
−
        // 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)
−
        XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument)UnoRuntime.queryInterface(
+
                UnoRuntime.queryInterface(XSpreadsheetDocument.class,
−
            XSpreadsheetDocument.class, xSpreadsheetComponent);
+
                                          xSpreadsheetComponent);
  
 +
            XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
 +
            xSpreadsheets.insertNewByName("MySheet", (short)0);
 +
            com.sun.star.uno.Type elemType = xSpreadsheets.getElementType();
  
−
        // use getSheets to get spreadsheets container
+
            System.out.println(elemType.getTypeName());
−
        XSpreadsheets xSpreadsheets = xSpreadsheetDocument.getSheets();
+
            Object sheet = xSpreadsheets.getByName("MySheet");
 +
            XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(
 +
                XSpreadsheet.class, sheet);
  
−
        //insert new sheet at position 0 and get it by name, then query its XSpreadsheet interface
+
            XCell xCell = xSpreadsheet.getCellByPosition(0, 0);
−
        xSpreadsheets.insertNewByName("MySheet", (short)0);
+
            xCell.setValue(21);
−
        Object sheet = xSpreadsheets.getByName("MySheet");
+
            xCell = xSpreadsheet.getCellByPosition(0, 1);
−
        XSpreadsheet xSpreadsheet = (XSpreadsheet)UnoRuntime.queryInterface(
+
            xCell.setValue(21);
−
             XSpreadsheet.class, sheet);
+
             xCell = xSpreadsheet.getCellByPosition(0, 2);
 +
            xCell.setFormula("=sum(A1:A2)");
  
−
          // use XSpreadsheet interface to get the cell A1 at position 0,0 and enter 21 as value
+
            XPropertySet xCellProps = (XPropertySet)UnoRuntime.queryInterface(
−
        XCell xCell = xSpreadsheet.getCellByPosition(0, 0);
+
                XPropertySet.class, xCell);
−
        xCell.setValue(21);
+
            xCellProps.setPropertyValue("CellStyle", "Result");
  
−
// enter another value into the cell A2 at position 0,1
+
            XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
−
        xCell = xSpreadsheet.getCellByPosition(0, 1);
+
                XModel.class, xSpreadsheetComponent);
−
        xCell.setValue(21);
+
            XController xSpreadsheetController = xSpreadsheetModel.getCurrentController();
 +
            XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)
 +
                UnoRuntime.queryInterface(XSpreadsheetView.class,
 +
                                          xSpreadsheetController);
 +
            xSpreadsheetView.setActiveSheet(xSpreadsheet);
  
−
// sum up the two cells
+
            // *********************************************************
−
        xCell = xSpreadsheet.getCellByPosition(0, 2);
+
            // example for use of enum types
−
        xCell.setFormula("=sum(A1:A2)");
+
            xCellProps.setPropertyValue("VertJustify",
 +
                                        com.sun.star.table.CellVertJustify.TOP);
  
−
        // 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");
+
            // example for a sequence of PropertyValue structs
 +
            // create an array with one PropertyValue struct, it contains
 +
            // references only
 +
            loadProps = new PropertyValue[1];
  
−
        // we want to make our new sheet the current sheet, so we need to ask the model
+
            // instantiate PropertyValue struct and set its member fields
−
        // for the controller: first query the XModel interface from our spreadsheet component
+
            PropertyValue asTemplate = new PropertyValue();
−
        XModel xSpreadsheetModel = (XModel)UnoRuntime.queryInterface(
+
            asTemplate.Name = "AsTemplate";
−
          XModel.class, xSpreadsheetComponent);
+
            asTemplate.Value = new Boolean(true);
−
       
+
−
        // 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
+
            // assign PropertyValue struct to array of references for PropertyValue
−
        // setActiveSheet
+
            // structs
−
        XSpreadsheetView xSpreadsheetView = (XSpreadsheetView)UnoRuntime.queryInterface(
+
            loadProps[0] = asTemplate;
−
          XSpreadsheetView.class, xSpreadsheetController);
+
  
−
        // make our newly inserted sheet the active sheet using setActiveSheet
+
            // load calc file as template
−
         xSpreadsheetView.setActiveSheet(xSpreadsheet);    
+
            //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 );
 +
        }
 
     }
 
     }
−
     catch( com.sun.star.lang.DisposedException e ) { //works from Patch 1
+
      
−
        xRemoteContext = null;
+
−
        throw e;
+
−
    }         
+
 
}
 
}
−
Alternatively, you can add FirstLoadComponent.java from the samples directory to your current project, it contains the changes shown above.
+
</source>
 +
 
 +
Alternatively, you can add ''FirstLoadComponent.java'' from the samples directory to your current project, it contains the changes shown above.
 +
 
 +
{{PDL1}}
  
−
[[Category: Development Concepts]]
+
[[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