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

[Hagar Delest]

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   Expand viewCollapse view

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   Expand viewCollapse view

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   Expand viewCollapse view

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

or

Code: Select all   Expand viewCollapse view

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?

[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.

[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?

[Albireo]

My test script in AutohotKey language .:

Code: Select all   Expand viewCollapse view

; #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
}



[Zizi64]

AutohotKey language

What is it???

[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.

[Albireo]

AutohotKey language

What is it???

The ultimate automation scripting language for Windows Autohotkey
(A scripting language as approaching a programming language in functionality)

[Albireo]

Thanks!

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   Expand viewCollapse view

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   Expand viewCollapse view

FileNameURL := "File:///" StrReplace(StrReplace(File, "\", "/",, Limit := -1), " ", "%20",, Limit := -1)

(I think you can do the same with RegExReplace)
- - - - - - - - -

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   Expand viewCollapse view

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   Expand viewCollapse view

oDoc.storeAsUrl(sSaveToURL, Array(MakePropertyValue(oSM, "FilterName", "Calc8")))

is the Array another array than? .:

Code: Select all   Expand viewCollapse view

   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"?

[JeJe]

The filterlist is here:

https://wiki.openoffice.org/wiki/Framew ... st_OOo_3_0

xstorable:

https://www.openoffice.org/api/docs/com ... rable.html

Edit:

Yes OOBasic's Array() is a variant array too:

https://wiki.openoffice.org/wiki/Docume ... ide/Arrays

[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)

[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   Expand viewCollapse view

; #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
}