11-09-2011 10:18 AM
I am writing a program to output clinical trial data to an excel spreadsheet. After doing a lot of googling, I would like to ask if there is a preference for using ODS HTML or the EXCEL XP tagset? Which is considered the most current solution? This is the first time that I will be creating an Excel output, and I expect that I will have this requirement a lot. I would like to use the best method from the start, so I don't have to re-write any code later on.
11-09-2011 10:53 AM
I would use either ODS MSOFFICE2K (creates Microsoft-friendly HTML file) or TAGSETS.EXCELXP (creates Microsoft-friendly Spreadsheet Markup Language XML file).
Issues which will impact your decision are:
1) what version of Excel do you/customers have?
-- if 2000 or lower, then use ODS MSOFFICE2K
-- if 2003 or higher, then use TAGSETS.EXCELXP
2) do you want logos or SAS/GRAPH output in the result file?
-- if yes, then use ODS MSOFFICE2K
-- if no, then use TAGSETS.EXCELXP
3) do you want single sheet workbooks or multi-sheet workbooks?
-- if single sheet, then you could use either ODS MSOFFICE2K (HTML) or ODS TAGSETS.EXCELXP (XML)
-- if multi sheet, then you could use ODS TAGSETS.EXCELXP (ODS MSOFFICE2K will only create single sheet workbooks)
These are the questions you need to answer when you start. Microsoft Excel doesn't like the "flavor" of HTML created by ODS HTML, because it is vanilla HTML 4.0 tags and Excel prefers its own "flavor" of HTML (as generated by ODS MSOFFICE2K).
Basically, ODS MSOFFICE2K and ODS MSOFFICE2K_X use Office 2000 specifications for HTML and TAGSETS.EXCELXP uses Office 2003 specifications for XML. If graphics are not an issue for you, I'd recommend starting with ODS TAGSETS.EXCELXP. This paper contains a very good overview of the extensive list of suboptions that you can use to control your result workbook/worksheets.
Do note that when you use ODS, no matter which destination you choose, you are NOT creating true, binary, proprietary .XLS files -- you are merely creating plain text ASCII text files which Excel knows how to open and render. You can open your result files with Notepad and see that they only contain HTML tags or XML tags. If you choose to name your result files with a .XLS file extension, this is merely a "convenience" that fools the Windows registry, so that when you double click on a file you launch Excel, instead of the default application for HTML/XML files (usually a browser).