Creating Excel files from "scratch" without an API can be tricky.
Question #1: Do you really need .XLS or .XLSX files?
Microsoft Excel can really only read generated CSV or HTML files as renamed direct inputs (*.XLS), but warns that these are not really XLS files if specified as file extensions. As Volker mentioned, there is a previous question addressing this topic.
The actual XLSX format itself is a ZIP archive of XML documents so it needs either an official API to generate it, or you need to do it yourself via some extra work to first generate the appropriate XML files and then zip it into that format. I would recommend an API-based approach.
Question #2: Does the XLSX file really need to be generated on the database server-side or can the client do it?
Most client approaches will attempt to do this from the client side, which is probably a lot easier to do given the variety of APIs available and the full scripting environment already available in Excel / .NET (and is the recommended approach in our other question also).
If you strictly need to generate an XLSX file on the database server side though, you could do this easily via launching some type of external process from the database server which makes use of an existing Excel API.
For SQL Anywhere 10 creating XLSX files on the database server side, you could try the approach:
For SQL Anywhere 16 creating XLSX files on the database server side, you could try the approach:
You may have a look at that similar question with several approaches (although it deals with the older .XLS file format):
In addition to the options already mentioned, you can use SQuirrel SQL client to query and export data from your server.
In is generally much faster than the interactiveSQL from sybase when used via WAN (Server in datacenter/cloud)
The export will be in XLS format (so you are limited to 64k Rows) but otherwise its realy fast.
answered 09 Nov '13, 05:14