Produce a XLS formatted file with FM Input

[Jalz]

Hi Guys,

Im trying to produce a report similar to the one in this attachment, with a similar range of colours etc. I have setup my FileMaker 6 table to include the rellevant fields, i.e. product, artwork rec etc etc. These are entered within a portal.

Are there any sample files I can disect, or do you know of any plugins I can have a go at creating reports like this.

Thanks Guys

Jalz

[Martin Brändle]

Where is the attachment?

[Jalz]

Oh Sorry,

was in a rush this morning....the boss has been on my back all day!

Anyways here is the spreadsheet, with some sample fm files that im trying to create it from

Cheers

Jalz

sample.zip

[Martin Brändle]

Somehow I have the feeling that you confuse XLS, the file extension for Excel spreadsheet, with XSL, the acronym for eXtensible Stylesheet Language, the latter being discussed here in this forum.

But anyway, at least for MS Excel 2002 on Windows you can produce Excel spreadsheets in XML. And for that you can export FileMaker data in XML format and transform it with a XSLT (eXtensible Stylesheet Language Transformation) stylesheet into the required XML for your XLS ( ).

Have a look at Jay Welshofers MS XML Spreadsheet (Office XP for Windows) example on the XSLT library on the FM website. Maybe you can adapt the stylesheets used there so that achieve the layout and the colors as in your example. Have also a look at Microsofts XML Spreadsheet Reference (you find the link in Jay Welshofers example).

However, if you would like to export your data in another way, I recommend to repost your question in the Importing & Exporting forum.

Martin

[Jalz]

Hi Martin

Thanks for your reply, nope I haven't confused the technologies here. I want to create an Excel file, like in my sample from a FM database, and the best method to do this in my opinion is to use XML, as I know you can format colours etc.

I will look at some of the examples out there, do you guys know whether a XML generated spreadsheet will work in Office X? I am going to be testing/developing in Office 2003 which I know supports XML.

Cheers

Jalz

[Jalz]

Hi Guys,

Downloaded the Stylesheets from the FileMaker library as suggested. The example will work for my solution.

I have a quick question though, the Stylesheet exports the fieldnames as headers for the columns. My fieldnames are codes, and I want to use descriptions. Is it easy to rename the header info for all the colums in the stylesheet?

Where can I find out more information about XML

[Jalz]

Hi Guys,

Im making slow progress..... anyways. I've attached a zip file with stylesheet, Metadata file and a test excel file) for those of you that might-"heres hoping??" put me out of my misery.

I'm using dreamweaver to alter the stylesheet, so the numbers might be different if you use a different editor. I've exported the fields that I want, if you open up the test excel file, I only want it to display data from columns 20 onwards.Columns 1 to 20 contain title names for my fields.

Ive used the following syntax on line 95 to get the fieldnames, whats wrong with the code?

<xsl:variable name="columnName1">

<xsl:value-of select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW"/>

</xsl:variable>

I've also changed the code for line 215 so that it uses the columnName1 variable for the Column Name instead of the field name.

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="columnName1"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

The code doesn't display the column headers at the top of my test document.

Any help would be much appreciated.

XML_Attempt.zip

[Martin Brändle]

Just a question to be able to help you: In which field are your descriptions of the column headers saved? Can you explain a little bit more on the data structure?

Then some comments to your code:

Ive used the following syntax on line 95 to get the fieldnames, whats wrong with the code?

<xsl:variable name="columnName1">

<xsl:value-of select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW"/>

</xsl:variable>

This would save a XML result tree fragment, e.g. the XML content within <ROW> and </ROW>, in variable columnName1. Obviously not of much help.

I've also changed the code for line 215 so that it uses the columnName1 variable for the Column Name instead of the field name.

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="columnName1"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

This does nothing, because you it's a loop through all <FIELD> tags in your XML output, and columnName1 is not defined ($columnName1 - that's how to address a variable - would be, but is not of help to you). The original code was <xsl:value-of select="@NAME"/>, e.g. get the content of the NAME attribute of the present <FIELD ....> in your loop.

Unfortunately the FMPXMLRESULT grammar is very unhandy for addressing fields with their names (the new fmresultset grammar for costum web publishing with FMS7A would be much better, but is not possible). There are techniques like saving a pointer to a named field in a variable, like this:

<xsl:variable name="pointer_xxx">

  <xsl:for-each select="fmp:FMPXMLRESULT/fmp:METADATA/fmp:FIELD">

    <xsl:if test="@NAME = 'your_field_name_xxx'">

      <xsl:value-of select="position()"/>

    </xsl:if>

  </xsl:for-each>

</xsl:variable>

and then to use the pointer to address the field column like this:

<xsl:for-each select="fmp:FMPXMLRESULT/fmp:RESULTSET/fmp:ROW">

  <xsl:value-of select="fmp:COL[position() = $pointer_xxx]"/>

</xsl:for-each>

but unless we don't know your data structure, this might not be of much help.

Martin

[Fenton]

I can't tell what you want for the column names. The names of the 1st row of data? Or the names of the fields?

If it's the latter, you don't need a variable set to the data. You just need the @NAME (attribute) of each FIELD:

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="@NAME"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

--------------------------------------------

If it's the former (which I doubt, 'cause there's data in the 1st row, not column names; and you have the names in the fields anyway; and it's awkward and probably not the best code; but what the heck)

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<xsl:variable name="colpos">

<xsl:value-of select="position()"/>

</xsl:variable>

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="../../fmp:RESULTSET/fmp:ROW[1]/fmp:COL[position()=$colpos]/fmp:DATA"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

------------------------------------------

If you indended to name your fields some other way, well, that's going to be tedious; but should be possible.

What I don't quite understand was how his original was supposed to work. As Martins says, he didn't use the $ in front of the column1 variable. And if you fix that, you end up getting all the data in the xml, which I'm sure wasn't intended. It's probably a case of last minute editing screw-up. Or I have no idea what he was intending; which is quite possible, since I'm sure he knows more about this than I do.

It's a pain dealing with anything Microsoft XML, because 90% of it is stuff you probably don't need. But I imagine if you did need it, you'd think it was great :-|

[Martin Brändle]

If it's the latter, you don't need a variable set to the data. You just need the @NAME (attribute) of each FIELD:

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="@NAME"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

Yes, this was the original code of Jay Welshofer's example.

Martin

P.S. This forum engine drives me crazy - you always have to write &lt; for the <, otherwise your code is not shown. Any possibility to change this behaviour?

[Jalz]

Hi Martin and Fenton,

Thanks for trying to help me out, I've managed to go to the library and take out the XML Bible, which I'm reading after my day job...seems like hard work compared to ScriptMaker.....my only real experience with languages.

In regards to Martin, the column names should be "Artwork Due at KC" to "Delivery Received at Mill W/C", i.e. columns with the column headings that start off from Gantt1 though to Gantt20.

A simple technique (excuse the syntax, still learning and trying to understand XML) might be to Look for Column Names;

"Gantt1:Startdate" and replace it with "Gantt1:TaskHeading" COL[1] Next

"Gantt2:Startdate" and replace it with "Gantt2:TaskHeading" COL[1] Next

.... "Gantt20:Startdate" and replace it with "Gantt20:TaskHeading" COL[1] Next

and then discard the first 20 columns which displaying data.

[Jalz]

Hi Fenton

If it's the former (which I doubt, 'cause there's data in the 1st row, not column names; and you have the names in the fields anyway; and it's awkward and probably not the best code; but what the heck):

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="/*/*/fmp:FIELD">

<xsl:variable name="colpos">

<xsl:value-of select="position()"/>

</xsl:variable>

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="../../fmp:RESULTSET/fmp:ROW[1]/fmp:COL[position()=$colpos]/fmp:DATA"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

/

Fantastic!! This code has worked! Now can I get the column names to start off at column 20, so Name in column 1 = the name column 20, name in column 2 = the name in column 21...etc.

[Martin Brändle]

To Fenton: excellent!

To Jalz: That should be easy. Change the line

<xsl:value-of select="position()"/>

to

<xsl:value-of select="position()+19"/>

Martin

[Jalz]

Hi Martin,

Thanks for that, I actually tried that before you mentioned it, its renaming my column 1 with the value of column 20. I need it to do the reverse.

Do you happen to know if test a reserved word? If made a statement which is

What im trying to say is if FieldName = "Product" do x otherwise do y

[Jalz]

Ooh, This is Great!!! Thanks Martin, Ive managed to use the code you suggested to get the result I wanted.

I recreated a different export order, so all the columns that I want to display are now being displayed first, with all the stuff that I dont need displaying anymore is starting at position (column) 22.

Now the final part in the hurdle for phase one, is to delete unwanted data, i.e. the columns and rows that contain the coumn header names. Once I've got them on top of my columns I don't need them. Any advice on how I can do this.

I think Im going ot use the pointer() command, and find the compand for delete.

Thanks to both of you that have helped me get this far.

[Fenton]

Now I'm really confused. The XML produces is a flat text file(s). How could you want data, then not want it? We're only taking the names out of the 1st row. That's what row[1] means.

There is no xsl "command" for delete. If you don't want something, you explicilty specify each and every field (row/col/data) you want, with <xsl:for-each>, and just don't specify the ones you don't want. It's more tedious that way. (Or don't export them from FileMaker in the first place.)

Or, alternatively, use a different method, calling named templates; create an individual template to match what you don't want, but don't specify anything to do with it.

[These are 2 different methods of XSL. So far we've been doing the 1st, specifying what we want with <xsl:for-each>, but using fairly generic targets: the fields in the metadata, the rows in the resultset. It's a little confusing, but quite logical. I consider myself only a beginner with XML, so the above may or may not match reality.]

Beverly Voth has a good book out, FileMaker Pro 6 Developer's Guide to XML/XSL, which is really required reading if you want to understand how to work with XML and FileMaker (nothing really has changed much in 7). A generic book on XML is needed also, as she cannot go into everything.

[Jalz]

Hi Fenton,

Thanks for the advice. If the grammer hasn't changed much from FM6 to FM7 then I think its worth purchasing the book you mention. I'll search for it on Amazon after I've posted this.

The only reason I've exported some of the data is because they contain the column names.

[Fenton]

So, some of the fields contain the column names, but others are data? That seems odd. But, if so, and you only want the other fields as data, then you could use the position() test in the "makerow" section of the xsl, instead of in the "column header" section.

[Martin Brändle]

Well, there is not "delete" in XSLT, but "select." One could work with pointer tables. Create a XML file pointertable.xml like this:

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

<pointers>

<pointer id="01">5</pointer>

<pointer id="02">7</pointer>

<pointer id="03">10</pointer>

.

.

.

</pointers>

In your XSLT, load it with the document() function:

<xsl:variable name="pointers" select="document('pointertable.xml')"/>

And then use the pointers here:

<xsl:template name="columnHeader">

<Row>

<xsl:for-each select="$pointers/pointers/pointer">

<xsl:variable name="colpos" select="current()"/>

<Cell ss:StyleID="styleHeaderRow">

<Data ss:Type="String">

<xsl:value-of select="/fmp:RESULTSET/fmp:ROW[1]/fmp:COL[position()=$colpos]/fmp:DATA"/>

</Data>

</Cell>

</xsl:for-each>

</Row>

</xsl:template>

I use this technique, e.g. for translations, but it can be used generally. For addressing of the pointers or translation values I have added the id attribute.

What do you think?

Martin

[Jalz]

Thanks Guys for the advice.

I think I will try and catch up on reading xml syntax at the weekend. Martin, I'm going to give your code a try, I'll let you know how I get on.

Have a good weekend all

Jalz

[Martin Brändle]

Oops, made a mistake. This line

<xsl:value-of select="/fmp:RESULTSET/fmp:ROW[1]/fmp:COL[position()=$colpos]/fmp:DATA"/>

must be changed to

<xsl:value-of select="/fmp:RESULTSET/fmp:ROW[1]/fmp:COL[$colpos]/fmp:DATA"/>

Martin

[Jalz]

Cheers Martin,

I've now managed finished off the report I started last week thanks to both you and Fenton. The contributors on this forum are Brill!

Jalz