Converting data from your favorite API or Data Source into a common format such as SpreadsheetML (Excel) or CSV is harder and uglier than it needs to be. Being tired of nested loops and ugly concatenated strings myself, I decided to look for an alternative. Insert XSL. Using XSL to produce exports has significantly cleaned up my code (especially the XML parsing), removed bugs, and enhanced readability. As with everything good in the programming world, it was a pain to learn mainly due to little obscurities.
Exporting to Excel
Let’s get the hardest part out of the way first: the syntax. The impossible to find reference is extremely handy for getting accustomed to SpreadsheetML. Given that syntax, here is a sample XSL file that would convert an XML file into the proper format.
<Font>
<Font /> <Table> </Table>
The above is nice because it is easier to read, and a non coder can go in there and make changes without having to sift through ugly XML parsing code. The SpreadsheetML format also allows for styling, equations, multiple workbooks, etc. Some of the features were harder to implement, if not impossible, when just using CSV or HTML as your export format to Excel. On top of that, foreign characters are fully supported using this method while when importing to Excel from CSV, UTF-8 is not recognized (correct me if I’m wrong on that).
Exporting to CSV
Comma Separated Values is a common output format that separates each value by a comma, and each row by a newline. Converting XML to CSV with the use of XSL is simple, simple, simple - just the way I like it.
"" ""
The first for-each is drawing the header values, while the second is looping through each row of data and drawing them. A check is done to make sure that each value is not the last in its row, and then a comma is slapped on. And lastly, you may be wondering why the code above is so ugly, and not broken into multiple lines. Well, by separating out the code lines, new lines were being placed in the output, which breaks the CSV format. If someone knows a way around this, please do share.
More Links to Get You Started
You should check out the symphony blog engine if you really like and understand XSL. It’s amazing.
symphony21.com
you can try wrapping your output in a variable tag and calling normalize-space on it:
this will put spaces all over the place though. not sure if csv cares about spaces between elements.
looks like the code got eaten, i’ll try again
ha nice
[xsl:variable name=”output”] [!— xsl goes here —] [/xsl:variable]
[xsl:value-of select=”normalize-space(output)”/]
Duh! Try this simple stuff. Generate simple HTML page with all the data in tabular format and wat ever styling you want to apply. Output as an .XLS file. Use it. The XLS file created using this way can be used in Open Office or even in MS Excel. Will some one hug me this time.