As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


SaviCellsPro

From sasCommunity
Revision as of 20:18, 16 April 2011 by Donh (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search


SaviCellsPro Guide and Sample Code

SaviCellsPro (SCP) is a product designed to move data to Excel, PDF, and HTML from any program that can create text files. SaviCellsPro relies on an XML file for its commands and has utilities to automatically extract information from an existing Excel workbook and use that to generate the initial XML.

From SAS, it is called using the X command (or an equivalent).



Code Download

SaviCellsPro

Latest version: 2.2, 2010-01-29

Requirements

SCP can be used standalone.

SCP also uses the .NET Framework 3.5.


Install Overview

SCP installs using a standard setup file. It creates a number of shortcuts in Program Files/Savian/SaviCellsPro that will help a programmer get started right away using it:

Programs Installed

Create Sample Output

Creates a sample output file from the complete.xml sample shipped with SCP. The resulting Excel file can be found in the Output directory in the SCP Program Files directory.

Read Me

Contains the latest changes by release number.

SaviCellsPro Help

Simple Windows Help file

SCP Tester

Starts a simple UI which allows SCO to be run from a Windows form.

SCP XML Creator

Examines an existing Excel file and automatically creates the starting XML to be used by SCP. this greatly simplifies the amount of time a user has to spend setting up SCP for the first time.

Command Line

scp.exe

This is the main program to launch if using SaviCellsPro via the command-line. it has a number of switches which can be accessed using scp.exe /?.


Types of Uses

SCP can be used in a variety of ways:

  1. Have a program generate the XML necessary for SCP to operate and then run SaviCellspro using the X command. (Hint: if using this method with SAS, it is suggested that you also use SaviPut which makes wrapping XML, or other code, easily in put statements)
  2. Run SCP standalone and have it use a local XML file.


Versions

Basic

Limited to 1 workbook, 3 worksheets, no print or chart options, no enhancements, no char level support within cells.


Premium

No limitations imposed.


Features

- Auto-discovers all of the elements in an Excel worksheet and creates the initial XML description as well as a separate stylesheet file. - Supports creating a worksheet and using it as a template
- Exports to Excel 2003 & 2007 in native binary
- Exports to PDF & HTML
- Stylesheets held in separate XML file
- Supports Column and Row fills to reduce XML size
- Cell can be abbreviated as C
- Supports character level formatting


Quick Start

1. Run SCP XML Creator and have SCP automatically discover the characteristics of an existing Excel file.
2. Look at the XML to see what SCP did and get a feel for the XML description.
3. Run Savian's utility SaviPut and merely copy the XML into the utility and have it wrap the XML with DATA STEP Put statements.
4. Locate the data references within the XML file and make any necessary modifications so that they are dynamic.
5. Run it using your SAS code and it will automatically create binary Excel files.


SAS Code

options noxwait noxsync; 

data _null_;
   file 'C:\temp\sasSCP.xml';
put 
        '<?xml version="1.0" encoding="utf-8"?>'
      / '<Actions log="Log\SaviCells.log" appendLog="false">'
      / '  <Export>'
...
       ;  
run;

x '"C:\Program Files\Savian\SaviCellsPro\SCP.exe" "C:\temp\sasSCP.xml"';

Log File

Here is a sample log file showing the processing information:
05Sep09 09:24:02.02 --> Beginning conversion of Test 1 to worksheet at Samples\Output\sample.xls
05Sep09 09:24:02.02 --> Get XML data...
05Sep09 09:24:02.02 --> Beginning conversion of Test 2 to worksheet at Samples\Output\sample.xls
05Sep09 09:24:02.02 --> Get XML data...
05Sep09 09:24:02.02 --> Beginning conversion of Test 3 to worksheet at Samples\Output\sample.xls
05Sep09 09:24:02.02 --> Get XML data...
05Sep09 09:24:02.02 --> Adjusting print scaling to 100% to account for column defined page breaks. This is an Excel requirement.
05Sep09 09:24:02.02 --> Adjusting print scaling to 100% to account for row defined page breaks. This is an Excel requirement.
05Sep09 09:24:02.02 --> Writing workbook to : C:\Users\Alan\AppData\Local\Temp\sample.xls
05Sep09 09:24:03.03 --> Writing PDF to : C:\Users\Alan\AppData\Local\Temp\sample.pdf
05Sep09 09:24:03.03 --> Writing HTML to : C:\Users\Alan\AppData\Local\Temp\sample.html