[BeanShell] OOo Calc macro example

[hol.sten]

This post shows a working BeanShell macro example for OOo Calc:

Code: Select all

import com.sun.star.uno.UnoRuntime;
import com.sun.star.sheet.XSpreadsheetView;
import com.sun.star.text.XText;
import com.sun.star.sheet.XCellRangeData;

//
// Get active spreadsheet
//
model = XSCRIPTCONTEXT.getDocument();
controller = model.getCurrentController();
view = UnoRuntime.queryInterface(XSpreadsheetView.class, controller);
sheet = view.getActiveSheet();


//
// Create a TEXT CELL
//

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

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

// Get the text cursor of the cell text
textCursor = cellText.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
cellText.insertString(textCursor, "BeanShell macro example", true);


//
// Access and modify VALUE CELLS
//

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

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

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

// 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("=100/25");

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

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


//
// Fill CELL RANGE
//

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

data = UnoRuntime.queryInterface(XCellRangeData.class, cellRange);
Object[][] values =
  {
    {new Double(1.1), new Integer(10)},
    {new Double(2.2), new String("Text")}
  };
data.setDataArray(values);


// BeanShell OpenOffice.org scripts should always return 0
return 0;

The macro gets the active spreadsheet and fills several cells with data (text, values, formulas). This example only works, if your active document is a spreadsheet. If you start it with another type of document you'll get an exception. Attention, if you start this macro in a not empty spreadsheet.

I've got most of the code from the OOo Developer's Guide Spreadsheet chapter. It's not complicated to get the Java examples working with BeanShell, because Java and BeanShell are very similar.

Installation of the BeanShell macro:
- Start OOo.
- Call "Tools" > "Macros" > "Organize Macros" > "BeanShell...".
- Choose one of the already existing BeanShell libraries or create a new one by pressing "Create..." after selecting "My Macros" or "OpenOffice.org Macros" or a document and enter a name for the new library.
- Select a BeanShell library, press "Create..." and enter a name for the new macro.
- Select the new macro and press "Edit".
- Select all the BeanShell macro code in the new macro and delete it.
- Copy the BeanShell macro from this post into the BeanShell macro.
- Press "Save" and "Run".
If everything is ok, you see the result in your active spreadsheet.

[hol.sten]

19. Jan 2008: Creation of the BeanShell macro