Calc

Calc represents a spreadsheet program. It is a part of the OpenOffice and LibreOffice package and can be used as Microsoft Excel alternative. It is used for table design, but can also be used to create complex sets of interconnected data with formulas and functions. The table is saved in new international standard for documents. The standard is based on XML and can be read even if the user does not have OpenOffice or LibreOffice installed. The default format of the Calc tables is the *.ods file.

Work with Calc database (Libre Office)

- The *.ods type files can not be connected via ADO Provider standard. Therefore usage of the PmaAdo or PmaDatabase objects is not possible.

- The table files can be accessed from the PROMOTIC application by script via API interface of Libre Office, Open Office.

Example1:

Creating of new empty file, setup of reference to first sheet and creation of description of the first row of the table. Writing values into the second row of the table and saving file to disk.

JavaScriptSelect and copy to clipboard



function onClose()

{

	oDoc.Close(false);

}


var oSM, oCR, oDesk, oDoc, oSh, oSheet, oCell, s, sPath, sFilePath, bDone;

var jArr = Pm.CreatePmArray().Create(1);

var args = jArr.SaveToVbArray();

var arg = jArr.SaveToVbArray();


sPath = Pm.DiscGetPath("data:");

s = Pm.StringReplace(sPath, "\\\\", "/");

sFilePath = s + "test2.ods";


// The service manager is always the starting point

// If there is no office running then an office is started up

oSM = Pm.AxGetObject("new", "com.sun.star.ServiceManager");


// Create the CoreReflection service that is later used to create structs

oCR = oSM.createInstance("com.sun.star.reflection.CoreReflection");


// Create the Desktop

oDesk = oSM.createInstance("com.sun.star.frame.Desktop");


// Open a new empty Calc document

oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, args);


// Create a sheet object

oSh = oDoc.getSheets();

oSheet = oSh.GetByIndex(0);


oCell = oSheet.getCellByPosition(0, 0);

oCell.String = "A col";

oCell.CellBackColor = 6710932;


oCell = oSheet.getCellByPosition(1, 0);

oCell.String = "B col";

oCell.CellBackColor = 6710932;


oCell = oSheet.getCellByPosition(2, 0);

oCell.String = "C col";

oCell.CellBackColor = 6710932;


oSheet.getCellByPosition(0, 1).Value = 123;

oSheet.getCellByPosition(1, 1).Value = 222;

oSheet.getCellByPosition(2, 1).Value = 333;


bDone = oDoc.storeAsURL("file:///" + sFilePath, arg);

pMe.Root.AddEventTimer(2000, 1, onClose);

Example2:

Open of existing file, setup of reference to first sheet of the table. Writing values into the third row of the table and saving file to disk.

JavaScriptSelect and copy to clipboard



function onClose()

{

	oDoc.Close(false);

}


var oSM, oCR, oDesk, oDoc, oSh, oSheet, oCell, s, sPath, sFilePath, bDone;

var jArr = Pm.CreatePmArray().Create(1);

var args = jArr.SaveToVbArray();

var arg = jArr.SaveToVbArray();


sPath = Pm.EvalMacro("$path.data:");

s = Pm.StringReplace(sPath, "\\\\", "/");

sFilePath = s + "test2.ods";


// The service manager is always the starting point

// If there is no office running then an office is started up

oSM = Pm.AxGetObject("new", "com.sun.star.ServiceManager");


// Create the CoreReflection service that is later used to create structs

oCR = oSM.createInstance("com.sun.star.reflection.CoreReflection");


// Create the Desktop

oDesk = oSM.createInstance("com.sun.star.frame.Desktop");


// Open Calc document

oDoc = oDesk.loadComponentFromURL("file:///" + sFilePath, "_blank", 0, args);


// Create a sheet object

oSh = oDoc.getSheets();

oSheet = oSh.GetByIndex(0);


oSheet.getCellByPosition(0, 2).Value = 375;

oSheet.getCellByPosition(1, 2).Value = Pm.Round(Pm.Random(0, 100), 0.1);

oSheet.getCellByPosition(2, 2).Value = Pm.Round(Pm.Random(0, 100), 0.1);


bDone = oDoc.storeAsURL("file:///" + sFilePath, arg);

pMe.Root.AddEventTimer(2000, 1, onClose);