Page 1 of 1

Communication with OpenOffice Calc

PostPosted: Tue 14. Jan 2020 15:19:44
by minder
Hello,

I have the working project on Promotic 8.3.26 version. Now the customer wants to put some data from Promotic to OpenOffice .ods file. I tired to use script from the help with smole modifications:
//////////////////////////////////////////////////////////////////////////
Dim objServiceManager, objCoreReflection, objDesktop, objDocument, objText
Dim oCell, args(), arg(), s, sPath, bDone
Dim testas, tDate, sDate, q, Eil_Nr, temp

Set testas = pMe.Pm("../Data/#vars/testas")
Set temp = pMe.Items("#vars/var1")
sPath = Pm.EvalMacro("$path.data:")
s = Replace(sPath, "\", "/")

'The service manager is always the starting point
'If there is no office running then an office is started up
Set objServiceManager = CreateObject("com.sun.star.ServiceManager")
testas.Value = 1

'Create the CoreReflection service that is later used to create structs
Set objCoreReflection = objServiceManager.createInstance("com.sun.star.reflection.CoreReflection")
testas.Value = 2

'Create the Desktop
Set objDesktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
testas.Value = 3

'Esamos ataskaitos pavadinimas
tDate = Pm.Time
sDate = Pm.FormatDate(tDate, "%Y%m%d")
q = "Ataskaita_" + CStr(sDate) + ".ods"

'Open a new empty calc document
'Set objDocument = objDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, args)
Set objDocument = objDesktop.loadComponentFromURL("file:///" & s & q, "_blank", 0, args)
testas.Value = 4

'Create a text object
Set objText = objDocument.getSheets.GetByIndex(0)
testas.Value = 5

Set oCell = objText.GetCellByPosition(0, 0)
Eil_Nr = oCell.Value
'oCell.CellBackColor = 6710932
Set oCell = objText.GetCellByPosition(1, Eil_Nr)
oCell.Value = temp.Value
objText.GetCellByPosition(2, Eil_Nr).String = "C1"
objText.GetCellByPosition(3, Eil_Nr).String = "CC"
Set oCell = objText.GetCellByPosition(0, 0)
oCell.Value = Eil_Nr + 1

sPath = Pm.EvalMacro("$path.data:")
s = Replace(sPath, "\", "/")
bDone = objDocument.storeAsURL("file:///" & s & q, arg)

objDocument.Close(false)

Set objDocument = Nothing
Set objDesktop = Nothing
Set objCoreReflection = Nothing
Set objServiceManager = Nothing
/////////////////////////////////////////////////////////////////////////////
Script works, but every time it opens the .ods file on desktop, above the scada. At the script end the file closes.
Is it possible to read/ write data from .ods with out opening the .ods file on desktop?

Re: Communication with OpenOffice Calc

PostPosted: Mon 10. Feb 2020 9:41:14
by admin
Hello,

please refer to the OO documentation:

https://wiki.openoffice.org/wiki/Opening_a_document

focus on the "bHidden" parameter.

(But please understand that this has not been properly tested with the Calc program)