Scripting of OO ? (migrating from Excel + Perl)

[Woland99]

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.

[RoryOF]

This is a sideways reply to your query

A Perl/OpenDocument project is at
http://search.cpan.org/dist/OpenOffice- ... /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.

[Woland99]

Well I looked at several different ideas:
Win32:OLE
viewtopic.php?f=25&t=10538&p=51485&hilit=Perl#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!

[Villeroy]

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.

[Woland99]

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?

[Woland99]

OK so found FilterOptions description here:
https://wiki.openoffice.org/wiki/Docume ... er_Options

[Villeroy]

viewtopic.php?f=20&t=64341&p=285734&hil ... 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.

[Woland99]

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

Code: Select all

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?

[Woland99]

viewtopic.php?f=20&t=64341&p=285734&hil ... 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.

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.

[rudolfo]

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

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.

[Villeroy]

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.

[Woland99]

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.

[Villeroy]

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.

[Woland99]

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 ?

[Woland99]

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.

[rudolfo]

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:

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.