Promotic
WikipediaLinkedInYoutubeTwitterFacebook

Calc

Calc represents a spreadsheet program. It is included in 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 PmAdo or PmDatabase objects is not possible.
- The table files can be accessed from PROMOTIC application by script via API inteface of Libre Office, Open Office.
 
Example1:
Creation 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 local disk.
Dim oSM, oCR, oDesk, oDoc, oSheet
Dim oCell, args(), arg(), s, sPath, sFilePath, bDone
sPath = Pm.EvalMacro("$path.data:")
s = Replace(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
Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the CoreReflection service that is later used to create structs
Set oCR = oSM.createInstance("com.sun.star.reflection.CoreReflection")
'Create the Desktop
Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
'Open a new empty calc document
Set oDoc = oDesk.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)
'Create a sheet object
Set oSheet = oDoc.getSheets.GetByIndex(0)
Set oCell = oSheet.GetCellByPosition(0, 0)
oCell.String = "A col"
oCell.CellBackColor = 6710932
Set oCell = oSheet.GetCellByPosition(1, 0)
oCell.String = "B col"
oCell.CellBackColor = 6710932
Set 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)
Set oDoc = Nothing
Set oDesk = Nothing
Set oCR = Nothing
Set oSM = Nothing
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 local disk.
Dim oSM, oCR, oDesk, oDoc, oSheet
Dim oCell, args(), arg(), s, sPath, sFilePath, bDone
sPath = Pm.EvalMacro("$path.data:")
s = Replace(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
Set oSM = CreateObject("com.sun.star.ServiceManager")
'Create the CoreReflection service that is later used to create structs
Set oCR = oSM.createInstance("com.sun.star.reflection.CoreReflection")
'Create the Desktop
Set oDesk = oSM.createInstance("com.sun.star.frame.Desktop")
'Open a new empty calc document
Set oDoc = oDesk.loadComponentFromURL("file:///" & sFilePath, "_blank", 0, args)
'Create a sheet object
Set oSheet = oDoc.getSheets.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)
Set oDoc = Nothing
Set oDesk = Nothing
Set oCR = Nothing
Set oSM = Nothing
© MICROSYS, spol. s r. o.Tavičská 845/21 703 00 Ostrava-Vítkovice