[Solved] API: Save a Excel file

[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

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

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

[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

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

[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

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

[Peter18]

Hello FJCC,

thank you again!

Code: Select all

  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

[John_Ha]

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

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.