OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How export XML
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
titlo
Newbie
Newbie


Joined: 15 Mar 2004
Posts: 2

PostPosted: Mon Mar 15, 2004 3:34 am    Post subject: How export XML Reply with quote

I looked for tutorial, but i found any, how export XML from CALC (table in format XML) as this http://www.designservis.sk/xml-export-from-msexcel.xml DocBook is only for Writer. Can you help me, please?

Thank you for everything.

Martin Hattas
Sorry, for my english
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Mar 15, 2004 9:05 am    Post subject: Reply with quote

Here is how i think it works. (Anyone please correct any misunderstanding I might have.)

If you have installed the XML Filter, then you can use an XSLT transformation to build your own import or export filter.

To install the XML Filter, you must have Java.
Run Setup.exe in your OOo\program folder.
Click (*) Modify.
Click [Next >>]
Unfold + Optional Components.
Be sure XSLT Sample Filters is checked.
Click Modify, OK, etc., etc., until it is installed.

Now open a new document.
Pick Tools --> XML Filter Settings....
(dialog appears)
(you might notice some samples filters already present, such as
DocBook File, Microsoft Word 2003, XHTML File)

Click New
(dialog box appears to define a new filter)
Name your filter.
Assign which application it applies to.
etc., etc.
On the Transformation tab, you must specify either or both an XSLT for export and/or an XSLT for import.

It looks like you can also specify a Template for import -- a nice touch.

Using this, you should be able to construct any type of XML import / export filter by writing an XSLT transformation document.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
titlo
Newbie
Newbie


Joined: 15 Mar 2004
Posts: 2

PostPosted: Wed Mar 17, 2004 2:14 am    Post subject: Reply with quote

Hi,

Thanks for you answer.

I understand, but how i do create XSLT tamplate for export?
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Wed Mar 17, 2004 7:57 am    Post subject: Reply with quote

XSLT is a powerful transformation language. You can transform a structured XML document into another structured XML document (such as, for example, XHTML for publishing on a web server).

Constructing an XSLT transformation is a non-trivial task. But if you have an XSLT transformation that transforms between some other format and OOo's format, then you can easily create new import/export filters, as explained above.

XSLT has a learning curve. Any fat book on XML in your local bookstore will have a chapter (or three) covering XSLT.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
erikanderson3
OOo Advocate
OOo Advocate


Joined: 25 Feb 2004
Posts: 332
Location: San Francisco peninsula

PostPosted: Wed Mar 17, 2004 7:05 pm    Post subject: Reply with quote

Conversely, if all you want is any old XML file with your content included, just save to the usual Calc .sxc format, and open it up in your favorite .zip reader. That'll give you a start, and show you what OOo XML looks like.

Cheers,

Erik
Back to top
View user's profile Send private message
neehnahw
Newbie
Newbie


Joined: 25 Oct 2004
Posts: 1

PostPosted: Mon Oct 25, 2004 6:27 am    Post subject: Reply with quote

Is it easier to just use a macro to export a plain XML file? I would like to do something similar. I want to convert an SXC file that looks like this

LastName | FirstName | MiddleName | Student Number | Telephone | ...
BlueBlue | Hey | Not ...

to something like

<person>
<lastname>blueblue</lastname>
<firstname>hey</firstname>
<middlename>not</middlename>
...

but XSLT seems overkill for such a task

or would it be more convenient if I converted first to CSV then converted to XML?
_________________
Linux Registered User #308259
Back to top
View user's profile Send private message
DannyB
Moderator
Moderator


Joined: 02 Apr 2003
Posts: 3991
Location: Lawrence, Kansas, USA

PostPosted: Mon Oct 25, 2004 8:35 am    Post subject: Reply with quote

You could write out xml from a macro.

Be sure to escape the values where necessary.

For instance, if the student's name was "Black & Decker", this would need to appear as...

<name>Black &amp; Decker</name>

Or if the student's name were <Fred>, then the output would need to be....

<name>&lt;Fred&gt;</name>

Or if the student's name were 12" drill.
i.e. twelve inch drill. A property inventory item in the spreadsheet being written out.
the output needs to be....
<name>12&quote; drill</name>

Otherwise, you would write out invalid XML like this....

<name>Black & Decker</name>
<name><Fred></name>
<name>12" drill</name>

The previous three lines are all invalid.
_________________
Want to make OOo Drawings like the colored flower design to the left?
Back to top
View user's profile Send private message
sylvaticus
Power User
Power User


Joined: 15 Jun 2006
Posts: 50

PostPosted: Wed Jun 13, 2007 11:57 pm    Post subject: Reply with quote

hello, I am sorry to take back such an old post, but I have the same need and I saw it is quite frequent on this forum, but no post ends with a solution...

Did anyone wrote a macro to perform such task? (write the data with Element names the values on the first row)
If someone is interested up to now I solved with a macro that fist save as CSV and then use CSV2XML by Jens Goedeke ( http://www.goedeke.net) to export as XML... but I was searching for an all-in-one solution...
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Jun 14, 2007 5:38 am    Post subject: Reply with quote

Since XML can describe anything you want, there is no other way than learning how to define your custom XML and how to transform your definitions to any other definitions and vice versa.
http://www-128.ibm.com/developerworks/library/x-oocalc/
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Dave_is_sexy
Power User
Power User


Joined: 11 Jan 2005
Posts: 61

PostPosted: Tue Nov 13, 2007 4:03 am    Post subject: Reply with quote

Thirded. This seems like such a basic need. I need the exact same output format as the other guys. Not at all impressed that it isn't built in Sad So far this has taken 4 hours
Back to top
View user's profile Send private message
Dave_is_sexy
Power User
Power User


Joined: 11 Jan 2005
Posts: 61

PostPosted: Tue Nov 13, 2007 4:05 am    Post subject: Reply with quote

For the search engines: This is the format required by Adobe applications, such as InDesign.
Back to top
View user's profile Send private message
sylvaticus
Power User
Power User


Joined: 15 Jun 2006
Posts: 50

PostPosted: Tue Nov 13, 2007 4:26 am    Post subject: Reply with quote

For those that may be interested I solved with a macro (see later) that exports selected sheets in CSV and then call CSV2XML (from http://www.goedeke.net) to convert them in XML.

It's a very ugly way, but it is working quite well for me...

Antonello


The file where is applied is available on:
http://regmas.org/cgi-bin/viewvc.cgi/regmas/data/regmasInput.ods?view=co

Code:

REM  *****  BASIC  *****

sub export2Csv

If Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools") then
   GlobalScope.BasicLibraries.loadLibrary("Tools")
Endif

'Objects and temp variables..
Dim myDoc As Object, indexSheet As Object, OSCell As Object, dirNameTempCell As Object, sheetNameTempCell As Object
Dim dirName, sheetName

'Final values...
Dim OS

'Scenario-idependet sheets to be exported:
dirNames   = Array ( "region",   "region",  "region",     "region",    "agents",  "region"    )
sheetNames = Array ( "settings", "objects", "activities", "resources", "farmers", "scenarios" )

myDoc = ThisComponent
indexSheet = myDoc.Sheets.getByName("index")
OSCell = indexSheet.getCellByposition(2,11)
OS = OSCell.getValue()

'Adding scenario sheets...
scenarioSheet = myDoc.Sheets.getByName("scenarios")
OSCell = indexSheet.getCellByposition(2,11)
For i = 1 To 101
   scenarioNameTempCell  = scenarioSheet.getCellByposition(0,i)
   scenarioName = scenarioNameTempCell.getString()
   If scenarioName <> "" Then
      scenarioActivitiesSheetName = scenarioName+"_activities"
      scenarioSettingsSheetName = scenarioName+"_settings"
      ' Adding scenario activities to the list of exportable sheets...
       If myDoc.Sheets.hasByName(scenarioActivitiesSheetName) Then
         Array1_AppendElement( dirNames,   "region/scenarios" )
         Array1_AppendElement( sheetNames, scenarioActivitiesSheetName )
      Next
      ' Adding scenario settings to the list of exportable sheets...
       If myDoc.Sheets.hasByName(scenarioSettingsSheetName) Then
         Array1_AppendElement( dirNames,   "region/scenarios" )
         Array1_AppendElement( sheetNames, scenarioSettingsSheetName )
      Next
   Next
Next

myFrame= ThisComponent.CurrentController.Frame
myDocUrl = ThisComponent.URL
myDocPath = DirectoryNameoutofPath(myDocUrl, "/")
sDocFileName = FileNameoutofPath(myDocUrl, "/")
sDocFileNameExtension = GetFileNameExtension(myDocUrl)
sDocFileNameWithoutExtension = GetFileNameWithoutExtension(myDocUrl, "/")

Dim filterProperties(5)  as new com.sun.star.beans.PropertyValue
Dim emptyFilterProperties()
Dim targetCell(1) as New com.sun.star.beans.PropertyValue
oDisp = createUnoService("com.sun.star.frame.DispatchHelper")

Dim debug as Integer
debug = UBound(sheetNames())

For i = LBound(sheetNames()) To UBound(sheetNames())

   targetCell(0).Name= "ToPoint" : targetCell(0).Value = sheetNames(i)+".$A$1"
   oDisp.executeDispatch(myFrame, ".uno:GoToCell", "", 0, targetCell())

   filenameToSave= myDocPath+"/"+sheetNames(i)+".csv"
   
    filterProperties(0).Name = "URL"
    filterProperties(0).Value = filenameToSave

    filterProperties(1).Name = "FilterName"
    filterProperties(1).Value = "Text - txt - csv (StarCalc)"

    filterProperties(2).Name = "FilterOptions"
    filterProperties(2).Value = "59,34,22"

    filterProperties(5).Name = "SelectionOnly"
    filterProperties(5).Value = true
   
    myDoc.storeToURL(filenameToSave,filterProperties())
       
Next

myDoc.storeAsURL(myDocUrl,emptyFilterProperties())
targetCell(0).Name= "ToPoint" : targetCell(0).Value = "index.$A$1"
oDisp.executeDispatch(myFrame, ".uno:GoToCell", "", 0, targetCell())

myDocPath=ConvertFromURL(myDocPath)

For i = LBound(sheetNames()) To UBound(sheetNames())

   If OS=0 Then
      csvFullPath = myDocPath+"/"+sheetNames(i)+".csv"
      xmlFullPath = myDocPath+"/"+dirNames(i)+"/"+sheetNames(i)+".xml"
      shellCommand1 = myDocPath+"/csv2xml_unx "+"-s:"+csvFullPath + " -t:" + xmlFullPath
      shellCommand2 = "rm "+csvFullPath
      Shell (shellCommand1,2)
      Wait(200)
      Shell (shellCommand2,2)
      Wait(200)
   Else
      csvFullPath = myDocPath+"\"+sheetNames(i)+".csv"
      xmlFullPath = myDocPath+"\"+dirNames(i)+"\"+sheetNames(i)+".xml"
      shellCommand1 = myDocPath+"\csv2xml_win.exe "+"-s:"+csvFullPath + " -t:" + xmlFullPath
      shellCommand2 = "del "+csvFullPath   
      Shell (shellCommand1,2)
      Wait(200)
      'Shell (shellCommand2,2) 'shellCommand2 doesn't works.. it doesn't find the "del" command!!!!
   Endif   

Next

end sub





' Danny's Basic Library:
' http://www.oooforum.org/forum/viewtopic.phtml?t=18762

'**********************************************************************
'   UtilBasic module
'
'   Module of utility routines to make OOo Basic easier to use.
'   Various type conversions, convenience routines, etc.
'   These utilities are not specific to Draw, or Writer,
'    or Calc, for instance.
'
'**********************************************************************
'   Copyright (c) 2003-2004 Danny Brewer
'   d29583@groovegarden.com
'
'   This library is free software; you can redistribute it and/or
'   modify it under the terms of the GNU Lesser General Public
'   License as published by the Free Software Foundation; either
'   version 2.1 of the License, or (at your option) any later version.
'
'   This library is distributed in the hope that it will be useful,
'   but WITHOUT ANY WARRANTY; without even the implied warranty of
'   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
'   Lesser General Public License for more details.
'
'   You should have received a copy of the GNU Lesser General Public
'   License along with this library; if not, write to the Free Software
'   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
'
'   See:  http://www.gnu.org/licenses/lgpl.html
'
'**********************************************************************
'   If you make changes, please append to the change log below.
'
'   Change Log
'      Danny Brewer         Revised 2005-04-02-01
'
'
'**********************************************************************


'--------------------
' Return the number of elements in a single dimensional array.
' Typically this array was created by calling Array().
Function Array1_Size( aArray ) As Long
   On Error GoTo ErrorHandler
    nNumElements = UBound( aArray ) + 1
    Array1_Size() = nNumElements
Exit Function
   
ErrorHandler:
    Array1_Size() = 0
End Function
   
   
'--------------------
' Append a new element onto the end of a single dimensional array.
' Typically this array was created by calling Array().
' Example:
'   aArray = Array()
'   Array1_AppendElement( aArray, "Red" )
'   Array1_AppendElement( aArray, "Orange" )
'   Array1_AppendElement( aArray, "Yellow" )
' is the same as writing...
'   aArray = Array( "Red", "Orange", "Yellow" )
' is the same as writing...
'   Dim aArray( 2 )
'   aArray( 0 ) = "Red"
'   aArray( 1 ) = "Orange"
'   aArray( 2 ) = "Yellow"
' except in the last example, aArray is statically known by the
'  compiler to be an array, so you must always use parenthesis
'  when passing the array to a function.   
Function Array1_AppendElement( aArray, uNewElement )
   nNumElements = Array1_Size( aArray )
   If nNumElements = 0 Then
      aArray = Array( uNewElement )
   Else
      Redim Preserve aArray( nNumElements )
      aArray( nNumElements ) = uNewElement
   EndIf
End Function
Back to top
View user's profile Send private message
Dave_is_sexy
Power User
Power User


Joined: 11 Jan 2005
Posts: 61

PostPosted: Tue Nov 13, 2007 5:31 am    Post subject: Reply with quote

Thanks for this, it seems promising. I get an errot at (ShellCommand1,2) though. I've tried to figure it out, but failed Sad
Back to top
View user's profile Send private message
sylvaticus
Power User
Power User


Joined: 15 Jun 2006
Posts: 50

PostPosted: Tue Nov 13, 2007 6:03 am    Post subject: Reply with quote

Dave_is_sexy wrote:
Thanks for this, it seems promising. I get an errot at (ShellCommand1,2) though. I've tried to figure it out, but failed Sad


You need the two files:
http://regmas.org/cgi-bin/viewvc.cgi/regmas/data/csv2xml_unx?view=co
http://regmas.org/cgi-bin/viewvc.cgi/regmas/data/csv2xml_win.exe?view=co

(they are just renames of csv2xml)

The macro works for my case, but you still needs to adapt to your own case (e.g. on which subdirectory export the data).. if someone is interested I may try to write a more general one..
Back to top
View user's profile Send private message
Dave_is_sexy
Power User
Power User


Joined: 11 Jan 2005
Posts: 61

PostPosted: Tue Nov 13, 2007 6:13 am    Post subject: Reply with quote

Sad

I have those already. They are in the same directory as the spreadsheet file. Oh Sad
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group