Dynamic import Excel table into DITA

Post by **CharlesJ** » Mon Jul 13, 2015 1:33 pm

I cannot get a clear picture of how to run the convert:/processor for an .xlsx to .dita dynamic import from Excel.

I have been through:

http://oxygenxml.com/doc/versions/17.0/ ... sions.html
https://github.com/oxygenxml/dita-glass ... _Glass.odp
https://github.com/oxygenxml/dita-glass ... xcel2d.xsl

Is there any further documentation someplace that I can read, or an example of a .dita file with an actual implementation of the processor?

My code is:

Code: Select all

<body>

    <image href="convert:/processor=xslt;ss=urn:processors:excel2d.xsl!;sn=Overview!/urn:files:AIDS.xlsx"/>

</body>

But I get the error in the linked screenshot. I'm a bit surprised the image element is used, but I guess that it is because it is one of the few that supports the href attribute.

Thanks for your help!

Charles

Post by **Radu** » Mon Jul 13, 2015 2:15 pm

Hi Charles,

If you downloaded the entire dita-glass GitHub project:

https://github.com/oxygenxml/dita-glass

in the presentationSamples folder it contains a samples.xpr project configuration which should be loaded in the Oxygen Project panel.
After this, if you look in the Oxygen Preferences->XML / XML Catalog page you should see an extra XML catalog path ${pdu}/catalog.xml contributed by the project.
This XML catalog which is located in the presentationSamples folder maps references like urn:files:... to actual resources on disk.
So in your particular case if you add in the folder presentationSamples/resources your AIDS.xslx the URL you are using should try to convert it to XML.
One more thing, out of the box Oxygen can only convert older Excel formats to XML. To convert the new XSLX format to XML you would need to download additional libraries:

http://www.oxygenxml.com/doc/versions/1 ... ormat.html

Regards,
Radu

Post by **CharlesJ** » Mon Jul 13, 2015 3:56 pm

Hi Radu,

Thank you for a super quick reply. Unfortunately it still does not work. I have:

Added the libraries for XLSX to C:\Program Files\Oxygen XML Editor 17\lib.
Installed the dita-glass-master package.
Confirmed that the XML catalog path ${pdu}/catalog.xml is in Preferences-> XML -> XML Catalog.
Added the Excel file to C:\\...\dita-glass-master\presentationSamples\resources.
Added the .dita file to C:\\...\dita-glass-master\presentationSamples.

My code is:

Code: Select all

<image href="convert:/processor=xslt;ss=urn:processors:excel2d.xsl!;sn=Overview!/urn:resources:AIDS.xlsx"/>

Charles

Post by **Radu** » Mon Jul 13, 2015 4:23 pm

Hi Charles,

What should happen after you took those steps is that if you go to the File menu->Open URL action and paste the URL

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl!;sn=Overview!/urn:resources:AIDS.xlsx

you should open in the editor an XML equivalent of the Excel file.
Does this work for you? If not, what error do you receive?

If this works for you, coming back to what you are trying to do, when you refer from a DITA image that URL you will get XML content so the publishing will not be able to use that as an image. Oxygen will not automatically create an image graphic from the Excel document. What you can do is to apply an XSLT stylesheet over that XML equivalent of the Excel document and obtain an SVG image document which represents a graph with values taken from that Excel document. But the stylesheet which creates the SVG needs to be manually built based on your particular needs.

Regards,
Radu

Post by **CharlesJ** » Mon Jul 13, 2015 4:55 pm

Thanks again, Radu,

No the URL doesn't work either. It seems to be a problem finding the XLSX. Please see screenshot.

I'v been wondering about the use of the image element, because I don't want to import an image, I want to import the table data. I'd assumed the XML image element is being used a bit like an iframe in HTML, because image supports the href attribute. But I'm new to DITA, so I don't know if there is another element that would be better suited.

Post by **adrian** » Mon Jul 13, 2015 5:31 pm

Hi,

There seems to be a mistake in the URL (you should use 'urn:files:' instead of 'urn:resources:'), so try:

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl!;sn=Overview!/urn:files:AIDS.xlsx

Regards,
Adrian

Post by **Radu** » Tue Jul 14, 2015 8:34 am

Hi,

Besides what Adrian said, about this remark:

I'v been wondering about the use of the image element, because I don't want to import an image, I want to import the table data. I'd assumed the XML image element is being used a bit like an iframe in HTML, because image supports the href attribute. But I'm new to DITA, so I don't know if there is another element that would be better suited.

The dita-glass project you downloaded from GitHub has a sample DITA Map called convertSamples.ditamap. This map references various resources (Excel, CSV, Markdown, HTML) with the convert protocol and obtains DITA topics out of them. For example the reference to the Excel file is something like:

Code: Select all

 <topicref href="convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:sample.xls" format="dita"/>

So it first applies the "excel" processor to obtain XML and then the excel2d.xsl XSLT stylesheet to obtain a DITA topic from the XML.

If you open that DITA Map in the Oxygen DITA Maps Manager view you should be able to click each topic reference and open it in the main editor as a DITA topic.
Also the Configure Transformation Scenario toolbar button should show you two special convert scenarios for producing WebHelp and PDF from the DITA Map.

Regards,
Radu

Post by **CharlesJ** » Tue Jul 14, 2015 11:09 am

Thanks guys,

I presume it makes no difference whether the XLSX is in urn:files or urn:resources - I've copied it to both and it makes no difference. Oxygen is still not finding them.

Do the files need to be in a specific path?

convertSamples.ditamap is empty in the Oxygen DITA Maps Manager.
The Configure Transformation Scenario toolbar button shows the WebHelp and PDF scenarios.

Post by **Radu** » Tue Jul 14, 2015 11:42 am

Hi Charles,

The DITA Maps manager view has an "Open..." drop down button located in the top left part of the first toolbar. So you should use that to open the DITA Map (instead of the root map toolbar open button which has a different purpose).

About the received error, Adrian's example had an error in it.
You can try to use File->Open URL to open something like:

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:AIDS.xlsx

Regards,
Radu

Post by **CharlesJ** » Tue Jul 14, 2015 12:19 pm

Aha! Some progress

When I open the DITA Maps manager view that way I get a list of errors.

Is there some way to copy this list to an editor? Otherwise If not, I can note them and report them. They are all reporting malformed URL.

Why does Sales Graph appear at all?

Post by **Radu** » Tue Jul 14, 2015 12:27 pm

Hi Charles,

Could you hover over one of those reported problems in the DITA Maps Manager? The tooltip should give us some indication about the problem.

So to double check:

1) You are using Oxygen 17.0.

2) in the Oxygen Project view you opened the sample.xpr project file which came with the dita-glass GitHub project, right?

3) In the Oxygen Preferences->XML / XML Catalog page you now have a new catalog added like ${pdu}/catalog.xml

Have you tried opening directly the URL I suggested you using the File menu->Open URL action?

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:AIDS.xlsx

If it does not work, what error is reported?

Regards,
Radu

Post by **CharlesJ** » Tue Jul 14, 2015 1:07 pm

Now we are getting there!

I had moved the files from the remote drive to my C drive and lost the ${pdu}/catalog.xml catalog.

I have reinstalled the catalog and now all the examples in the DITA Maps manager view are working.

I have been able to get a simplified XLSX (just one worksheet) to open

But for the AIDS file (which contains multiple worksheets) I now get this error message

Code: Select all

Cannot open the specified file.

net.sf.saxon.trans.XPathException: When 'standalone' or 'doctype-system' is specified, the document must be well-formed; but this document contains a top-level text node

I don't understand that. But I note that the sn parameter is missing from the URL. If I add that

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/sn=Overview!/urn:files:AIDS.xlsx

then I get the following error message:

Code: Select all

Cannot open the specified file.

C:\Program Files\Oxygen XML Editor 17\sn=Overview!\urn:files:AIDS.xlsx (The filename, directory name, or volume label syntax is incorrect)

Post by **Radu** » Tue Jul 14, 2015 1:54 pm

Hi Charles,

Great.
The URL you tried to use contained two ! in it, please replace the first one with ;

So please try to open this URL instead:

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel;sn=Overview!/urn:files:AIDS.xlsx

Regards,
Radu

Post by **CharlesJ** » Tue Jul 14, 2015 2:37 pm

MAGIC!! Perfect! Thank you so much for your help!
Charles

Post by **CharlesJ** » Wed Jul 15, 2015 3:11 pm

Just when you thought you had gotten rid of me!

Is there some way to get the converted table to display automatically in the same topic (same .dita file)?

If I use the href attribute in the fig, section, xref and topicref elements, I have to click on the link, which then displays the table in a separate file.

If I use the codeblock and coderef elements I can import the XML code directly into the topic, but I still have to click to display the XML as a table.

Post by **Radu** » Wed Jul 15, 2015 3:30 pm

Hi Charles,

So if you have a DITA topic and at some point you want to display in it only the table part of the Excel spreadsheet you can add a DITA conref to the table defined in the Excel document, something like:

Code: Select all

<table conref="convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:sample.xls#sample/table_sample">

        <tgroup cols="2">

          <tbody>

            <row>

              <entry></entry>

            </row>

          </tbody>

        </tgroup>

      </table>

http://www.oxygenxml.com/dita/styleguid ... rence.html
http://oxygenxml.com/doc/versions/17.0/ ... onref.html

Regards,
Radu

Post by **CharlesJ** » Thu Jul 16, 2015 10:43 am

Thanks again, Radu,

Your conref example works perfectly, also on a simple example file I created!

However, it doesn't work on the auto-generated Excel spreadsheets (about 100) that I need it to work on.

I get the following error:

Code: Select all

net.sf.saxon.trans.XPathException: When 'standalone' or 'doctype-system' is specified, the document must be well-formed; but this document contains a top-level text node

Do you have any suggestions?

Charles

Post by **Radu** » Thu Jul 16, 2015 12:36 pm

Hi Charles,

Possibly you will need to re-write the XSLT stylesheet excel2d.xsl.
If you use the File->Open URL to open the Excel spreadsheet for which the conref does not work, something like (depending on the stylesheet name and excel file name):

Code: Select all

convert:/processor=excel;sn=Overview!/urn:files:AIDS.xlsx

do you obtain an XML content for it?
The excel2d.xsl is applied over it and needs to produce the DITA content from it.

Regards,
Radu

Post by **CharlesJ** » Thu Jul 16, 2015 1:11 pm

Thanks Radu,

Yes, the conversion to XML is fine, so I will dig into excel2d.

Charles

Post by **CharlesJ** » Thu Jul 16, 2015 5:33 pm

I should have thought of this before

- the problem is caused by cell references between the worksheets in the Excel file.

Many thanks again for your help, Radu - you have been great!

Charles

Post by **CharlesJ** » Fri Jul 17, 2015 12:20 pm

Hi Radu,

I think there is a bug somewhere in your code. I have observed the same error several times. Even though the URN for a file is specified as files, the file must actually be in resources.

For example, if I run the following in Open URL, it works fine if AIDS.xlsx is in resources. But if I delete AIDS.xlsx in resources, I get an error stating the file cannot be found.

Code: Select all

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel;sn=Overview!/urn:files:AIDS.xlsx

Charles

Post by **Radu** » Fri Jul 17, 2015 1:57 pm

Hi Charles,

When Oxygen tries to obtain some content from the URL you composed, it finds references to resources like urn:files:AIDS.xlsx. These references are not explicit references to files on disk so Oxygen will look in the XML catalogs configured in it.
If you open the XML catalog which comes with the dita-glass project in Oxygen, it should have a mapping for the urn:files prefix to an actual location on disk (relative to the XML catalog).
In our particular case the mapping is this one:

Code: Select all


<rewriteURI uriStartString="urn:files:" rewritePrefix="resources/"/>

So this is why "urn:files" is resolved to the "resources" folder. But of course you can change the mappings in any way you want.

Regards,
Radu

Post by **CharlesJ** » Tue Jul 21, 2015 10:30 am

OK - many thanks Radu!
Charles

Post by **CharlesJ** » Tue Jul 28, 2015 11:46 am

Hi Radu,

I have read George Bina's Magic URLs in an XML Universe from XML London and it is really cool what you are doing here and I don't see any other DITA tool that has this degree of really useful flexibility – Oxygen is way out in front!

I note that dita-glass was first uploaded to GitHub just four months ago and XML London was only last month, so I guess this is a relatively new package for you, so I hope you'll think it worthwhile investing a bit of time to iron out this transform issue!

Firstly, a correction – it is not referenced cells that are causing the problem. If there is more than one sheet in the Excel file, then the XSLT doesn’t work.

This has got me baffled, because the XML can be extracted regardless of the number of sheets by:

Code: Select all

convert:/processor=excel;sn=Sheetname!/urn:…

I presume this uses the same processor as the conref string:

Code: Select all

conref="convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn…”

So, if the conref string extracts the XML in the same way, this suggests the issue is something in the handshake between the extracted XML file and the XSLT (excel2d.xsl). I can’t see anything wrong with the XSL, but given my level of expertise this isn’t so surprising

Solving this problem will be seriously popular with many DITA users, so I hope you can spare some time to help with it!

Post by **CharlesJ** » Tue Jul 28, 2015 5:17 pm

Also the XSL-FO transform for the conref (relative link) does not work - even though the conref to a single-sheet Excel table displays fine in the topic.
Do you have any recommendations about that?
Charles

Post by **Radu** » Mon Aug 03, 2015 3:36 pm

Hi Charles,

Sorry for the delay.
Yes, this is a relatively new idea George Bina had about dynamic conversions between various sources and XML.
And I'm glad you find this interesting.

The excel2d.xsl stylesheet could probably be better changed to support multiple sheets in an Excel file.
For example I tried making these changes to it:

Code: Select all

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"

    xmlns:xs="http://www.w3.org/2001/XMLSchema"

    exclude-result-prefixes="xs"

    version="2.0">

    <xsl:output method="xml" doctype-public="-//OASIS//DTD DITA Topic//EN" 

        doctype-system="http://docs.oasis-open.org/dita/v1.1/OS/dtd/topic.dtd" indent="yes"/>

    <xsl:template match="sheets">

        <xsl:choose>

            <xsl:when test="count(sheet) > 1">

                <topic id="sheets">

                    <title>Multiple Excel Sheets:</title>

                    <xsl:apply-templates/>

                </topic>

            </xsl:when>

            <xsl:otherwise>

                <xsl:apply-templates/>

            </xsl:otherwise>

        </xsl:choose>

    </xsl:template>

    <xsl:template match="sheet">

        <topic id="{@name}">

            <title>[Excel] Imported Table "<xsl:value-of select="@name"/>" </title>

            <body>

                <table frame="none" id="table_{@name}">

                    <title>Flowers</title>

                    <tgroup>

                        <xsl:attribute name="cols"><xsl:value-of select="count(//row[1]/*)"/></xsl:attribute>

                        <thead>

                            <row>

                                <xsl:for-each select="//row[1]/*">

                                    <entry><xsl:value-of select="."/></entry>

                                </xsl:for-each>

                            </row>

                        </thead>

                        <tbody>

                            <xsl:for-each select="//row[position() != 1]">

                                <row>

                                    <xsl:for-each select="*">

                                        <entry><xsl:value-of select="."/></entry>

                                    </xsl:for-each>

                                </row>

                            </xsl:for-each>

                        </tbody>

                    </tgroup>

                </table>

            </body>

        </topic>

    </xsl:template>

</xsl:stylesheet>

About this question:

Also the XSL-FO transform for the conref (relative link) does not work - even though the conref to a single-sheet Excel table displays fine in the topic.

I remember testing this a couple of weeks ago and it seemed to work for me, but I guess I could double check.
How exactly does that conref attribute value look like? Does it properly work in the XHTML output?

Regards,
Radu

Post by **ann.jensen** » Wed Sep 23, 2015 2:30 pm

Sorry, removing question as I figured it out.
I hadn't expanded the conref to include the Excel sheet name and generated table name

Regards,
Ann

Post by **Radu** » Wed Sep 23, 2015 4:22 pm

Hi Ann,

Right. Instead of manually coding the conref value you could have used the standard "Insert Content Reference" toolbar action, paste the convert URL in the URL field and choose the table as a target.

Regards,
Radu

Post by **ann.jensen** » Fri Nov 06, 2015 2:21 pm

Hi,
After successfully importing a single worksheet spreadsheet I am now trying to use this functionality to import a multi-worksheet spreadsheet. Should this be possible?
I am trying the following to import the first worksheet from a multi-worksheet spreadsheet

Code: Select all

 <table conref="convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:AgentAssignment-TaskTemplate.xls#Task/table_Task">

    <tgroup cols="7">

     <tbody>

      <row>

       <entry></entry>

      </row>

     </tbody>

    </tgroup>

   </table>

but am getting the following error -

Cannot open the specified file.
net.sf.saxon.trans.XPathException: When 'standalone' or 'doctype-system' is specified, the document must be well-formed; but this document contains a top-level text node

.
If I reduce the spreadsheet to just one worksheet it works fine.
Can anyone advise how I can get this working?
Thanks in advance,
Ann

Post by **Radu** » Fri Nov 06, 2015 2:39 pm

Dear Ann,

You can use Oxygen File menu->Open URL action to open the URL:

convert:/processor=xslt;ss=urn:processors:excel2d.xsl/processor=excel!/urn:files:AgentAssignment-TaskTemplate.xls

just to see the actual DITA content from which the table will be conreffed.
It's possible that the "excel2d.xsl" needs to be modified in order to output the proper DITA content in your particular Excel case.

Regards,
Radu

Dynamic import Excel table into DITA

Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA

Re: Dynamic import Excel table into DITA