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

Creating a macro - Writing a Script - Using the API

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

Postby Albireo » Mon Sep 16, 2019 4:14 pm

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?
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
Albireo
 
Posts: 51
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Postby Zizi64 » Mon Sep 16, 2019 4:21 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8476
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Postby Zizi64 » Mon Sep 16, 2019 4:28 pm

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8476
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Postby Albireo » Mon Sep 16, 2019 4:57 pm

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
}

OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 51
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Postby Zizi64 » Mon Sep 16, 2019 9:55 pm

AutohotKey language

What is it???
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8476
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Postby JeJe » Mon Sep 16, 2019 10:02 pm

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.
Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 612
Joined: Wed Mar 09, 2016 2:40 pm

Re: Save a ods spreadsheet with API - complicated?

Postby Albireo » Mon Sep 16, 2019 11:49 pm

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: 51
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Postby Albireo » Tue Sep 17, 2019 12:26 am

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   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)
- - - - - - - - -
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   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"?
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 51
Joined: Wed Apr 15, 2009 12:05 pm

Re: Save a ods spreadsheet with API - complicated?

Postby JeJe » Tue Sep 17, 2019 1:53 am

Openoffice 4.1.2
Windows 8
JeJe
Volunteer
 
Posts: 612
Joined: Wed Mar 09, 2016 2:40 pm

Re: Save a ods spreadsheet with API - complicated?

Postby Zizi64 » Tue Sep 17, 2019 7:04 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.3; AOO4.1.6
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: 8476
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Save a ods spreadsheet with API - complicated?

Postby Albireo » Wed Sep 25, 2019 9:08 pm

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
}
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 51
Joined: Wed Apr 15, 2009 12:05 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 6 guests