01-22-2015 11:14 AM
What is the code for creating xlsx extension excel file through ODS?
Whenever i try to use basic ODS command for this purpose, i am unable to create the XLSX file.
Please provide code and solution to this problem.
01-22-2015 11:21 AM
There isn't one. XLSX is an Open Office format document. That means is a Zip file, with subdirectories and xml files. Ods uses tagsets to output a single XML file which Excel can read and interpret. It doesn't matter really what you call the extension, that is just there to indicate the type of data or associate it with a certain program. If you do:
ods tagsets.excelxp file="abc.xls"
This doesn't create an Excel binary file, but a text file you can right click and open with notepad.
As far as I am aware there is no inbuilt function to write Open Office documents - although theres no reason you couldn't write one yourself if you know how.
01-22-2015 12:46 PM
Well, there's the ODS destination for Excel, available as "pre-production" in 9.4M2 and production in 9.4M3. This destination produces an .xlsx file. This is the 2nd of our Office Open XML destinations, after the destination for PowerPoint.
You're right, there's no reason you couldn't write your own code to create .xlsx files, except that (to paraphrase J.B.S. Haldane), it's not only harder than you imagine, it's harder than you can imagine.
01-22-2015 01:06 PM
Nice to hear there is one coming out (or is out), 9.4 any release is a bit far off for me at the moment though. I agree its not easy to create XLSX, the actual package is easy with ods package (structure is pretty basic), what always threw me was the lack of debugging, Excel just opens and states it doesn't like the file if there is even the slightest thing and will not open it.
Of course, you could just export CSV data and have a small macro which opens the CSV, and uses Excel SaveAs functionality...
01-22-2015 01:09 PM
Very nice " it's not only harder than you imagine, it's harder than you can imagine ".
I have seen the open office specs (OASIS) knowing MS added some things to that. Not an easy one to implement.
But they were already published as of 2007. (Office 2007 new type), that is almost 8 years ago.
01-23-2015 04:07 AM
Search is your friend: https://communities.sas.com/message/219824#219824
As for the VB or VBA script to open a csv and save as xlsx, well, that is outside of the remit of a SAS forum but to get started:
This is using VBA within Excel itself, so open an Excel file and run a macro:
And saving as:
You can do the same thing using VB (search for it on Google), been a while since I even looked at it so at a guess, create and office object, then use the inbuilt functions of the object to save/parse/saveas.
Got to ask though, why both with XSLX in the first place, there's no real benefit.