[Solved] Save a ods spreadsheet with API - complicated?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

[Solved] Save a ods spreadsheet with API - complicated?

Post by Albireo »

I've written a program in Autohotkey, that opens a blank spreadsheet, then fills cells with content. (and it works)
Now I want to save this spreadsheet to a name that exists in some cells with the same program.
The path => C3 (c:\temp) the filename => C4 (Test.ods)
I tried with this code

Code: Select all

sPath := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C3").getString
sFileName := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C4").getString
It seams to work, but when I want to use

Code: Select all

sSaveToURL := ConvertToURL(sPath & sFileName)
I got an error
Error: Call to nonexistent function.
Specifically: ConvertToURL(sPath & sFileName)
Skipped the conversion and did the following steps .:

Code: Select all

sSaveToURL := "file:///c:/temp/Test.ods"	; Windows "c:\Temp\Test.ods"
oDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))
or

Code: Select all

oDoc.storeToURL("file:///c:/temp/Test.ods", Array())
Both tests give the same error
Error: 0x80020005 - Typmatchningsfel.
Specifically: storeToURL
I don't know if "FilterName" and "Calc8" are correct (I have no idea what else can be in this fields)

How to save the unnamed spreadsheet to the hard drive?
Last edited by Hagar Delest on Wed Sep 25, 2019 9:36 pm, edited 2 times in total.
Reason: tagged solved
OOo 4.1.X on Windows XP, Win7, 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Post by Zizi64 »

You need a / or \ character between the path string and the file name string. Or you need
The path => C3 (c:\temp\)
instead of the
The path => C3 (c:\temp)
string.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Post by Zizi64 »

Please upload your full macro code.

How you get the new, unnamed document (oDoc) by your code? By usage the Thiscomponent? Or the code will create a new empty document?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Post by Albireo »

My test script in AutohotKey language .:

Code: Select all

; #Warn   ; Enable warnings to assist with detecting common errors.
SetBatchLines -1
;#SingleInstance   force
;#NoEnv
SendMode Input ; Recommended for new scripts due to its superior speed and reliability. 
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.

Gosub OpenNewCalc	; Create a NEW spreadsheet


; Rename the TAB
oSheet := oSheets.getByIndex(0)
oSheet.Name := "Test"

FilePath = c:\temp
FileName = Test.ods

; Adjust the column width
oColumns := oSheet.getColumns()
oColumn := oColumns.getByName( "C" )	; Get a specific column.
oColumn.Width := 3000 ; 3 cm / 30mm - Change width of column.

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B3" )
oCell.setString( "Path .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C3" )
oCell.setString( FilePath )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B4" )
oCell.setString( "Name .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C4" )
oCell.setString( FileName )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"


; Ready with the spreadsheet

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
; Save the spreadsheet from the name in cell C3 - TestFile.ods (directory eg. C:\temp)

SheetName := oSheets.getByIndex(0).Name
sPath := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C3").getString
sFileName := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C4").getString
; sSaveToURL := ConvertToURL(sPath & sFileName)

sSaveToURL := "file:///c:/temp/Test.ods"	; Windows "c:\Temp\TestFile.ods"
; oDoc.storeToUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))
; oDoc.storeToURL("file:///c:/temp/Test.ods", Array())

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
MsgBox ,,, Ready!, 1
ExitApp



; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
OpenNewCalc:
	; Create the structure so that I know the values are all zero.
	; Init - CreateUnoService - initialize COM object in AHK?
	oSM := ComObjCreate("com.sun.star.ServiceManager")				; This line is mandatory with AHK for OOo API
	oDesk := oSM.createInstance("com.sun.star.frame.Desktop")	; Create the first and most important service
	
	Array := ComObjArray(VT_VARIANT:=12, 2)
	Array[1] := MakePropertyValue(oSM, "hidden", ComObject(0xB,true))
	sURL := "private:factory/scalc"
	oDoc := oDesk.loadComponentFromURL(sURL, "_blank", 0, Array)
	
	; https://www.openoffice.org/api/docs/common/ref/com/sun/star/table/BorderLine.html
	oBorder := oSM.Bridge_GetStruct("com.sun.star.table.BorderLine")

	; Name on the first tab
	oSheets := oDoc.getSheets()
	SheetName := oSheets.getByIndex(0).Name
	
	oFormats := oDoc.getNumberFormats()	; Set formatting for dates and currency
	oLocale := oSM.Bridge_GetStruct("com.sun.star.lang.Locale")	;  Same as createUnoStruct( "com.sun.star.lang.Locale" )

	; http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
	; https://docs.oracle.com/cd/E13214_01/wli/docs92/xref/xqisocodes.html
	; SWEDEN SE SWE 752
	; oLocale.Language := "SWE"
	; oLocale.Country := "SE"
	oLocale.Language := "sv"
	oLocale.Country := "SE"
Return

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Function MakePropertyValue(cName, uValue)
; Ver 9 sept 2019
MakePropertyValue(oSM, cName, uValue)
{	; oSM - Defined in Subroutine  OpenNewCalc/CreateCalc
	; Like this .: oSM := ComObjCreate("com.sun.star.ServiceManager")

	oPropertyValue := oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	
	If cName
		oPropertyValue.Name := cName
	
	If uValue
		oPropertyValue.Value := uValue
	
	Return oPropertyValue
}

; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ESC::
{	; Close the spreadsheet (don't save)
	oDoc.Close(True)
	oDoc := ""

	MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, ESC - Program ends!, 1
	ExitApp
}

OOo 4.1.X on Windows XP, Win7, 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Post by Zizi64 »

AutohotKey language
What is it???
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Save a ods spreadsheet with API - complicated?

Post by JeJe »

I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.

All I can suggest with storeToURL is to take the simplest case, your

oDoc.storeToURL("file:///c:/temp/Test.ods", Array())

but declare the Array properly as a PropertyValue array - without the complication of an external function.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Post by Albireo »

Zizi64 wrote:
AutohotKey language
What is it???
The ultimate automation scripting language for Windows Autohotkey
(A scripting language as approaching a programming language in functionality)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Post by Albireo »

Thanks!
JeJe wrote:I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.
Andrew Pitonyak use it in his dokumentation "OpenOffice.org Macros Explained"
13.4.1. Document properties from a closed document
It is easy to access the document properties from a document that is not open.
Listing 6. Read the document properties from a document that is not open.like this .:

Code: Select all

Sub loadExternalProperties
  Dim sPath$
  Dim sPathUrl
  Dim oDocProps
  sPath = ConvertToUrl("/andrew0/home/andy/MoveFigsFromFrames.odt")
  oDocProps = CreateUnoService("com.sun.star.document.DocumentProperties")
  oDocProps.loadFromMedium(sPath, Array())
  printDocProperties(oDocProps)
End Sub
In chapter 13:11 Saving a document he say .:
OOo Basic supports the functions ConvertToURL and ConvertFromURL to convert between the two notations. The XStorable interface defines object methods to save a document to a URL (see Table 8).
One way to solve ConvertToURL in AutohotKey is to use an instruction like this .:

Code: Select all

FileNameURL := "File:///" StrReplace(StrReplace(File, "\", "/",, Limit := -1), " ", "%20",, Limit := -1)
(I think you can do the same with RegExReplace)
- - - - - - - - -
JeJe wrote:All I can suggest with storeToURL is to take the simplest case, your
oDoc.storeToURL("file:///c:/temp/Test.ods", Array())...
Finally I managed to save an open spreadsheet with AHK (AutohotKey)

Code: Select all

FileNameOUT := "c:\temp\Test.ods"
sSaveToURL := "File:///" StrReplace(StrReplace(FileNameOut, "\", "/",, Limit := -1), " ", "%20",, Limit := -1)
oDoc.storeAsURL(sSaveToURL, Array)
But may have more questions now than before I started trying different suggestions.
like .: Whats the differ between storeAsURL(work) and storeToURL(doesn't work)
Why does this not work?

Code: Select all

oDoc.storeAsUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))
is the Array another array than? .:

Code: Select all

	Array := ComObjArray(VT_VARIANT:=12, 2)
	Array[1] := MakePropertyValue(oSM, "hidden", ComObject(0xB,true))
	sURL := "private:factory/scalc"
How to get information about "FilterName" and "Calc8"?
OOo 4.1.X on Windows XP, Win7, 10
JeJe
Volunteer
Posts: 2763
Joined: Wed Mar 09, 2016 2:40 pm

Re: Save a ods spreadsheet with API - complicated?

Post by JeJe »

Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Post by Zizi64 »

I don't know Autohotkey but I presume that ConvertToURL is being treated as a Autohotkey function not an OO one.
Andrew Pitonyak use it in his dokumentation "OpenOffice.org Macros Explained"
Yes, but those examples can run in the StarBasic IDE, and they are not called from a third party environment like the Autohotkey...
The ConvertToURL() API function works for me fine - called from a Basic function/sub launched in the StarBasic IDE.

("StarBasic" is the original name of the Basic language and IDE of the OpenOffice.org, Oracle OpenOffice, Apache OpenOffice, LibreOffice)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Albireo
Posts: 113
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Post by Albireo »

Thanks!
It wasn't as difficult as I thought when I got all the parts in place.

For information, I send with a solution that works (for me)
This program does the following (Language: Autohotkey):
  • Open a new spreadsheet.
  • Give the first tab a new name (Test)
  • Change the width on column C
  • The desired file name is written into two cells
  • The file name and path is write / read from these cells
  • The spreadsheet is saved with this file name
(with some error handling ;) )

Code: Select all

; #Warn   ; Enable warnings to assist with detecting common errors.
SetBatchLines -1
#SingleInstance   force
#NoEnv
SendMode Input ; Recommended for new scripts due to its superior speed and reliability. 
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.

Gosub CreateNewCalc	; Create a NEW spreadsheet


; Rename the TAB
oSheet := oSheets.getByIndex(0)
oSheet.Name := "Test"
; SheetName := oSheets.getByIndex(0).Name

FilePath = c:\temp
FileName = Test.ods

; Adjust the column width
Column = C
oColumns := oSheet.getColumns()
oColumn := oColumns.getByName( Column )	; Get a specific column.
oColumn.Width := 3000 ; 3 cm / 30mm - Change width of column.

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B3" )
oCell.setString( "Path .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C3" )
oCell.setString( FilePath )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"

; - - - - - - - - - - - - - - - - - - - - - - - - - 
oCell := oSheet.getCellRangeByName( "B4" )
oCell.setString( "Name .:" )
oCell.CharFontName := Arial
oCell.CharHeight := "10"

oCell := oSheet.getCellRangeByName( "C4" )
oCell.setString( FileName )
oCell.CharFontName := "arial-rounded-mt-bold"
oCell.HoriJustify := 2
oCell.CharHeight := "12"


; Ready with the spreadsheet

; ------------------------------------------------------------------------------------
; ------------------------------------------------------------------------------------
; Save the spreadsheet from the name in cell C3 - TestFile.ods (directory eg. C:\temp)


SheetName := oSheets.getByIndex(0).Name
sPath := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C3").getString
sFileName := oDoc.getSheets.getByName(SheetName).getCellRangeByName("C4").getString

If !sPath	; If path is missing - add ScriptDir path.
	sPath := A_ScriptDir
If !( SubStr(sPath, StrLen(sPath)-1, 1) = "\" )	; If "\" is missing after path - add "\"
	sPath := sPath "\"

If !sFileName	; If filename is missing
{	; Close the spreadsheet (don't save)
	oDoc.Close(True)
	oDoc := ""
	MsgBox 16, Row %A_LineNumber% -> %A_ScriptName%, Filename is missing! `n`nThis program ends!
	ExitApp
}
	
FileNameOut := sPath sFileName	; create the filename
If FileExist( FileNameOut ) ; If the resultfile exist - try to remove
{	FileDelete %FileNameOut%
	If ErrorLevel
	{	SplitPath FileNameOut, OutFileName, OutDir, OutExtension, OutNameNoExt, OutDrive
		MsgBox 64, Row %A_LineNumber% -> %A_ScriptName%,
		( LTrim Join
		%	"The file exist, and could not be removed! `n`n
			Filename .: " OutFileName "`n
			Path .: "OutDir "`n`n
			(maybe is open with another program?)
			This program ends! `n`n"
		)
		
		; Close the spreadsheet (don't save)
		oDoc.Close(True)
		oDoc := ""
		MsgBox ,,, Program ends!, 1
		ExitApp
	}
}

; sSaveToURL := FileURL( FileNameOut )
; MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, % "sPath .: " sPath "`nsFileName .: " sFileName "`n`n sSaveToURL .: " sSaveToURL
; oDoc.storeAsURL(sSaveToURL, Array)
oDoc.storeAsURL(FileURL(FileNameOut), Array)

MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%,
( LTrim
	- A new spreadsheet has been opened.
	- The first tab has been given a new name (%SheetName%)
	- Column %Column% has given a different width.
	- The desired file name has been entered into two cells
	- The file name is read from these cells
	- The spreadsheet has been saved with this file name
	
	(the spreadsheet closes after "OK")
)


; Close the created spreadsheet
oDoc.Close(True)
oDoc := ""

MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, Ready!, 1
ExitApp



; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
CreateNewCalc:
	; Create the structure so that I know the values are all zero.
	; Init - CreateUnoService - initialize COM object in AHK?
	oSM := ComObjCreate("com.sun.star.ServiceManager")				; This line is mandatory with AHK for OOo API
	oDesk := oSM.createInstance("com.sun.star.frame.Desktop")	; Create the first and most important service
	
	Array := ComObjArray(VT_VARIANT:=12, 2)
	Array[1] := MakePropertyValue(oSM, "hidden", ComObject(0xB,true))
	sURL := "private:factory/scalc"
	oDoc := oDesk.loadComponentFromURL(sURL, "_blank", 0, Array)
	
	; https://www.openoffice.org/api/docs/common/ref/com/sun/star/table/BorderLine.html
	oBorder := oSM.Bridge_GetStruct("com.sun.star.table.BorderLine")

	; Name on the first tab
	oSheets := oDoc.getSheets()
	SheetName := oSheets.getByIndex(0).Name
	
	oFormats := oDoc.getNumberFormats()	; Set formatting for dates and currency
	oLocale := oSM.Bridge_GetStruct("com.sun.star.lang.Locale")	;  Same as createUnoStruct( "com.sun.star.lang.Locale" )

	; http://www.chemie.fu-berlin.de/diverse/doc/ISO_3166.html
	; https://docs.oracle.com/cd/E13214_01/wli/docs92/xref/xqisocodes.html
	; SWEDEN SE SWE 752
	; oLocale.Language := "SWE"
	; oLocale.Country := "SE"
	oLocale.Language := "sv"
	oLocale.Country := "SE"
Return


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Version .: 9 sept 2019
MakePropertyValue(oSM, cName, uValue)
{	; From .: Function MakePropertyValue(cName, uValue)
	; oSM - Defined in Subroutine  OpenNewCalc/CreateCalc
	; Like this .: oSM := ComObjCreate("com.sun.star.ServiceManager")

	oPropertyValue := oSM.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
	
	If cName
		oPropertyValue.Name := cName
	
	If uValue
		oPropertyValue.Value := uValue
	
	Return oPropertyValue
}


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
; Version .: 9 dec 2018
FileURL( File )
; FileURL() Converts filepath to file URI - By SKAN on D1CA @ goo.gl/UqXL6n
; https://www.autohotkey.com/boards/viewtopic.php?f=6&t=59805
; 
{	Local v, INTERNET_MAX_URL_LENGTH := 2048   
	VarSetCapacity(v,4200,0)
	DllCall( "Shlwapi.dll" ( SubStr(File,1,5)="file:" ? "\PathCreateFromUrl" : "\UrlCreateFromPath" )
			, "Str",File, "Str",v, "UIntP",INTERNET_MAX_URL_LENGTH, "UInt",0 )
	Return v
}


; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
ESC::
{	; Close the spreadsheet (don't save)
	oDoc.Close(True)
	oDoc := ""

	MsgBox ,, Row %A_LineNumber% -> %A_ScriptName%, ESC - Program ends!, 1
	ExitApp
}
OOo 4.1.X on Windows XP, Win7, 10
Post Reply