Scripting of OO ? (migrating from Excel + Perl)
Scripting of OO ? (migrating from Excel + Perl)
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.
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
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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.
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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: Scripting of OO ? (migrating from Excel + Perl)
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!
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!
JT
OpenOffice 4.0.1 on Win 7
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Scripting of OO ? (migrating from Excel + Perl)
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?
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
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
OK so found FilterOptions description here:
https://wiki.openoffice.org/wiki/Docume ... er_Options
https://wiki.openoffice.org/wiki/Docume ... er_Options
JT
OpenOffice 4.0.1 on Win 7
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Scripting of OO ? (migrating from Excel + Perl)
OK so this seems to work to save file as tab delimited:
Am I on a right track?
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();
JT
OpenOffice 4.0.1 on Win 7
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)Villeroy wrote: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.
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
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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.
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())
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.
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.
Re: Scripting of OO ? (migrating from Excel + Perl)
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.Woland99 wrote: Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Scripting of OO ? (migrating from Excel + Perl)
Well yes - spreadsheets are legacy things - I agree with you that database would have been better way to go.Villeroy wrote: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.Woland99 wrote: Thanks - I need mostly opposite problem - saving as tab-delimited - which seems to work (see my other post)
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.
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
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Scripting of OO ? (migrating from Excel + Perl)
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).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.
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
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
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.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.
JT
OpenOffice 4.0.1 on Win 7
OpenOffice 4.0.1 on Win 7
Re: Scripting of OO ? (migrating from Excel + Perl)
There is an introduction about OLE automation of OpenOffice:Woland99 wrote:Quick question about methods calls like:
createUnoService("com.sun.star.frame.DispatchHelper")
What is the object here?
Or - similar case - convertFromURL ?
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.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.
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.
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.