Excel table to CALS table for InDesign

I recently saw a post about bringing tabular data straight from Microsoft Excel to InDesign. When you do, it is very difficult to apply a table style (the automatic appearance for an InDesign table, much like an object style automatically sets the appearance of a text or graphic frame). You may have to apply cell styles, convert the top row to a header row, etc., to get the table to look the way you want.

The post didn’t seem to go anywhere, but it got me thinking. InDesign works with CALS tables. You can export an Excel table as an XML spreadsheet. And (drum roll), you can convert one kind of xml to another using XSLT (XSL transforms.)
So you should be able to export the XML spreadsheet, convert it to a CALS table, then import it into InDesign. The benefit is that the table style will work perfectly on the imported table.

Unfortunately, I work with XSL so rarely that I basically have to relearn it each time I do. The first thing was to create a dummy Excel table (the native source) and a dummy InDesign table (the native result).

The dummy Excel table

The dummy ID CS3 table

Then I exported the Excel to an XML spreadsheet by clicking on (in Excell) File→Save as…, then selecting XML spreadsheet from the drop down menu. I also exported the ID table to an XML file by clicking on (in InDesign) File→Cross-media Export, then selecting XML… I clicked on “Save” in the next dialog, which brought up the final dialog. I made sure “Export Untagged Tables as CALS XML was selected.

Make sure "Export Untagged Tables as CALS XML" is selected

Once I had what the XML source code would look like (in XSL, it is called the source tree) and knew what the result tree was supposed to look like, I wrote my XSL. I used oXygen, the cross-platform XML editor to write and test my code. And after many, many tries (a pro could havd done it in minutes I am sure) I was successful. Here is an image of the XSL (WordPress doesn’t have the capability of showing XML documents as unparsed character data, but you can download this XSL document from the Box.net icon below.)

The XSLT Stylesheet

I ran the transform (using oXygen’s transform scenario) and got an XML document which looks almost like the result tree I had exported from InDesign. The names of the columns did not match exactly, but the names I provided for the columns worked fine.

So then I just imported my XML document into InDesign, dragged the Story element from the Structure Pane onto a page, and up popped a table, complete with header row. Then I untagged the table’s text frame, and applied the table style. I had to tweak the size of the rows and columns.

Now, this XSLT only works for Excel tables without any merged cells, and it converts only the first row to an InDesign header row. The new header row will repeat based on the configuration in your table setup.

My next goal is to write an transform for Excel tables with merged cells. This will be a bit harder, since I’ll have to use some of XSLT’s math functions, and unlike you all, I’m a total math phobe.

See ya soon


About this entry