! sylk.sqc - SYLK procedure library for SQR
! Copyright (C) 2004 Fastenal Company
!
! This program is free software; you can redistribute it and/or modify
! it under the terms of the GNU General Public License as published by
! the Free Software Foundation; either version 2 of the License, or
! (at your option) any later version.
!
! This program is distributed in the hope that it will be useful,
! but WITHOUT ANY WARRANTY; without even the implied warranty of
! MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
! GNU General Public License for more details.
!
! You should have received a copy of the GNU General Public License
! along with this program; if not, write to the Free Software
! Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
!
! Author: Jim Womeldorf (jim@viking-ship.com)
!         2366 Siewers Spring Rd
!         Decorah, IA 52101
!
! $Id: sylk.sqc,v 1.5 2004/07/23 21:06:20 rayo Exp $
!
! $Log: sylk.sqc,v $
! Revision 1.5  2004/07/23 21:06:20  rayo
! Added Jim's physical address
!
! Revision 1.4  2004/07/13 18:40:03  rayo
! fixed Jim's e-mail address
!
! Revision 1.3  2004/07/13 18:21:41  rayo
! added copyright notice
!
!
! Program Descr: This is an include file intended to be used by programmers who
! need to produce a text file which will be loaded by Excel.
!
! It allows for some limited formatting which can be used to produce files which
! will appear in a readable condition without user intervention.  
!
! The fonts size (8, 10, 12) and bold/italic condition can be specified for entire 
! rows and columns.  When the font is specified for a row the height of the row is 
! adjusted to be proportional to the font.
!
! Column width can be specified.  The number of width-units represents the number
! of numeric digits that will appear in a column in 'Regular' print.
! 
! Columns can be formatted as General, Text, Date (several formats available, see
! Sylk-Format-Cell-To-Date for details), and Number (from 0 to 13 digits to the 
! right of the decimal).  
! 
! Formulas can be placed in cells.  A procedure is provided to convert a numeric 
! column to its alpha equivalent.  The formulas are the Excel formulas except that
! cell references must have preceeding and following spaces.  (See Sylk-Write-Formula
! for details)
!  
! If the number of columns or rows exceeds the limits of Excel (256 x 65536)
! an error message is placed in cell A1 which is also set to Font size 12 bold so 
! it will not be overlooked.                                                       
!
! 
! For any output files that may exceed the row limits of Excel (earlier versions of 
! Excel allowed 16384 rows, the current version allows 65536 rows) it will be necessary 
! for the programmer to do a little work as follows:
!
! The program should check the value of the Row number as it is incremented.  When it 
! approaches the desired maximum the following should be done:
!   1. Announce the name of the next file, in the first column of the last row printed such as:
!           'Data incomplete.  Continued in C:\temp\ThisIsMySylkOutput_23453_ws2.xls'
!    2. The file should be closed
!    3. Open the file with the next name in the naming sequence
!    4. The row number should be reset
!    5. Heading and formatting information should be  written to the file
!    6. Printing continues
!
! If you need column headings for successive months you may wish to use use Sylk-Write-Month-Year-Column-Headings
!   which will place headings in columns for a date range.
!
! For files that may become large may I suggest using the following procedure which will produce consistent names:
!
!   Sylk-Get-Worksheet-Filename($BaseFileName, #ProcessInstance, #WorksheetNumber, $WorksheetFileName)
!
!   where, for example
!   $BaseFileName = 'C:\Temp\ThisIsMySylkOutput' 
!   #ProcessInstance = 23453 
!   #WorksheetNumber = 1 
!   and $WorksheetFileName returns the value 'C:\Temp\ThisIsMySylkOutput_23453_ws1.xls'
!   
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
! Procedures whose names end in 'DoNotCall' are intended to be internal procedures that are called only
!   by the other procedures in this file
!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!The following procedures are available.
!NOTE: When setting the Format and/or Font using more than one
!   method (by row, by column, by cell) each cell will have the
!   LAST format/font applied to it.
!
! Sylk-Initialize-File($FileName, #FileNumber)
! Sylk-Close-File(#FileNumber)

! Sylk-Format-Cell-To-Date(#FileNumber, #Row, #Col, $DateFormat, $Justification)
! Sylk-Format-Cell-To-General(#FileNumber, #Row, #Col, $Justification)
! Sylk-Format-Cell-To-Number(#FileNumber, #Row, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Sylk-Format-Cell-To-Percentage(#FileNumber, #Row, #Col, #Decimals, $Justification)
! Sylk-Format-Cell-To-Text(#FileNumber, #Row, #Col, $Justification)

! Sylk-Format-Column-To-Date(#FileNumber, #Col, $DateFormat, $Justification)
! Sylk-Format-Column-To-General(#FileNumber, #Col, $Justification)
! Sylk-Format-Column-To-Number(#FileNumber, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Sylk-Format-Column-To-Percentage(#FileNumber, #Col, #Decimals, $Justification)
! Sylk-Format-Column-To-Text(#FileNumber, #Col, $Justification)

! Sylk-Format-Row-To-Date(#FileNumber, #Row, $DateFormat, $Justification)
! Sylk-Format-Row-To-General(#FileNumber, #Row, $Justification)
! Sylk-Format-Row-To-Number(#FileNumber, #Row, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Sylk-Format-Row-To-Percentage(#FileNumber, #Row, #Decimals, $Justification)
! Sylk-Format-Row-To-Text(#FileNumber, #Row, $Justification)

! Sylk-Set-Cell-Font(#FileNumber, #Row, #Col, #Size, $BoldItalic)
! Sylk-Set-Column-Font(#FileNumber, #Col, #Size, $BoldItalic)
! Sylk-Set-Column-Width(#FileNumber, #Col, #Width)    

! Sylk-Set-Row-Font(#FileNumber, #Row, #Size, $BoldItalic)              

! Sylk-Write-Date(#FileNumber, #Row, #Col, $Date)
! Sylk-Write-Formula(#FileNumber, #Row, #Col, $Formula)
! Sylk-Write-Month-Year-Column-Headings(#FileNumber, #Row, #StartCol, #RepeatCols, $StartDate, $EndDate, $Format, :#NextCol)
! Sylk-Write-Number-Suppress-Zero(#FileNumber, #Row, #Col, #Number)
! Sylk-Write-Number(#FileNumber, #Row, #Col, #Number)
! Sylk-Write-Text(#FileNumber, #Row, #Col, $Text)        

! Sylk-Column-Number-To-Alpha(#ColumnIn, $YNColAbsolute, :$ColumnOut)
! Sylk-Get-Margin-Formula($CostCell, $PriceCell, :$Formula)
! Sylk-Get-Margin(#Cost, #Price, :#Margin)
! Sylk-Get-Safe-Division-Formula($Dividend, $Divisor, :$Formula)
! Sylk-Get-Worksheet-Filename($BaseFileName, #ProcessInstance, #WorksheetNumber, :$WorksheetFileName)
! Sylk-Row-Number-Column-Number-To-Alpha(#RowIn, $YNRowAbsolute, #ColumnIn, $YNColAbsolute, :$RowColumnOut)
!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!****************************************************************
! Procedure: Sylk-Initialize-File($FileName, #FileNumber)
! Desc: Opens an output file and writes information necessary for
! all files.
! $FileName is the output filepath and name (probably with .xls
! extension)
! #FileNumber is the file number to be used for this file.
! If not the file cannot be opened the program stops with a 
! message in the log.
!****************************************************************
begin-procedure Sylk-Initialize-File($FileName, #FileNumber)
    let $_SylkErrorMessage = 'Sylk-Initialize-File'
    let #Exists = exists($FileName)
    if #Exists = 0
        let #Result = delete($FileName)
        if #Result <> 0
            show 'Unable to remove existing file named ' $FileName
            stop quiet
        end-if
    end-if
    open $FileName as #FileNumber FOR-WRITING RECORD=1000:vary Status=#open
    !must initialize SylkWriteDataDoNotCall
    do SylkWriteDataDoNotCall(-1, 0, 0, '')
    If #open <> 0
        do SylkErrorDoNotCall
        Show 'Error Opening File ' $FileName ' for Writing'
        Stop Quiet
    End-if
    write #FileNumber from 'ID;PWXL;N;E'                                                     
    write #FileNumber from 'P;PGeneral'                                                      
    write #FileNumber from 'P;P@'                                                            
    write #FileNumber from 'P;P0'                                                            
    write #FileNumber from 'P;P0.0'                                                          
    write #FileNumber from 'P;P0.00'                                                         
    write #FileNumber from 'P;P0.000'                                                        
    write #FileNumber from 'P;P0.0000'                                                       
    write #FileNumber from 'P;P0.00000'                                                      
    write #FileNumber from 'P;P0.000000'                                                     
    write #FileNumber from 'P;P0.0000000'                                                    
    write #FileNumber from 'P;P0.00000000'                                                   
    write #FileNumber from 'P;P0.000000000'                                                  
    write #FileNumber from 'P;P0.0000000000'                                                     
    write #FileNumber from 'P;P0.00000000000'                                                    
    write #FileNumber from 'P;P0.000000000000'                                                   
    write #FileNumber from 'P;P0.0000000000000'                                                  
    !thousands separators
    write #FileNumber from 'P;P#,##0'                                                            
    write #FileNumber from 'P;P#,##0.0'                                                          
    write #FileNumber from 'P;P#,##0.00'                                                         
    write #FileNumber from 'P;P#,##0.000'                                                        
    write #FileNumber from 'P;P#,##0.0000'                                                       
    write #FileNumber from 'P;P#,##0.00000'                                                      
    write #FileNumber from 'P;P#,##0.000000'                                                     
    write #FileNumber from 'P;P#,##0.0000000'                                                    
    write #FileNumber from 'P;P#,##0.00000000'                                                   
    write #FileNumber from 'P;P#,##0.000000000'                                                  
    write #FileNumber from 'P;P#,##0.0000000000'                                                     
    write #FileNumber from 'P;P#,##0.00000000000'                                                    
    write #FileNumber from 'P;P#,##0.000000000000'                                                   
    write #FileNumber from 'P;P#,##0.0000000000000'                                                  
    !with $
    write #FileNumber from 'P;P$0'                                                            
    write #FileNumber from 'P;P$0.0'                                                          
    write #FileNumber from 'P;P$0.00'                                                         
    write #FileNumber from 'P;P$0.000'                                                        
    write #FileNumber from 'P;P$0.0000'                                                       
    write #FileNumber from 'P;P$0.00000'                                                      
    write #FileNumber from 'P;P$0.000000'                                                     
    write #FileNumber from 'P;P$0.0000000'                                                    
    write #FileNumber from 'P;P$0.00000000'                                                   
    write #FileNumber from 'P;P$0.000000000'                                                  
    write #FileNumber from 'P;P$0.0000000000'                                                     
    write #FileNumber from 'P;P$0.00000000000'                                                    
    write #FileNumber from 'P;P$0.000000000000'                                                   
    write #FileNumber from 'P;P$0.0000000000000'                                                  
    !thousands separators with $
    write #FileNumber from 'P;P$#,##0'                                                            
    write #FileNumber from 'P;P$#,##0.0'                                                          
    write #FileNumber from 'P;P$#,##0.00'                                                         
    write #FileNumber from 'P;P$#,##0.000'                                                        
    write #FileNumber from 'P;P$#,##0.0000'                                                       
    write #FileNumber from 'P;P$#,##0.00000'                                                      
    write #FileNumber from 'P;P$#,##0.000000'                                                     
    write #FileNumber from 'P;P$#,##0.0000000'                                                    
    write #FileNumber from 'P;P$#,##0.00000000'                                                   
    write #FileNumber from 'P;P$#,##0.000000000'                                                  
    write #FileNumber from 'P;P$#,##0.0000000000'                                                     
    write #FileNumber from 'P;P$#,##0.00000000000'                                                    
    write #FileNumber from 'P;P$#,##0.000000000000'                                                   
    write #FileNumber from 'P;P$#,##0.0000000000000'                                                  

    !Percentage formats
    write #FileNumber from 'P;P0%'                                                            
    write #FileNumber from 'P;P0.0%'                                                          
    write #FileNumber from 'P;P0.00%'                                                         
    write #FileNumber from 'P;P0.000%'                                                        
    write #FileNumber from 'P;P0.0000%'                                                       
    write #FileNumber from 'P;P0.00000%'                                                      
    write #FileNumber from 'P;P0.000000%'                                                     
    write #FileNumber from 'P;P0.0000000%'                                                    
    write #FileNumber from 'P;P0.00000000%'                                                   
    write #FileNumber from 'P;P0.000000000%'                                                  
    write #FileNumber from 'P;P0.0000000000%'                                                     
    write #FileNumber from 'P;P0.00000000000%'                                                    
    write #FileNumber from 'P;P0.000000000000%'                                                   
    write #FileNumber from 'P;P0.0000000000000%'                                                  
    !date formats
    write #FileNumber from 'P;Pd\-mmm\-yyyy'
    write #FileNumber from 'P;Pdd\-mmm\-yyyy'
    write #FileNumber from 'P;Pd\-mmm\-yy'
    write #FileNumber from 'P;Pdd\-mmm\-yy'
    write #FileNumber from 'P;Pm/d/yy'
    write #FileNumber from 'P;Pm/d/yyyy'
    write #FileNumber from 'P;Pmm/dd/yy'
    write #FileNumber from 'P;Pmm/dd/yyyy'
    !can add more date formats here
    write #FileNumber from 'P;FArial;M200'
    write #FileNumber from 'P;FArial;M200'
    write #FileNumber from 'P;FArial;M200'
    write #FileNumber from 'P;FArial;M200'    
    !size 8 
    write #FileNumber from 'P;EArial;M160'     
    write #FileNumber from 'P;EArial;M160;SI'  
    write #FileNumber from 'P;EArial;M160;SB'  
    write #FileNumber from 'P;EArial;M160;SBI' 
    !size 10
    write #FileNumber from 'P;EArial;M200'     
    write #FileNumber from 'P;EArial;M200;SI'  
    write #FileNumber from 'P;EArial;M200;SB'  
    write #FileNumber from 'P;EArial;M200;SBI' 
    !size 12
    write #FileNumber from 'P;EArial;M240'     
    write #FileNumber from 'P;EArial;M240;SI'  
    write #FileNumber from 'P;EArial;M240;SB'  
    write #FileNumber from 'P;EArial;M240;SBI' 
    write #FileNumber from 'F;P0;DG0G8;M255'                                                 
    write #FileNumber from 'O;L;D;V0;K47;G100 0.001'                                         
end-procedure ! Sylk-Initialize-File
                                                               
!****************************************************************
! Procedure: Sylk-Write-Text(#FileNumber, #Row, #Col, $Text)
! Desc: Writes $Text to #Filenumber at row number #Row and 
! column number #Col.
!****************************************************************
begin-procedure Sylk-Write-Text(#FileNumber, #Row, #Col, $Text)        
    let $_SylkErrorMessage = 'Sylk-Write-Text'
    do SylkFixSemicolonsDoNotCall($Text, $Text)
    do SylkStripCharDoNotCall($Text, 13, $Text)
    do SylkStripCharDoNotCall($Text, 10, $Text)
    let $TextData = chr(34) || $Text || chr(34)
    do SylkWriteDataDoNotCall(#FileNumber,#Row, #Col, $TextData)
end-procedure ! Sylk-Write-Text

!****************************************************************
! Procedure: Sylk-Write-Date(#FileNumber, #Row, #Col, $Date)
! Desc: Writes $Date to #Filenumber at row number #Row and 
! column number #Col.
!****************************************************************
begin-procedure Sylk-Write-Date(#FileNumber, #Row, #Col, $Date)
    !Converter  here changes $Date to a number based    upon 1/1/1900 as 1
    !If $Date is earlier than 1/1/1900 then $Date is written to the cell as text
    declare-variable
        date $TheDate $DateOne
    end-declare                
    let $_SylkErrorMessage = 'Sylk-Write-Date'
    if $Date = ''
        let #DateNumber = 0
    else
        let $DateOne = strtodate('01-JAN-1900','dd-mon-yyyy')
        let $TheDate = $Date    
        let #DateNumber = datediff($TheDate, $DateOne, 'DAY') + 1
        if #DateNumber > 59 
            !this corrects the situation where Sylk thinks 1900 was a leap year
            let #DateNumber = #DateNumber + 1
        end-if
    end-if
    if #DateNumber > 0
        do Sylk-Write-Number(#FileNumber,  #Row, #Col, #DateNumber)
    else                                              
        do Sylk-Write-Text(#FileNumber, #Row, #Col, $Date)
    end-if
end-procedure ! Sylk-Write-Date

!****************************************************************
! Procedure: Sylk-Write-Number(#FileNumber, #Row, #Col, #Number)
! Desc: Writes #Number to #Filenumber at row number #Row and 
! column number #Col.
!****************************************************************
begin-procedure Sylk-Write-Number(#FileNumber, #Row, #Col, #Number)
    let $_SylkErrorMessage = 'Sylk-Write-Number'
    let $Data = to_char(#Number)
    do SylkWriteDataDoNotCall(#FileNumber,#Row, #Col, $Data)
end-procedure ! Sylk-Write-Number
                                                                             
!****************************************************************
! Procedure: Sylk-Write-Number-Suppress-Zero(#FileNumber, #Row, #Col, #Number)
! Desc: Writes #Number to #Filenumber at row number #Row and 
! column number #Col.                        
! If #Number = 0 nothing is printed
!****************************************************************
begin-procedure Sylk-Write-Number-Suppress-Zero(#FileNumber, #Row, #Col, #Number)
    let $_SylkErrorMessage = 'Sylk-Write-Number-Suppress-Zero'
    if #Number <> 0
        let $Data = to_char(#Number)
        do SylkWriteDataDoNotCall(#FileNumber,#Row, #Col, $Data)
    end-if
end-procedure ! Sylk-Write-Number-Suppress-Zero
                                                                             
!****************************************************************
! Procedure: Sylk-Set-Row-Font(#FileNumber, #Row, #Size, $BoldItalic)
! Desc: Sets the font of the entire row #Row to #Size. (8, 10, 12)
! If $BoldItalic contains a 'b' or a 'B' the font will be bold.
! If $BoldItalic contains an 'i' or an 'I' the font will be italic.
! If $BoldItalic contains both the font will be bold italic.
!****************************************************************
begin-procedure Sylk-Set-Row-Font(#FileNumber, #Row, #Size, $BoldItalic)              
    let $_SylkErrorMessage = 'Sylk-Set-Row-Font'
    do SylkSetRowColFontDoNotCall(#FileNumber, #Row, #Size, $BoldItalic, 'R')
end-procedure ! Sylk-Set-Row-Font    

!****************************************************************
! Procedure: Sylk-Set-Column-Font(#FileNumber, #Col, #Size, $BoldItalic)
! Desc: Sets the font of the entire column #Colmn to 
! #Size. (8, 10, 12)
! If $BoldItalic contains a 'b' or a 'B' the font will be bold.
! If $BoldItalic contains an 'i' or an 'I' the font will be italic.
! If $BoldItalic contains both the font will be bold italic.
!****************************************************************
begin-procedure Sylk-Set-Column-Font(#FileNumber, #Col, #Size, $BoldItalic)
    let $_SylkErrorMessage = 'Sylk-Set-Column-Font'
    do SylkSetRowColFontDoNotCall(#FileNumber, #Col, #Size, $BoldItalic, 'C')
end-procedure ! Sylk-Set-Column-Font

!****************************************************************
! Procedure: Sylk-Set-Cell-Font(#FileNumber, #Row, #Col, #Size, $BoldItalic)
! Desc: Sets the font of the cell to 
! #Size. (8, 10, 12)
! If $BoldItalic contains a 'b' or a 'B' the font will be bold.
! If $BoldItalic contains an 'i' or an 'I' the font will be italic.
! If $BoldItalic contains both the font will be bold italic.
!****************************************************************
begin-procedure Sylk-Set-Cell-Font(#FileNumber, #Row, #Col, #Size, $BoldItalic)
    let $_SylkErrorMessage = 'Sylk-Set-Cell-Font'
    do SylkGetlFontDoNotCall(#Size, $BoldItalic, $FontOut)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col)     
    let $Txt = $FontOut || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Set-Column-Font

!****************************************************************
! Procedure: Sylk-Set-Column-Width(#FileNumber, #Col, #Width)
! Desc: Sets the width of #Col so it will display #Width
! numeric characters (in 'Regular' print).
!****************************************************************
begin-procedure Sylk-Set-Column-Width(#FileNumber, #Col, #Width)    
    let $_SylkErrorMessage = 'Sylk-Set-Column-Width'
    let $Txt = 'F;W' || to_char(#Col) || ' ' || to_char(#Col) || ' ' || to_char(#Width)
    !this command does not affect the 'row/col' routines
    write #FileNumber from $Txt
end-procedure ! Sylk-Set-Column-Width
                                   
!****************************************************************
! Procedure: Sylk-Format-Column-To-General(#FileNumber, #Col, $Justification)
! Desc: Formats the column #Col to General.
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Column-To-General(#FileNumber, #Col, $Justification)
    do SylkFormatToGeneralDoNotCall($Justification, $Format)
    do SylkCheckColDoNotCall(#FileNumber, #Col, $Col)                 
    let $Txt = $Format || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Column-To-Text

!****************************************************************
! Procedure: Sylk-Format-Column-To-Text(#FileNumber, #Col, $Justification)
! Desc: Formats the column #Col to Text.
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Column-To-Text(#FileNumber, #Col, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Column-To-Text'
    do SylkFormatToTextDoNotCall($Justification, $Format)
    let $Txt = $Format || ';C' || to_char(#Col)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Column-To-Text

!****************************************************************
! Procedure: Sylk-Format-Column-To-Date(#FileNumber, #Col, $DateFormat, $Justification)
! Desc: Formats the column #Col to Date. 
! Data is displayed per $Format which can be any of the following:
!    'd-mmm-yyyy'
!    'd-mon-yyyy'
!    'dd-mmm-yyyy'
!    'dd-mon-yyyy'
!    'd-mmm-yy'                                   
!    'd-mon-yy'                                   
!    'dd-mmm-yy'                               
!    'dd-mon-yy'                               
!    'm/d/yy'                  
!    'm/d/yyyy'                  
!    'mm/dd/yy'
!    'mm/dd/yyyy'
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the format is not legal the program stops with a message 
! in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Column-To-Date(#FileNumber, #Col, $DateFormat, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Column-To-Date'
    do SylkFormatToDateDoNotCall($DateFormat, $Justification, $Format)
    let $Txt = $Format || ';C' || to_char(#Col)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Column-To-Date
             
!****************************************************************
! Procedure: Sylk-Format-Column-To-Number(#FileNumber, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Desc: Formats the column #Col to Number with 
! #Decimals (0 to 13). 
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Column-To-Number(#FileNumber, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Column-To-Number'
    do SylkFormatToNumberDoNotCall(#Decimals, $CommasYN, $DollarsYN, $Justification, $Format)
    let $Txt = $Format || ';C' || to_char(#Col)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Column-To-Number

!****************************************************************
! Procedure: Sylk-Format-Column-To-Percentage(#FileNumber, #Col, #Decimals, $Justification)
! Desc: Formats the column #Col to Percentage with 
! #Decimals (0 to 13). 
! When cells are formatted to percentage a value of 0.15 will appear as 15%.
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Column-To-Percentage(#FileNumber, #Col, #Decimals, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Column-To-Percentage'
    do SylkFormatToPercentageDoNotCall(#Decimals, $Justification, $Format)
    let $Txt = $Format || ';C' || to_char(#Col)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Column-To-Percentage

!****************************************************************
! Procedure: Sylk-Format-Row-To-General(#FileNumber, #Row, $Justification)
! Desc: Formats the Row #Row to General.
! If $Justification is one of GLCR then the Row is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Row-To-General(#FileNumber, #Row, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Row-To-General'
    do SylkFormatToGeneralDoNotCall($Justification, $Format)
    do SylkCheckRowDoNotCall(#FileNumber, #Row, $Row)                 
    let $Txt = $Format || $Row
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Row-To-Text

!****************************************************************
! Procedure: Sylk-Format-Row-To-Text(#FileNumber, #Row, $Justification)
! Desc: Formats the Row #Row to Text.
! If $Justification is one of GLCR then the Row is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Row-To-Text(#FileNumber, #Row, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Row-To-Text'
    do SylkFormatToTextDoNotCall($Justification, $Format)
    let $Txt = $Format || ';R' || to_char(#Row)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Row-To-Text

!****************************************************************
! Procedure: Sylk-Format-Row-To-Date(#FileNumber, #Row, $DateFormat, $Justification)
! Desc: Formats the Row #Row to Date. 
! Data is displayed per $Format which can be any of the following:
!    'd-mmm-yyyy'
!    'd-mon-yyyy'
!    'dd-mmm-yyyy'
!    'dd-mon-yyyy'
!    'd-mmm-yy'                                   
!    'd-mon-yy'                                   
!    'dd-mmm-yy'                               
!    'dd-mon-yy'                               
!    'm/d/yy'                  
!    'm/d/yyyy'                  
!    'mm/dd/yy'
!    'mm/dd/yyyy'
! If $Justification is one of GLCR then the Row is set to
! General, Left, Right, or Center justification respectively. 
! If the format is not legal the program stops with a message 
! in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Row-To-Date(#FileNumber, #Row, $DateFormat, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Row-To-Date'
    do SylkFormatToDateDoNotCall($DateFormat, $Justification, $Format)
    let $Txt = $Format || ';R' || to_char(#Row)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Row-To-Date
             
!****************************************************************
! Procedure: Sylk-Format-Row-To-Number(#FileNumber, #Row, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Desc: Formats the Row #Row to Number with 
! #Decimals (0 to 13). 
! If $Justification is one of GLCR then the Row is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Row-To-Number(#FileNumber, #Row, #Decimals, $CommasYN, $DollarsYN, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Row-To-Number'
    do SylkFormatToNumberDoNotCall(#Decimals, $CommasYN, $DollarsYN, $Justification, $Format)
    let $Txt = $Format || ';R' || to_char(#Row)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Row-To-Number
                         
!****************************************************************
! Procedure: Sylk-Format-Row-To-Percentage(#FileNumber, #Row, #Decimals, $Justification)
! Desc: Formats the Row #Row to Percentage with 
! #Decimals (0 to 13). 
! When cells are formatted to percentage a value of 0.15 will appear as 15%.
! If $Justification is one of GLCR then the Row is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Row-To-Percentage(#FileNumber, #Row, #Decimals, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Row-To-Percentage'
    do SylkFormatToPercentageDoNotCall(#Decimals, $Justification, $Format)
    let $Txt = $Format || ';R' || to_char(#Row)
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Row-To-Percentage
                         
!****************************************************************
! Procedure: Sylk-Format-Cell-To-General(#FileNumber, #Row, #Col, $Justification)
! Desc: Formats the Cell #Row, #Col to General.
! If $Justification is one of GLCR then the Cell is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Cell-To-General(#FileNumber, #Row, #Col, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Cell-To-General'
    do SylkFormatToGeneralDoNotCall($Justification, $Format)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col) 
    let $Txt = $Format || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Cell-To-Text

!****************************************************************
! Procedure: Sylk-Format-Cell-To-Text(#FileNumber, #Row, #Col, $Justification)
! Desc: Formats the Cell #Row, #Col to Text.
! If $Justification is one of GLCR then the Cell is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure Sylk-Format-Cell-To-Text(#FileNumber, #Row, #Col, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Cell-To-Text'
    do SylkFormatToTextDoNotCall($Justification, $Format)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col) 
    let $Txt = $Format || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Cell-To-Text

!****************************************************************
! Procedure: Sylk-Format-Cell-To-Date(#FileNumber, #Row, #Col, $DateFormat, $Justification)
! Desc: Formats the Cell #Row, #Col to Date. 
! Data is displayed per $Format which can be any of the following:
!    'd-mmm-yyyy'
!    'd-mon-yyyy'
!    'dd-mmm-yyyy'
!    'dd-mon-yyyy'
!    'd-mmm-yy'                                   
!    'd-mon-yy'                                   
!    'dd-mmm-yy'                               
!    'dd-mon-yy'                               
!    'm/d/yy'                  
!    'm/d/yyyy'                  
!    'mm/dd/yy'
!    'mm/dd/yyyy'
! If $Justification is one of GLCR then the Cell is set to
! General, Left, Right, or Center justification respectively. 
! If the format is not legal the program stops with a message 
! in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Cell-To-Date(#FileNumber, #Row, #Col, $DateFormat, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Cell-To-Date'
    do SylkFormatToDateDoNotCall($DateFormat, $Justification, $Format)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col) 
    let $Txt = $Format || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Cell-To-Date
             
!****************************************************************
! Procedure: Sylk-Format-Cell-To-Number(#FileNumber, #Row, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
! Desc: Formats the Cell #Row, #Col to Number with 
! #Decimals (0 to 13). 
! If $Justification is one of GLCR then the Cell is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Cell-To-Number(#FileNumber, #Row, #Col, #Decimals, $CommasYN, $DollarsYN, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Cell-To-Number'
    do SylkFormatToNumberDoNotCall(#Decimals, $CommasYN, $DollarsYN, $Justification, $Format)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col) 
    let $Txt = $Format || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Cell-To-Number
                         
!****************************************************************
! Procedure: Sylk-Format-Cell-To-Percentage(#FileNumber, #Row, #Col, #Decimals, $Justification)
! Desc: Formats the Cell #Row, #Col to Percentage with 
! #Decimals (0 to 13). 
! When cells are formatted to percentage a value of 0.15 will appear as 15%.
! If $Justification is one of GLCR then the Cell is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure Sylk-Format-Cell-To-Percentage(#FileNumber, #Row, #Col, #Decimals, $Justification)
    let $_SylkErrorMessage = 'Sylk-Format-Cell-To-Percentage'
    do SylkFormatToPercentageDoNotCall(#Decimals, $Justification, $Format)
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col) 
    let $Txt = $Format || $Row || $Col
    write #FileNumber from $Txt
end-procedure ! Sylk-Format-Cell-To-Percentage
                         
!****************************************************************
! Procedure: Sylk-Write-Formula(#FileNumber, #Row, #Col, $Formula)
! Desc: Writes $Formula in cell #Row, #Col
! Cell references (E.G. A5 must have a space in front of them and 
! after them, so a formula that appears in Sylk as
! =sum($a$1:b6)
! Must be passed in as
! =sum( $a$1 : b6 )
!****************************************************************
begin-procedure Sylk-Write-Formula(#FileNumber, #Row, #Col, $Formula)
    let $_SylkErrorMessage = 'Sylk-Write-Formula'
    do SylkConvertFormulaToMultiPlanDoNotCall(#Row, #Col, $Formula, $FormulaOut)
    do SylkStripCharDoNotCall($FormulaOut, 13, $FormulaOut)
    do SylkStripCharDoNotCall($FormulaOut, 10, $FormulaOut)
    do SylkWriteDataDoNotCall(#FileNumber, #Row, #Col, $FormulaOut)    
end-procedure ! Sylk-Write-Formula

!****************************************************************
! Procedure: Sylk-Column-Number-To-Alpha(#ColumnIn, $YNColAbsolute, :$ColumnOut)
! Desc: Converts a numeric column reference #ColIn to the alpha 
! reference $ColumnOut as used in Sylk (with a leading space).
! E.g. 1 becomes ' A', 28 becomes ' AB', etc.
!****************************************************************
begin-procedure Sylk-Column-Number-To-Alpha(#ColumnIn, $YNColAbsolute, :$ColumnOut)
    let $_SylkErrorMessage = 'Sylk-Column-Number-To-Alpha'
    uppercase $YNColAbsolute
    if $YNColAbsolute = 'Y'
        let $YNColAbsolute = '$'
    else
        let $YNColAbsolute = ''
    end-if
    let $ColumnOut = ''
    if #ColumnIn < 1 or #ColumnIn > 256
        do SylkErrorDoNotCall
        show 'Illegal #ColumnIn (' #ColumnIn ') in Sylk-Column-Number-To-Alpha  (Must be 1 to 256)'
        stop quiet
    else
        let #ColumnIn = #ColumnIn - 1
        let #First = trunc(#ColumnIn / 26, 0)
        let #Second = mod(#ColumnIn, 26) + 1
        if #First > 0
            let $ColumnOut = chr(#First + 64)
        end-if
        if #Second > 0
            let $ColumnOut = $ColumnOut || chr(#Second + 64)
        end-if  
        let $ColumnOut = ' ' || $YNColAbsolute || $ColumnOut
    end-if
end-procedure ! Sylk-Column-Number-To-Alpha                                 
                                               
!****************************************************************
! Procedure: Sylk-Get-Margin-Formula($CostCell, $PriceCell, :$Formula)
! Desc: Send in the cell references for Price and Cost and the
! formula for the margin is returned in $Formula.
! This is the decimal margin that should be displayed in a cell
! formatted to Percentage.
!****************************************************************
begin-procedure Sylk-Get-Margin-Formula($CostCell, $PriceCell, :$Formula)
    let $_SylkErrorMessage = 'Sylk-Get-Margin-Formula'
    let $Formula = '=IF( ' || $PriceCell || ' =0,0,1-( ' || $CostCell || ' /  ' || $PriceCell || ' ))'
end-procedure ! Sylk-Get-Margin-Formula
                                               
!****************************************************************
! Procedure: Sylk-Get-Safe-Division-Formula($Dividend, $Divisor, :$Formula)
! Desc: Send in the cell references for $Dividend and $Divisor and the
! formula for the division is returned in $Formula.
! This formula will return zero if the Divisor is zero
!****************************************************************
begin-procedure Sylk-Get-Safe-Division-Formula($Dividend, $Divisor, :$Formula)
    let $_SylkErrorMessage = 'Sylk-Get-Safe-Division-Formula'
    let $Formula = '=IF( ' || $Divisor || ' =0,0,' || $Dividend || '/' || $Divisor || ')'
end-procedure ! Sylk-Get-Safe-Division-Formula

!****************************************************************
! Procedure: Sylk-Get-Margin(#Cost, #Price, :#Margin)
! Desc: Send in the Price and Cost and the margin is returned 
! in #Margin
! This is the decimal margin that should be displayed in a cell
! formatted to Percentage.
!****************************************************************
begin-procedure Sylk-Get-Margin(#Cost, #Price, :#Margin)
if #Price=0
    Let #Margin=0
else
    let #Margin=1-(#Cost/#Price)
    if #Margin > 99999.999
    	let #Margin = 99999.999
    end-if
    if #Margin < -99999.999
    	let #Margin = -99999.999
    end-if
end-if
end-procedure ! Sylk-Get-Margin
                                               
!****************************************************************
! Procedure: Sylk-Row-Number-Column-Number-To-Alpha(#RowIn, $YNRowAbsolute, #ColumnIn, $YNColAbsolute, :$RowColumnOut)
! Desc: Converts a numeric row and column reference #RowIn, #ColumnIn to the alpha 
! reference $ColumnOut as used in Sylk (with a leading space and a following space for use in formulas).
! E.g. 1, 4 becomes ' A4 ', 28, 4 becomes ' AB4 ', etc.       
! If either $YNRowAbsolute or $YNColAbsolute are 'Y' then $ is placed before that part of the reference
!****************************************************************
begin-procedure Sylk-Row-Number-Column-Number-To-Alpha(#RowIn, $YNRowAbsolute, #ColumnIn, $YNColAbsolute, :$RowColumnOut)
    let $_SylkErrorMessage = 'Sylk-Row-Number-Column-Number-To-Alpha'
    let $Edit = '99999'                                                                                                     
    do Sylk-Column-Number-To-Alpha(#ColumnIn, $YNColAbsolute, $ColumnOut)
    uppercase $YNRowAbsolute
    if $YNRowAbsolute = 'Y'
        let $YNRowAbsolute = '$'
    else
        let $YNRowAbsolute = ''
    end-if
    move #RowIn to $RowOut :$edit                   
    let $RowOut = ltrim($RowOut, ' ')
    let $RowColumnOut = $ColumnOut || $YNRowAbsolute || $RowOut || ' '
end-procedure ! Sylk-Row-Number-Column-Number-To-Alpha

!****************************************************************
! Procedure: Sylk-Write-Month-Year-Column-Headings(#FileNumber, #Row, #StartCol, #RepeatCols, $StartDate, $EndDate, $Format, :#NextCol)
! Desc: Writes successive months in successive columns in $Format format 
!       (e.g. 'Mon-YYYY') for every month in the the date range.       
!       #RepeatCols indicates the number of columns each heading
!       is to be printed in before the next heading is printed.
!       if #RepeatCols is negative then the heading is printed
!       in the first column only and the other columns are left blank.
!       Returns the next column in #NextCol                                                                  
!****************************************************************
begin-procedure Sylk-Write-Month-Year-Column-Headings(#FileNumber, #Row, #StartCol, #RepeatCols, $StartDate, $EndDate, $Format, :#NextCol)
    declare-variable
        date $StartDate $EndDate $ADate
    end-declare
    
    let $_SylkErrorMessage = 'Sylk-Write-Month-Year-Column-Headings'
    do SylkGetFirstOfMonthDoNotCall($StartDate, $StartDate)
    do SylkGetFirstOfMonthDoNotCall($EndDate, $EndDate)
    
    let $Adate = $StartDate
    let #NextCol = #StartCol       
    if #RepeatCols < 0 
        let #RepeatCols = - #RepeatCols
        let #Skip = #RepeatCols 
    else
        let #Skip = 1
    end-if
    while $Adate <= $EndDate                         
        let $Text = datetostr($Adate, $Format)
        let #N = 1
        while #N <= #RepeatCols
            do Sylk-Write-Text(#FileNumber, #Row, #NextCol, $Text)
            let #NextCol = #NextCol + #Skip
            let #N = #N + #Skip
        end-while
        let $Adate = dateadd($Adate, 'MONTH', 1)
    end-while
end-procedure ! Sylk-Write-Month-Year-Column-Headings

!****************************************************************
! Procedure: Sylk-Get-Worksheet-Filename($BaseFileName, #ProcessInstance, #WorksheetNumber, :$WorksheetFileName)
! Desc: Returns the BaseFileName with the Process instance and Worksheet number and .xls attached
!       in a standard manner.
!****************************************************************
begin-procedure Sylk-Get-Worksheet-Filename($BaseFileName, #ProcessInstance, #WorksheetNumber, :$WorksheetFileName)
    let $_SylkErrorMessage = 'Sylk-Get-Worksheet-Filename'
    if #ProcessInstance > 0 
        let $PI = '_' || to_char(#ProcessInstance)
    end-if
    let $WS = '_ws' || to_char(#WorksheetNumber)
    let $WorksheetFileName = $BaseFileName || $PI || $WS || '.xls'
end-procedure ! Get-Worksheet-Number

!****************************************************************
! Procedure: Sylk-Close-File(#FileNumber)
! Desc: Writes the final record and closes the file #FileNumber.
!****************************************************************
begin-procedure Sylk-Close-File(#FileNumber)
    let $_SylkErrorMessage = 'Sylk-Close-File'
    !End of data - close the file
    write #FileNumber from 'E'
    close #FileNumber                        
end-procedure ! Sylk-Close-File

!End of Public Procedures
!#########################################################################################################################################                             
!#########################################################################################################################################                             
!#########################################################################################################################################                             
!****************************************************************
! Procedure: SylkCheckJustificationDoNotCall($Function, $JustificationIn, :$JustificationOut)
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! Tests $JustificationIn to be certain it is legal.
! If not legal the program stops with a message in the log, as 
! coding is incorrect.
!****************************************************************
begin-procedure SylkCheckJustificationDoNotCall($Function, $JustificationIn, :$JustificationOut)
    If $JustificationIn = '' 
        let $JustificationOut = 'G'
    else
        let $JustificationOut = substr($JustificationIn, 1, 1)
        uppercase $JustificationOut
    end-if                         
    let $Legal = 'GLCR'
    if 0 = instr($Legal, $JustificationOut,0)
        do SylkErrorDoNotCall
        show 'Illegal Justification (' $JustificationIn ') in ' $Function '  (Legal justifications are: ' $Legal ')'
        stop quiet
    end-if
end-procedure ! SylkCheckJustificationDoNotCall
        
!****************************************************************
! Procedure: SylkConvertFormulaToMultiPlanDoNotCall(#Row, #Col, $FormulaIn, :$FormulaOut)
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! Converts the Sylk-format formula into a SYLK-format formula
! $FormulaIn is the formula as entered in Sylk EXCEPT all cell references must have a space before 
! them and after them. All 'space delimited' strings will be examined.  If they are Sylk cell references
! they will converted to SYLK cell references.  Otherwise they remain unchanged.
!****************************************************************
begin-procedure SylkConvertFormulaToMultiPlanDoNotCall(#Row, #Col, $FormulaIn, :$FormulaOut)
    let $FormulaIn = ltrim($FormulaIn, ' ')
    if substr($FormulaIn,1,1) <> '='
        do SylkErrorDoNotCall
        Show 'Formula in cell Row ' #row ', Col ' #Col ' must begin with an equal sign.'
        Stop Quiet
    end-if
    let #L  = length($FormulaIn)
    let #I = 1
    let #L = length($FormulaIn)
    let $S = ''
    let #QuotesOn = 0
    let $Chr1 = chr(1)
    !Replace all non-quoted spaces to chr(1)
    While #I <= #L 
        let $T = substr($FormulaIn, #I, 1)
        if $T = '"'    
            if #QuotesOn = 0 
                let #Quotes = 1
            else    
                let #Quotes = 0
            end-if
        end-if
        if $T = ' ' and #QuotesOn = 0
            let $T = $Chr1
        end-if
        let $S = $S || $T
        let #I = #I + 1
    end-while
    let $FormulaOut = ''
    let #I = instr($S, $chr1, 1) 
    let #L = length($S)
    while #I > 0                  
        let #I = #I -1
        let $T = substr($S, 1, #I)
        let #I = #I + 2
        let $S = substr($S, #I, #L)
        do SylkIsACellDoNotCall($T, $YN, $Alpha, $Numeric)
        if $YN = 'Y'
            do SylkConvertCellDoNotCall(#Row, #Col, $Alpha, $Numeric, $T)
        end-if
        let $FormulaOut = $FormulaOut || $T
        let #I = instr($S, $chr1, 1) 
    end-while
    let $FormulaOut = $FormulaOut || $S
    do SylkFixSemicolonsDoNotCall($FormulaOut, $FormulaOut)
    !Must replace the leading '=' with ';E'
    let #L = length($FormulaOut) 
    let $FormulaOut = ';E' || substr($FormulaOut, 2, #L)
    end-procedure ! SylkConvertFormulaToMultiPlanDoNotCall    

!****************************************************************
! Procedure: SylkIsACellDoNotCall($SIn, :$YN, :$Alpha, :$Numeric)
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! If $SIn is a cell reference
!    $YN = 'Y' 
!    $Alpha contains the Alpha part (e.g. AA, $AA)
!    $Numeric contains the numeric part (e.g. 57, $57)
! else
!    $YN = 'N'                                                 
!    $Alpha = ''
!    $Numeric = ''
!****************************************************************
begin-procedure SylkIsACellDoNotCall($SIn, :$YN, :$Alpha, :$Numeric)
    let $YN = ''
    let $Alpha = ''
    let $Numeric = ''                                             
    let $AlphaNumeric = 'A'               
    let $S = $SIn
    uppercase $S
    let #L = length($S)
    let #I = 1
    while #I <= #L    
        let $T = substr($S, #I, 1)
        if #I = 1        
            !First character of the string
            if $T = '$' or ($T >=  'A' and $T <= 'Z')
                !These are legal first characters
                let $Alpha = $T
                let $YN = 'Y'
            else     
                let $YN = 'N'
                let $Alpha = ''
                let $Numeric = ''                                             
                break
            end-if
        else      
            !Not first character of the string
            if $AlphaNumeric = 'A'            
                !Processing the alpha portion    
                if $T >=  'A' and $T <= 'Z'
                    let $Alpha = $Alpha || $T
                else                         
                    !Alpha portion is finished so this must be 
                    !the first character of the numeric portion
                    if $T = '$' or ($T >=  '0' and $T <= '9')
                        let $AlphaNumeric = 'N'
                        let $Numeric = $T
                    else
                        let $YN = 'N'
                        let $Alpha = ''
                        let $Numeric = ''                                             
                        break
                    end-if
                end-if
            else                          
                !processing numeric portion        
                if $T >=  '0' and $T <= '9'
                    let $Numeric = $Numeric || $T
                else
                    let $YN = 'N'
                    let $Alpha = ''
                    let $Numeric = ''                                             
                    break
                end-if
            end-if
        end-if
        let #I = #I + 1
    end-while                                      
    If $YN = 'Y'
        !check that alpha portion is legal
        let #L = length($Alpha)
        if substr($Alpha, 1, 1) = '$'     
            let #L = #L - 1
            let $T = substr($Alpha, 2, #L)
        else
            let $T = $Alpha
        end-if
        let #LT = length($T)
        if (#LT = 1 AND ($T < 'A' OR $T > 'Z')) OR (#LT = 2 AND ($T < 'A' OR $T > 'IV'))
            let $YN = 'N'
            let $Alpha = ''
            let $Numeric = ''                                             
        end-if
        !check that numeric portion is legal
        let #L = length($Numeric)
        if substr($Numeric, 1, 1) = '$'     
            let #L = #L - 1
            let $T = substr($Numeric, 2, #L)
        else
            let $T = $Numeric
        end-if                       
        let #N = to_number($T)
        if not (#N >= 1 and #T <= 65636)
            let $YN = 'N'
            let $Alpha = ''
            let $Numeric = ''                                             
        end-if
    end-if
end-procedure ! SylkIsACellDoNotCall

!****************************************************************
! Procedure: SylkConvertCellDoNotCall(#Row, #Col, $AlphaIn, $NumericIn, :$CellOut)
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! Converts the cell referenced by $AlphaIn and $NumericIn
! to a MultiPlan reference and returns it in $CellOut
!****************************************************************
begin-procedure SylkConvertCellDoNotCall(#Row, #Col, $AlphaIn, $NumericIn, :$CellOut)
    !Handle Row                         
   if substr($NumericIn,1,1) = '$'
        let $LP = 'R'
        let $RP = ''       
        let $NumericIn = substr($NumericIn,2,10) 
    else
        let $LP = 'R['
        let $RP = ']'
    end-if
    let #NI = to_number($NumericIn)
    if $LP = 'R'
        !absolute reference
        let #Row = #NI
    else          
        !relative reference
        let #Row = #NI - #Row
    end-if
    let $R = to_char(#Row)
    let $CellOut = $LP || $R || $RP
               
    !Handle Column
    uppercase $AlphaIn
    if substr($AlphaIn,1,1) = '$'
        let $LP = 'C'
        let $RP = ''       
        let $AlphaIn = substr($AlphaIn,2,10) 
    else
        let $LP = 'C['
        let $RP = ']'
    end-if

    if length($AlphaIn) = 1
        let #CI = ascii($AlphaIn) - 64
    else                         
        let #CI = 26 *(ascii(substr($AlphaIn,1,1)) - 64) + (ascii(substr($AlphaIn,2,1)) - 64)
    end-if        

    if $LP = 'C'
        !absolute reference
        let #Col = #CI
    else          
        !relative reference
        let #Col = #CI - #Col
    end-if
    let $C = to_char(#Col)
    let $CellOut = $CellOut || $LP || $C || $RP
end-procedure ! SylkConvertCellDoNotCall
                                                 
!****************************************************************
! Procedure: SylkWriteDataDoNotCall(#FileNumber, #Row, #Col, $Data)
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! If the row or column exceed Sylk's limits the program stops 
! with a message in the log.  This messsage is also placed in 
! cell A1 of the spreadsheet to alert the user.
!****************************************************************
begin-procedure SylkWriteDataDoNotCall(#FileNumber, #Row, #Col, $Data)
    !This routine is called by the various User Print Functions      
    !If called with a negative #FileNumber it is assumed that a new file is being 
    !  initialized
    if #FileNumber < 0
        let #OldFileNumber = 0
        let #OldRow = 0
        let #OldCol = 0                
        do SylkCheckRowColDoNotCall(#FileNumber, #OldRow, $Row, #OldCol, $Col)
    else
        if #Row > 65536    
            let $ErrMess = 'ERROR - Data not loaded - too many rows.  (Maximum is 65536)'
            do SylkErrorDoNotCall
            show $ErrMess
            do Sylk-Write-Text(#FileNumber, 1, 1, $ErrMess)
            do Sylk-Set-Row-Font(#FileNumber, 1, 12, 'B')
            do Sylk-Set-Column-Width(#FileNumber, 1, 70)
            do Sylk-Close-File(#FileNumber)
            stop quiet
        end-if
        if #Col > 256
            do SylkErrorDoNotCall
            let $ErrMess = 'ERROR - Data not loaded - too many columns.  (Maximum is 256)'
            show $ErrMess
            do Sylk-Write-Text(#FileNumber, 1, 1, $ErrMess)
            do Sylk-Set-Row-Font(#FileNumber, 1, 12, 'B')
            do Sylk-Set-Column-Width(#FileNumber, 1, 70)
            do Sylk-Close-File(#FileNumber)
            stop quiet
        end-if                          
        if #Row < 1
            let $ErrMess = 'ERROR - ROW must be greater than zero'
            do SylkErrorDoNotCall
            show $ErrMess
            do Sylk-Write-Text(#FileNumber, 1, 1, $ErrMess)
            do Sylk-Set-Row-Font(#FileNumber, 1, 12, 'B')
            do Sylk-Set-Column-Width(#FileNumber, 1, 70)
            do Sylk-Close-File(#FileNumber)
            stop quiet
        end-if
        if #Col < 1
            do SylkErrorDoNotCall
            let $ErrMess =  'ERROR - COLUMN must be greater than zero'
            show $ErrMess
            do Sylk-Write-Text(#FileNumber, 1, 1, $ErrMess)
            do Sylk-Set-Row-Font(#FileNumber, 1, 12, 'B')
            do Sylk-Set-Column-Width(#FileNumber, 1, 70)
            do Sylk-Close-File(#FileNumber)
            stop quiet
        end-if                          
        do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, #Col, $Col)
        let $Phrase = 'C' || $Row || $Col || ';K' || $Data 
        write #FileNumber from $Phrase
    end-if
end-procedure ! SylkWriteDataDoNotCall                          

!****************************************************************
! Procedure: SylkSetRowColFontDoNotCall(#FileNumber, #RowCol, #Size, $BoldItalic, $RC)    
! Desc: An internal procedure not meant to be called from outside
! this SQC.
!****************************************************************
begin-procedure SylkSetRowColFontDoNotCall(#FileNumber, #RowCol, #Size, $BoldItalic, $RC)    
    do SylkGetlFontDoNotCall(#Size, $BoldItalic, $FontOut)
    let $Txt = $FontOut || ';' || $RC || to_char(#RowCol)    
    write #FileNumber from $Txt
end-procedure ! SylkSetRowColFontDoNotCall

!****************************************************************
! Procedure: SylkGetlFontDoNotCall(#Size, $BoldItalic, :$FontOut)    
! Desc: An internal procedure not meant to be called from outside
! this SQC.
! If #Size is not legal the program stops with a message in the 
! log, as coding is incorrect.
! Legal sizes are 8, 10, 12
! $BoldItalic:
!    '' = regular
!    if contains 'B' = bold
!    if contains 'I' = italic                                       
! Returns the font information in $FontOut 
!****************************************************************

begin-procedure SylkGetlFontDoNotCall(#Size, $BoldItalic, :$FontOut)    
    if #Size <> 8 and #Size <> 10 and #Size <> 12 
        do SylkErrorDoNotCall
        show 'Illegal Font Size ' #Size '.  Legal sizes are 8, 10, 12)'
        stop quiet                             
    end-if
    uppercase $BoldItalic   
    let #Off = 0
    if instr($BoldItalic, 'B', 1) > 0
        let #Off = 2
    end-if
    if instr($BoldItalic, 'I', 1) > 0
        let #Off = #Off + 1
    end-if      
    let #I = (80 * (#Size - 8)/2 + 20 * #Off) + 1
    let $Search = 'F;SM5;M225         ^F;SIM6;M225        ^F;SDM7;M225        ^F;SIDM8;M210       ^F;SM9              ^F;SIM10            ^F;SDM11            ^F;SIDM12           ^F;SM13;M300        ^F;SIM14;M300       ^F;SDM15;M315       ^F;SIDM16;M300      ^'
    let $FontOut = substr($Search, #I, 19)
    let $FontOut = rtrim($FontOut, ' ')
end-procedure ! SylkGetlFontDoNotCall

!****************************************************************
! Procedure: SylkFormatToGeneralDoNotCall($Justification, :$Format)
! Desc: Returns $Format
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure SylkFormatToGeneralDoNotCall($Justification, :$Format)
    do SylkCheckJustificationDoNotCall('SylkFormatToGeneral', $Justification, $Justification)
    let $Format = 'F;P0;FG0' || $Justification
end-procedure ! SylkFormatToGeneralDoNotCall

!****************************************************************
! Procedure: SylkFormatToTextDoNotCall($Justification, :$Format)
! Desc: Returns $Format
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
!****************************************************************
begin-procedure SylkFormatToTextDoNotCall($Justification, :$Format)
    do SylkCheckJustificationDoNotCall('SylkFormatToText', $Justification, $Justification)
    let $Format = 'F;P1;FG0' || $Justification
end-procedure ! SylkFormatToTextDoNotCall

!****************************************************************
! Procedure: SylkFormatToDateDoNotCall($DateFormat, $Justification, :$Format)
! Desc: Returns $Format 
! Data is displayed per $Format which can be any of the following:
!    'd-mmm-yyyy'
!    'd-mon-yyyy'
!    'dd-mmm-yyyy'
!    'dd-mon-yyyy'
!    'd-mmm-yy'                                   
!    'd-mon-yy'                                   
!    'dd-mmm-yy'                               
!    'dd-mon-yy'                               
!    'm/d/yy'                  
!    'm/d/yyyy'                  
!    'mm/dd/yy'
!    'mm/dd/yyyy'
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the format is not legal the program stops with a message 
! in the log, as coding is incorrect.
!****************************************************************
begin-procedure SylkFormatToDateDoNotCall($DateFormat, $Justification, :$Format)
    do SylkCheckJustificationDoNotCall('SylkFormatToDate', $Justification, $Justification)
    Let $FormatX = $DateFormat
    let #Z = instr($FormatX, 'mon', 1)
    if #Z > 0
        let $FormatX = substr($FormatX, 1, #Z) || 'mm' || substr($FormatX, #Z + 3, length($FormatX))
    end-if    
    lowercase $FormatX    
    let #POffset = 72
    Evaluate $FormatX
        When = 'd-mmm-yyyy'
            let #X = #POffset 
            break                                     
        When = 'dd-mmm-yyyy'
             !This ends up as 'custom'
            let #X = #POffset + 1          
            break                                      
        When = 'd-mmm-yy'                                   
            let #X = #POffset + 2                         
            break                                         
        When = 'dd-mmm-yy'                               
            let #X = #POffset + 3            
            break                            
        When = 'm/d/yy'                  
            let #X = #POffset + 4            
            break                            
        When = 'm/d/yyyy'                  
            let #X = #POffset + 5
            break                            
        When = 'mm/dd/yy'
            let #X = #POffset + 6            
            break                            
        When = 'mm/dd/yyyy'
             !This ends up as 'custom'
            let #X = #POffset + 7            
            break                            
        When-Other                           
            do SylkErrorDoNotCall
            show 'Illegal Date Format ' $Format 
            stop quiet                             
    End-Evaluate            
    let $Format = 'F;P' || to_char(#X) || ';FG0' || $Justification
end-procedure ! SylkFormatToDateDoNotCall
             
!****************************************************************
! Procedure: SylkFormatToNumberDoNotCall(#Decimals, $CommasYN, $DollarsYN, $Justification, :$Format)
! Desc: Returns the format in $Format to Number with 
! #Decimals (0 to 13). 
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure SylkFormatToNumberDoNotCall(#Decimals, $CommasYN, $DollarsYN, $Justification, :$Format)
    do SylkCheckJustificationDoNotCall('SylkFormatToNumber', $Justification, $Justification)
    uppercase $CommasYN
    if substr($CommasYN, 1, 1) = 'Y'
        let #Offset = 16
    else
        let #Offset = 2
    end-if         
    uppercase $DollarsYN
    if substr($DollarsYN, 1, 1) = 'Y'
        let #Offset = #Offset + 28
    end-if
    if #Decimals < 0 or #Decimals > 13
        do SylkErrorDoNotCall
        show 'Illegal #Decimals (' #Decimals ') in Sylk-Format-Column-To-Number  (Must be 0 to 13)'
        stop quiet                             
    else    
        let #Decimals = #Decimals + #Offset   !the offset of the 'P's     
    end-if
    uppercase $Justification
    let $Format = 'F;P' || to_char(#Decimals) || ';FI0' || $Justification
end-procedure ! SylkFormatToNumberDoNotCall

!****************************************************************
! Procedure: SylkFormatToPercentageDoNotCall(#Decimals, $Justification, :$Format)
! Desc: Returns the format in $Format to Percentage with 
! #Decimals (0 to 13). 
! When cells are formatted to percentage a value of 0.15 will appear as 15%.
! If $Justification is one of GLCR then the column is set to
! General, Left, Right, or Center justification respectively. 
! If the number of decimals is outside the accepted range the 
! program stops with a message in the log, as coding is incorrect.
!****************************************************************
begin-procedure SylkFormatToPercentageDoNotCall(#Decimals, $Justification, :$Format)
    do SylkCheckJustificationDoNotCall('SylkFormatToPercentage', $Justification, $Justification)
    if #Decimals < 0 or #Decimals > 13  
        do SylkErrorDoNotCall
        show 'Illegal #Decimals (' #Decimals ') in Sylk-Format-Column-To-Percentage  (Must be 0 to 13)'
        stop quiet                             
    else    
        let #Decimals = #Decimals + 16 + 14 + 14 + 14  !the offset of the 'P's     
    end-if
    uppercase $Justification
    let $Format = 'F;P' || to_char(#Decimals) || ';FI0' || $Justification
end-procedure ! SylkFormatToPercentageDoNotCall

!****************************************************************
! Procedure: SylkFixSemicolonsDoNotCall($TextIn, $TextOut)
! Desc: Replaces every semicolon with a pair of semicolons
!****************************************************************
begin-procedure SylkFixSemicolonsDoNotCall($TextIn, :$TextOut)
    let #I = instr($TextIn, ';', 1)    
    let $TextOut = ''
    if #I = 0
        let $TextOut = $TextIn
    else                                             
        let #L = length($TextIn)
        while #I > 0
            let $TextOut = $TextOut || substr($TextIn, 1, #I) || ';'
            let $TextIn = substr($TextIn, #I + 1, #L)
            let #I = instr($TextIn, ';', 1)
        end-while          
        let $TextOut = $TextOut || $TextIn
    end-if
end-procedure ! SylkFixSemicolonsDoNotCall

!****************************************************************
! Procedure: SylkStripCharDoNotCall($TextIn, #AsciiOfCharToStrip, :$TextOut)
! Desc: Removes all chr(Chr(#AsciiOfCharToStrip)) characters from TextIn
!   If any are found a message is written to the log file
!****************************************************************
begin-procedure SylkStripCharDoNotCall($TextIn, #AsciiOfCharToStrip, :$TextOut)
    let #L = length($TextIn)         
    let $C = Chr(#AsciiOfCharToStrip)
    let #I = instr($TextIn, $C, 1)    
    if #I = 0              
        let $TextOut = $TextIn
    else
        show 'The following text had at least one chr('  #AsciiOfCharToStrip ') removed from it.'
        show $TextIn
        let $TextOut = ''     
        while #I > 0
            let $TextOut = $TextOut || substr($TextIn, 1, #I - 1)
            let $TextIn = substr($TextIn, #I + 1, #L)
            let #I = instr($TextIn, $C, 1)    
        end-while
        let $TextOut = $TextOut || $TextIn
    end-if
end-procedure ! SylkStripCharDoNotCall

!****************************************************************
! Procedure: SylkGetFirstOfMonthDoNotCall($InDate, :$OutDate)
! Desc: Sets $OutDate to the first of the month in which $InDate occurs
!****************************************************************
begin-procedure SylkGetFirstOfMonthDoNotCall($InDate, :$OutDate)
    declare-variable
        date $InDate  $OutDate
    end-declare
    let $OutDate = strtodate(datetostr($InDate, 'YYYY-MON' || '-01'), 'YYYY-MON-DD')
end-procedure ! SylkGetFirstOfMonthDoNotCall 

!****************************************************************
! procedure SylkCheckColDoNotCall(#FileNumber #Col, :$Col)
! Desc: Keeps track of the most recently printed column so
! redundency can be eliminated.  Returns the colmn string necessary
!****************************************************************
begin-procedure SylkCheckColDoNotCall(#FileNumber, #Col, :$Col)                 
    do SylkCheckRowColDoNotCall(#FileNumber, 0, $Row, #Col, $Col) 
end-procedure ! SylkCheckColDoNotCall

!****************************************************************
! procedure SylkCheckRowDoNotCall(#FileNumber #Row, :$Row)
! Desc: Keeps track of the most recently printed row so
! redundency can be eliminated.  Returns the row string necessary
!****************************************************************
begin-procedure SylkCheckRowDoNotCall(#FileNumber, #Row, :$Row) 
    do SylkCheckRowColDoNotCall(#FileNumber, #Row, $Row, 0, $Col) 
end-procedure ! SylkCheckRowDoNotCall

!****************************************************************
! procedure SylkCheckRowColDoNotCall(#FileNumber, #Row, :$Row, #Col, :$Col)
! Desc: Keeps track of the most recently printed row and column so
! redundency can be eliminated.  Returns the row and colmn strings necessary
!****************************************************************
begin-procedure SylkCheckRowColDoNotCall(#FileNumber, #Row, :$Row, #Col, :$Col) 
    let $Row = ''  !must erase the values from the previous call
    let $Col = ''
    if #OldFileNumber = #FileNumber
        !this is the same file as last time so we can look for repetition
        if #Row >0
            If #OldRow = #Row 
                !do not need to print row info
                let $Row = ''
            Else
                let #OldRow = #Row
                let $Row = ';Y' || to_char(#Row)
            End-If        
        end-if
        if #col > 0
            If #OldCol = #Col 
                !do not need to print col info
                let $Col = ''
            Else
                let #OldCol = #Col
                let $Col = ';X' || to_char(#Col)
            End-If
        end-if
    Else
        let #OldFileNumber = #FileNumber
        let #OldRow = #Row
        let #OldCol = #Col                  
        let $Row = ';Y' || to_char(#Row)
        let $Col = ';X' || to_char(#Col)
    End-If
end-procedure ! SylkCheckRowColDoNotCall

begin-procedure SylkErrorDoNotCall local
    if length($_SylkErrorMessage) > 0 
        show 'Error in Sylk.sqc in the procedure ' $_SylkErrorMessage
    end-if
end-procedure ! SylkErrorDoNotCall