Converting Excel to XML

Apache Ant · Wikibooks

0%

Contents

    Text

    Size
    100%
    Theme
    Typeface
    Line length
    Spacing

    From Wikibooks, under CC BY-SA 4.0, as the source states it. This page at Wikibooks.

    Motivation

    You want to automatically extract a well-formed XML file from a binary Excel document.

    Method

    We will us the java Ant task within a build target.

    Input File

    We will create a sample Microsoft Excel file that has two columns like the following:

    Screen image for spreadsheet input
    Screen image for spreadsheet input

    Save this into a file 'sample.xls'.

    Next, download the Apache Tika jar file and put is on your local hard drive.

    You can get the downloads from here: http://tika.apache.org/download.html the Main Tika jar file is about 27MB.

    I put the tika jar file in D:\Apps\tika but you can change this.

    Create a file called "build.xml"

    Sources

    <project name="extract-xml-from-xsl" default="extract-xml-from-xsl">
    <description>Sample Extract XML from Excel xsl file with Apache Tika</description>
    <property name="lib.dir" value="D:\Apps\tika"/>
    <property name="input-file" value="sample.xls"/>
    
    <target name="extract-xml-from-xsl">
        <echo message="Extracting XML from Excel file: ${input-file}"/>
        <java jar="${lib.dir}/tika-app-1.3.jar" fork="true" failonerror="true"
            maxmemory="128m" input="${input-file}" output="sample.xml">
            <arg value="-x" />
        </java>
    </target> 
</project>

    The <java> task will run tika. The argument "-x" (for XML will extract the XML from the input.

    Other command line options are listed here: http://tika.apache.org/1.3/gettingstarted.html

    Now open your DOS or UNIX shell and cd into the place with your build file. Type "ant" into a command shell.

    Run

    $ ant
Buildfile: D:\ws\doc-gen\trunk\build\tika\build.xml

extract-xml-from-xsl:
     [echo] Extracting XML from Excel file: sample.xls

BUILD SUCCESSFUL
Total time: 1 second

    Sample Output

    Note that the output is a well formed HTML file with a table in it: 

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <meta name="meta:last-author" content="Dan" />
        <meta name="meta:creation-date" content="2013-03-04T17:20:19Z" />
        <meta name="dcterms:modified" content="2013-03-04T17:22:01Z" />
        <meta name="meta:save-date" content="2013-03-04T17:22:01Z" />
        <meta name="Last-Author" content="Dan" />
        <meta name="Application-Name" content="Microsoft Excel" />
        <meta name="dc:creator" content="Dan" />
        <meta name="Last-Modified" content="2013-03-04T17:22:01Z" />
        <meta name="Author" content="Dan" />
        <meta name="dcterms:created" content="2013-03-04T17:20:19Z" />
        <meta name="date" content="2013-03-04T17:22:01Z" />
        <meta name="modified" content="2013-03-04T17:22:01Z" />
        <meta name="creator" content="Dan" />
        <meta name="Creation-Date" content="2013-03-04T17:20:19Z" />
        <meta name="meta:author" content="Dan" />
        <meta name="extended-properties:Application" content="Microsoft Excel" />
        <meta name="Content-Type" content="application/vnd.ms-excel" />
        <meta name="Last-Save-Date" content="2013-03-04T17:22:01Z" />
        <title></title>
    </head>
    <body>
        <div class="page"><h1>Sheet1</h1>
            <table>
                <tbody>
                    <tr>
                        <td>Name</td>
                        <td>Phone</td>
                    </tr>
                    <tr>
                        <td>Peg</td>
                        <td>123</td>
                    </tr>
                    <tr>
                        <td>Dan</td>
                        <td>456</td>
                    </tr>
                    <tr>
                        <td>John</td>
                        <td>789</td>
                    </tr>
                    <tr>
                        <td>Sue</td>
                        <td>912</td>
                    </tr>
                </tbody>
            </table>
        </div>

</html>