Scripting of OO ? (migrating from Excel + Perl)

The Application Programming Interface and the OASIS Open Document Format

Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 7:43 am

Hi -
I am supporting data handling for my organization -
we store all our data in Excel spreadsheets.

We have about 100 spreadsheets for diff product lines.
Typical spreadsheet is has 15-20 columns and 2000-3000 rows.

We develop several Perl scripts that do all the data handling tasks:
1. extraction of necessary data from Excel to use in our software tool.
2. verification of data consistency.
3. bulk data modification - typical task is eg. changing product availability - which may involve anywhere from 100 to 2500 changes across several spreadsheets (based on input from text file - but the change is usually not simple cut and paste but requires running some logic so it becomes rather tedious and error prone to do such changes manually).

Tools that I wrote use Perl's Win32:OLE package.
I wrote library of typical tasks including among others:
a. read Excel file content into array
b. read comments and formatting into array
c. write array of content out as Excel file
d. add comments and formatting to existing Excel file

So my question is what are the OO APIs I can use to accomplish tasks a through d above?
They do not have to be Perl ones - I can call Java or Python from my Perl library.

I would appreciate a friendly pointer in the right direction.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby RoryOF » Tue Jan 07, 2014 10:15 am

This is a sideways reply to your query

A Perl/OpenDocument project is at
http://search.cpan.org/dist/OpenOffice-OODoc/OODoc/Intro.pod
This may be of some use to you.

I have no knowledge of Perl so cannot help with the detailed information you request.
Apache OpenOffice 4.1.7 on Xubuntu 18.04.4 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 30361
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 2:25 pm

Well I looked at several different ideas:
Win32:OLE
https://forum.openoffice.org/en/forum/v ... erl#p51485
where one of the post suggested:
#Win32::OLE->GetActiveObject
my $objServiceManager =
Win32::OLE->GetActiveObject("com.sun.star.ServiceManager") || Win32::OLE->new("com.sun.star.ServiceManager") || die "CreateObject: $!"; # Get the currently running process or create a new one
my $Stardesktop = $objServiceManager->createInstance("com.sun.star.frame.Desktop");
my $calc = $Stardesktop->loadComponentfromUrl("$input_file", "MyCalc", 0);

well so far so good but I could not find the way to do the same trick as with manipulating Excel via OLE:
my $file_type = -4158; #Constant equivalent for tab delimited file
$calc->SaveAs({ FileName => $fullname_output_file, FileFormat => $file_type })
|| print STDERR "didnt save the file $fullname_output_file: $!\n";

but maybe I should define range of cells and grab the content directly instead of saving it as tab delimited file.

Do you know what is API for accessing comments - in Excel I could grab them as Cell property "Comment":
$comment = $wsheet->Cells($row_num,$col_num)->{Comment};

Thanks!
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Tue Jan 07, 2014 5:07 pm

Nobody knows all that shit by heart. As a Perl coder you should be aware that reading numbers, dates and times from csv files takes a lot more than the column delimiter.
There is no easy way to master this API which is a true monster while being completely different from Excel. First of all, you should master the spreasdheet application itself (e.g. text import options). Otherwise you don't understand what you are hacking upon.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27706
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 7:32 pm

I understand that much - the way I wrote it for Excel is I had documentation of VB APIs for Excel and few Perl examples Win32::OLE.
I do have APIs for Calc (and I found how to extract comment info):
https://wiki.openoffice.org/wiki/Calc/API
but I cannot seem to find anything resembling:
my $file_type = -4158; #Constant equivalent for tab delimited file
$calc->SaveAs({ FileName => $fullname_output_file, FileFormat => $file_type })
there is this example:
Dim Doc As Object
Dim FileProperties(0) As New com.sun.star.beans.PropertyValue
Dim Url As String
' ... Initialize Doc

Url = "file:///c:/test3.odt"
FileProperties(0).Name = "Overwrite"
FileProperties(0).Value = True
Doc.storeAsURL(Url, FileProperties())

which I presume in Perl with Win32:OLE would look like:
my $Url = 'file:///c:/test3.odt'
my @FileProperties = ();
$FileProperties[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue");
$FileProperties[0]->{Name} = 'Overwrite';
$FileProperties[0]->{Value} = 1;
$Doc->storeAsURL($Url, \@FileProperties)
where:
$Doc is Calc object created via eg. $Doc = $Stardesktop->loadComponentfromUrl("$input_file_string", "_blank", 0, \@propValue );
but I cannot seem to find description of properties that would create tab delimited file.
There is this part (in https://wiki.openoffice.org/wiki/Docume ... tarDesktop)
Dim Doc As Object
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
Dim Url As String

Url = "file:///C:/doc.csv"
FileProperties(0).Name = "FilterName"
FileProperties(0).Value ="Text - txt - csv (StarCalc)"
FileProperties(1).Name = "FilterOptions"
FileProperties(1).value = "44,34,0,1"

Doc = StarDesktop.loadComponentFromURL(Url, "_blank", 0, FileProperties())

so I presume I would need something similar to store as tab-delimited...

Am I on a right track?
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 7:42 pm

OK so found FilterOptions description here:
https://wiki.openoffice.org/wiki/Docume ... er_Options
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Tue Jan 07, 2014 9:29 pm

https://forum.openoffice.org/en/forum/v ... ns#p285734
This Basic macro gets the FilterOptions for you after you successfully imported your csv file.
Of course you can do the same in any other language and print the FilterOptions to your terminal or debug console.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27706
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 9:38 pm

OK so this seems to work to save file as tab delimited:

Code: Select all   Expand viewCollapse view
use Win32::OLE;
Win32::OLE->Option(Warn => 3); # Turn on warnings for easier debugging

#Win32::OLE->GetActiveObject
my $objServiceManager =
Win32::OLE->GetActiveObject("com.sun.star.ServiceManager")
||
Win32::OLE->new("com.sun.star.ServiceManager")
||
die "CreateObject: $!"; # Get the currently running process or create a new one

my $Stardesktop = $objServiceManager->createInstance("com.sun.star.frame.Desktop");

#not sure why I need this....
my @propValue = ();
$propValue[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue");
$propValue[0]->{Name} = 'Hidden'; # This does not work!
$propValue[0]->{Value} = 1;

my $input_file_URL = 'file:///C:/Perl/myStuff/OO_stuff/test.ods';
my $calc = $Stardesktop->loadComponentfromUrl("$input_file_URL", "MyCalc", 0, \@propValue );

my @FileProperties = ();
$FileProperties[0] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue");
$FileProperties[0]->{Name} = 'FilterName';
$FileProperties[0]->{Value} = 'Text - txt - csv (StarCalc)';
$FileProperties[1] = $objServiceManager->Bridge_GetStruct("com.sun.star.beans.PropertyValue");
$FileProperties[1]->{Name} = 'FilterOptions';
$FileProperties[1]->{Value} = '9,34,0,1'; #9 for TAB delimiter
my $output_file_URL = 'file:///C:/Perl/myStuff/OO_stuff/test.txt';
$calc->storeAsURL($output_file_URL, \@FileProperties);

$Stardesktop->terminate();


Am I on a right track?
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Tue Jan 07, 2014 10:05 pm

Villeroy wrote:https://forum.openoffice.org/en/forum/viewtopic.php?f=20&t=64341&p=285734&hilit=showfilteroptions#p285734
This Basic macro gets the FilterOptions for you after you successfully imported your csv file.
Of course you can do the same in any other language and print the FilterOptions to your terminal or debug console.


Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)
I cannot really convert XLS to tab-delimited - modify it and convert to back XLS - I need to preserve file formatting and comments.
So typically I either modify file in place and save it or - if I need to eg. merge two XLS files I convert them both to a text format
that I created (sortta terse quasi-XML) merge them and then write out XLS based on that quasi-XML. I probably should have use XML
here but there were some advantages of very compact format.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby rudolfo » Wed Jan 08, 2014 12:28 am

I guess the main point of the macro for the import filter options was to show you a method how you can get hold of the settings that you have used in the text or csv import dialog. So the preferred approach would be to use a sample csv/text file, tweak the settings (separator, delimiter,..) until the imported data in Calc suites your needs and then run the macro to retrieve the backend API names, labels and values for this settings. Because the exchange between Calc and csv is symetrical, the required PropertyValues are the same for Import and Export.

Another method to get hold of these rather internal values is to use the macro recorder while you save your spreadsheet as csv file. You will get something like:
Code: Select all   Expand viewCollapse view
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file:///C:/Users/rolf/pages/ipv6-Tutorial.xml"
args1(1).Name = "FilterName"
' "Text CSV" is displayed in the SaveAs Dialog, but the Macro Recorder uses:
args1(1).Value = "Text - txt - csv (StarCalc)"
args1(2).Name = "FilterOptions"
' The first column are Ascii Values of the separator and the Text Delimiter (9 is TAB and 34 the qoute)
args1(2).Value = "9,34,ANSI,1"

dispatcher.executeDispatch(document, ".uno:SaveAs", "", 0, args1())

When you use .storeAsURL() or .storeToURL() you pass the filename (in URI format) directly as parameter. So you don't need to wrap it into the PropertyValues Array/Sequence. And compared to the dispatcher call the Properties in the array are all shifted to the front by one: "FilterName" will be Property 0, FilterOptions Property 1, ...
And surely you will use a more meaningful name as args1 in your own macro code.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Wed Jan 08, 2014 12:41 am

Woland99 wrote:Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)

Sorry, I missed that point. I do not understand what you are trying to do and why a Perl coder tries to solve any kind of problem with the help of a spreadsheet program. Spreadsheets are for non-coders.
If you need some type of "calculating GUI" for csv data, OpenOffice comes with a limited but still very useful database component called Base.
If your csv data come from some kind of relational database (virtually all csv comes from databases), you may even skip the whole csv export and import thing, connect a Base document directly to that database and use the database data almost seamlessly in Writer and Calc.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27706
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Wed Jan 08, 2014 1:21 am

Villeroy wrote:
Woland99 wrote:Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)

Sorry, I missed that point. I do not understand what you are trying to do and why a Perl coder tries to solve any kind of problem with the help of a spreadsheet program. Spreadsheets are for non-coders.
If you need some type of "calculating GUI" for csv data, OpenOffice comes with a limited but still very useful database component called Base.
If your csv data come from some kind of relational database (virtually all csv comes from databases), you may even skip the whole csv export and import thing, connect a Base document directly to that database and use the database data almost seamlessly in Writer and Calc.


Well yes - spreadsheets are legacy things - I agree with you that database would have been better way to go.
But for better or worse - data sit now in 100 Excel files - about 3 million cells. People sometimes modify
spreadsheets manually - sometimes use scripts. I want to start playing with Base next - but I guess for
immediate need (we may migrate to OO within 2-3 months) I need to make sure we can run old tools.

And saving Excel as tab is just one of several shortcuts I took early - I had problems with OLE automation
at that point - so dumping file as text and going from there was just the simplest move.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Villeroy » Wed Jan 08, 2014 1:28 am

I would stick with what you have (Perl+Excel) or leave the spreadsheet path all together. Switching from one inappropriate tool (Excel) to another inappropriate tool (Calc) is not worth the effort.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27706
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Wed Jan 08, 2014 2:31 am

rudolfo wrote:I guess the main point of the macro for the import filter options was to show you a method how you can get hold of the settings that you have used in the text or csv import dialog. So the preferred approach would be to use a sample csv/text file, tweak the settings (separator, delimiter,..) until the imported data in Calc suites your needs and then run the macro to retrieve the backend API names, labels and values for this settings. Because the exchange between Calc and csv is symetrical, the required PropertyValues are the same for Import and Export.
(...)
Another method to get hold of these rather internal values is to use the macro recorder while you save your spreadsheet as csv file. You will get something like:
When you use .storeAsURL() or .storeToURL() you pass the filename (in URI format) directly as parameter. So you don't need to wrap it into the PropertyValues Array/Sequence. And compared to the dispatcher call the Properties in the array are all shifted to the front by one: "FilterName" will be Property 0, FilterOptions Property 1, ...
And surely you will use a more meaningful name as args1 in your own macro code.


Thanks - I had to read your email 5 times before parsing it but I get it now (my place flooded yesterday so had a bit of sleepless night).
I actually did the same - tried to record macro but somehow missed the "Organize Macro" option to edit it.
Got the shift part - file name is passed directly.

Quick question about methods calls like:
createUnoService("com.sun.star.frame.DispatchHelper")
What is the object here?

Or - similar case - convertFromURL ?
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby Woland99 » Wed Jan 08, 2014 2:34 am

Villeroy wrote:I would stick with what you have (Perl+Excel) or leave the spreadsheet path all together. Switching from one inappropriate tool (Excel) to another inappropriate tool (Calc) is not worth the effort.


Right - but we will be losing MS Office license at some point soon. So I need to patch something together to reuse all the accumulate Perl.
JT
OpenOffice 4.0.1 on Win 7
Woland99
 
Posts: 15
Joined: Tue Jan 07, 2014 7:01 am

Re: Scripting of OO ? (migrating from Excel + Perl)

Postby rudolfo » Thu Jan 09, 2014 3:20 am

Woland99 wrote:Quick question about methods calls like:
createUnoService("com.sun.star.frame.DispatchHelper")
What is the object here?

Or - similar case - convertFromURL ?


There is an introduction about OLE automation of OpenOffice:
www.openoffice.org/udk wrote:Once instantiated, the service manager allows access to different office components, for example
Set objDesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")
This is essentially the same as calling
objDesktop= createunoservice("com.sun.star.frame.Desktop")
as is done within StarBasic.


In short createUnoService and also convertFromURL and convertToURL are some handy functions of StarBasic to save you some typing. The other scripting languages for OpenOffice (Java, Python,...) don't have these functions. They way how you instantiate UNO objects/services in Python is very similar to the above cited approach for OLE automation.
The Dispatcher Service "com.sun.star.frame.DispatchHelper" won't make you happy. The dispatcher is used in the macro recorder and mimics key strokes or clicks on menu items. It is always better to talk directly to the UNO objects either through the COM automation bridge or through the pyUNO bridge.

And the convert...URL functions are just string manipulation function. Mainly used to fill the gap due to lack of a good regular expression implementation in Basic. In a powerful scripting language like Perl or Python you simply use the nice functionality for regular expressions and string manipulation to achieve the same thing. Spaces in filenames will become %20 in the URI scheme, so it is not only adding a protocol specifier and converting backslashes to forward slashes, but it is not really difficult. Find 3 or 4 test files run them through convertToURL in a Basic Test macro. This helps you to see, what needs to be converted.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1488
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 1 guest