[Solved] API: Save a Excel file

Creating a macro - Writing a Script - Using the API

[Solved] API: Save a Excel file

Postby Peter18 » Wed Feb 03, 2021 5:17 pm

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
Last edited by robleyd on Thu Feb 04, 2021 12:13 pm, edited 2 times in total.
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 102
Joined: Thu May 12, 2011 1:01 pm

Re: Api: Save a Exel file

Postby FJCC » Wed Feb 03, 2021 6:14 pm

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())
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8060
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Api: Save a Exel file

Postby Peter18 » Wed Feb 03, 2021 6:49 pm

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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 102
Joined: Thu May 12, 2011 1:01 pm

Re: Api: Save a Exel file

Postby FJCC » Wed Feb 03, 2021 7:19 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 8060
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved]Api: Save a Excel file

Postby Peter18 » Thu Feb 04, 2021 12:11 pm

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
OpenOffice 3.3; OpenOffice 4.1.1
Peter18
 
Posts: 102
Joined: Thu May 12, 2011 1:01 pm

Re: Api: Save a Exel file

Postby John_Ha » Thu Feb 04, 2021 1:59 pm

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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8343
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 5 guests