Page 1 of 1

[Solved] API: Save a Excel file

PostPosted: Wed Feb 03, 2021 5:17 pm
by Peter18
A friendly hello to anybody,

I wrote a programm in pascal to read data from a spreadsheet with Calc. So I can read ".ods" and ".xls" files. Now I want to save changes in ".xls" files. But I think although it has the ".xls" extension it is stored as ".ods".

I open the spreadsheet:

Code: Select all   Expand viewCollapse view
function T_OO.OpenCalc: Boolean;       
var
  Path : String;                     
  Par  : OLEVariant;                 
begin
  Result := false;
  if FileExists( oFilNam ) then
  begin
    Progress( 'SetCap', 0,0, 'Öffne Datei "' + oFilNam + '"' );
    try
      oAge         := FileAge( oFilNam )                ;
      Par          := VarArrayCreate([0, 0], varVariant);   // [lowest index, highest index]
      oOpenOffice  := CreateOleObject('com.sun.star.ServiceManager')                    ;
      Par[0]       := oOpenOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue')  ;
      Par[0].Name  := 'Hidden'                                                          ;
      Par[0].Value := True                                                              ;
      oStarDesktop := oOpenOffice.createInstance('com.sun.star.frame.Desktop')          ;
      oFCP         := oOpenOffice.createInstance('com.sun.star.ucb.FileContentProvider');
      Path         := oFCP.getFileURLFromSystemPath('', oFilNam)                        ;
      oFil         := oStarDesktop.loadComponentFromURL( Path, '_blank', 0, Par )       ;
      oTab         := oFil.Sheets.getByName( oShNam )                                   ;
      Result       := true                                                              ;
    except


And I save it:

Code: Select all   Expand viewCollapse view
procedure T_OO.SaveCalc;
var
  Par  : OLEVariant;   S : String;

begin
  Par          := VarArrayCreate([0, 0], varVariant)                              ;
  Par[0]       := oOpenOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  Par[0].Name  := 'Hidden'                                                        ;
  Par[0].Value := True                                                            ;
  try
    oFil.storeAsURL( oFil.Url, Par );
  except                                       


I think I need an other parameter to save it in Xls format.

Greetings from the north sea with wite borders

Peter

Re: Api: Save a Exel file

PostPosted: Wed Feb 03, 2021 6:14 pm
by FJCC
The Basic code would look like this. ThisComponent is a Basic variable that refers to the document that called the macro.
Code: Select all   Expand viewCollapse view
Dim Propval(0) as New com.sun.star.beans.PropertyValue
XLSName = convertToURL("C:\Users\fjcc\Desktop\Excel.xls")
Propval(0).Name = "FilterName"
Propval(0).Value = "MS Excel 97"
ThisComponent.storeToURL(XLSName, Propval())

Re: Api: Save a Exel file

PostPosted: Wed Feb 03, 2021 6:49 pm
by Peter18
Hello FJCC,

thank you for your answer! I'll try it tomorrow, but I think I know the way.

I tried to find the answer within the api reference, no way. Is there someware a introduction how to use it? Or can you explain?

Greetings from the rainy north sea

Peter

Re: Api: Save a Exel file

PostPosted: Wed Feb 03, 2021 7:19 pm
by FJCC
Using the API reference is difficult. I always rely on the MRI extension to see the properties and methods of an object. There is a tutorial here.. I still occasionally go to the API reference but I then have a good idea of what I am looking for.
MRI would not have helped you find the right filter name for exporting to Excel, however. Searching the Macro forum here is probably the best way to find that, though there are many posts that mention Excel, so choosing the right search terms can make a big difference. Searching for the two terms Excel save, I got 158 hits and found a post with the right answer on the first page.

Re: [Solved]Api: Save a Excel file

PostPosted: Thu Feb 04, 2021 12:11 pm
by Peter18
Hello FJCC,

thank you again!

Code: Select all   Expand viewCollapse view
  Par          := VarArrayCreate([0, 1], varVariant)                              ;
  Par[0]       := oOpenOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  Par[0].Name  := 'Hidden'                                                        ;
  Par[0].Value := True                                                            ;
  Par[1]       := oOpenOffice.Bridge_GetStruct('com.sun.star.beans.PropertyValue');
  Par[1].Name  := 'FilterName'                                                    ;
  Par[1].Value := 'MS Excel 97'                                                   ;


works! I get a for Excel readable file.

But API looks like a long way to Amarillo. Try a macro and then pascal. Thank you!

Greetings from the north sea with wite borders again

Peter

Re: Api: Save a Exel file

PostPosted: Thu Feb 04, 2021 1:59 pm
by John_Ha
Peter18 wrote:I tried to find the answer within the api reference, no way. Is there someware a introduction how to use it? Or can you explain?

It is always worth searching Useful Macro Information For OpenOffice.org by Andrew Pitonyak. p56 says

To export a document to a different type, an export filter must be defined and any required properties must be set. You must know the name of the export filter and the file extension. Use Listing 5.45 to generate a list of filter names.

A separate method is required for the graphics filters and the rest. To export using a nongraphics format, use a form similar to the following code snippet.


Code: Select all   Expand viewCollapse view
Listing 5.43: Export a document.

Dim args2(1) As New com.sun.star.beans.PropertyValue
args2(0).Name = "InteractionHandler"
args2(0).Value = ""
args2(1).Name = "FilterName"
args2(1).Value = "MS Excel 97" REM Change the export filter
REM Use the correct file extension
oDoc.storeToURL("file:///c:/new_file.xls",args2())

Notice that I used the correct file extension and I specified the specific import filter. Graphics documents are a little different. First, you instantiate a GraphicExportFilter and then you tell it to export one page at a time.