Creating Excel Files - HTML or EXCEL XP

[LSchafer]

Hi,

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.

Thanks

[Cynthia_sas]

Hi:

  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).

  Other possibilities also exist, but they may or may not be of interest to you. If you care about getting the procedure results, and only the procedure results  (no colors, fonts or formatting) into Excel, then consider using ODS CSV or ODS CSVALL, which can create a comma-delimited or tab-delimited result file. You might also be interested in ODS TAGSETS.TABLEEDITOR, which creates HTML with some embedded JavaScript to send the HTML file to Excel -- this destination allows you to do some cool things, but you need to be able to run JavaScript when the HTML file is opened in the browser. A new destination -- ODS TAGSETS.MSOFFICE2K_X -- is also an HTML-based destination, with a bit of XML added. It creates sort of a hybrid HTML/XML file. It has more suboptions than ODS MSOFFICE2K and there is a way to create multi-sheet workbooks using ODS TAGSETS.MSOFFICE2K_X, but it is more indirect (you have to create multiple HTML files and then link the HTML files together).

  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.

http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

  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).

cynthia