LibreOffice Calc Sample

libo_calc.bat

@echo off

set JJS=C:\path\to\java\bin\jrunscript.exe
set LIBO_DIR=C:\Program Files (x86)\LibreOffice 4

set CLASSPATH=%LIBO_DIR%\program\classes\unoil.jar
set CLASSPATH=%CLASSPATH%;%LIBO_DIR%\URE\java\juh.jar
set CLASSPATH=%CLASSPATH%;%LIBO_DIR%\URE\java\jurt.jar
set CLASSPATH=%CLASSPATH%;%LIBO_DIR%\URE\java\ridl.jar
rem echo %CLASSPATH%

set SCRIPT=%1

%JJS% -encoding utf-8 -classpath "%CLASSPATH%" -f %SCRIPT%

libo_calc.js

importClass(Packages.com.sun.star.uno.UnoRuntime);
importClass(Packages.com.sun.star.frame.XComponentLoader);
importClass(Packages.com.sun.star.frame.XStorable);
importClass(Packages.com.sun.star.sheet.XSpreadsheet);
importClass(Packages.com.sun.star.sheet.XSpreadsheetDocument);
importClass(Packages.com.sun.star.util.XCloseable);
importClass(Packages.com.sun.star.connection.NoConnectException);
importClass(Packages.com.sun.star.container.XIndexAccess);
importClass(Packages.com.sun.star.comp.helper.Bootstrap);
importClass(Packages.com.sun.star.comp.helper.BootstrapException);
importClass(Packages.com.sun.star.beans.PropertyValue);
importClass(Packages.com.sun.star.comp.servicemanager.ServiceManager);


////////////////////////////////


function puts(){
    for(var i=0,len=arguments.length; i<len; i++){
        print(arguments[i]);
        print("\n");
    }
}

function putsp(k, v){
    puts("" + k + " (" + v + ")");
}

function dump(obj){
    for(var k in obj){
        putsp(k, obj[k]);
    }
}

function sleep(sec){
    java.lang.Thread.sleep(sec * 1000);
}


////////////////////////////////


var Calc = (function(){

    function Calc(){}

    Calc.open = function(path, fn){
        var /* XMultiComponentFactory */ mcf,
        /* XComponent */ component,
        /* XComponentContext */ context,
        /* XSpreadsheetDocument */ doc,
        /* XComponentLoader */ componentLoader,
        /* com.sun.star.frame.Desktop */ desktop,
        /* com.sun.star.beans.PropertyValue[] */ args,
        fileUrl = "file:///" + path;

        function arg(name, value){
            var _arg = new PropertyValue();
            _arg.Name = name;
            _arg.Value = value;
            return _arg;
        }

        context = Bootstrap.bootstrap();

        mcf = context.getServiceManager();

        desktop = mcf.createInstanceWithContext(
            "com.sun.star.frame.Desktop", context);

        componentLoader = UnoRuntime.queryInterface(XComponentLoader, desktop);

        // GUI表示なし
        args = [arg("Hidden", true)];

        component = componentLoader.loadComponentFromURL(
            fileUrl, "_blank", 0, args);

        try{
            doc = new CalcDocument(component);
            fn(doc);
        }finally{
            doc.close();
        }
    };

    return Calc;
})();


////////////////////////////////


/**
 * Excel の Book に相当。
 * 
 * _component: com.sun.star.lang.XComponent
 * _doc: com.sun.star.sheet.XSpreadsheetDocument
 */
var CalcDocument = (function(){
        
    /**
     * @param {com.sun.star.lang.XComponent} component
     */
    function CalcDocument(component){
        this._component = component;

        this._doc = UnoRuntime.queryInterface(
            XSpreadsheetDocument, this._component);
    }

    CalcDocument.prototype.save = function(){
        var /* XStorable */ storable = UnoRuntime.queryInterface(
            XStorable, this._component);
        storable.store();
    };

    CalcDocument.prototype.close = function(){
        var /* XCloseable */ closable = UnoRuntime.queryInterface(
            XCloseable, this._component);
        closable.close(true);
    };

    CalcDocument.prototype.getSheets = function(){
        var sheets = [],
        sheetNames = this._doc.getSheets().getElementNames(),
        i, sheetName;

        for(i=0,len=sheetNames.length; i<len; i++){
            sheetName = sheetNames[i];
            var /* XSpreadsheet */ sheet = this.getSheetByIndex(i);
            sheets.push(new Sheet(sheet, sheetName));
        }

        return sheets;
    };

    CalcDocument.prototype.each = function(fn){
        var sheetNames = this._doc.getSheets().getElementNames(),
        i;

        for(i=0,len=sheetNames.length; i<len; i++ ){
            fn(this.getSheetByIndex(i), i);
        }
    };

    /**
     * @return {XSpreadsheet}
     */
    CalcDocument.prototype.getSheetByIndex = function(i){
        var /* XSpreadsheets */ sheets,
        /* XIndexAccess */ indexAccess;

        sheets = this._doc.getSheets();

        indexAccess = UnoRuntime.queryInterface(XIndexAccess, sheets);

        return UnoRuntime.queryInterface(
            XSpreadsheet, indexAccess.getByIndex(i));
    };

    /**
     * @return {Sheet}
     */
    CalcDocument.prototype.getSheetByName = function(name){
        var sheets = this._doc.getSheets(),
        /* XSpreadsheet */ sheet,
        sheetNames = sheets.getElementNames(),
        sheetName,
        i, targetIndex;

        for(i=0,len=sheetNames.length; i<len; i++ ){
            sheetName = sheetNames[i];
            if(sheetName == name){
                targetIndex = i;
                break;
            }
        }

        sheet = this.getSheetByIndex(targetIndex);
        return new Sheet(sheet, sheetName);
    };

    return CalcDocument;
})();


////////////////////////////////


/**
 * _sheet: com.sun.star.sheet.XSpreadsheet
 */
var Sheet = (function(){

    /**
     * @param {com.sun.star.sheet.XSpreadsheet} sheet
     * @param {string} name
     */
    function Sheet(sheet, name){
        this._sheet = sheet;
        this.name = name;
    }

    /**
     * @return {string} JavaScript の Stringプリミティブ値
     *     （nullは返さない）
     */
    Sheet.prototype.get = function(col, row){
        var /* XCell */ cell;

        cell = this._sheet.getCellByPosition(col, row);
        return "" + cell.getFormula();
    };

    Sheet.prototype.set = function(col, row, val){
        var /* XCell */ cell;

        cell = this._sheet.getCellByPosition(col, row);
        cell.setFormula(val);
    };

    Sheet.prototype.getInt = function(col, row){
        var cell = this._sheet.getCellByPosition(col, row);
        return parseInt(cell.getFormula(), 10);
    };

    Sheet.prototype.getFloat = function(col, row){
        var cell = this._sheet.getCellByPosition(col, row);
        return parseFloat(cell.getFormula());
    };

    return Sheet;
})();

libo_calc.sh

#!/bin/bash

JJS=/usr/bin/jrunscript
LIBO_DIR=/usr/lib/libreoffice
URE_DIR=/usr/lib/ure

CLASSPATH=$LIBO_DIR/program/classes/unoil.jar
CLASSPATH=$CLASSPATH:$URE_DIR/share/java/juh.jar
CLASSPATH=$CLASSPATH:$URE_DIR/share/java/jurt.jar
CLASSPATH=$CLASSPATH:$URE_DIR/share/java/ridl.jar
CLASSPATH=$CLASSPATH:$URE_DIR/lib/libjpipe.so
# echo $CLASSPATH

SCRIPT=$1

$JJS -encoding utf-8 -classpath "$CLASSPATH" -f $SCRIPT

sample.js

load("libo_calc.js");

Calc.open(
    "C:/Users/user/foo/bar/sample.ods",
    function(doc){
    
    // シートの一覧を取得
    var sheets = doc.getSheets();
    // シートをイテレート
    sheets.forEach(function(sheet, i){
        puts("----------------");
        puts(i + ": " + sheet.name);
        // col, row / 文字列でセルの内容を取得
        puts("(0, 0) => " + sheet.get(0, 0));
        // セルに文字列をセット
        sheet.set(0, 0, "(0, 0) 日本語テキスト " + new Date());
        puts("(0, 0) => " + sheet.get(0, 0));
    });

    puts("----------------");
    // 名前でシートを取得
    var sheet = doc.getSheetByName("Sheet1");
    puts("(1, 1) => " + sheet.get(1, 1));
    sheet.set(1, 1, "(1, 1) " + new Date());
    puts("(1, 1) => " + sheet.get(1, 1));

    puts("----------------");
    sheet.set(1, 2, "12.34");
    // 整数として取得
    puts("getInt   => " + sheet.getInt(1, 2));
    // 浮動小数として取得
    puts("getFloat => " + sheet.getFloat(1, 2));

    puts("----------------");
    var count = sheet.getInt(1, 3);
    if(isNaN(count)){
        count = 0;
    }
    sheet.set(1, 3, count + 1);
    puts("count => " + sheet.get(1, 3));

    // 上書き保存
    doc.save();
});