Here at MineQuest Business Analytics, we us Windows desktops and servers, Linux servers and Apple’s OS X on the desktop. As a Value Added Reseller I can tell you that a lot of WPS applications are run and are being developed on these platforms. Mainly due to pricing of hardware and software (commodity x86 CPU’s), most of our sales relate to Windows and Linux as it pertains to WPS software.
One issue that I run into is that reading an Excel file is a piece of cake on Windows, it’s not so easy on OS X or Linux. It seems that Microsoft doesn’t support an ODBC driver on OS X to read Excel. That does seem strange since they have an Office product that runs on OS X.
We have developed a utility to read and write Excel files for the three platforms that were mentioned above. We are currently writing the documentation for the utilities so that will take another week perhaps. MineQuest will be releasing these utilities to MineQuest’s customers as a free add-on just for ordering their WPS licenses through us. If you procured your WPS licenses through another reseller or through World Programming LTD directly, there is a small one time charge to acquire these macros.
We will also provide the source code to these two utilities (ReadExcel and WriteExcel) so that developers can enhance and modify the code themselves. The support for the product will only be to update code that was developed by others to enhance the product.
If you want to use the utilities in a product that you or your company will be selling, then you will be required to have a commercial license. There is a small charge for a commercial license so that you can redistribute the product in your own application.
Note that these utilities are not designed to work in SAS. They are specific to WPS.
The utility to read an Excel workbook is a simple macro call.
%ReadExcel( data = mydataset,
Workbook = “c:\temp\testbook1.xlsx”,
Sheet = “sheet1″);
Data is the name of the WPS/WPD data set you want to create.
Workbook is the name of the Excel Workbook you want to read the data from.
Sheet is the name of the sheet in the workbook from which you want to read the data from.
The utility to write an Excel Workbook is just as simple.
%WriteExcel( data = a,
Workbook = “c:\excelutils\data\testworkbook.xlsx”,
Sheet = “sheet1″,
Datefmt = mm/dd/yyyy,
javamemsize = 500m,
Replace = TRUE);
Data is the WPS/WPD data set that you want to write out to Excel.
Workbook is the name and location of the workbook you want to create.
Sheet is the name of the sheet in the workbook that you want to create.
Datefmt is the format that you want the date fields to look like.
Javamemsize is the amount of memory you want to allocate to java (optional).
Replace is whether you want to delete the existing workbook before creating the new workbook.
What I like about these two utilities is that you can use the exact same syntax across all three OS platforms to create an Excel Workbook. If you are creating or already have a product (i.e. a vertical market application) in WPS, you can open up your product to other platforms without changing any code.
About the author: Phil Rack is President of MineQuest Business Analytics, LLC located in Grand Rapids, Michigan. Phil has been a SAS language developer for more than 25 years. MineQuest provides WPS and SAS consulting and contract programming services and is a authorized reseller of WPS in North America.