proc template; define tagset Tagsets.ExcelXP; /*-----------------------------------------------------------eric-*/ /*-- "This product may incorporate intellectual property owned --*/ /*-- by Microsoft Corporation. The terms and conditions upon --*/ /*-- which Microsoft is licensing such intellectual property --*/ /*-- may be found at --*/ /*-- http://msdn.microsoft.com/library/en-us/odcXMLRef/html/odcXMLRefLegalNotice.asp."--*/ /*--------------------------------------------------------1Aug 05-*/ notes "Excel XP (2002) XML format."; Parent = tagsets.config_debug; output_type = "xml"; indent = 0; split = " "; default_event = ''; /*-----------------------------------------------------------eric-*/ /*-- This seems to act like a preferred split but only works if --*/ /*-- Wrapit is set on the style. But if wrapit is set on the --*/ /*-- style everything wraps if it doesn't fit. Very strange. --*/ /*-- It's best to not wrap. --*/ /*--------------------------------------------------------29Jul03-*/ /*----------------------------------------------------------Vince-*/ /*-- Using for the split for column headings will work --*/ /*-- providing WrapText is set to 1 on the Alignment element --*/ /*-- for that cell. Added the logic for this in the --*/ /*-- xl_style_elements event and modified the width calculation --*/ /*-- in the sub_colspec_header and colspec_entry events. --*/ /*--------------------------------------------------------20Dec04-*/ map = '<>&'; mapsub = '/</>/&/'; copyright='©'; trademark='™'; registered_tm='®'; nobreakspace = ' '; stacked_columns = no; embedded_stylesheet = yes; pure_style=no; /* breaktext_ratio = 2.5; breaktext_width = 40; breaktext_length = 80; */ /* default_event = "default"; */ /*-----------------------------------------------------------eric-*/ /*-- If 'yes' system titles and footnotes will be placed as --*/ /*-- spanning cells above and below each table. - A part of --*/ /*-- the table really. --*/ /*--------------------------------------------------------22Aug03-*/ mvar embedded_titles; /*-----------------------------------------------------------eric-*/ /*-- If yes, cause the top of the worksheet to be stationary --*/ /*-- while the data scrolls. --*/ /*--------------------------------------------------------4Aug 04-*/ mvar frozen_headers; /*-----------------------------------------------------------eric-*/ /*-- If yes, cause the left of the worksheet to be stationary --*/ /*-- while the data scrolls. --*/ /*--------------------------------------------------------4Aug 04-*/ mvar frozen_rowheaders; /*-----------------------------------------------------------eric-*/ /*-- If all or a range like 1-10, causes autofilter to be --*/ /*-- turned on for all or some of the columns in the table --*/ /*--------------------------------------------------------4Aug 04-*/ mvar autofilter; mvar width_points; mvar width_fudge; mvar default_column_width; mvar formulas; /*-----------------------------------------------------------eric-*/ /*-- If 'no' do not turn percentages into numbers. --*/ /*-- Display them as strings. The default behavior --*/ /*-- is to divide them by 100 before displaying as --*/ /*-- Percent format. --*/ /*--------------------------------------------------------23Aug03-*/ mvar convert_percentages; /*-----------------------------------------------------------eric-*/ /*-- Set orientation to landscape to get landscape oriented printing.--*/ /*--------------------------------------------------------14Jun04-*/ mvar orientation; /*-----------------------------------------------------------eric-*/ /*-- Set sheetname to this macro var instead of anything else --*/ /*--------------------------------------------------------20Apr06-*/ mvar override_sheetname; /*-----------------------------------------------------------eric-*/ /*-- Supposedly there is a 31 worksheet limit. But we have --*/ /*-- not seen that to be the case. --*/ /*--------------------------------------------------------25Jul03-*/ log_note = "NOTE: This is the Excel XP tagset (SAS 9.1.3, v1.37, 05/31/06). Add options(doc='help') to the ods statement for more information."; /*-----------------------------------------------------------Eric-*/ /*-- The excel xml specification is here. --*/ /*-- --*/ /*-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp--*/ /*--------------------------------------------------------24Jul03-*/ notes "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp"; define event default; start: put "<" event_name ">" nl; finish: put "" nl; end; /*-----------------------------------------------------------eric-*/ /*-- excel can currently only handle one table per worksheet so --*/ /*-- any options other than table or bygroup will not work. --*/ /*-- The specification reads like that may change in the future --*/ /*-- so I'll leave the code in place for now. --*/ /*--------------------------------------------------------21Jul03-*/ /* log_note = 'NOTE: Experimental Excel XP tagset. Use alias=("proc" | "page" | "bygroup" | "table" | "none") to determine how worksheets will be created. The default is page.'; */ /*-------------------------------------------------------------eric-*/ /*-- The specification for this xml is here. --*/ /*-- http://msdn.microsoft.com/library/default.asp? --*/ /*-- url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp --*/ /*----------------------------------------------------------4Jul 03-*/ /*-------------------------------------------------------------eric-*/ /*-- Use this event to reset the worksheet interval to what was --*/ /*-- given on the ods statement or to the default. If no value --*/ /*-- is given then it will reset. If a value of proc, page, --*/ /*-- bygroup, table, or none is given then the interval will be --*/ /*-- set to the value given. --*/ /*-- Use like this: --*/ /*-- --*/ /*-- ods tagsets.excelxp event=sheet_interval(text="bygroup"); --*/ /*-- --*/ /*-- or to reset the interval --*/ /*-- --*/ /*-- ods tagsets.excelxp event=sheet_interval; --*/ /*----------------------------------------------------------4Jul 03-*/ define event documentation; break /if ^$options; trigger quick_reference /if cmp($options['DOC'], 'quick'); trigger help /if cmp($options['DOC'], 'help'); trigger settings /if cmp($options['DOC'], 'settings'); end; define event help; putlog "=============================================================================="; putlog "The EXCELXP Tagset Help Text."; putlog " "; putlog "This Tagset/Destination creates Microsoft's spreadsheetML XML."; putlog "It is used specifically for importing data into Excel."; putlog " "; putlog "Each table will be placed in its own worksheet within a workbook."; putlog "This destination supports ODS styles, traffic lighting, and custom formats."; putlog " "; putlog "Numbers, Currency and percentages are correctly detected and displayed."; putlog "Custom formats can be given by supplying a style override on the tagattr"; putlog "style element."; putlog " "; putlog "By default, titles and footnotes are part of the spreadsheet, but are part"; putlog "of the header and footer."; putlog " "; putlog "Also by default, printing will be in 'Portrait'."; putlog "The orientation can be changed to landscape."; putlog " "; putlog "The specification for this xml is here."; putlog "http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp"; putlog " "; putlog "See Also:"; putlog "http://support.sas.com/rnd/base/topics/odsmarkup/"; putlog "http://support.sas.com/rnd/papers/index.html#excelxml"; putlog " "; trigger quick_reference; end; define event quick_reference; putlog "=============================================================================="; putlog " "; putlog "These are the options supported by this tagset."; putlog " "; putlog "Sample usage:"; putlog " "; putlog "ods tagsets.excelxp file='test.xml' data='test.ini' options(doc='Help'); "; putlog " "; putlog "ods tagsets.excelxp options(doc='Quick'); "; putlog " "; putlog "ods tagsets.excelxp options(embedded_titles='No' Orientation='Landscape'); "; putlog " "; putlog "Doc: No default value."; putlog " Help: Displays introductory text and options."; putlog " Quick: Displays available options."; putlog " "; putlog "Orientation: Default Value 'Portrait'"; putlog " Tells excel how to format the page when printing."; putlog " The only other value is 'landscape'."; putlog " Also available as a macro variable."; putlog " "; putlog "Embedded_Titles: Default Value 'No'"; putlog " If 'Yes' titles will appear in the worksheet."; putlog " By default, titles are a part of the print header and footer."; putlog " Also available as a macro variable."; putlog " "; putlog "Embedded_Footnotes: Default Value 'No'"; putlog " If 'Yes' footnotes will appear in the worksheet."; putlog " By default, footnotes are a part of the print header and footer."; putlog " Also available as a macro variable."; putlog " "; putlog "Embed_Titles_Once: Default Value 'No'"; putlog " If 'Yes' Embedded titles will only appear at the top of each worksheet."; putlog " "; putlog "Print_Header: Default Value ''"; putlog " If Embedded titles are on, this value will be used as the header for"; putlog " printing. Everything about the appearance of the 3 part header can"; putlog " be controlled with this value. The Excel syntax for this string follows."; putlog " Of course the easiest way to create a header or footer is to do it in"; putlog " Excel. Then save the workbook to xml. Search the XML for
&C
&R '; putlog " "; putlog " A very simple example is this:"; putlog " "; putlog ' &LLeft header text&CCenter header text&RRight Header Text'; putlog " "; putlog ' Newlines can be introduced by inserting within the text.'; Putlog " Other special values follow "; putlog " "; putlog ' Newline: '; putlog ' Page Number: &P'; putlog ' Pages: &N'; putlog ' Date: &D'; putlog ' Time: &T'; putlog ' File Path: &Z&F'; putlog ' File: &F'; putlog ' Sheet Name: &A'; putlog ' Underline: &U One to start underlining, another to stop it.'; putlog ' Font Size: &8'; putlog " "; putlog " The font size can be controlled by placing the font size in points right"; putlog " before the text. This is a left sided header with a font size of 8."; putlog " "; putlog ' &L&&8This is a test;'; putlog " "; putlog " The font, bold and Italic can be changed using this syntax."; putlog " "; putlog ' " , "'; putlog " "; putlog " This example changes the font, turns on bold and Italic, changes the font size"; putlog " and turns underline on and off."; putlog " "; putlog ' &L&"Palatino,Bold Italic"&9&UThis is a test&U'; putlog " "; putlog " This is a complete example, showing the various possibilities"; putlog " "; putlog '&L&"Palatino,Bold Italic"&9&UThis is underlined &U'; putlog 'This is not &12This is bigger&CThis is the Center Page: &P '; putlog 'Pages: &N Date: &D Time: &T Path: &Z&F '; putlog 'File: &F Sheet: &A&R&14This is bigger and on the right &P'; putlog " "; putlog " "; putlog "Print_Footer: Default Value ''"; putlog " If Embedded footers are on, this value will be used as the footer for"; putlog " printing. Everything about the appearance of the 3 part header can"; putlog " be controlled with this value. The syntax for this value is the same"; putlog " as that for the Print_Header option."; putlog " "; putlog "Suppress_Bylines: Default Value 'No'"; putlog " If 'Yes' Bylines will not appear in the worksheet. This is useful with"; putlog " Proc Print because turning off bylines will defeat the tagset's bygroup"; putlog " processing abilities when using proc print."; putlog " "; putlog "Zoom: Default Value '100'"; putlog " This value determines the zoom level on the worksheet."; putlog " "; putlog "Scale: Default Value '100'"; putlog " This value determines the scale level for printing"; putlog " "; putlog "DPI: Default Value '300'"; putlog " This value determines the dots per inch for printing"; putlog " "; putlog "Pages_FitWidth: Default Value '1'"; putlog " This value determines the number of pages to fit the worksheet across"; putlog " when printing."; putlog " "; putlog "Pages_FitHeight: Default Value '1'"; putlog " This value determines the number of pages down to fit the worksheet"; putlog " when printing."; putlog " "; putlog "FitToPage: Default Value 'no'"; putlog " Fit to Page when printing."; putlog " "; putlog "Page_Order_Across: Default Value 'no'"; putlog " If set to yes, the worksheet page order will be set to print across,"; putlog " then down."; putlog " "; putlog "Center_Vertical: Default Value 'no'"; putlog " This value controls vertical centering for printing"; putlog " "; putlog "Center_Horizontal: Default Value 'no'"; putlog " This value controls horizontal centering for printing"; putlog " "; putlog "Row_Repeat: Default Value 'none'"; putlog " Values: None, number, range, header."; putlog " If a number is specified that row will be repeated across pages."; putlog " When a worksheet breaks across pages when printing."; putlog " If a range such as '3-5' is given, that range of rows will be"; putlog " repeated. If 'header' is given, the table headers for the first"; putlog " table of the worksheet will be repeated."; putlog " "; putlog "Column_Repeat: Default Value 'none'"; putlog " Values: None, number, range, header."; putlog " If a number is specified that column will be repeated across pages."; putlog " When a worksheet breaks across pages when printing."; putlog " If a range such as '3-5' is given, that range of columns will be"; putlog " repeated. If 'header' is given, the columns that contain the"; putlog " row headers for the first table of the worksheet will be repeated."; putlog " "; putlog "Frozen_Headers: Default Value 'No'"; putlog " Values: Yes, No, number."; putlog " If 'Yes' The rows down to the bottom of the headers will be frozen when"; putlog " the table data scrolls. This includes any titles created with the"; putlog " embedded titles option. If a number is given, that is the row count"; putlog " that will be frozen."; putlog " Also available as a macro variable."; putlog " "; putlog "Frozen_RowHeaders: Default Value 'No'"; putlog " Values: Yes, No, number."; putlog " If 'Yes' The header columns on the left will be frozen when"; putlog " the table data scrolls. If a number is given, that is the column"; putlog " count that will be frozen."; putlog " Also available as a macro variable."; putlog " "; putlog "AutoFilter: Default Value 'none'"; putlog " Values: None, All, range."; putlog " If 'all' An auto filter will be applied to all columns."; putlog " If a range such as '3-5' The auto filter will be applied to the"; putlog " in that range of columns."; putlog " "; putlog "AutoFilter_Table: Default Value '1'"; putlog " Values: Any number"; putlog " If sheet interval is anything but table or bygroup, this value"; putlog " Determines which table gets the autofilter applied. If the sheet"; putlog " interval is table, or bygroup the only table get's the autofilter"; putlog " regardless of this setting."; putlog " "; putlog "Formulas: Default Value 'yes'"; putlog " Values: yes, no"; putlog " By default, data values that start with an '=' will become formulas"; putlog " instead of cell values. This behavior can be turned off by setting"; putlog " this option to 'no'. Excel only understands relative column references"; putlog " in it's XML. A formula like sum(C2,C3) or A2+B3 will not work."; putlog " An equivalent might be sum(R[-2]C,R[-1]C) or RC[-2]+RC[-1]."; putlog " See the Proc Print example under Default_Column_Width."; putlog " "; putlog "Width_Fudge: Default Value '0.75'"; putlog " Values: None, Number."; putlog " By default this value is used along with Width_Points and column width"; putlog " to calculate an approximate width for the table columns."; putlog " width = Data_Font_Points * number_Of_Chars * Width_Fudge."; putlog " If 'none' this feature is turned off."; putlog " "; putlog "Width_Points: Default Value 'None'"; putlog " Values: None, Number."; putlog " By Default the point size from the data or header style"; putlog " elements are used to calculate a pseudo column width."; putlog " The column width is calculated from the given column width or"; putlog " the length of the column's header text. If the header is bigger."; putlog " In the case the header length is used, so is the header's point size."; putlog " This value overrides that point size."; putlog " This value is used along with WidthFudge and column width"; putlog " to calculate an approximate width for the table columns."; putlog " width = Width_Points * number_Of_Chars * Width_Fudge."; putlog " "; putlog "Default_Column_Width: Default Value 'None'"; putlog " Values: None, Number, list of numbers."; putlog " Most procedures provide column widths, but occasionally a column"; putlog " will not have a width. Excel will resize the column to fit any"; putlog " numbers but will not auto-size for character string headings."; putlog " In the case that a column does not have a width, this value will be"; putlog " used instead. The value should be the width in characters."; putlog " If the value of this option is a comma separated list."; putlog " Each number will be used for the column in the same position. If"; putlog " the table has more columns, the list will start over again."; putlog " "; putlog "Absolute_Column_Width: Default Value 'None'"; putlog " Values: None, Number, list of numbers."; putlog " This option works similarly to the default column width option"; putlog " The difference is that these widths will be used regardless"; putlog " of any column widths the procedure might provide."; putlog " The value should be the width in characters."; putlog " If the value of this option is a comma separated list."; putlog " Each number will be used for the column in the same position. If"; putlog " the table has more columns, the list will start over again."; putlog " "; putlog "Row_Heights: Default Value '0,0,0,0,0,0,0'"; putlog " This option controls how tall the rows will be for each type of row."; putlog " The numbers are in points. By default the values will be taken from the"; putlog " font size used for the row. The font sizes are collected from the style"; putlog " definitions for each item. The table row height is defined by the font"; putlog " size in the header style."; putlog " "; putlog " The parameters of this option are positional, but not all values must be"; putlog " specified. A value of 0 means that the height should be taken from the style."; putlog " The first value is the height for table header rows. The next is the height "; putlog " for the table body rows. "; putlog " The next value is the row height for bylines. The fourth is for titles, "; putlog " the fifth is for footers, the sixth is the pagebreak height, "; putlog " and the last value is the height for paragraph skip"; putlog " "; putlog " The default values are:"; putlog " Table_head : 0 "; putlog " Table : 0 "; putlog " Byline : 0 "; putlog " Title : 0 "; putlog " Footer : 0 "; putlog " PageBreak : 0 "; putlog " Parskip : 0 "; putlog " "; putlog "row_height_fudge: Default Value '4'"; putlog " Values: Number."; putlog " This value is added to the row height for each row. The additional height"; putlog " makes the spreadsheet easier to read."; putlog " "; putlog "Autofit_height: Default Value 'no'"; putlog " Values: yes, no"; putlog " If yes no row heights will be specified. This allows the auto fit height"; putlog " of Excel to do it's job, sometimes not so well."; putlog " "; putlog "Sheet_Interval: Default Value 'Table'"; putlog " Values: Table, Page, Bygroup, Proc, None."; putlog " This option controls how many tables will go in a worksheet."; putlog " In reality only one table is allowed per worksheet. To get more"; putlog " than one table, the tables are actually combined into one."; putlog " "; putlog " Specifying a sheet interval will cause the current worksheet to close."; putlog " It is recommended that this always be the first option to insure that"; putlog " The options following it apply to the new worksheet rather than the"; putlog " last worksheet."; putlog " "; putlog "Sheet_Name: Default Value 'None'"; putlog " Values: Any string "; putlog " Worksheet names can be up to 31 characters long. This name will"; putlog " be used in combination with a worksheet counter to create a unique name."; putlog " "; putlog "Sheet_Label: Default Value 'None'"; putlog " Values: Any String"; putlog " This option is used in combination with the various worksheet naming."; putlog " heuristics which are based on the sheet interval."; putlog " This string will be used as the first part of the name instead of the"; putlog " predefined string it would normally use."; putlog " "; putlog " These are the defaults:"; putlog " "; putlog " 'Proc ' total_Proc_count - label"; putlog " 'Page ' total_page_count - label"; putlog " 'By ' numberOfWorksheets byGroupLabel - label"; putlog " 'Table ' numberOfWorksheets - label"; putlog " "; putlog "Contents_Workbook: Default Value 'Contents, Index'"; putlog " Values: Contents, Index, Workbooks, All"; putlog " If set to all, The contents file will contain 3 worksheets,"; putlog " a list of workbooks, a hierarchical table of contents, and a"; putlog " list of worksheets. "; putlog " "; putlog "Contents: Default Value 'No'"; putlog " Values: Yes, No"; putlog " If set to yes, The first worksheet will contain a table of contents"; putlog " With links to each worksheet in the workbook."; putlog " "; putlog "Index: Default Value 'No'"; putlog " Values: Yes, No"; putlog " If set to yes, The first worksheet will contain a table of contents"; putlog " With a single link to each worksheet in the workbook. If both this"; putlog " option and the Contents option are set, then the index of worksheets"; putlog " will be the second worksheet and it will be named 'Worksheets'."; putlog " "; putlog "Missing_Align: Default Value 'right'"; putlog " Values: left, center, right"; putlog " Sets the alignment for missing values."; putlog " By default a dataMissing style is created from the data style, the "; putlog " dataMissing style is created in 3 versions. One for each justification."; putlog " When a style has the string 'data' in it's name, the value is checked."; putlog " If it is missing then the dataMissing style will be used instead."; putlog " A dataMissing style can be provided in the style. If found, the tagset"; putlog " will use that style as a basis for the 3 dataMissing styles."; putlog " "; putlog "Auto_SubTotals: Default Value 'No'"; putlog " Values: Yes, No"; putlog " If yes, this option causes a subtotal formula to be placed in the"; putlog " subtotal cells on the last table row of the Print Procedure's tables."; putlog " WARNING: This does not work with Sum By. It only works if the "; putlog " totals only happen once per table."; putlog " "; putlog "Convert_Percentages: Default Value 'Yes'"; putlog " Remove percent symbol, apply excel percent format, and multiply by 100."; putlog " This causes percentage values to display as numeric percentages in Excel."; putlog " If 'No' percentage values will be untouched and will appear as"; putlog " strings in Excel."; putlog " Will be deprecated in a future release when it is no longer needed."; putlog " "; putlog "Currency_symbol: Default Value '$'"; putlog " Used for detection of currency formats and for "; putlog " removing those symbols so excel will like them."; putlog " Will be deprecated in a future release when it is"; putlog " no longer needed. "; putlog " "; putlog "Currency_format: Default Value 'Currency'"; putlog " The currency format specified for excel to use."; putlog " Another possible value is 'Euro Currency'."; putlog " Will be deprecated in a future release when it is"; putlog " no longer needed. "; putlog " "; putlog "Decimal_separator: Default Value '.'"; putlog " The character used for the decimal point."; putlog " Will be deprecated in a future release when it is no longer needed."; putlog " "; putlog "Thousands_separator: Default Value ','"; putlog " The character used for indicating thousands in numeric values."; putlog " Used for removing those symbols from numerics so excel will like them."; putlog " Will be deprecated in a future release when it is no longer needed."; putlog " "; putlog "Numeric_Test_Format: Default Value '12.'"; putlog " Used for determining if a value is numeric or not."; putlog " Other useful values might be COMMAX or NLNUM formats."; putlog " Will be deprecated in a future release when it is no longer needed."; putlog " "; putlog "Minimize_Style: Default Value 'No'"; putlog " If set to 'yes' the stylesheet will be filtered so that only the most."; putlog " necessary definitions are printed. This can have the reverse effect"; putlog " if style attribute over rides are used on the proc statements."; putlog " It is best to define a new style with the appropriate over rides built in."; putlog " The proc can use the new style, but without individual attribute over-rides."; putlog " The result is a much smaller style section. - In that case, this option"; putlog " should be set to No."; putlog " "; putlog "Skip_Space: Default Value '1,0,1,1,1'"; putlog " This option controls how much space follows the different types of output"; putlog " that can occur within a worksheet. The number given is a multiplier that"; putlog " is used against the height given in the Parskip style element. In the"; putlog " absence of the Parskip style element the font size from the Header"; putlog " style is used."; putlog " "; putlog " The parameters of this option are positional, but not all values must be"; putlog " specified. The first value is for the space following each table. The"; putlog " second value is the space following bylines. The third is for titles, "; putlog " the fourth is for footers and the last value is the space following"; putlog " pagebreaks if the do_pagebreak option is turned on and a pagebreak style"; putlog " element exists."; putlog " "; putlog " The default values are:"; putlog " Table : 1 "; putlog " Byline : 0 "; putlog " Title : 1 "; putlog " Footer : 1 "; putlog " PageBreak : 1 "; putlog " "; putlog "PageBreaks: Default Value 'No'"; putlog " If set to 'yes' page breaks will be inserted into the stylesheet. The"; putlog " pagebreak style element will be used to define what that pagebreak looks"; putlog " like. A sample style definition looks like this."; putlog " "; putlog " style pagebreak /"; putlog " cellheight=8"; putlog " foreground=black"; putlog ' tagattr="HorzStripe";'; putlog " "; putlog " It is not necessary to have a style element. In it's absence a blank row"; putlog " will be inserted."; putlog " "; putlog " "; putlog " "; putlog "Using Style Elements"; putlog " "; putlog " There are a few style attributes that can be used to good effect"; putlog " in the ExcelXP tagset. The TagAttr attribute can be used to add"; putlog " formula's and formats. CellWidth can be used to control the column"; putlog " widths. Flyover can be used to add comments to cells."; putlog " A URL on a cell will cause it to be a link."; putlog " Additionally, Formulas can be given as the actual data values."; putlog " "; putlog " An alternative to setting widths is to use the cellwidth / width"; putlog " Style attribute. This value will be used regardless of any other"; putlog " column width calculations. Cellwidth can be specified in any of"; putlog " These units. Inch, centimeter, millimeter, points or pixel."; putlog " If a cellwidth for column is given more than once, the first width"; putlog " is used. This can happen when there is more than one table per"; putlog " worksheet."; putlog " "; putlog " The following example shows formulas as data, comment text on a"; putlog " header, and absolute control of a columns width."; putlog " "; putlog " "; putlog " ods tagsets.excelxp file='test.xml' options(zoom='75');"; putlog " "; putlog " data test;"; putlog " length a b 8 c $20;"; putlog " input a b c $;"; putlog " cards;"; putlog " 1 2 3"; putlog " 2 3 =RC[-2]+RC[-1]"; putlog " 3 4 =RC[-2]+RC[-1]"; putlog " . . =SUM(R[-3]C:R[-1]C)"; putlog " ;"; putlog " run;"; putlog " "; putlog " "; putlog " proc print noobs;"; putlog " var a b;"; putlog ' var c / style(head) = {flyover="Hello World"}";'; putlog " style(data) = {cellwidth=50pt};"; putlog " run;"; putlog " "; putlog " ods tagsets.excelxp close;"; putlog " "; putlog " "; putlog "TagAttr Style Element: Default Value ''"; putlog " Values: or"; putlog " "; putlog " "; putlog " "; putlog " "; putlog " This is not a tagset option but a style attribute that the tagset will"; putlog " use to get formula's and column formats. The format and formula's given"; putlog " must be a valid to excel. The rotation must be a valid angle for text."; putlog " 90 through -90."; putlog " The Type should be General, String, Number, or DateTime. Excel is case sensitive"; putlog " It should be unecessary to specify type except when DateTime is being used."; putlog " Even when doing numbers as text format"; putlog " "; putlog " A single value without a keyword is interpreted as a format."; putlog " A formula, format and rotation can be specified together with keywords."; putlog " There should be no spaces except for those between the two values"; putlog " The keyword and value must be separated by a ':'"; putlog " tagattr='format:###.## formula:SUM(R[-4]C:R[-1]C rotate:90')."; putlog " "; putlog " Text ---- @"; putlog " "; putlog " Type = DateTime"; putlog " Time - 0:00 ---- Short Time"; putlog " Time - 0:00:0 ---- h:mm:s"; putlog " Time - 00:00.0 ---- mm:ss.0"; putlog " Time - 00:00 AM ---- Medium Time"; putlog " Time - 12:00:00 AM ---- Long Time"; putlog " Time - 24:00:00 ---- [h]:mm/:s"; putlog " Time - 3/14/01 1:30 PM ---- m/d/yy\ h:mm\ AM/P "; putlog " "; putlog " Percentage - 6 decimals ---- 0.00000%"; putlog " Special - zip code ---- 00000"; putlog " Special - zip code + 4 ---- 00000\-0000"; putlog " "; putlog " Scientific ---- Scientific"; putlog " Scientific - 4 decimals ---- 0.0000E+00 "; putlog " Fraction - As sixteenths (8/16) ---- #\ ??/1"; putlog " "; putlog " "; putlog "Margins:"; putlog " Margins can be set two ways. With the system options or through styles."; putlog " The system options win over the style settings. In the style, the margins"; putlog " must be set on the 'Body' style element."; putlog " "; putlog " Setting the margins with the options statement is the easiest."; putlog " "; putlog " options topmargin=1in"; putlog " bottommargin=1in"; putlog " leftmargin=.5in"; putlog " rightmargin=.5in;"; putlog " "; putlog " As as style definition, the Body element might look like this. This "; putlog " approach is more reusable since each program that uses the style "; putlog " automatically gets the margins"; putlog " "; putlog " style Body from Body /"; putlog " topmargin=.5in"; putlog " leftmargin=.25in;"; putlog " "; putlog "Options Center/NoCenter"; putlog " Setting the center/nocenter option will cause titles and bylines to be"; putlog " centered or left justified within the worksheet. If nocenter is set"; putlog " the cells are not merged. This causes excel to do a better job of"; putlog " printing when the text is long."; putlog " "; putlog " options nocenter;"; trigger config_debug_help; putlog "=============================================================================="; end; define event compile_regexp; unset $currency_sym; unset $decimal_separator; unset $thousands_separator; /*=========================================================*/ /* If the currency symbol, decimal separator, or thousands */ /* separator are Perl regular expression metacharacters, */ /* then they must be escaped with a backslash. */ /*=========================================================*/ set $currency_sym "\" $currency; set $currency_sym "\$" /if ^$currency_sym; set $decimal_separator "\." /if ^$decimal_separator; set $thousands_separator "," /if ^$thousands_separator; set $punctuation $currency_sym $thousands_separator "%"; set $integer_re "\d+"; set $sign_re "[+-]?"; set $group_re "\d{1,3}(?:" $thousands_separator "\d{3})*"; set $whole_re "(?:" $group_re "|" $integer_re ")"; set $exponent_re "[eE]" $sign_re $integer_re; set $fraction_re "(?:" $decimal_separator "\d*)"; set $real_re "(?:" $whole_re $fraction_re "|" $fraction_re $integer_re "|" $whole_re ")"; set $percent_re $sign_re $real_re "\%"; set $scinot_re $sign_re "(?:" $real_re $exponent_re "|" $real_re ")"; set $cents_re "(?:" $decimal_separator "\d\d)"; set $money_re $sign_re $currency_sym "(?:" $whole_re $cents_re "|" $cents_re "|" $whole_re ")"; set $number_re "/^(?:" $real_re "|" $percent_re "|" $scinot_re "|" $money_re ")\Z/"; eval $number prxparse($number_re); /* $test1 = "format:0_);[Red]\(0\) formula:=RC[-1]-50 formula:=RC[-1]-50 */ /* +format:0_);[Red]\(0\) formula:=ABS(RC[-1]*10)"; */ /* $test2 = "Formula:'Response Results'!j2"; */ set $tagattr_regexp "/^([Ff][Oo][Rr][Mm][Aa][Tt]:|[Ff][Oo][Rr][Mm][Uu][Ll][Aa]:|"; set $tagattr_regexp $tagattr_regexp "[Rr][Oo][Tt][Aa][Tt][Ee]:|[Tt][Yy][Pp][Ee])/"; eval $tagattr_regex prxparse($tagattr_regexp); eval $cm_re prxparse('/[0-9]*[cC][mM]/'); eval $in_re prxparse('/[0-9]*[iI][nN]/'); eval $mm_re prxparse('/[0-9]*[mM][mM]/'); eval $px_re prxparse('/[0-9]*px/'); eval $pt_re prxparse('/[0-9]*pt/'); end; define event sheet_interval; unset $tmp_interval; do /if value; set $tmp_interval value; else; set $tmp_interval tagset_alias; done; trigger set_sheet_interval /if $tmp_interval; end; define event set_sheet_interval; trigger worksheet finish /if $tmp_interval; set $tmp_interval lowcase($tmp_interval); /*-------------------------------------------------------eric-*/ /*-- Table and bygroup are really synonymous. The others --*/ /*-- do not currently work, because excel doesn't handle --*/ /*-- multiple tables per worksheet. It might later so --*/ /*-- I'm leaving the code here. --*/ /*----------------------------------------------------21Jul03-*/ do /if $tmp_interval in ('table', 'bygroup'); /*do /if $tmp_interval in ('table', 'page', 'proc', 'bygroup', 'none');*/ set $sheet_interval $tmp_interval; else /if cmp($tmp_interval, "page"); set $sheet_interval 'page'; else /if cmp($tmp_interval, "proc"); set $sheet_interval 'proc'; else /if cmp($tmp_interval, "none"); set $sheet_interval 'none'; trigger worksheet finish; else; set $sheet_interval 'table'; done; end; /*-----------------------------------------------------------eric-*/ /*-- Procs that we shouldn't create new worksheets for. --*/ /*--------------------------------------------------------19Aug03-*/ define event proc_list; /* Init proc list */ set $proclist['Gchart'] '1'; set $proclist['Gplot'] '1'; set $proclist['Gmap'] '1'; set $proclist['Gcontour'] '1'; set $proclist['G3d'] '1'; set $proclist['Gbarline'] '1'; set $proclist['Gareabar'] '1'; set $proclist['Gradar'] '1'; set $proclist['Gslide'] '1'; set $proclist['Ganno'] '1'; end; define event nls_numbers; unset $currency; unset $currency_format; unset $decimal_separator; unset $thousands_separator; unset $test_format; /*-------------------------------------------------------eric-*/ /*-- The currency symbol for the US is $, set it --*/ /*-- accordingly. It is used for detection of currency --*/ /*-- formats and for removing those symbols so excel will --*/ /*-- like them. --*/ /*----------------------------------------------------14Jun04-*/ set $currency $options['CURRENCY_SYMBOL'] /if $options; set $currency "$" /if ^$currency; set $currency_compress $currency ","; /*-------------------------------------------------------eric-*/ /*-- Currency or Euro Currency. The format to use for currency.--*/ /*----------------------------------------------------14Jun04-*/ set $currency_format $options['CURRENCY_FORMAT'] /if $options; set $currency_format "Currency" /if ^$currency_format; /*set $currency_format "Euro Currency" /if ^$currency_format;*/ set $decimal_separator $options['DECIMAL_SEPARATOR'] /if $options; set $decimal_separator '\.' /if ^$decimal_separator; set $thousands_separator $options['THOUSANDS_SEPARATOR'] /if $options; set $thousands_separator ',' /if ^$thousands_separator; /*-------------------------------------------------------eric-*/ /*-- The format to use for checking values. If the value --*/ /*-- is missing after applying this format then it is not a --*/ /*-- number. Default is '12.' NLNUM12. may be needed in --*/ /*-- other locals. --*/ /*----------------------------------------------------14Jun04-*/ set $test_format $options['NUMERIC_TEST_FORMAT'] /if $options; set $test_format '12.' /if ^$test_format; end; define event bad_fonts; set $bad_fonts[] 'Times'; set $bad_fonts[] 'Times Roman'; set $bad_fonts[] 'Trebuchet MS'; end; /*-----------------------------------------------------------eric-*/ /*-- We need at least these styles. If the style doesn't --*/ /*-- provide them we will create empty style definitions. --*/ /*--------------------------------------------------------14Jun04-*/ define event needed_styles; set $missing_styles['data'] 'True'; set $missing_styles['header'] 'True'; set $missing_styles['footer'] 'True'; set $missing_styles['rowheader'] 'True'; set $missing_styles['table'] 'True'; set $missing_styles['batch'] 'True'; set $missing_styles['byline'] 'True'; set $missing_styles['systemfooter'] 'True'; set $missing_styles['systemtitle'] 'True'; set $missing_styles['contentprocname'] 'True'; end; define event skip_multipliers; eval $skip_factor['Title'] 1; eval $skip_factor['Footer'] 1; eval $skip_factor['Table'] 1; eval $skip_factor['Byline'] 0; eval $skip_factor['PageBreak'] 1; end; define event row_heights; set $row_heights['Title'] '0'; set $row_heights['Footer'] '0'; set $row_heights['Table_head'] '0'; set $row_heights['Table'] '0'; set $row_heights['Byline'] '0'; set $row_heights['PageBreak'] '0'; set $row_heights['Parskip'] '0'; end; define event options_setup; /*--------------------------------------------------------------*/ /* options should exist, but avoid bad resolution if it doesn't */ /* This only happens in SAS 9.1.2 and earlier */ /* 9.1 and 9.1.2 have a known bug. If an array/dictionary is */ /* Accessed in a set, and the array is not defined, the value */ /* resolves to the subscript. Not good, but avoidable. */ /* Normally just putting '/if $options' on the set will fix it */ /* but this logic becomes needlessly complex. This is cleaner */ /*--------------------------------------------------------------*/ set $options['test'] "test" /if ^$options; trigger set_config_debug_options; /* trigger set_papersize; */ do /if $options['SHEET_INTERVAL']; set $tmp_interval lowcase($options['SHEET_INTERVAL']); trigger set_sheet_interval; /* this is so we can detect when it is set. */ /* each time it's set we should close the current */ /* worksheet if one is open */ unset $options['SHEET_INTERVAL']; done; do /if $options['SHEET_NAME']; do /if cmp($options['SHEET_NAME'], "none"); unset $sheet_name; else; set $sheet_name $options['SHEET_NAME']; done; done; do /if $options['SHEET_LABEL']; do /if cmp($options['SHEET_LABEL'], "none"); unset $sheet_label; else; set $sheet_label $options['SHEET_LABEL']; done; done; /*-------------------------------------------------------eric-*/ /*-- make the page do landscape setup. --*/ /*----------------------------------------------------14Jun04-*/ unset $landscape; set $landscape "True" /if cmp($options['ORIENTATION'], 'landscape'); do /if ^$landscape; set $landscape "True" /if cmp(orientation, 'landscape'); done; do /if $options['EMBEDDED_TITLES']; do /if cmp($options['EMBEDDED_TITLES'], "yes"); set $embedded_titles "true" ; else; unset $embedded_titles; done; else; do /if cmp(embedded_titles, "yes"); set $embedded_titles "true" ; else; unset $embedded_titles; done; done; do /if $options['EMBEDDED_FOOTNOTES']; do /if cmp($options['EMBEDDED_FOOTNOTES'], "yes"); set $embedded_footnotes "true" ; else; unset $embedded_footnotes; done; done; unset $one_embedded_title_set; do /if $options['EMBED_TITLES_ONCE']; do /if cmp($options['EMBED_TITLES_ONCE'], "yes"); set $one_embedded_title_set "true" ; else; unset $one_embedded_title_set; done; done; do /if $options['PRINT_HEADER']; set $print_header $options['PRINT_HEADER']; else; unset $print_header; done; do /if $options['PRINT_FOOTER']; set $print_footer $options['PRINT_FOOTER']; else; unset $print_footer; done; do /if $options['ZOOM']; set $zoom $options['ZOOM']; eval $zoom inputn($zoom, 'BEST'); do /if missing($zoom); eval $zoom 100; done; set $pageLayoutZoom "0"; done; do /if $options['SCALE']; set $scale $options['SCALE']; eval $scale inputn($scale, 'BEST'); do /if missing($scale); eval $scale 100; done; done; eval $print_dpi 300; do /if $options['DPI']; set $print_dpi $options['DPI']; eval $print_dpi inputn($print_dpi, 'BEST'); do /if missing($print_dpi); eval $print_dpi 300; done; done; /*-------------------------------------------------------eric-*/ /*-- fit to page and fitwidth and height. width or height --*/ /*-- implies fit to page. --*/ /*----------------------------------------------------12May06-*/ unset $fittopage; do /if $options['FITTOPAGE'] ; do /if cmp($options ['FITTOPAGE'] , "yes"); set $fittopage "True"; else; unset $fittopage; done; done; eval $pages_fitwidth 1; do /if $options['PAGES_FITWIDTH']; set $pages_fitwidth $options['PAGES_FITWIDTH']; eval $pages_fitwidth inputn($pages_fitwidth, 'BEST'); do /if missing($pages_fitwidth); eval $pages_fitwidth 1; done; set $fittopage "True"; done; eval $pages_fitheight 1; do /if $options['PAGES_FITHEIGHT']; set $pages_fitheight $options['PAGES_FITHEIGHT']; eval $pages_fitheight inputn($pages_fitheight, 'BEST'); do /if missing($pages_fitheight); eval $pages_fitheight 1; done; set $fittopage "True"; done; unset $left_to_right; do /if $options['PAGE_ORDER_ACROSS']; do /if cmp($options['PAGE_ORDER_ACROSS'], "yes"); set $left_to_right "true" ; else; unset $left_to_right; done; done; unset $center_vertical; do /if $options['CENTER_VERTICAL']; do /if cmp($options['CENTER_VERTICAL'], "yes"); set $center_vertical "true" ; else; unset $center_vertical; done; done; unset $center_horizontal; do /if $options['CENTER_HORIZONTAL']; do /if cmp($options['CENTER_HORIZONTAL'], "yes"); set $center_horizontal "true" ; else; unset $center_horizontal; done; done; unset $do_contents; do /if $options['CONTENTS']; do /if cmp($options['CONTENTS'], "yes"); set $do_contents "true" ; else; unset $do_contents; done; done; unset $do_tabs; do /if $options['INDEX']; do /if cmp($options['INDEX'], "yes"); set $do_tabs "true" ; else; unset $do_tabs; done; done; set $missing_align 'r'; do /if $options['MISSING_ALIGN']; do /if cmp($options['MISSING_ALIGN'], "right" ); set $missing_align "r" ; else /if cmp($options['MISSING_ALIGN'], "center" ); set $missing_align "c" ; else /if cmp($options['MISSING_ALIGN'], "left" ); set $missing_align "l" ; done; done; set $do_contents_contents 'True'; set $do_contents_tabs 'True'; unset $do_workbooks; do /if $options['CONTENTS_FILE']; unset $do_contents_contents; unset $do_contents_tabs; set $toc lowcase($options['CONTENTS_FILE']); do /if contains($toc, "all" ); set $do_contents_contents 'True'; set $do_contents_tabs 'True'; set $do_workbooks 'True'; done; do /if contains($toc, "contents" ); set $do_contents_contents 'True'; done; do /if contains($toc, "index" ); set $do_contents_tabs 'True'; done; do /if contains($toc, "workbooks" ); set $do_workbooks 'True'; done; done; unset $row_repeat; do /if $options['ROW_REPEAT']; do /if cmp($options['ROW_REPEAT'], "none"); unset $row_repeat; else; set $row_repeat $options['ROW_REPEAT'] ; done; done; do /if $options['COLUMN_REPEAT']; do /if cmp($options['COLUMN_REPEAT'], "none"); unset $col_repeat; else; set $col_repeat $options['COLUMN_REPEAT'] ; done; done; set $formulas "true" ; do /if $options['FORMULAS']; do /if cmp($options['FORMULAS'], "no"); unset $formulas; else; set $formulas "true" ; done; else; do /if cmp(formulas, "no"); unset $formulas; else; set $formulas "true" ; done; done; do /if $options['FROZEN_HEADERS']; unset $tmp; set $tmp $options['FROZEN_HEADERS']; do /if cmp($tmp, "yes"); set $frozen_headers "true" ; else /if cmp($tmp, "no"); unset $frozen_headers; else; eval $frozen_header_count inputn($tmp, 'BEST'); do /if ^missing($frozen_header_count); set $frozen_headers "true"; else; unset $frozen_header_count; unset $frozen_headers; done; done; else; do /if cmp(frozen_headers, "yes"); set $frozen_headers "true" ; else /if cmp(frozen_headers, 'no'); unset $frozen_headers; else; eval $frozen_header_count inputn(frozen_headers, 'BEST'); do /if ^missing($frozen_header_count); set $frozen_headers "true"; else; unset $frozen_header_count; unset $frozen_headers; done; done; done; do /if $options['FROZEN_ROWHEADERS']; unset $tmp; set $tmp $options['FROZEN_ROWHEADERS']; do /if cmp($tmp, "yes"); set $frozen_rowheaders "true" ; else /if cmp($tmp, "no"); unset $frozen_rowheaders; else; eval $frozen_rowheader_count inputn($tmp, 'BEST'); do /if ^missing($frozen_rowheader_count); set $frozen_rowheaders "true"; else; unset $frozen_rowheader_count; unset $frozen_rowheaders; done; done; else; do /if cmp(frozen_rowheaders, "yes"); set $frozen_rowheaders "true" ; else /if cmp(frozen_rowheaders, 'no'); unset $frozen_rowheaders; else; eval $frozen_rowheader_count inputn(frozen_rowheaders, 'BEST'); do /if ^missing($frozen_rowheader_count); set $frozen_rowheaders "true"; else; unset $frozen_rowheader_count; unset $frozen_rowheaders; done; done; done; do /if $options['CONVERT_PERCENTAGES']; do /if cmp($options['CONVERT_PERCENTAGES'], "no"); unset $convert_percentages; else; set $convert_percentages "true" ; done; else; do /if cmp(convert_percentages, "no"); unset $convert_percentages; else; set $convert_percentages "true" ; done; done; do /if $options['AUTOFILTER']; do /if cmp($options['AUTOFILTER'], "none"); unset $autofilter; else; set $autofilter $options['AUTOFILTER'] ; done; else; do /if cmp(autofilter, "none"); unset $autofilter; else; set $autofilter autofilter ; done; done; eval $widthfudge 0.75; do /if $options['WIDTH_FUDGE']; do /if cmp($options['WIDTH_FUDGE'], "none"); unset $widthFudge; else; set $widthFudge $options['WIDTH_FUDGE']; eval $widthFudge inputn($widthFudge, 'BEST'); ; done; else; do /if cmp(width_fudge, "none"); unset $widthFudge; else /if width_fudge; eval $widthFudge inputn(width_Fudge, 'BEST'); ; done; done; unset $widthpoints; do /if $options['WIDTH_POINTS']; do /if cmp($options['WIDTH_POINTS'], "none"); unset $widthPoints; else; set $widthPoints $options['WIDTH_POINTS']; eval $widthPoints inputn($widthPoints, 'BEST'); done; else; do /if cmp(width_points, "none"); unset $widthPoints; else /if width_points; eval $widthPoints inputn(width_points, 'BEST'); ; done; done; unset $default_widths; do /if $options['DEFAULT_COLUMN_WIDTH']; do /if cmp($options['DEFAULT_COLUMN_WIDTH'], "none"); unset $default_widths; else; set $defwid $options['DEFAULT_COLUMN_WIDTH']; trigger set_default_widths; done; else; do /if cmp(default_column_width, "none"); unset $default_widths; else /if default_column_width; set $defwid default_column_width; trigger set_default_widths; done; done; do /if $options['ABSOLUTE_COLUMN_WIDTH']; do /if cmp($options['ABSOLUTE_COLUMN_WIDTH'], "none"); unset $absolute_widths; else; set $abswid $options['ABSOLUTE_COLUMN_WIDTH']; trigger set_absolute_widths; done; done; unset $auto_sub_totals; do /if $options['AUTO_SUBTOTALS']; set $auto_sub_totals 'True' /if cmp($options['AUTO_SUBTOTALS'], 'yes'); done; /*-------------------------------------------------------eric-*/ /*-- autofilter table is 1 unless there are multiple tables --*/ /*-- per worksheet. --*/ /*----------------------------------------------------23Dec04-*/ eval $autofilter_table 1; do /if $sheet_interval ^in ('table', 'bygroup'); do /if $options['AUTOFILTER_TABLE']; set $tmp $options['AUTOFILTER_TABLE']; eval $autofilter_table inputn($tmp, 'BEST'); done; do /if missing($autofilter_table); eval $autofilter_table 1; done; done; do /if $options['MINIMIZE_STYLE']; do /if cmp($options['MINIMIZE_STYLE'], 'yes'); set $minimize_style 'True'; else; unset $minimize_style; done; done; do /if $options['PAGEBREAKS']; do /if cmp($options['PAGEBREAKS'], 'yes'); set $do_pagebreaks 'True'; else; unset $do_pagebreaks; done; done; do /if $options['SUPPRESS_BYLINES']; do /if cmp($options['SUPPRESS_BYLINES'], 'yes'); set $no_bylines 'True'; else; unset $no_bylines; done; done; do /if $options['SKIP_SPACE']; set $skip_spaces $options['SKIP_SPACE']; /*---------------------------------------------------eric-*/ /*-- This is a bug. We shouldn't even be here if --*/ /*-- options['skip_space'] has no value. --*/ /*------------------------------------------------17Aug05-*/ /* stop /if ^$skip_spaces; */ do /if index($skip_spaces, ','); set $skip_space scan($skip_spaces, 1, ','); eval $count 1; do /while !cmp($skip_space, ' '); do /if $count = 1; eval $skip_factor['Table'] inputn(strip($skip_space), 'BEST'); else /if $count = 2; eval $skip_factor['Byline'] inputn(strip($skip_space), 'BEST'); else /if $count = 3; eval $skip_factor['Title'] inputn(strip($skip_space), 'BEST'); else /if $count = 4; eval $skip_factor['Footer'] inputn(strip($skip_space), 'BEST'); else /if $count = 5; eval $skip_factor['PageBreak'] inputn(strip($skip_space), 'BEST'); done; eval $count $count + 1; set $skip_space scan($skip_spaces, $count, ','); done; else /if $skip_spaces; eval $skip_factor['Table'] inputn(strip($skip_spaces), 'BEST'); done; done; do /if $options['ROW_HEIGHTS']; set $row_height_str $options['ROW_HEIGHTS']; do /if index($row_height_str, ','); set $row_height scan($row_height_str, 1, ','); eval $count 1; do /while !cmp($row_height, ' '); do /if $count = 1; set $row_heights['Table_head'] strip($row_height); else /if $count = 2; set $row_heights['Table'] strip($row_height); else /if $count = 3; set $row_heights['Byline'] strip($row_height); else /if $count = 4; set $row_heights['Title'] strip($row_height); else /if $count = 5; set $row_heights['Footer'] strip($row_height); else /if $count = 6; set $row_heights['PageBreak'] strip($row_height); else /if $count = 7; set $row_heights['Parskip'] strip($row_height); done; eval $count $count + 1; set $row_height scan($row_height_str, $count, ','); done; else /if strip($row_height_str); eval $row_heights['Table_head'] strip($row_height_str); done; do /if $debug_level >= 1; putlog "ROW HEIGHTS!!!!: " htmlclass; iterate $row_heights; do /while _name_; putlog _name_ ": " _value_; next $row_heights; done; done; done; eval $row_height_fudge 4; do /if $options['ROW_HEIGHT_FUDGE']; set $row_height_fudge $options['ROW_HEIGHT_FUDGE']; eval $row_height_fudge inputn($row_height_fudge, 'BEST'); ; done; do /if $options['AUTOFIT_HEIGHT']; do /if cmp($options['AUTOFIT_HEIGHT'], 'yes'); set $do_auto_fit_height 'True'; else; unset $do_auto_fit_height; done; done; end; define event set_papersize; set $papersize_name getoption('PAPERSIZE'); set $papersize '0'; set $papersize $papersize_names[$papersize_name]; end; define event set_default_widths; unset $default_widths; /* KEEP it FROM GROWING */ do /if index($defwid, ','); set $def_width scan($defwid, 1, ','); eval $count 1; do /while !cmp($def_width, ' '); set $default_widths[] strip($def_width); eval $count $count + 1; set $def_width scan($defwid, $count, ','); done; else; set $default_widths[] strip($defwid); done; end; define event set_absolute_widths; unset $absolute_widths; /* KEEP it FROM GROWING */ do /if index($abswid, ','); set $abs_width scan($abswid, 1, ','); eval $count 1; do /while !cmp($abs_width, ' '); set $absolute_widths[] strip($abs_width); eval $count $count + 1; set $abs_width scan($abswid, $count, ','); done; else; set $absolute_widths[] strip($abswid); done; end; /*-----------------------------------------------------------eric-*/ /*-- This one happens when options(...) are given on the ods markup--*/ /*-- statement. It only happens after the first statement though.--*/ /*--------------------------------------------------------14Jun04-*/ define event options_set; trigger set_options; end; define event set_options; trigger nls_numbers; trigger compile_regexp; trigger options_setup; trigger documentation; end; define event initialize; trigger row_heights; trigger skip_multipliers; trigger set_options; trigger bad_fonts; trigger needed_styles; trigger set_border_styles; set $align getoption('center'); set $sheet_names['#$%!^&&&&'] 'junk'; trigger proc_list; set $weight[] '0'; set $weight[] '1'; set $weight[] '2'; set $weight[] '3'; set $font_size["xx-small"] "8"; set $font_size["x-small"] "10"; set $font_size["small"] "12"; set $font_size["medium"] "14"; set $font_size["large"] "16"; set $font_size["x-large"] "18"; set $font_size["xx-large"] "20"; eval $numberOfWorksheets 0; eval $format_override_count 0; unset $got_global_margins; /*------------------------------------------------------------------eric--*/ /* if we were given an alias try to set the sheet interval with it. */ /* it should be none, proc, bygroup, page, or table. The default is page */ /*----------------------------------------------------------------4Jul 03-*/ set $tmp_interval tagset_alias; trigger set_sheet_interval; trigger set_paper_size_index; end; define event set_paper_size_index; set $papersize_spelling getoption('PAPERSIZE'); set $papersize '0'; /* 1 Letter 8 1/2" x 11" 2 Letter small 8 1/2" x 11" 3 Tabloid 11" x 17" 4 Ledger 17" x 11" 5 Legal 8 1/2" x 14" 6 Statement 5 1/2" x 8 1/2" 7 Executive 7 1/4" x 10 1/2" 8 A3 297mm x 420mm 9 A4 210mm x 297mm 10 A4 small 210mm x 297mm 11 A5 148mm x 210mm 12 B4 250mm x 354mm 13 B5 182mm x 257mm 14 Folio 8 1/2" x 13" 15 Quarto 215mm x 275mm 16 10" x 14" 17 11" x 17" 18 Note 8 1/2" x 11" 19 #9 Envelope 3 7/8" x 8 7/8" 20 #10 Envelope 4 1/8" x 9 1/2" 21 #11 Envelope 4 1/2" x 10 3/8" 22 #12 Envelope 4 3/4" x 11" 23 #14 Envelope 5" x 11 1/2" 24 C Sheet 17" x 22" 25 D Sheet 22" x 34" 26 E Sheet 34" x 44" 27 DL Envelope 110mm x 220mm 28 C5 Envelope 162mm x 229mm 29 C3 Envelope 324mm x 458mm 30 C4 Envelope 229mm x 324mm 31 C6 Envelope 114mm x 162mm 32 C65 Envelope 114mm x 229mm 33 B4 Envelope 250mm x 353mm 34 B5 Envelope 176mm x 250mm 35 B6 Envelope 125mm x 176mm 36 Italy Envelope 110mm x 230mm 37 Monarch Envelope 3 7/8" x 7 1/2" 38 6 3/4 Envelope 3 5/8" x 6 1/2" 39 US Standard Fanfold 14 7/8" x 11" 40 German Std. Fanfold 8 1/2" x 12" 41 German Legal Fanfold 8 1/2" x 13" */ set $papersize_names['Letter'] '1'; set $papersize_names['LETTER'] '1'; set $papersizes['8 1/2" x 11"'] '1'; set $papersize_names['Letter small'] '2'; set $papersizes ['8 1/2" x 11"'] '2'; set $papersize_names['Tabloid'] '3'; set $papersize_names['Tabloid Extra'] '3'; set $papersize_names['Tabloid Maximum'] '3'; set $papersize_names['Tabloid Plus'] '3'; set $papersize_names['TABLOID'] '3'; set $papersize_names['TABLOID EXTRA'] '3'; set $papersize_names['TABLOID MAXIMUM'] '3'; set $papersize_names['TABLOID PLUS'] '3'; set $papersizes['11" x 17"'] '3'; set $papersize_names['Ledger'] '4'; set $papersize_names['LEDGER'] '4'; set $papersizes['17" x 11"'] '4'; set $papersize_names['Legal'] '5'; set $papersize_names['LEGAL'] '5'; set $papersizes['8 1/2" x 14"'] '5'; set $papersize_names['Statement'] '6'; set $papersize_names['STATEMENT'] '6'; set $papersizes['5 1/2" x 8 1/2"'] '6'; set $papersize_names['Executive'] '7'; set $papersize_names['EXECUTIVE'] '7'; set $papersizes['7 1/4" x 10 1/2"'] '7'; set $papersize_names['A3'] '8'; set $papersize_names['ISO A3'] '8'; set $papersizes['297mm x 420mm'] '8'; set $papersize_names['A4'] '9'; set $papersize_names['ISO A4'] '9'; set $papersizes['210mm x 297mm'] '9'; set $papersize_names['A4 small'] '10'; set $papersizes['210mm x 297mm'] '10'; set $papersize_names['A5'] '11'; set $papersize_names['ISO A5'] '11'; set $papersizes['148mm x 210mm'] '11'; set $papersize_names['B4'] '12'; set $papersize_names['ISO B4'] '12'; set $papersizes['250mm x 354mm'] '12'; set $papersize_names['B5'] '13'; set $papersize_names['ISO B5'] '13'; set $papersizes['182mm x 257mm'] '13'; set $papersize_names['Folio'] '14'; set $papersize_names['FOLIO'] '14'; set $papersizes['8 1/2" x 13"'] '14'; set $papersize_names['Quarto'] '15'; set $papersize_names['QUARTO'] '15'; set $papersizes['215mm x 275mm'] '15'; set $papersizes['10" x 14"'] '16'; set $papersizes['11" x 17"'] '17'; set $papersize_names['Note'] '18'; set $papersize_names['NOTE'] '18'; set $papersizes['8 1/2" x 11"'] '18'; set $papersize_names['#9 Envelope'] '19'; set $papersizes['3 7/8" x 8 7/8"'] '19'; set $papersize_names['#10 Envelope'] '20'; set $papersizes['4 1/8" x 9 1/2"'] '20'; set $papersize_names['#11 Envelope'] '21'; set $papersizes['4 1/2" x 10 3/8"'] '21'; set $papersize_names['#12 Envelope'] '22'; set $papersizes['4 3/4" x 11"'] '22'; set $papersize_names['#14 Envelope'] '23'; set $papersizes['5" x 11 1/2"'] '23'; set $papersize_names['C Sheet'] '24'; set $papersizes['17" x 22"'] '24'; set $papersize_names['D Sheet'] '25'; set $papersizes['22" x 34"'] '25'; set $papersize_names['E Sheet'] '26'; set $papersizes['34" x 44"'] '26'; set $papersize_names['DL Envelope'] '27'; set $papersize_names['DL ENVELOPE'] '27'; set $papersizes['110mm x 220mm'] '27'; set $papersize_names['C5 Envelope'] '28'; set $papersize_names['C5 ENVELOPE'] '28'; set $papersizes['162mm x 229mm'] '28'; set $papersize_names['C3 Envelope'] '29'; set $papersize_names['C3 ENVELOPE'] '29'; set $papersizes['324mm x 458mm'] '29'; set $papersize_names['C4 Envelope'] '30'; set $papersize_names['C4 ENVELOPE'] '30'; set $papersizes['229mm x 324mm'] '30'; set $papersize_names['C6 ENVELOPE'] '31'; set $papersize_names['C6 Envelope'] '31'; set $papersizes['114mm x 162mm'] '31'; set $papersize_names['C65 Envelope'] '32'; set $papersize_names['C65 ENVELOPE'] '32'; set $papersizes['114mm x 229mm'] '32'; set $papersize_names['B4 Envelope'] '33'; set $papersize_names['B4 ENVELOPE'] '33'; set $papersizes['250mm x 353mm'] '33'; set $papersize_names['B5 Envelope'] '34'; set $papersize_names['B5 ENVELOPE'] '34'; set $papersizes['176mm x 250mm'] '34'; set $papersize_names['B6 Envelope'] '35'; set $papersize_names['B6 ENVELOPE'] '35'; set $papersizes['125mm x 176mm'] '35'; set $papersize_names['Italy Envelope'] '36'; set $papersize_names['ITALY ENVELOPE'] '36'; set $papersize_names['Envelope Italy'] '36'; set $papersize_names['ENVELOPE ITALY'] '36'; set $papersizes['110mm x 230mm'] '36'; set $papersize_names['Monarch Envelope'] '37'; set $papersize_names['MONARCH ENVELOPE'] '37'; set $papersizes['3 7/8" x 7 1/2"'] '37'; set $papersize_names['6 3/4 Envelope'] '38'; set $papersize_names['6 3/4 ENVELOPE'] '38'; set $papersizes['3 5/8" x 6 1/2"'] '38'; set $papersize_names['US Standard Fanfold'] '39'; set $papersize_names['US STANDARD FANFOLD'] '39'; set $papersizes['14 7/8" x 11"'] '39'; set $papersize_names['German Std. Fanfold'] '40'; set $papersize_names['GERMAN STD. FANFOLD'] '40'; set $papersizes['8 1/2" x 12"'] '40'; set $papersize_names['GERMAN LEGAL FANFOLD'] '41'; set $papersizes['8 1/2" x 13"'] '41'; end; define event break_margin; unset $margin_unit; eval $match prxmatch($margin_re, $margin); set $margin_size prxposn($margin_re, 1, $margin) ; /* regex gives back strings... We don't want 0 margins */ /* might as well break too, (bif) since we don't want anything else */ unset $margin_size /breakif inputn($margin_size, "BEST") = 0; set $margin_unit prxposn($margin_re, 2, $margin) ; set $margin_unit lowcase($margin_unit) /if $margin_unit; set $margin_unit "in" /if !$margin_unit; end; /*----------------------------------------------------------eric-*/ /*-- get the margin options. They win over the style margins --*/ /*-- set on the body style element. Convert them to inches --*/ /*-- without units. If there is no margin option convert the --*/ /*-- margin we got from the style earlier on... --*/ /*-------------------------------------------------------22Aug05-*/ define event get_global_Margins; break /if $got_global_margins; set $got_global_margins "True"; eval $margin_re prxparse('(([0-9]*[\.]?[0-9]*)(IN|CM)?)') ; set $margin getoption('leftmargin'); trigger break_margin; do /if $margin_size; set $marginleft $margin_size; else /if $marginleft; /* this is the margin from the style */ set $convert_this_size $marginleft; trigger convert_to_inches; set $marginleft $converted_this_size; done; set $margin getoption('rightmargin'); trigger break_margin; do /if $margin_size; set $marginright $margin_size; else /if $marginright; /* this is the margin from the style */ set $convert_this_size $marginright; trigger convert_to_inches; set $marginright $converted_this_size; done; set $margin getoption('topmargin'); trigger break_margin; do /if $margin_size; set $marginTop $margin_size; else /if $marginTop; /* this is the margin from the style */ set $convert_this_size $marginTop; trigger convert_to_inches; set $marginTop $converted_this_size; done; set $margin getoption('bottommargin'); trigger break_margin; do /if $margin_size; set $marginBottom $margin_size; else /if $marginBottom; /* this is the margin from the style */ set $convert_this_size $marginBottom; trigger convert_to_inches; set $marginBottom $converted_this_size; done; unset $margin; unset $margin_size; unset $margin_unit; end; /*----------------------------------------------------------eric-*/ /*-- I haven't figured out where to put papersize. If we can --*/ /*-- find the XML for it these values could be plugged in and --*/ /*-- then Excel would be set up to print for the appropriate --*/ /*-- page size. --*/ /*-------------------------------------------------------22Aug05-*/ define event paperHeightWidth; /* tranwrd just makes the regex easier. Get rid of optional quotes */ set $papersize tranwrd($papersize, '"', " "); set $papersize tranwrd($papersize, "'", " "); /* could be centimeters, could be quoted, or not... default is supposedly inches but could be installation dependent. (8in 11in); ('8in', '11in'); ("8in", "11in"); ("8", "11"); */ eval $re prxparse('( *([0-9]+) *(IN|CM)* *[,]+ *([0-9]+) *(IN|CM)*.*)') ; eval $match prxmatch($re, $papersize); set $pwidth prxposn($re, 1, $papersize) ; set $pwidth_unit prxposn($re, 2, $papersize) ; set $pwidth_unit lowcase($pwidth_unit) /if $pwidth_unit; set $pwidth_unit "in" /if !$pwidth_unit; set $pheight prxposn($re, 3, $papersize) ; set $pheight_unit prxposn($re, 4, $papersize) ; set $pheight_unit lowcase($pheight_unit) /if $pheight_unit; set $pheight_unit "in" /if !$pheight_unit; /* Only if they are non-zero */ set $paper_height $pheight $pheight_unit /if $pheight; set $paper_width $pwidth $pwidth_unit /if $pwidth; unset $papersize; unset $re; unset $pwidth; unset $pwidth_unit; unset $pheight; unset $pheight_unit; end; define event contents; start: trigger doc; finish: trigger write_to_contents; trigger doc; end; define event doc; start: eval $numberOfWorksheets 0; put '" CR CR; putl ''; putl ''; putl ''; do /if operator; putl '' operator ''; putl '' operator ''; done; putl '' date 'T' time ''; putl '' date 'T' time ''; putl 'SAS Institute Inc. http://www.sas.com'; putl '' saslongversion ''; putl ''; finish: putl ''; end; define event embedded_stylesheet; start: unset $currency_styles; unset $percentage_styles; unset $style_list; eval $format_override_count 0; unset $have_parskip_style ; open style /if cmp(dest_file, 'body'); put "" nl; trigger alignstyle; finish: open style /if cmp(dest_file, 'body'); trigger Need_parskip_style /if ^$have_parskip_style; trigger Need_pagebreak_style /if ^$have_pagebreak_style; trigger Need_datamissing_style; close; put "" nl /if cmp(dest_file, 'contents'); end; define event Need_datamissing_style; put $$datamissing_style; unset $$datamissing_style; end; define event Need_parskip_style; set $parskip_style_name 'parskip'; putl ''; end; define event Need_pagebreak_style; do /if cmp($row_heights['PageBreak'], '0'); set $row_heights['PageBreak'] '8'; done; putl ''; end; define event doc_body; start: /*---------------------------------------------------eric-*/ /*-- Get the margins, Just in case the body style --*/ /*-- element wasn't defined. --*/ /*------------------------------------------------22Aug05-*/ trigger get_global_margins; open worksheet; finish: /* close the worksheet if it's open. We're shutting down */ trigger worksheet; close; /*---------------------------------------------------eric-*/ /*-- Write the end of the styles section. We have to --*/ /*-- wait until now in case there were style ver-rides --*/ /*-- written during worksheet creation. --*/ /*------------------------------------------------4Jul 03-*/ open style; putl '' nl; close; /* write out the style definitions and delete the stream */ put $$style; delstream style; /* write out the table of contents and delete the stream */ do /if $do_contents; trigger contents_worksheet start; put $$contents_worksheet; trigger contents_worksheet finish; done; do /if $do_tabs; trigger tabs_worksheet start; put $$tabs_worksheet; trigger tabs_worksheet finish; done; /* do /if contents_name; open cont_contents_worksheet; put $$contents_worksheet nl; open cont_tabs_worksheet; put $$tabs_worksheet nl; close; done; */ delstream contents_worksheet; delstream tabs_worksheet; /* write out the worksheets and delete the stream */ put $$master_worksheet; delstream master_worksheet; end; define event write_to_contents; do /if $do_workbooks; trigger workbooks_worksheet start; put $$workbooks_worksheet nl; trigger workbooks_worksheet finish; done; do /if $do_contents_contents; trigger contents_worksheet start; put $$cont_contents_worksheet nl; trigger contents_worksheet finish; done; do /if $do_contents_tabs; trigger tabs_worksheet start; put $$cont_tabs_worksheet nl; trigger tabs_worksheet finish; done; delstream workbooks_worksheet; delstream cont_contents_worksheet; delstream cont_tabs_worksheet; end; define event workbooks_worksheet; start: putq '' NL; put '' nl; putl '' nl; putl '' nl; finish: put '' nl; put "" nl; end; define event contents_worksheet; start: putq '' NL; put '' nl; finish: put '' nl; put "" nl; end; define event tabs_worksheet; start: do /if ^$do_contents; putq '' NL; else; putq '' NL; done; put '' nl; putl '' nl; putl '' nl; finish: put '' nl; put "" nl; end; define event write_tabs_entry; break /if ^$do_tabs; unset $url; set $url path_url /if path_url; do /if body_url; set $url $url body_url; else; set $url $url body_name; done; set $url $url "#'" $worksheetName "'!A" $content_row; open tabs_worksheet; trigger write_tab_entry; close; open cont_tabs_worksheet; trigger write_tab_entry; close; end; define event write_tab_entry; put '' nl; putq ''; put '' nl; put ''; put ''; put $worksheetName; put ''; put '' nl; end; define event write_wb_entry; break /if ^$do_workbooks; unset $url; set $url path_url /if path_url; do /if body_url; set $url $url body_url; else; set $url $url body_name; done; break /if cmp($last_file, $url); set $last_file $url; open workbooks_worksheet; put '' nl; putq ''; put '' nl; put ''; put ''; put $worksheetName; put ''; put '' nl; close; end; define event contents_entry; break /if ^$do_contents; set $content_values[] value; set $content_class[] lowcase(htmlclass); set $content_level[] toc_level; unset $url; set $url path_url /if path_url; do /if body_url; set $content_url[] $url body_url; else; set $content_url[] $url body_name; done; end; define event write_contents_entries; break /if ^$do_contents; break /if ^$content_values; open contents_worksheet; eval $entry_count 1; do /while $entry_count <= $content_values; trigger write_contents_entry; eval $entry_count $entry_count + 1; done; close; do /if contents_name; open cont_contents_worksheet; eval $entry_count 1; do /while $entry_count <= $content_values; trigger write_contents_entry; eval $entry_count $entry_count + 1; done; close; done; unset $content_values; unset $content_class; unset $content_level; unset $content_url; end; define event write_contents_entry; /*set $name tranwrd($worksheetName, ' ', '%20');*/ set $url $content_url[$entry_count] "#'" $worksheetName "'!A" $content_row; set $toclevel $content_level[$entry_count]; eval $toclevel inputn($toclevel, 'BEST'); put '' nl; eval $count 0; do /while $toclevel > $count; eval $count $count+1; putq ''; put '' nl; done; put ''; put ''; put $content_values[$entry_count]; put ''; put '' nl; end; define event branch; trigger contents_entry; end; define event proc_branch; unset $proc_label; set $proc_label value; trigger contents_entry; end; define event leaf; trigger contents_entry; end; define event shortstyles; flush; open style /if cmp(dest_file, 'body'); iterate $missing_styles; do /while _name_; set $cell_class _name_; trigger empty_style; next $missing_styles; done; close; unset $cell_class; end; define event empty_style; put ''; /*-------------------------------------------------------eric-*/ /*-- Create a style for missing values that has right justification.--*/ /*----------------------------------------------------23Aug05-*/ do /if cmp(htmlclass, 'data') | cmp(htmlclass, 'dataMissing'); do /if cmp(htmlclass, 'dataMissing'); unset $$datamissing_style; set $datamissing_style_name lowcase(htmlclass); else; set $datamissing_style_name 'datamissing'; break /if $$datamissing_style; done; open datamissing_style; putq ''; putq ''; putq ''; close; open style /if cmp(dest_file, 'body'); done; end; define event align_tag; start: break /if $align_tag; put ''; unset $align_tag; end; define event xl_style_elements; delstream style_elements; open style_elements; set $headerString lowcase(htmlclass); do /if index ($headerString, 'header'); trigger align_tag; unset $headerString; done; putq ' ss:Rotate=' strip($attrs['rotate']) /if $attrs; do /if $vjust; trigger align_tag start; put ' ss:Vertical='; put '"Center"' /if cmp($vjust, 'm'); put '"Top"' /if cmp($vjust, 't'); put '"Bottom"' /if cmp($vjust, 'b'); done; unset $vjust; do /if $just; trigger align_tag start; put ' ss:Horizontal='; put '"Center"' /if cmp($just, 'c'); put '"Left"' /if cmp($just, 'l'); put '"Right"' /if cmp($just, 'r'); put '"Right"' /if cmp($just, 'd'); else /if contains(htmlclass, "SystemTitle") or contains(htmlclass, "SystemFooter") or cmp(htmlclass, "Byline"); do /if ^$just; do /if cmp($align, "center"); trigger align_tag start; put ' ss:Horizontal="Center"'; done; done; done; unset $just; trigger align_tag finish; trigger write_all_borders; trigger font_interior; put '' NL; flush; close; end; define event write_all_borders; /* putlog htmlclass "WIDTHS: " borderwidth ":" borderleftwidth "::" borderrightwidth ":::" bordertopwidth "::::" borderbottomwidth; */ unset $borderwidth; set $borderwidth trimn(borderleftwidth); trigger get_borderwidth; /*putlog "Resolved widths"; putlog "left: " $borderwidth;*/ set $linestyle borderleftstyle; trigger get_borderstyle; set $border_position "Left"; set $bordercolor borderleftcolor; trigger write_borderstyle; unset $borderwidth; set $borderwidth trimn(borderrightwidth); trigger get_borderwidth; /*putlog "right: " $borderwidth;*/ set $linestyle borderrightstyle; trigger get_borderstyle; set $border_position "Right"; set $bordercolor borderrightcolor; trigger write_borderstyle; unset $borderwidth; set $borderwidth trimn(bordertopwidth); trigger get_borderwidth; /*putlog "top: " $borderwidth;*/ set $linestyle bordertopstyle; trigger get_borderstyle; set $border_position "Top"; set $bordercolor bordertopcolor; trigger write_borderstyle; unset $borderwidth; set $borderwidth trimn(borderbottomwidth); trigger get_borderwidth; /*putlog "bottom: " $borderwidth;*/ set $linestyle borderbottomstyle; trigger get_borderstyle; set $border_position "Bottom"; set $bordercolor borderbottomcolor; trigger write_borderstyle; /* put ''; */ putl '' /if $borders; unset $borders; end; define event get_borderwidth; /* putlog "GET_BORDERWIDTH:"; putlog "borderwidth: " "|" borderwidth "|"; putlog "$borderwidth: " "|" $borderwidth "|"; */ set $borderwidth trimn(borderwidth) /if ^$borderwidth; /* putlog "borderwidth: " ":" borderwidth ":"; putlog "$borderwidth: " ":" $borderwidth ":"; */ do /if ^$borderwidth; eval $borderwidth 0; else; set $convert_this_size $borderwidth; trigger convert_to_scale; do /if $converted_this_size > 4; eval $borderwidth 4; else; eval $borderwidth $converted_this_size; done; done; end; define event get_borderstyle; set $borderstyle borderstyle /if ^$borderstyle; set $borderstyle $borderstyles[$borderstyle]; do /if ^$borderstyle; set $borderstyle "Continuous" /if $borderwidth; done; end; define event set_border_styles; set $borderstyles ["DOTTED"] "Dot"; set $borderstyles ["DASHED"] "Dash"; set $borderstyles ["SOLID"] "Continuous"; set $borderstyles ["DOUBLE"] "Double"; set $borderstyles ["GROOVE"] "DashDot"; set $borderstyles ["RIDGE"] "DashDotDot"; set $borderstyles ["INSET"] "SlashDashDot"; set $borderstyles ["OUTSET"] "Continuous"; set $borderstyles ["HIDDEN"] "None"; end; define event write_borderstyle; break /if $borderwidth < 1; do /if ^$borders; putl ''; set $borders "TRUE"; done; put ''; end; define event cell_format; /*------------------------------------------------------------eric-*/ /*-- General is the default and it's the best we can do for now. --*/ /*---------------------------------------------------------4Jul 03-*/ put '' NL; end; define event font_interior; do /if any(font_face, font_size, font_weight, foreground); put ' 0; eval $comma $comma+1; eval $comma_count $comma_count + 1; do /if $comma_count = 3; eval $comma_index $comma_index -1; set $fontFace substr($fontFace, 1, $comma_index); stop; done; set $tmp_fontFace substr($tmp_fontFace, $comma); eval $comma index($tmp_fontFace, ","); eval $comma_index $comma_index + $comma ; done; putq ' ss:FontName=' strip($fontFace); unset $fontFace; done; do /if font_size; trigger get_font_height; putq ' ss:Size=' $font_height; else; eval $font_height 0; done; eval $row_height 0; do /if $font_height; eval $row_height $font_height + $row_height_fudge; done; do /if $row_height = 0; eval $row_height 12 + $row_height_fudge; done; /*---------------------------------------------------eric-*/ /*-- Save away the point size for the data to be used --*/ /*-- when the column widths are calculated. --*/ /*------------------------------------------------5Oct 04-*/ do /if cmp(htmlclass, 'data'); stop /if $data_point_size; set $data_point_size $font_height; done; do /if contains(htmlclass, "SystemTitle"); do /if cmp($row_heights['Title'], '0'); set $row_heights['Title'] $row_height; done; done; do /if contains(htmlclass, "SystemFooter"); do /if cmp($row_heights['Footer'], '0'); set $row_heights['Footer'] $row_height; done; done; do /if cmp(htmlclass, "byline"); do /if cmp($row_heights['Byline'], '0'); set $row_heights['Byline'] $row_height; done; done; do /if cmp(htmlclass, 'header'); stop /if $header_point_size; set $header_point_size $font_height; do /if cmp($row_heights['Table_head'], '0'); set $row_heights['Table_head'] $row_height; done; do /if cmp($row_heights['Parskip'], '0'); set $row_heights['Parskip'] $row_height; done; done; put ' ss:Italic="1"' / if cmp(FONT_STYLE, 'italic'); put ' ss:Bold="1"' / if cmp(FONT_WEIGHT, 'bold'); putq ' ss:Color=' FOREGROUND /if ^cmp(foreground, 'transparent'); put ' />' NL; do /if $debug_level >= 1; putlog "CLASS: " htmlclass; iterate $row_heights; do /while _name_; putlog _name_ ": " _value_; next $row_heights; done; done; done; /*-------------------------------------------------------eric-*/ /*-- If this is the pagebreak style, we need to extract a --*/ /*-- pattern and set the colors. Until there is a pattern --*/ /*-- style attribute tagattr will have to do. --*/ /*----------------------------------------------------16Aug05-*/ do /if cmp(htmlclass, "pagebreak"); stop /if ^any(background, tagattr, foreground); put '' NL; else; do /if background; put '' NL; done; done; end; define event get_font_height; /* find out if the font size is in points */ eval $pt_pos index(FONT_SIZE, "pt") - 1; do /if $pt_pos > 0; /* if it is a point size take off the unit */ set $size substr(font_size, 1, $pt_pos); else; /* translate small, medium, large into numbers. */ set $size $font_size[FONT_SIZE]; done; set $convert_this_size $size; trigger convert_to_points; eval $font_height $converted_this_size; end; define event pagebreak; break /if ^$worksheet_started; break /if cmp($sheet_interval, 'Table'); break /if ^$do_pagebreaks; do /if cmp($sheet_interval, "bygroup"); do /if ^cmp($last_byval, $byvars[$byval_name]); break; done; done; set $height $row_heights['PageBreak']; eval $worksheet_row $worksheet_row + 1; putq '' nl; put '' nl; put '' nl; break /if ^$skip_factor['pageBreak']; set $skip_multiplier $skip_factor['PageBreak']; trigger parskip; end; /*-----------------------------------------------------------eric-*/ /*-- set $skip_multiplier to the NUMBER you want to multiply --*/ /*-- the height by. 0 will result in no row. otherwise the --*/ /*-- point size of the parskip height will be multiplied and --*/ /*-- used to create this one row. --*/ /*--------------------------------------------------------17Aug05-*/ define event parskip; set $skip_multiplier '0' /if ^$skip_multiplier; eval $skip_multiplier inputn($skip_multiplier, 'BEST'); break /if ^$skip_multiplier | missing($skip_multiplier); do /if cmp($row_heights['Parskip'], '0'); set $row_heights['Parskip'] '10'; done; set $height $row_heights['Parskip']; eval $height inputn($height, 'BEST') * $skip_multiplier; eval $worksheet_row $worksheet_row + 1; putq '' nl; put '' nl; put '' nl; unset $skip_multiplier; end; define event output; start: trigger worksheet /if cmp($sheet_interval, "table"); trigger worksheet /if cmp($sheet_interval, "proc"); finish: trigger worksheet /if cmp($sheet_interval, "table"); /* for proc freq.... */ do /if cmp($proc_name, 'Freq'); trigger worksheet /if cmp($sheet_interval, "bygroup"); done; end; define event proc; start: /* in case embedded_titles or convert_percents has changed. */ trigger options_setup; set $align getoption('center'); set $proc_name name; /*-----------------------------------------------------eric-*/ /*-- We don't really want to start a worksheet here --*/ /*-- because the titles haven't come out yet. So we'll --*/ /*-- just be sure to turn off the worksheet when the proc --*/ /*-- ends. --*/ /*--------------------------------------------------3Jul 03-*/ finish: trigger worksheet /if cmp($sheet_interval, "proc"); end; /*-----------------------------------------------------------eric-*/ /*-- Redefine this event if you want to change the way --*/ /*-- worksheets get labeled. --*/ /*--------------------------------------------------------4Jul 03-*/ define event worksheet_label; break /if $sheet_name; set $over_ride_sheetName strip(Override_sheetname); set $worksheetName Override_sheetname /breakif $Over_ride_sheetname; do /if label; set $label label; else; do /if $proc_label; set $label $proc_label; else; set $label proc_name; done; done; /*---------------------------------------------------eric-*/ /*-- Try to create a reasonable worksheet label based --*/ /*-- on the type of sheet interval we are using. --*/ /*------------------------------------------------4Jul 03-*/ set $worksheetName $sheet_label ' '; do /if cmp($sheet_interval, 'none'); set $worksheetName 'Job ' /if ^$sheet_label; set $worksheetName $worksheetName $numberOfWorksheets ' - ' $label; else /if cmp($sheet_interval, 'proc'); do /if $proc_label; set $worksheetName $proc_label /if ^$sheet_label; else; set $worksheetName 'Proc ' /if ^$sheet_label; set $worksheetName $worksheetName total_Proc_count ' - ' $label; done; else /if cmp($sheet_interval, 'page'); set $worksheetName 'Page ' /if ^$sheet_label; set $worksheetName $worksheetName total_page_count ' - ' $label; else /if cmp($sheet_interval, 'bygroup'); do /if ^$byval_name; do /if $sheet_label; set $worksheetName $label ' ' $byGroupLabel; else; set $worksheetName 'By ' ; set $worksheetName $worksheetName $numberOfWorksheets ' ' $byGroupLabel; done; else; do /if $sheet_label; set $sheet_label $last_byval; else; set $worksheetName $worksheetName $byval_name '=' $last_byval; done; done; else /if cmp($sheet_interval, 'table'); set $worksheetName 'Table ' /if ^$sheet_label; set $worksheetName $worksheetName $numberOfWorksheets ' - ' $label; done; /*-------------------------------------------------------eric-*/ /*-- If we have a bygroup label then we should use it. --*/ /*----------------------------------------------------21Jul03-*/ /* do /if $byGroupLabel; set $worksheetName 'By ' $numberOfWorksheets ' ' $byGroupLabel ' - ' $label; done; */ unset $byGroupLabel; unset $label; end; /*-----------------------------------------------------------eric-*/ /*-- make sure the worksheet label doesn't have any invalid --*/ /*-- characters and that it is not too long. The length can --*/ /*-- be no longer than 31. --*/ /*--------------------------------------------------------4Jul 03-*/ define event clean_worksheet_label; set $worksheetName $sheet_name /if $sheet_name; /*set $worksheetName compress($worksheetName, "/\?*:'"); */ set $worksheetName tranwrd($worksheetName, '/', ' '); set $worksheetName tranwrd($worksheetName, '\', ' '); set $worksheetName tranwrd($worksheetName, '?', ' '); set $worksheetName tranwrd($worksheetName, '*', ' '); set $worksheetName tranwrd($worksheetName, ':', ' '); set $worksheetName tranwrd($worksheetName, "'", ' '); do /if $debug_level = -2; putlog "SOURCE EVENT" ":" event_name; putlog "WORKSHEET NAME" ":" $worksheetname; iterate $sheet_names; do /while _name_; putlog _name_ ": " _value_; next $sheet_names; done; done; eval $name_count 0; do /if $sheet_names[$worksheetName]; eval $name_count $sheet_names[$worksheetName] + 0; eval $name_count $name_count + 1; eval $sheet_names[$worksheetName] $name_count; else; eval $sheet_names[$worksheetName] 1; done; eval $available_length 31; do /if $name_count; set $count_str $name_count; eval $available_length 31 - (length($count_str) + 1); unset $count_str; done; eval $worksheetNameLength length($worksheetName); do /if $worksheetNameLength > $available_length; set $worksheetName substr($worksheetName, 1, $available_length); done; do /if $name_count; set $worksheetName $worksheetName ' ' $name_count; done; end; define event set_print_repeats; trigger set_row_repeat; trigger set_col_repeat; end; define event set_row_repeat; unset $row_repeat_start; unset $row_repeat_end; break /if ^$row_repeat; do /if $debug_level > 0; putlog "==============================================================================="; putlog "Event Name" event_name; putlog "Possible Row Start" $proc_name ": " $possible_row_repeat_start " : " $possible_row_repeat_end; putlog "ROW_REPEAT" $row_repeat; putlog "==============================================================================="; done; do /if cmp($row_repeat, 'header'); eval $row_repeat_start $possible_row_repeat_start; eval $row_repeat_end $possible_row_repeat_end; else; do /if index($row_repeat, '-'); eval $row_repeat_start inputn(scan($row_repeat, 1, '-'), 'BEST'); eval $row_repeat_end inputn(scan($row_repeat, 2, '-'), 'BEST'); else; eval $row_repeat_start inputn($row_repeat, 'BEST'); done; done; end; define event set_col_repeat; unset $col_repeat_start; unset $col_repeat_end; break /if ^$col_repeat; do /if $debug_level > 0; putlog "Possible Col Start" $proc_name ": " $possible_col_repeat_start " : " $possible_col_repeat_end; done; do /if cmp($col_repeat, 'header'); eval $col_repeat_start $possible_col_repeat_start; eval $col_repeat_end $possible_col_repeat_end; else; do /if index($col_repeat, '-'); eval $col_repeat_start inputn(scan($col_repeat, 1, '-'), 'BEST'); eval $col_repeat_end inputn(scan($col_repeat, 2, '-'), 'BEST'); else; eval $col_repeat_start inputn($col_repeat, 'BEST'); done; done; end; define event Print_repeats; /* */ trigger set_print_repeats; do /if any($row_repeat_start, $col_repeat_start); put "" nl; put '' nl; put "" nl; done; end; define event worksheet_tab; break /if $tabname_is_done; do /if ^$content_row; do /if $worksheet_row; set $content_row $worksheet_row; else; set $content_row "1"; done; done; trigger write_tabs_entry; trigger write_wb_entry; set $tabname_is_done "True"; end; define event do_papersize; trigger set_papersize; /* look up the papersize by spelling here... */ put '' $papersize '' nl; end; define event worksheet; start: do /if $proclis