Promotic
WikipediaLinkedInYoutubeTwitterFacebook

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 tables are 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 *.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 PROMOTIC application by script via API inteface 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.
JavaScriptVBScriptSelect and copy to clipboard

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);
oDoc.Close(false);
Example2:
Opening 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.
JavaScriptVBScriptSelect and copy to clipboard

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);
oDoc.Close(false);

History:
Pm8.03.12: The documentation now contains new chapter describing usage of Calc database (OpenOffice, LibreOffice).
PROMOTIC 9.0.7 SCADA system documentation - MICROSYS, spol. s r.o.

Send page remarkContact responsible person
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice