[Java] OOo Writer and Calc macro examples

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

[Java] OOo Writer and Calc macro examples

Post by hol.sten »

This post shows working Java macro examples for OOo Writer and OOo Calc.

After some posted questions about macros in Java I asked myself, if and how it might be possible, to use Java as one of OOo's scripting languages. The OOo Developer's Guide explains, that it's possible: http://api.openoffice.org/docs/Develope ... work.xhtml

So, let's start with a simple example: The first example is the "HelloWorld (in Java)" example, which comes with every OOo installation (so, it's not Java code I've written, I added only a little bit of text to xTextRange.setString() to demonstrate how to call the macro from command line):

Code: Select all

import com.sun.star.frame.XModel;
import com.sun.star.script.provider.XScriptContext;
import com.sun.star.text.XText;
import com.sun.star.text.XTextDocument;
import com.sun.star.text.XTextRange;
import com.sun.star.uno.UnoRuntime;

public class HelloWorld {
  public static void printHelloWorld(XScriptContext xScriptContext) {
    XModel xDocModel = xScriptContext.getDocument();

    // getting the text document object
    XTextDocument xtextdocument = (XTextDocument) UnoRuntime.queryInterface(XTextDocument.class, xDocModel);
    XText xText = xtextdocument.getText();
    XTextRange xTextRange = xText.getEnd();
    xTextRange.setString("Hello World (in Java)!\nThis macro can be called from command line with\n\tsoffice test.odt \"vnd.sun.star.script:mylib.HelloWorld.printHelloWorld?language=Java&location=user\"");
  }
}
The second example is a rewritten and enhanced (set cell number formats and set column width) version of [BeanShell] OOo Calc macro example:

Code: Select all

import com.sun.star.awt.ActionEvent;
import com.sun.star.awt.KeyEvent;
import com.sun.star.awt.MouseEvent;
import com.sun.star.beans.PropertyValue;
import com.sun.star.beans.PropertyVetoException;
import com.sun.star.beans.UnknownPropertyException;
import com.sun.star.beans.XPropertySet;
import com.sun.star.frame.XComponentLoader;
import com.sun.star.frame.XController;
import com.sun.star.frame.XDesktop;
import com.sun.star.frame.XModel;
import com.sun.star.lang.IllegalArgumentException;
import com.sun.star.lang.Locale;
import com.sun.star.lang.WrappedTargetException;
import com.sun.star.lang.XMultiComponentFactory;
import com.sun.star.script.provider.XScriptContext;
import com.sun.star.sheet.XCellRangeData;
import com.sun.star.sheet.XSpreadsheet;
import com.sun.star.sheet.XSpreadsheetDocument;
import com.sun.star.sheet.XSpreadsheetView;
import com.sun.star.table.CellHoriJustify;
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.text.XText;
import com.sun.star.text.XTextCursor;
import com.sun.star.text.XTextRange;
import com.sun.star.uno.UnoRuntime;
import com.sun.star.uno.XComponentContext;
import com.sun.star.util.XNumberFormats;
import com.sun.star.util.XNumberFormatsSupplier;

import ooo.connector.BootstrapSocketConnector;

public class Calc {

  /**
   * Folder name where "soffice.exe" (on Windows) or "soffice" (on Linux/Unix) is located.
   * On Windows for example something like "C:/Program Files/OpenOffice.org 3.2/program/".
   * On Ubuntu for example something like "/usr/lib/openoffice/program/".
   */
  private static final String OOO_EXEC_FOLDER = "/usr/lib/openoffice/program/";

  /**
   * Called from a toolbar.
   */
  public static void fillSpreadsheet(XScriptContext xScriptContext, Short ignored) {
   fillSpreadsheet(xScriptContext);
  }

  /**
   * Called from a button with an action.
   */
  public static void fillSpreadsheet(XScriptContext xScriptContext, ActionEvent ignored) {
   fillSpreadsheet(xScriptContext);
  }

  /**
   * Called from a button with a key.
   */
  public static void fillSpreadsheet(XScriptContext xScriptContext, KeyEvent ignored) {
   fillSpreadsheet(xScriptContext);
  }

  /**
   * Called from a button with the mouse.
   */
  public static void fillSpreadsheet(XScriptContext xScriptContext, MouseEvent ignored) {
   fillSpreadsheet(xScriptContext);
  }

  /**
   * Called from a menu or the "Run Macro..." menu.
   */
  public static void fillSpreadsheet(XScriptContext xScriptContext) {
    XModel model = xScriptContext.getDocument();
    fillSpreadsheetDocument(getSpreadsheetDocument(model));
  }

  private static XSpreadsheetDocument getSpreadsheetDocument(XModel model) {
    if (model == null)
      return null;

    return (XSpreadsheetDocument) UnoRuntime.queryInterface(XSpreadsheetDocument.class, model);
  }

  private static XSpreadsheet getActiveSpreadsheet(XSpreadsheetDocument sheetDocument) {
    if (sheetDocument == null)
      return null;

    XModel model = (XModel) UnoRuntime.queryInterface(XModel.class, sheetDocument);

    // Get active spreadsheet
    XController controller = model.getCurrentController();
    XSpreadsheetView view = (XSpreadsheetView) UnoRuntime.queryInterface(XSpreadsheetView.class, controller);
    return (view != null)? view.getActiveSheet(): null;
  }

  private static void fillSpreadsheetDocument(XSpreadsheetDocument xspreadsheetdocument) {
    if (xspreadsheetdocument == null)
      return;

    XSpreadsheet sheet = getActiveSpreadsheet(xspreadsheetdocument);

    try {
        //
        // Create a TEXT CELL
        //

        // Get cell by position
        XCell cell = sheet.getCellByPosition(0, 0);

        // Access the cell text
        XText text = (XText) UnoRuntime.queryInterface(XText.class, cell);

        // Get the text cursor of the cell text
        XTextCursor cursor = text.createTextCursor();

        // Insert a string in the cell through the text cursor and overwrite the cell content
        // Using 'false' as third parameter adds the inserted string 
        text.insertString(cursor, "Java macro example", true);


        //
        // Access and modify VALUE CELLS
        //

        // Get cell by position
        cell = sheet.getCellByPosition(0, 2);

        // Set cell value
        cell.setValue(2345);

        // Get cell value and multiply the value by 4
        double nDblValue = cell.getValue() * 4;

        // Set cell value with result
        sheet.getCellByPosition(0, 3).setValue(nDblValue);


        //
        // Create FORMULA CELLS
        //

        // Get cell by position
        cell = sheet.getCellByPosition(0, 5);

        // Set formula string
        cell.setFormula("=1024/128");

        // Get cell by name
        cell = sheet.getCellByPosition(1, 3);

        // Set formula string
        cell.setFormula("=A3+A4");


        //
        // Fill CELL RANGE
        //

        // Get cell range by name
        XCellRange cellRange = sheet.getCellRangeByName("C3:D4");

        XCellRangeData data = (XCellRangeData) UnoRuntime.queryInterface(XCellRangeData.class, cellRange);
        Object[][] values =
          {
            {new Double(3.3), new Integer(128)},
            {new Double(4.4), new String("Entry")}
          };
        data.setDataArray(values);


        int line = 7;

        //
        // Create VALUE CELLS and format them with different formats
        //

        // Get cells by position and set cell value and number format
        cell = sheet.getCellByPosition(0, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0");

        cell = sheet.getCellByPosition(1, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,0");

        cell = sheet.getCellByPosition(2, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,00");

        cell = sheet.getCellByPosition(3, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,000");

        cell = sheet.getCellByPosition(4, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,0000");

        cell = sheet.getCellByPosition(5, line);
        cell.setValue(111.1234);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,00000");


        // Change line
        line += 2;

        cell = sheet.getCellByPosition(0, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0");

        cell = sheet.getCellByPosition(1, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,0");

        cell = sheet.getCellByPosition(2, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,00");

        cell = sheet.getCellByPosition(3, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,000");

        cell = sheet.getCellByPosition(4, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,0000");

        cell = sheet.getCellByPosition(5, line);
        cell.setValue(111.5678);
        setNumberFormat(xspreadsheetdocument,cell,"#.##0,00000");

    
        //
        // Create TEXT CELLS for assigning 0123 as text to a cell without loosing the leading zero
        //

        // Change line
        line += 2;

        // Get cell by position and set formula
        cell = sheet.getCellByPosition(0, line);
        cell.setFormula("'0123"); // The Excel-way of doing it

        // Get cell by position and set text using the XText interface
        cell = sheet.getCellByPosition(1, line);
        XText cellText = (XText) UnoRuntime.queryInterface(XText.class, cell);
        XTextCursor cellTextCursor = cellText.createTextCursor();
        cellText.insertString(cellTextCursor, "0123", true);

        // Get cell by position and set text using the XTextRange interface
        cell = sheet.getCellByPosition(2, line);
        XTextRange cellTextRange = (XTextRange) UnoRuntime.queryInterface(XTextRange.class,cell);
        cellTextRange.setString("0123");

        // Get cells by position and set cell value and number format
        cell = sheet.getCellByPosition(3, line);
        cell.setValue(123);
        setNumberFormat(xspreadsheetdocument,cell,"0000");

        cell = sheet.getCellByPosition(4, line);
        cell.setValue(123);
        setNumberFormat(xspreadsheetdocument,cell,"00000000");


        // Change line
        line += 2;

        //
        // Create FORMULA CELLS and format them with different formats
        //

        // Get cells by position and set formula and number format
        cell = sheet.getCellByPosition(0, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"TT.MM.JJ");

        cell = sheet.getCellByPosition(1, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"TT.MM.JJJJ");

        cell = sheet.getCellByPosition(2, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"T. MMM JJ");

        cell = sheet.getCellByPosition(3, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"T. MMM JJJJ");

        cell = sheet.getCellByPosition(4, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"T. MMMM JJ");

        cell = sheet.getCellByPosition(5, line);
        cell.setFormula("=TODAY()");
        setNumberFormat(xspreadsheetdocument,cell,"T. MMMM JJJJ");

        
        // Change line
        line += 2;

        //
        // Create cells with different cell alignments
        //

        XPropertySet cellProp;

        // Get cells by position and set formula and number format
        cell = sheet.getCellByPosition(0, line);
        cellTextRange = (XTextRange) UnoRuntime.queryInterface(XTextRange.class,cell);
        cellTextRange.setString("Left aligned");
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.LEFT);

        cell = sheet.getCellByPosition(1, line);
        cellTextRange = (XTextRange) UnoRuntime.queryInterface(XTextRange.class,cell);
        cellTextRange.setString("Center aligned");
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.CENTER);

        cell = sheet.getCellByPosition(2, line);
        cellTextRange = (XTextRange) UnoRuntime.queryInterface(XTextRange.class,cell);
        cellTextRange.setString("Right aligned");
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.RIGHT);

        cell = sheet.getCellByPosition(3, line);
        cell.setValue(1234);
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.LEFT);

        cell = sheet.getCellByPosition(4, line);
        cell.setValue(2345);
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.CENTER);

        cell = sheet.getCellByPosition(5, line);
        cell.setValue(3456);
        cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);
        cellProp.setPropertyValue("HoriJustify", CellHoriJustify.RIGHT);

    
        //
        // Get columns and set column width
        //

        // Get cell range by by name
        cellRange = sheet.getCellRangeByName("A1:F1");

        // Get columns
        XColumnRowRange columnRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, cellRange);
        XTableColumns columns = columnRowRange.getColumns();

        // Set column width
        Object column;
        XPropertySet columnProp;

        //Set the width of the column (in 1/100th mm)
        column = columns.getByIndex(0);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        //Set the width to 3 cm
        columnProp.setPropertyValue("Width", new Integer(3000));
        column = columns.getByIndex(1);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        //Set the width to 4 cm
        columnProp.setPropertyValue("Width", new Integer(4000));
        column = columns.getByIndex(2);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        //Set the width to 5 cm
        columnProp.setPropertyValue("Width", new Integer(5000));

        //Set the optimal column width
        column = columns.getByIndex(3);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        columnProp.setPropertyValue("OptimalWidth", new Boolean(true));
        column = columns.getByIndex(4);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        columnProp.setPropertyValue("OptimalWidth", new Boolean(true));
        column = columns.getByIndex(5);
        columnProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, column);
        columnProp.setPropertyValue("OptimalWidth", new Boolean(true));
    } catch (UnknownPropertyException e) {
      e.printStackTrace();
    } catch (PropertyVetoException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } catch (WrappedTargetException e) {
      e.printStackTrace();
    } catch (com.sun.star.lang.IndexOutOfBoundsException e) {
      e.printStackTrace();
    }
  }

  private static void setNumberFormat(XSpreadsheetDocument spreadsheetDocument, XCell cell, String numberFormatString) {
    try {
      // Get cell properties
      XPropertySet cellProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, cell);

      // Get the key of the number format string
      int numberFormatKey = getNumberFormat(spreadsheetDocument, numberFormatString);

      // Set cell property "NumberFormat" to the key of the number format string
      cellProp.setPropertyValue("NumberFormat", new Integer(numberFormatKey));
    } catch (UnknownPropertyException e) {
      e.printStackTrace();
    } catch (PropertyVetoException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } catch (WrappedTargetException e) {
      e.printStackTrace();
    }
  }

  /**
   * This method is based on code from the OOo Developer's Guide
   * http://api.openoffice.org/docs/DevelopersGuide/OfficeDev/OfficeDev.xhtml#1_2_5_Number_Formats
   */
  private static int getNumberFormat(XSpreadsheetDocument spreadsheetDocument, String numberFormatString) {
    if (spreadsheetDocument == null || numberFormatString == null || numberFormatString.trim().equals(""))
      return 0;
    
    // Query the number formats supplier of the spreadsheet document
    XNumberFormatsSupplier xNumberFormatsSupplier = (XNumberFormatsSupplier) UnoRuntime.queryInterface(XNumberFormatsSupplier.class, spreadsheetDocument);

    // Get the number formats from the supplier
    XNumberFormats xNumberFormats = xNumberFormatsSupplier.getNumberFormats();

    // Get the default locale
    Locale defaultLocale = new Locale();
    
    // Check if the number format string already exists
    int numberFormatKey = xNumberFormats.queryKey(numberFormatString, defaultLocale, false);
    
    // If not, add the number format string to number formats collection
    if (numberFormatKey == -1) {
      try {
        numberFormatKey = xNumberFormats.addNew(numberFormatString,defaultLocale);
      }
      catch( com.sun.star.util.MalformedNumberFormatException e) {
        System.out.println( "Bad number format code '"+numberFormatString+"': " + e);
        numberFormatKey = -1;
      }
    }
    
    return numberFormatKey;
  }

  public static void main(String[] args) {
    // This example works with the following arguments:
    // 1) A valid name of an OOo Calc document like "file:///c:/temp/calc.ods"
    // 2) "private:factory/scalc" to create a new OOo Calc document
    // 3) No argument at all to get the current OOo Calc document
    String loadUrl = (args.length > 0)? args[0]: null;
    try {
      // OOo executable folder
      XComponentContext context = BootstrapSocketConnector.bootstrap(OOO_EXEC_FOLDER);
        
      XMultiComponentFactory multiComponentFactory = context.getServiceManager();
      Object desktopFrame = multiComponentFactory.createInstanceWithContext("com.sun.star.frame.Desktop", context);
      XComponentLoader componentloader = (XComponentLoader) UnoRuntime.queryInterface(XComponentLoader.class,desktopFrame);

      Object component;
      if (loadUrl != null) {
        // Load the document
        try {
          component = componentloader.loadComponentFromURL(loadUrl, "_blank", 0, new PropertyValue[0]);
        } catch (Exception e) {
          component = null;
        }
      } else {
        // Get the current document
        XDesktop desktop = (XDesktop) UnoRuntime.queryInterface(XDesktop.class,desktopFrame);
        component = desktop.getCurrentComponent();
      }

      // Get the active spreadsheet of the component
      XModel model = (XModel)UnoRuntime.queryInterface(XModel.class, component);
      if (model != null) {
        System.out.println("File URL: "+model.getURL());
      }
      XSpreadsheetDocument spreadsheetDocument = getSpreadsheetDocument(model);
      
      if(spreadsheetDocument == null) {
        // Either the document couldn't be loaded, the loaded document wasn't
        // a spreadsheet, there wasn't a current document or the current
        // document wasn't a spreadsheet.
        // Whatever the reason has been, we create a new spreadsheet.
        component = componentloader.loadComponentFromURL("private:factory/scalc", "_blank", 0, new PropertyValue[0]);
        model = (XModel) UnoRuntime.queryInterface(XModel.class, component);
        spreadsheetDocument = getSpreadsheetDocument(model);
      }

      fillSpreadsheetDocument(spreadsheetDocument);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      System.exit(0);
    }
  }
}
The problem with scripts in Java is, that OOo offers no IDE and not even an editor for this. For this reason, scripting OOo in Java needs much more steps than scripting in BeanShell, JavaScript or OOo Basic. The following steps show how to create scripts in Java using NetBeans 6.0 with the two examples from above:
1) Start NetBeans IDE and create a new project called "OOoJavaMacros" by calling "File" > "New Project..."
2) Right-click with the mouse on the brand new project name and select "Properties" from the context menu
3) Select in the Project Properties from Categories the category "Libraries" and there the tab "Compile"
4) Press the button "Add JAR/Folder" and locate some of OOo's JAR files from OOo's installation directory which is on Windows for example "c:\program files\openoffice.org 2.3\program\classes". Add at least juh.jar, jurt.jar, ridl.jar and unoil.jar. Adding these JAR files exactly here in your Project Properties is very important!
5) Create two new classes "HelloWorld.java" and "Calc.java" containing the Java code posted above and save them. The two new classes must be put into the default package, although NetBeans complains about this: "Warning: It is highly recommended that you do NOT place Java classes in the default package". For Java macros I highly recommend quite the contrary!
6) Create a JAR file with NetBeans containing the class files of the two examples:
- Set the project containing the two examples as main project through "File" > "Set Main Project" > Select project from the list of projects
- Create the JAR file through "Build" > "Clean and Build Main Project"
- The output window of NetBeans shows the path of the JAR file: It's located inside the project folder in a subfolder named "dist"
7) Create a "parcel-descriptor.xml" file for the Java macros (this is done automatically for scripts in BeanShell and JavaScript by saving the script):

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>
<parcel language="Java" xmlns:parcel="scripting.dtd">
  <script language="Java">
    <locale lang="en">
      <displayname value="HelloWorld.printHelloWorld"/>
      <description>Prints "Hello World (in Java)".</description>
    </locale>
    <functionname value="HelloWorld.printHelloWorld"/>
    <logicalname value="HelloWorld.printHelloWorld"/>
    <languagedepprops>
      <prop name="classpath" value="OOoJavaMacros.jar"/>
    </languagedepprops>
  </script>
  <script language="Java">
    <locale lang="en">
      <displayname value="Calc.fillSpreadsheet"/>
      <description>Fills some cells in the active spreadsheet.</description>
    </locale>
    <functionname value="Calc.fillSpreadsheet"/>
    <logicalname value="Calc.fillSpreadsheet"/>
  </script>
</parcel>
OOo Developer's Guide - 19 Scripting Framework wrote:The "parcel-descriptor.xml" file is used by the Scripting Framework to find macros. The functionname element indicates the name of the Java method which should be executed as a macro. The classpath element can be used to indicate any jar or class files which are used by the macro. If the classpath element is not included, then the directory in which the "parcel-desciptor.xml" file is found and any jar files in that directory will be used as the classpath. All of the jar files in the program/classes directory are automatically placed in the classpath.
The "parcel-descriptor.xml" file above shows how to use the JAR file "OOoJavaMacros.jar" for the HelloWorld Java macro and a class file for the Calc Java macro. (I've crossed out "or class" in the quote above from the "OOo Developer's Guide - 19 Scripting Framework" chapter, because I didn't get the Java macros working by putting a class file into the classpath of the "parcel-descriptor.xml" file.)
8) Create a new folder in OOo's user directory script folder. On a Windows system the name of this folder is something like "c:\documents and settings\<USERNAME>\application data\OpenOffice.org2\user\Scripts\". Create inside this folder a subfolder "java" and within that another like "mylib" for example. The complete name of the Java script folder is than something like ""c:\documents and settings\<USERNAME>\application data\OpenOffice.org2\user\Scripts\java\mylib".
9) Following the above "parcel-descriptor.xml" file put into the created Java script folder the following files:
- parcel-descriptor.xml
- Calc.class (from the NetBeans project subfolder "build\classes")
- OOoJavaMacros.jar (from the NetBeans project subfolder "dist")
10) Start OOo (if it is already running, exit OOo and restart it)
11) Create a new OOo Writer document and call the HelloWorld macro through
- "Tools" > "Macros" > "Run Macro..."
- Expand Library tree of "My Macros" and select the library "mylib"
- Select the Java macro "HelloWorld.printHelloWorld" and press "Run"
12) Create a new OOo Calc document and call the Calc macro through
- "Tools" > "Macros" > "Run Macro..."
- Expand Library tree of "My Macros" and select the library "mylib"
- Select the Java macro "Calc.fillSpreadsheet" and press "Run"

If everything went without errors, you can see the result of both Java macros in OOo.

If you encounter problems during executing the Java macros, you'll see the drawback of Java macros: You cannot debug them in OOo like JavaScript and OOo Basic. To solve this, I added a "main" method to the "Calc.java" file. This main method connects through the BootstrapSocketConnector to OOo and depending on the runtime argument opens a given document, creates a new one or gets the current OOo document (if OOo is running). Using this main method you can debug the "Calc.fillSpreadsheet" example with NetBeans. If you want to set a document name like "file:///c:/temp/calc.ods" or "private:factory/scalc" (to create a new document) as runtime argument, you can do this in NetBeans this way:
a) Right-click with the mouse on the project name of the Java script project (see 1) above) and select "Properties" from the context menu
b) Select in the Project Properties from Categories the category "Run"
c) Set "Calc" as "Main Class" and "file:///c:/temp/calc.ods" or "private:factory/scalc" as "Arguments"
d) Set a breakpoint in the main method of "Calc.java" and call "Debug Main Project (Strg+F5)" from NetBean's toolbar.
Now you can debug the Java macro.

After reading through this thread http://user.services.openoffice.org/en/ ... =20&t=8232, I found out how to execute both macros from command line:
1) To execute HelloWorld.printHelloWorld you first need an OOo Writer document and this document must be closed. We name the OOo Writer document test.odt for example. Calling now

Code: Select all

soffice test.odt "vnd.sun.star.script:mylib.HelloWorld.printHelloWorld?language=Java&location=user"
from command line opens test.odt and adds some text at the end of the document.
2) To execute Calc.fillSpreadsheet you first need an OOo Calc document and this document must be closed. We name the OOo Calc document test.ods for example. Calling now

Code: Select all

soffice test.ods "vnd.sun.star.script:mylib.Calc.fillSpreadsheet?language=Java&location=user"
from command line opens test.ods and fills some cells in the document.
Last edited by hol.sten on Mon Dec 27, 2010 1:05 pm, edited 8 times in total.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Change log

Post by hol.sten »

27. Dec. 2010: Executing Java macros from command line added
16. Mar. 2008: Different settings of left, center and right horizontal cell alignments added
12. Mar. 2008: Setting the optimal column width added
09. Mar. 2008: Enhanced Java code example that can be called from a toolbar and a button, too (http://www.rugludallur.com/index.php?id=31)
28. Feb. 2008: Adding text cell examples for assigning 0123 without loosing the leading zero
17. Feb. 2008: Setting cell number formats and setting column width added
20. Jan. 2008: Creation of the Java macro
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
droidguy
Posts: 2
Joined: Sun May 27, 2012 8:59 am

Re: [Java] OOo Writer and Calc macro examples

Post by droidguy »

Hi,
A very nice post. Thanks!! Why do we have to specify the location of the OO installation, though: private static final String OOO_EXEC_FOLDER = "/usr/lib/openoffice/program/";
It makes it difficult to create a macro and give it to someone. He'd have to set the location himself and to know how to recompile the macro. I wouldn't work for many users, even like my brother who asked me to make a macro for him and he has no idea about java (and I don't even think he drinks coffee :lol: ). Can we avoid it, somehow?
droidguy
OpenOffice 3.3 on Windows 7
hol.sten
Volunteer
Posts: 495
Joined: Mon Oct 08, 2007 1:31 am
Location: Hamburg, Germany

Re: [Java] OOo Writer and Calc macro examples

Post by hol.sten »

droidguy wrote:Why do we have to specify the location of the OO installation, though: private static final String OOO_EXEC_FOLDER = "/usr/lib/openoffice/program/";
It makes it difficult to create a macro and give it to someone. He'd have to set the location himself and to know how to recompile the macro. I wouldn't work for many users, even like my brother who asked me to make a macro for him and he has no idea about java (and I don't even think he drinks coffee :lol: ). Can we avoid it, somehow?
It seems to me, you didn't read through the whole post or I didn't make myself clear enough. Anyway, take a closer look at the first example: Does it come with an OOO_EXEC_FOLDER declaration? No, it does not. Why? Because a macro does not need such an OOO_EXEC_FOLDER declaration.
2) Take a closer look at the second example: Where is the OOO_EXEC_FOLDER declaration used? Well, in the main() method of the code. And only there! What is the purpose of the main() method? I wrote that in my post: "If you encounter problems during executing the Java macros, you'll see the drawback of Java macros: You cannot debug them in OOo like JavaScript and OOo Basic. To solve this, I added a "main" method to the "Calc.java" file."

Conclusion: The main() method and the OOO_EXEC_FOLDER declaration is only need for debugging purposes. Working macros don't need them any more. You can leave them in the macro, the will not do any harm, or you can remove them previously before you build your macro package for your brother.
OOo 3.2.0 on Ubuntu 10.04 • OOo 3.2.1 on Windows 7 64-bit and MS Windows XP
richearle
Posts: 2
Joined: Fri Oct 12, 2018 4:04 pm

Re: [Java] OOo Writer and Calc macro examples

Post by richearle »

When I run the calc macro from Tools->Macro->Run Macro.. it works. But if I run it as an Approve action on a button it generates this command for me.. rje_javas.Calc.fillSpreadsheet (share, Java) but when I hit the button I get...

LibreOffice Error:
A Scripting Framework error occured while running the Java script
Calc.fillSpreadsheet.
Message: java.lang.NoSuchMethodException: StrictResolver.getProxy: Can't find
method: fillSpreadsheet


Can anybody tell me how best to resolve this please?

Also a note, about an earlier problem I came across. That is, every place code has something like this ..

Code: Select all

        setNumberFormat(xspreadsheetdocument,cell,"#.##0,000");
it produces a RunTimeException error. The comma after the decimal point, in the UK Locale at least, causes this. When you take out the comma (and there are 10 instances) so that the line becomes this

Code: Select all

        setNumberFormat(xspreadsheetdocument,cell,"#.##0000");
.. it works.
LIbreOffice 6.0.6.2 on Ubuntu 16.04
richearle
Posts: 2
Joined: Fri Oct 12, 2018 4:04 pm

Re: [Java] OOo Writer and Calc macro examples

Post by richearle »

I found the answer to my problem. When you call the macro from the 'Execute Action' rather than the 'Approve Action' it works as it should. In case that helps anybody.
LIbreOffice 6.0.6.2 on Ubuntu 16.04
Post Reply