File Conversion through OpenOffice

WFC · March 12, 2006

I found a VBscript to do this. Don't recall where on the OOo Forum I found it or who wrote it. I managed to convert it to AutoIt. It runs from the command line to test the conversion routine and can convert spreadsheets (XLS, SXC, ODS) to HTML. It can be easily changed to convert to PDF by changing the file extention to PDF and the output filter to "calc_pdf_Export". You can find a list of output filters in this message: http://www.oooforum.org/forum/viewtopic.ph...t=filters#73715

I assume but have not tried that you can convert any file OpenOffice can open to any format it is able to save to by just changing the export filter although you will have to do some more work to make it work with output files that require options. I have not been able to make it work with a CSV file for example.

The only error checking the following script does is check to see that a filename is given. The HTML file will be saved to the same directory as the source file although that can easily be changed. A full path name is required. One remaining problem is that although I tell it to run hidden, it does not. "Hidden", True should do it but I can't make it work. If you figure it out please let me know.

I don't know much about this so can't answer many questions about it. Bits and pieces were gathered from several sources. Valery Vi's inspector AutoIt program was the main one aside from the VBscript and the OpenOffice.org Forum.

#include <Array.au3>

Dim $ServiceManager

Dim $Desktop

ConvertOOo($cmdline[1])

MsgBox(0,"", "Conversion Complete")

Exit

Func ConvertOOo($fn)

Local $args, $oDoc, $cURL, $cURL, $oSave

If $fn = "" Then

MsgBox(0, "", "No Filename Specified")

Return

EndIf

$ServiceManager = ObjCreate("com.sun.star.ServiceManager")

$Desktop = $ServiceManager.createInstance("com.sun.star.frame.Desktop")

$cURL = Convert2URL($fn)

$args = _ArrayCreate(MakePropertyValue("ReadOnly", True), _

MakePropertyValue("Password", "secret"), _

MakePropertyValue("Hidden", True))

$oDoc = $Desktop.loadComponentFromURL( $cURL, "_blank", 0, $args)

$oSave = _ArrayCreate(MakePropertyValue("FilterName", "HTML (StarCalc)"))

$cURL = StringLeft($cURL, StringLen($cURL) - 4) & ".html"

$oDoc.storeToURL( $cURL, $oSave)

$oDoc.close(True)

EndFunc

Func MakePropertyValue( $cName, $uValue)

Local $Pstruc

$Pstruc = $ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")

$Pstruc.Name = $cName

$Pstruc.Value = $uValue

Return $Pstruc

EndFunc

Func Convert2URL($fname)

$fname = StringReplace($fname, ":", "|")

$fname = StringReplace($fname, " ", "%20")

$fname = "file:///" & StringReplace($fname, "\", "/")

Return $fname

EndFunc

Enjoy!

WFC

randallc · March 12, 2006

Hi,

Hey, it looks great!

Can you give an example?... I can't see where you get "commandline[1]" from either?

What is your command line?

Best, Randall

WFC · March 12, 2006

Hi,
Hey, it looks great!
Can you give an example?... I can't see where you get "commandline[1]" from either?
What is your command line?
Best, Randall

Hi Randall. From the help file:

Command Line Parameters

The special array $CmdLine is initialized with the command line parameters passed in to your AutoIt script.

From the comand line you would type script.exe c:\myfiles\mysheet.xls where script.exe is whatever you name the script The path is passed to the script as $cmdline[1]. The script only checks that there is a filename but it has to be a complete path to an XLS, ODS or SXC for it to work with the HTML filter. I don't think I said in the original message that to convert to PDF all you have to do is change the extention to .PDF and the filter to "calc_pdf_Export".

I just wrote the command line version to test the function. Once I add it to my program I will check for the proper filenames and probably hardcode the path as @TempDir or add the output name as a second variable to the function. Now if I can figure out how to make it run hidden like I thought I was telling it to.

This is what I was trying to get you to do. Next I would like to figure out how to load the sheet and access the cells by row and column and write the HTML table the way I want to see it with a row starting with the <TR> tag and ending with the </TR> tag all on a single line and without the font etc. tags. I can use CSS to do that. Not knowing VBscript or Open Office Basic (most examples I see are in it) it wasn't an easy thing for me to do.

Try it on one of your excel files and let me know how you make out.

Best,

WFC

randallc · March 12, 2006

Thanks,

I see..

Is there a small obj file download, or do I have to download and install all of OOo?

I can't seem to get anything from the download sites..

Randall

WFC · March 12, 2006

Thanks,
I see..
Is there a small obj file download, or do I have to download and install all of OOo?
I can't seem to get anything from the download sites..
Randall

You need to download the program. I would get the full package and also the SDK if you plan on doing any programming with it. My installation is messed up. I had an old version and downloaded a later version without uninstalling. The new version installed in a different directory so I had both versions but I figured the registry entries pointed to the latest. When the latest version was released I unistalled but of course it left the older version on my system so I deleted it and went through the registry and deleted all entries that refferenced it. I am sure there were class ids left in the registry but I am not about to try to figure them out! Their uninstaller does not clean up the registry so you might want to set up a restore point before installing and then if you don't want it on your system, restore to it.

At least for my purposes OpenOffice does the job. There are three working computers in this house and two in storage. I have at least three versions of MS Office from Office 97 to Office 2003 Business Edition. Unfortunately the one I use is a Notebook that doesn't have it but does have Word Perfect Office and Quattro Pro. While I don't like it I don't use a word processor and all I use a spreadsheet for is data entry for my Civil War site. Quattro Pro works fairly well for that but Open Office Calc works as well as Excel. I am not going to buy Office to replace it. The only reason I have Office 2003 is because my wife just had to have it on her computer. She uses Word at work and didn't want to get use to yet another word processor. She wouldn't even let me show her OO Writer. She wanted to be able to email her word files home, work on them and email them back. I told her Writer would import and export to Word but she wants to open and save without doing a save as. Just can't teach old dogs new tricks. :-)

Download it, introduce your workplace to it, save them some money, get a promotion.:-) With your programming skills I am sure you can do a lot with it. I put the function in my AutoIt program and tested with XLS, SXC and ODS spreadsheets and it works fine although it is slow since I convert the resulting HTML file to the format I want to see. Since I won't use it often it doesn't really matter. Next I want to get it to access the cells by row and column and write the HTML file directly. That should make it faster by eliminating a couple of steps. I still have not been able to get it to run hidden. Valery Vi said to make it "Hidden", -1 instead of True but it made no difference. Eliminating the Password and Readonly made no difference and in fact were not in the original script I converted. I saw that in an OO basic script and tried it.

I am excited that you want to try it. There is a lot of information on the OOo forum and on the OpenOffice developers site. There is also a Yahoo AutoIt group. You could understand it I am sure. I don't even speak the lanuage and my programming background is some ASM Dos programming years ago and some Dos C programming but no formal training. I also don't know VBscript and am surprised I could get this to work, not that I fully understand it. OpenOffice and AutoIt are a perfect match -- both great free programs. Another great free program is 1st Page 2000 for web development.

WFC

WFC · March 12, 2006

I can now access the cells in a spreadsheet. Still can't make it run in hidden mode though!

#include <Array.au3>

Dim $ServiceManager
Dim $Desktop
Dim $Range
Dim $oCursor, $oStart, $oEnd

;================================================================
;  
; To test from the command line, name this script convert.au3 or
; whatever and compile it.  From a Dos window execute it:
;
; convert c:\mydir\mysheet.xls
;
; where convert is the name you chose with a complete path to the
; spreadsheet which can also be an ODS or SXC file. The sheet is
; is converted to a .table file and put in the same directory as
; the original file.
;
; Demonstrates accessing the cells of a spreadsheet.  

ConvertOOo($cmdline[1])  
MsgBox(0,"", "Sheet Converted to a Table", 2)
Exit

;================================================================

Func ConvertOOo($fn)
    Local $args, $oDoc, $cURL, $cURL, $oSave, $x, $y, $temp, $row
    
    If $fn = "" Then
        MsgBox(0, "", "No Filename Specified")
        Return
    EndIf
    $temp = StringRight($fn, 3)
    If $temp = "SXC" Or $temp = "XLS" Or $temp = "ODS" Then
    Else
        MsgBox(0, "", "Must be an XLS, SXC or ODS file. Exiting")
        Exit; Delete when used in a real program
        Return
    EndIf
    $ServiceManager  = ObjCreate("com.sun.star.ServiceManager")
    $Desktop  = $ServiceManager.createInstance("com.sun.star.frame.Desktop")
    $cURL = Convert2URL($fn)
    $args = _ArrayCreate(MakePropertyValue("Hidden", -1));, _
                    ;MakePropertyValue("ReadOnly", False), _    ; no need to use this if it is not necessary
                    ;MakePropertyValue("Password", "secret"))
    $oDoc = $Desktop.loadComponentFromURL( $cURL, "_blank", 0, $args)
    $oSheet=$oDoc.CurrentController.ActiveSheet 
    UsedRange($oSheet)
    $mydata = $Range.getdataarray()
    $temp = StringLen($fn)
    $fn = StringLeft($fn, $temp  -4) & ".table"
    $outh = FileOpen($fn, 2)
; at this point you would insert code for an html page
; I am only writing the table to be inserted in the page
    FileWriteLine($outh, "<TABLE COLS=" & $oEnd.EndColumn +1 & " BORDER=1>" & @CRLF)
    For $x = 0 To $oEnd.EndRow ; for the 1st row through the last row
        $row = $mydata[$x]
        $temp = "<TR>"
        For $y = 0 To $oEnd.EndColumn ; first column through the last column
            $temp = $temp & "<TD>" & $row[$y] & "</TD>"; $row[$y] is the cell data
        Next
        FileWriteLine($outh, $temp & "</TR>" & @CRLF); complete row, write it
    Next
    FileWriteLine($outh, "</TABLE>" & @CRLF); write tag
; More code for an HTML page
    FileClose($outh)  ; and close the file
    $oDoc.close(True) ; close soffice -- True works here but not for hidden!
EndFunc ;=== ConvertOOo

Func MakePropertyValue( $cName, $uValue)
    Local $Pstruc
    $Pstruc = $ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
    $Pstruc.Name = $cName
    $Pstruc.Value = ($uValue)
    MsgBox(0,"", $Pstruc.Value)
    Return $Pstruc
EndFunc ;===> MakePropertyValue

Func Convert2URL($fname)
    $fname = StringReplace($fname, ":", "|")
    $fname = StringReplace($fname, " ", "%20")
    $fname = "file:///" & StringReplace($fname, "\", "/")
    Return $fname
EndFunc;=== Convert2URL

Func UsedRange($Sheet) 
   $oCursor = $Sheet.createCursor() 
   $oCursor.gotoStartOfUsedArea(False) 
   $oStart = $oCursor.getRangeAddress() 
   $oCursor.gotoEndOfUsedArea(False) 
   $oEnd = $oCursor.getRangeAddress() 
   $Range = $Sheet.getCellRangeByPosition($oStart.EndColumn, $oStart.EndRow, $oEnd.EndColumn, $oEnd.EndRow) 
EndFunc ;=== UsedRange

This cut the time to produce a table from my 457K .XLS test file from about 32 seconds to about 6 secs. And it is not in the format I want to see. Now for that hidden problem...

WFC

randallc · March 12, 2006

thanks,

I got it working, but I don't use the command line much!; I name the file.

I watch with interest; pdf and html OK

best, Randall

Leagnus · June 21, 2009

Works fine! Many thanks to WFC!

Only problem is digits after comma. I've got 38.7450000000001 even from hidden values after comma.

Of course this digits were in the source xls-file. Still I wanna to have possibility to round the digits or to clean them from unnecessary fraction.

After For $y = 0 To $oEnd.EndColumn this code helps:

$s_Cell = $row[$y]; $row[$y] is the cell data
if IsNumber ($s_Cell) then $s_Cell = Round ($s_Cell,0)
$temp = $temp & "<TD>" & $s_Cell & "</TD>"

Edited June 21, 2009 by Leagnus

apo · September 21, 2009

Hi All,

Thank you for this code, guys!

Please, change line 58 AND line 61 as follows:

For $x = 0 To ( $oEnd.EndRow - $oStart.EndRow ); for the 1st row through the last row
        ...
        ...
        For $y = 0 To ( $oEnd.EndColumn - $oStart.EndColumn ); first column through the last column

Best regards,

Apo

Daunra31 · December 2, 2009

Hi,

Did anyone manage successfuly to run the OpenOffice spreadsheet creation in HIDDEN mode ???

I don't understand why it's not possible with Auto-It while it's quiet simple with a VBS script...

Thanks if you have the answer (this bug is driving me crazy.....)

+++

AndyG · December 3, 2009

Hi,

playing around with my simple OpenOfficeCalc-Functions(which are working properly in my applications), i noticed a difference in the return value of the "PropertyValue"-struct between the AI 3.3.0 and latest beta.

Dim $OpenPar[3]
Global $osm

_OOInit("Pass", True, True)      ;Password, ReadOnly,Hidden
_OOInit("Pass", False, False)


Func _OOInit($pass = "", $readonly = False, $hidden = False) ;verbindung zu OO herstellen
    $osm = ObjCreate("com.sun.star.ServiceManager")
    If @error Then
        MsgBox(0, "Fehler", "Der Zugriff auf OpenOffice ist nicht möglich")
        Exit
    EndIf
    $oDesk = $osm.createInstance("com.sun.star.frame.Desktop")
    $OpenPar[0] = setProp("ReadOnly", $readonly)
    $OpenPar[1] = setProp("Password", $pass) ;setzt das passwort des dokuments
    $OpenPar[2] = setProp("Hidden", $hidden)
EndFunc   ;==>_OOInit

Func setProp($cName, $uValue) ;Eigenschaften in struct übergeben
    ; $osm = ObjCreate("com.sun.star.ServiceManager")
    $oPropertyValue = $osm.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
    $oPropertyValue.Name = $cName
    $oPropertyValue.Value = $uValue
    ConsoleWrite('@@ Debug(' & @ScriptLineNumber & ') : $oPropertyValue.value = ' & $oPropertyValue.Value & @CRLF & '>Error code: ' & @error & @CRLF) ;### Debug Console
    $setOOoProp = $oPropertyValue
    Return $setOOoProp
EndFunc   ;==>setProp

Running in Scite with F5, the Console shows:

@@ Debug(25) : $oPropertyValue.value = 0
@@ Debug(25) : $oPropertyValue.value = Pass
@@ Debug(25) : $oPropertyValue.value = 0
@@ Debug(25) : $oPropertyValue.value = 0
@@ Debug(25) : $oPropertyValue.value = Pass
 @@ Debug(25) : $oPropertyValue.value = 0

True or False for ReadOnly or Hidden, the returned value is 0. Definetly not the previously written datatype

Running in Scite with ALT+F5 (Beta-Run)), the Console shows:

@@ Debug(25) : $oPropertyValue.value = False
@@ Debug(25) : $oPropertyValue.value = Pass
@@ Debug(25) : $oPropertyValue.value = False
@@ Debug(25) : $oPropertyValue.value = False
@@ Debug(25) : $oPropertyValue.value = Pass
@@ Debug(25) : $oPropertyValue.value = False

True or False for ReadOnly or Hidden, the returned value is False. The datatype is the right one now, but it seems that $oPropertyValue.Value = $uValue doesn´t work!

http://api.openoffice.org/docs/common/ref/com/sun/star/beans/PropertyValue.html

Edited December 3, 2009 by AndyG

Daunra31 · December 3, 2009

but it seems that $oPropertyValue.Value = $uValue doesn´t work!

Hello,

I agree with you AndyG : $oPropertyValue.Value = $uValue seems to be the origin of the bug.

But still, why it works with a VBS script using the same Open Office API (PropertyValue).

Here I give an example of a basis structure of such a VBS code :

Dim OpenParam(1)

'OpenOffice template file
TemplateFilePath = "file:///C:/Template.ots"

'OpenOffice saved file
strFileOut = "file:///C:/FinishedFile.ods"

Set objServiceManager= CreateObject("com.sun.star.ServiceManager")
Set Stardesktop= objServiceManager.createInstance("com.sun.star.frame.Desktop")

Set OpenParam(0) = MakePropertyValue("Hidden", TRUE) 'FALSE : affichage à l'écran, TRUE : en caché

Set TemplateFile = Stardesktop.loadComponentFromURL(TemplateFilePath, "_blank", 0, Array(OpenParam(0)))

Set oSheet = TemplateFile.getSheets().getByIndex(0)             
oSheet.getCellRangeByName("A1").setFormula("your texte")

TemplateFile.storeToURL strFileOut,Array()

TemplateFile.Close(True)

Function MakePropertyValue(cName,uValue)
    Dim oStruct, oServiceManager 
    Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
    Set oStruct = oServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue")
    oStruct.Name = cName
    oStruct.Value = uValue
    Set MakePropertyValue = oStruct
End Function

So, if this works, then the problem may come from Auto-IT (and not the API), doesn't-it ?

And you talk about "AI 3.3.0 and latest beta", what's this ? (I think you would understand that I'm not a real developer lol!)

Zedna · December 3, 2009

Looks VERY nice!!

I tried it with my OOO version 3.0 (Czech) on WIN XP but script from first post show only error message:

---------------------------
AutoIt Error
---------------------------
Line -1:
Error: The requested action with this object has failed.

What are requierements?

What OS/OOO version do you have?

AndyG · December 4, 2009

But still, why it works with a VBS script using the same Open Office API (PropertyValue).

because it´s a "Bug?!" in AutoIt? I don´t know exactly how the assign of a variable to an object (a struct in this example) works, but every other assign except a BOOL variable works properly. Seems to be a question to the DEV-team

@Zedna, win XP, SP3....OO newest version

/EDIT/ Try the functions from the link i posted in #11, there is an example included.

/EDIT2/ i hope i have used the right words now...

Edited December 4, 2009 by AndyG

reb · December 6, 2009

Looks good. I will try it.

REB

File Conversion through OpenOffice

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members