Welcome to JRI's Help page for building true Excel files using Perl

We are now able to build true Excel files directly from Avante using UniBasic code. This is accomplished via a Perl module that we have downloaded from John McNamara (giving credit where credit is due). Click here and here to see the full on-line documentation. By following these basic concepts, we will be able to build a Perl script which will in turn build an Excel file. The resulting Excel file can then be attached as an E-mail (if not too big) or placed out on a shared drive (G:, K:, etc.)

By adhearing to the following steps, you should be able to write a basic program that will:

  • Use a standard naming convention
  • Purge prior jobs by port & date
  • Build a Perl script
  • Execute the Perl script to build an Excel file
  • E-mail or move the Excel file


To prevent a crazy-big Perl script from crashing (and taking down the Perl executable itself), logic has been added to test the size of the Perl script. There are two ways to test the Perl script:

  • After the Perl script is build and before the script is executed
  • Periodically during the building of the Perl script

In both cases, the driving program must be written to know how to handle the situation if a size issue is encountered. These tests use a control record called DSCTBL PERL.CTL to determine if:

  • The Perl script size exceeds the WARNING limit &
  • The Perl script size exceeds the FATAL limit

Let's get started...

Your basic program should write the Perl script into a UNIX directory called PERL. There is a VOC and DICT for this which is also caled PERL. There are 2 Dict items that automatically report the file sizes and creation dates. Here is an example of sorting the PERL file:

The naming format of the file is the program title/desc, followed by the port (so multiple people could run the same report at the same time), followed by the extension. Note that the name of the Perl script and the Excel file are the same except for the extension. Fortunately, all you need to do is give it a name and subroutine SYSS9118.1 will do all of the initial setup this for you (see the next tab).

Prior to building your Perl Script, you should always call SYSS9118.1. This subroutine will consistantly:

  • Purge any files for your port
  • Purge any files older than 2 days
  • Build various variables that are needed later

This will also ensure that the PERL file/directory does not fill up with forgotten clutter. We want to purge any prior files with your port to ensure that your current report does not conflict with a prior report. We will not purge the Perl or Excel files when finished. This is by design. If there is a problem, we want to be able to review the script and possibly the Excel file. These files will ultimately be purged based on the criteria above.

The naming format of the file is the name/description of the report, followed by the port, followed by the extension. Note that the name of the Perl script and the Excel file are the same except for the extension. Fortunately, SYSS9118.1 will do the initial setup this for you.

See the Common Subroutines for details...

The snippets of code have been altered to better facilitate building multiple worksheets. It assumes that each of the "steps" below will be done in its own subroutine. YMMV.

There are a number of external subroutines that should be used to consistantly handle the piece of this in a consistent manor.
You should also keep track of the running 0-based Excel row with a variable called EXCEL.ROW which gets incramented with each row of data.
Also note that EXCEL.ROW gets reset by SYSS9118.5 when we add another worksheet.
PURS9034.1 has a simple example of multiple worksheets.
Initialize several variables in the calling program that will be used by the new Perl script size testing:
  •       ABORTED = @FALSE
  •       ABORTED.TYPE = ""
  •       ABORTED.MSG = ""
This subroutine cleans up prior Perl and Excel files in the PERL file/directory based on the user's port number and the age of any existing files. This ensures that we do not have a bazillion forgotten files sitting in PERL. It also establishes several common variables that should be used through out the calling program. For example:
  •       OPEN "PERL" TO F.PERL ELSE STOP "Unable to open PERL file!"
  •       MASTER.TITLE = "Active Parts Exports"
  •       SHOW = @TRUE;* TRUE turns on CRTs/FALSE turns them off
  •       EXTENSION = "XLSX"
  •       EXTRAS = ""
  •       EXTRAS<2> = EXTENSION
  •       EXTRAS<3> = 3 ;* Indent amount for CRTs if SHOW
  •       FUTURE2 = ""
  •       FUTURE3 = ""
  •       CALL SYSS9118.1(MASTER.TITLE, FINAL.FILE, MASTER.KEY, PERL.KEY, EXCEL.KEY, PERL.PATH, EXCEL.PATH, SHOW, EXTRAS, FUTURE2, FUTURER3)
  •       EXTENSION = EXTRAS<2>
Its arguments are as follows:
Argument In/Out Note
MASTER.TITLE Input
Output		 The title of the report (upper and lower case with spaces)
Unchanged
FINAL.FILE Input
Output		 n/a
This is derived from the MASTER.TITLE but with spaces converted to underscores
MASTER.KEY Input
Output		 n/a
This is derived from the MASTER.TITLE but with spaces and periods converted to underscores and with the the port number appended to the end
PERL.KEY Input
Output		 n/a
Same as MASTER.KEY with ".pl" appended to the end
EXCEL.KEY Input
Output		 n/a
Same as MASTER.KEY with ".xls" appended to the end
PERL.PATH Input
Output		 n/a
"PERL/":PERL.KEY
EXCEL.PATH Input
Output		 n/a
"PERL/":EXCEL.KEY
SHOW Input
Output		 Should the results of SYSS9118.1 be CRTed to the screen or not (true/false)
Unchanged
EXTRAS<1>

EXTRAS<2>

EXTRAS<3>

EXTRAS<4, 1>

EXTRAS<4, 2>
 Input
Output
Input
Output
Input
Output
Input
Output
Input
Output 		Border type (used in SYSS9118.2)
Unchanged
null - build xls format, xlsx - build xlsx format
If null, force to xls - UPCASEd
# of spaces to indent any CRTs
Unchanged
Tells SYSS9118.5 (& optionally SYSS9118.11) if/how totest the Perl script's size - see below
Varies
n/a
Warning/Fatal message based on EXTRAS<4, 1>
FUTURE2 Input
Output		 For future use
For future use
FUTURE3 Input
Output		 For future use
For future use
Every Perl script/program needs to have a number of common variables set in it for it to work.
  •       TAB.TITLES = MASTER.TITLE ;* name of the 1st tab/worksheet1
  •       CALL SYSS9118.2(PERL.REC, TAB.TITLES, F.PERL, EXCEL.KEY, MASTER.TITLE, EXTRAS, FUTURE2, FUTURE3)
1 This is the name of the 1st worksheet. Use a different value if there are going to be multiple worksheets or if you do not want the 1st worksheet to have the same name as the workbook. SYSS9118.2 establishes the following variables/formats:
  • Building the initial Perl script itself
  • Naming the initial worksheet (based on MASTER.TITLE from SYSS9118.1)
  • Freezing the top row
  • Establishing the DSC "blue" color
  • Defining the header row characteristics:
    • Blue background, white letters, bold, left ($hdr_format)
    • Blue background, white letters, bold, center ($hdr_center_format)
    • Blue background, white letters, bold, right ($hdr_right_format)
    • Blue background, white letters, bold, left, text-wrapping ($wrap_hdr_format)
  • Define the following formats:
    • 2-digit zero padding ($company_padding)
    • 3-digit zero padding ($padding3)
    • 6-digit zero padding ($padding6)
    • 9-digit zero padding ($quote_padding)
    • MD0 ($MD0_format)
    • MD1 ($MD1_format)
    • MD2 ($MD2_format)
    • MD3 ($MD3_format)
    • MD4 ($MD4_format)
    • MD5 ($MD5_format)
    • MM/DD/YY ($date_format)
    • MM/DD/YY HH:MM ($date_time_format)
    • Left-top justified ($left_format)
    • Center-top justified ($center_format)
    • Right-top justified ($right_format)
    • Keep leading zeros (part #s) ($keep_leading_zeros)
    • Top justified ($top_format)
    • Top justified, text-wrapping ($wrap_format)
ArgumentIn/OutComment
PERL.REC Input
Output		 n/a
The initial, common header for all Perl scripts - a dynamic array
TAB.TITLES Input
Output		 A FM delimited list of tab titles
unchanged
F.PERL Input
Output		 The open handle to the PERL file
unchanged
EXCEL.KEY Input
Output		 The name of the Excel file as defined by SYSS9118.1
unchanged
MASTER.TITLE Input
Output		 The title of the Excel worksheet as defined by SYSS9118.1
unchanged
EXTRAS<1>

EXTRAS<2>

EXTRAS<3>		 Input
Output
Input
Output
Input
Output		 The cell border type as defined here (xlsx) or here (xls - old format) - null or zero for no border
Unchanged
null - build xls format, xlsx - build xlsx format
unchanged
# of spaces to indent any CRTs
Unchanged
FUTURE2 Input
Output		 For future use
For future use
FUTURE3 Input
Output		 For future use
For future use
If we find we have standard formats that are not being build, we can add them to SYSS9118.2 later.
If a program has a unique format that it needs, simply append that to PERL.REC after returning from SYSS9118.2.
If you want to have the text in a header wrap, you must take some extra steps. Immediately after returning from SYSS9118.2, add a unique format.
  •       CALL SYSS9118.2(PERL.REC, TAB.TITLES, F.PERL, EXCEL.KEY, MASTER.TITLE, EXTRAS, FUTURE2, FUTURE3)
  •  
  •       * Wrapping text in a header cell (title)
  •       PERL.REC<-1> = '# Wrapping text in a cell format'
  •       PERL.REC<-1> = '$wrap_hdr_format = $workbook->add_format(); # Add the header format'
  •       PERL.REC<-1> = '$wrap_hdr_format->set_bold();'
  •       PERL.REC<-1> = '$wrap_hdr_format->set_color("white");'
  •       PERL.REC<-1> = '$wrap_hdr_format->set_bg_color($DSC_Blue);'
  •       PERL.REC<-1> = '$wrap_hdr_format->set_align("bottom");'
  •       PERL.REC<-1> = '$wrap_hdr_format->set_text_wrap();'
  •       PERL.REC<-1> = ''
  •  
  •       * Wrapping text in a data cell
  •       PERL.REC<-1> = '# Wrapping text in a cell format'
  •       PERL.REC<-1> = '$wrap_format = $workbook->add_format(); # Add the data format'
  •       PERL.REC<-1> = '$wrap_format->set_align("top");'
  •       PERL.REC<-1> = '$wrap_format->set_text_wrap();'
  •       PERL.REC<-1> = ''
  •  
  •       * Add 12-digit zero-filled format
  •       PERL.REC<-1> = '# Add and define zero padding justified format for 12-digits'
  •       PERL.REC<-1> = '$padding12 = $workbook->add_format(num_format => "000000000000");'
  •       PERL.REC<-1> = '$padding12->set_align("right");'
  •       PERL.REC<-1> = '$padding12->set_align("top");'
  •       PERL.REC<-1> = ''
  •  
  •       PERL.REC<-1> = "# Set Properties'"
  •       PERL.REC<-1> = "$workbook->set_properties("
  •       PERL.REC<-1> = "   title    => '":MASTER.TITLE:"',"
  •       PERL.REC<-1> = "   author   => 'John Israel, Sr ERP Programmer/Analyst',"
  •       PERL.REC<-1> = "   company  => 'Dayton Superior',"
  •       PERL.REC<-1> = "   keywords => '":THIS.PROGRAM:"',"
  •       PERL.REC<-1> = "   comments => 'This workbook shows all open quotes that have not expired.'"
  •       PERL.REC<-1> = ");"
  •       PERL.REC<-1> = ""
We now have a big Pick record (PERL.REC) that is the initial part of the Perl script (formating & headers, but no real data).
It is vital that we build PERL.REC as a dynamic array so that we can insert custom formating if needed (see SYSS9118.2).
Write PERL.REC to F.PERL like a normal record, close F.PERL, then open a sequencial file (using a different name to be clear).
Note that the 1st comment is actually going to refer to the data in Avante you will loop through.
It might look something like this:
  •       * Write the initial stuff to the Perl script then close the file
  •       WRITE PERL.REC ON F.PERL, PERL.KEY
  •  
  •       CLOSE F.PERL
  •  
  •       * Openseq the file so we can append data for better performance
  •       OPENSEQ "PERL", PERL.KEY TO F.PERLSEQ ELSE
  •          CRT STATUS()
  •          STOP "Unable to OPENSEQ PERL ":PERL.KEY:"!"
  •       END
Set the worksheet #,
  • ******
  • 2000 * Build the 1st worksheet
  • ******
  •       WORKSHEET = 1
  •       GOSUB 6000 ;* Common column and header setup
  •       GOSUB 2100 ;* Select & loop thru the parts for worksheet 1
  •       RETURN
Based on the value of WORKSHEET, define the column formats and header values.
  • ******
  • 6000 * Common column and header setup
  • ******
  •       NO.PERL.WRITES = 0
  •       * Set column properties
  •       BEGIN CASE
  •          CASE (WORKSHEET = 1)
  •             PERL.BLOCK = '# Set the column widths & formats for worksheet ':WORKSHEET
  •             PERL.BLOCK<-1> = '$worksheet->set_column("A:A", 5, $company_padding);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("B:B", 10, $quote_padding);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("C:C", 8, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("D:D", 19, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("E:E", 32, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("F:F", 22, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("G:G", 10, $center_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("H:H", 60, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("I:I", 11, $center_format);'
  •             NO.COLS = 8 ;* # of columns minus 1
  •          CASE (WORKSHEET = 2)
  •             PERL.BLOCK = '# Set the column widths & formats for worksheet ':WORKSHEET
  •             PERL.BLOCK<-1> = '$worksheet->set_column("A:A", 7, $padding3);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("B:B", 10, $left_format);'
  •             PERL.BLOCK<-1> = '$worksheet->set_column("C:C", 10, $left_format);'
  •             NO.COLS = 2 ;* # of columns minus 1
  •          CASE 1
  •       END CASE
  •  
  •       * Initialize the col & row
  •       PERL.BLOCK<-1> = ''
  •       PERL.BLOCK<-1> = '$col = $row = 0;'
  •  
  •       * Note that Perl uses a zero offset so:
  •       *     $col 0 equals Excel column 1
  •       *     $row 0 eauals Excel row 1 (i.e. A)
  •       EXCEL.ROW = 0
  •  
  •       PERL.BLOCK<-1> = ''
  •       PERL.BLOCK<-1> = '# Build the header row for worksheet ':WORKSHEET
  •  
  •       * Build the header row
  •       BEGIN CASE
  •          CASE (WORKSHEET = 1)
  •             PERL.BLOCK<-1> = '$worksheet->write("A1", "Part\nNumber", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("B1", "Part Description", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("C1", "UOM", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("D1", "SLS\nCat", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("E1", "SLS Cat\nDescription", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("F1", "Weight", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("G1", "Prop\n65", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("H1", "Buyer", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("I1", "M/P", $wrap_hdr_format);'
  •          CASE (WORKSHEET = 2)
  •             PERL.BLOCK<-1> = '# Set the column widths & formats for worksheet ':WORKSHEET
  •             PERL.BLOCK<-1> = '$worksheet->write("A1", "ZIPXRF", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("B1", "City", $wrap_hdr_format);'
  •             PERL.BLOCK<-1> = '$worksheet->write("C1", "State", $wrap_hdr_format);'
  •          CASE 1
  •       END CASE
  •  
  •       * Write the PERL.BLOCK to disk
  •       PERL.BLOCK<-1> = ''
  •       PERL.BLOCK<-1> = '# Build the individual rows'
  •       GOSUB 8000 ;* Common WRITESEQ logic for PERL.BLOCK
  •  
  •       * Reset NO.PERL.WRITES - it is inteneded to track rows of data (not the headers
  •       NO.PERL.WRITES = 0
  •       RETURN
Each program will be unique, based on the requirements.
However, each row of Excel data should probably be populated into a Pick array, and like the header, we will let SYSS9118.3 do the heavy lifting.
To keep the code tidy, extract and massage the data that you will ultimately put in the array before you populate PERL.ARRAY. It is important to note that we need to treat several types of data differently.
If the field in question has leading zeros but is fixed length, that should be handled by the column setup (see above).
If a field might have leading zeros (like a part #) and is not fixed length, it needs to be treated differently.
For these conditions, put a null into the field of the array that is passed to SYSS9118.3, and we will have to specifically populate those cells.
It might look something like this:
  •       READV SCHD.DT FROM F.QUDEL, QUDET.KEY:"*":DEL.NO, 4 ELSE SCHD.DT = ''
  •       CALL SYSS9118.4(EXCEL.SCHD.DT, SCHD.DT, "D", "", "", "") ;* Convert Pick date to Excel date
  •  
  •       * Build an array of the needed data
  •       PERL.ARRAY = COMPANY
  •       PERL.ARRAY<2> = QUHDR.KEY
  •       PERL.ARRAY<3> = HDR.STATUS.DESC
  •       PERL.ARRAY<4> = HDR.CONF.FACTOR.DESC
  •       PERL.ARRAY<5> = CONTACT.NAME
  •       PERL.ARRAY<6> = CONTACT.PHONE
  •       PERL.ARRAY<7> = BILL.TO.CUSTOMER
  •       PERL.ARRAY<8> = BILL.TO.NAME
  •       PERL.ARRAY<9> = QUHDR.REC<3> ;* Ship-to
  •       PERL.ARRAY<10> = QUHDR.REC<4> ;* Ship-to name
  •       PERL.ARRAY<11> = REGION
  •       PERL.ARRAY<12> = QUHDR.REC<249> ;* territory
  •       PERL.ARRAY<13> = DD1 ;* Line #
  •       PERL.ARRAY<14> = DET.STATUS.DESC ;* quote line status
  •       PERL.ARRAY<15> = QUDET.REC<32> ;* ship warehouse
  •       PERL.ARRAY<16> = "" ;* Scheduled data - added later
  •  
  •       IF (ITMMST.REC<1>[1,1] = "0") THEN
  •         * Part # has a leading zero, so we need to handle this differently
  •         PERL.ARRAY<17> = ""
  •       END ELSE
  •         PERL.ARRAY<17> = ITMMST.REC<1>
  •       END
  •  
  •       CALL SYSS9118.3(PERL.ARRAY) ;* Convert a Pick array to a PERL.ARRAY
  •       PERL.ARRAY = '@row = (':PERL.ARRAY:');'
  •  
  •       EXCEL.ROW += 1
  •  
  •       PERL.BLOCK = PERL.ARRAY
  •       PERL.BLOCK<-1> = '$array_ref = \@row;'
  •       PERL.BLOCK<-1> = '$worksheet->write_row(':EXCEL.ROW:', 0, $array_ref);'
  •  
  •       * Because dates, times and part #s require special "writes", we need to do them seperately.
  •       * Since the write_row logic is going off a zero-based reference and the following lines are using a
  •       * reference that starts with 1, we need to add 1 to the Excel row to line it up correctly.
  •       PERL.BLOCK<-1> = '$worksheet->write_date_time("P':(EXCEL.ROW+1):'", "':EXCEL.SCHD.DT:'", $date_format);'
  •  
  •       IF (ITMMST.REC<1>[1,1] = "0") THEN
  •         * Part # has a leading zero, so we need to handle this differently
  •         PERL.BLOCK<-1> = '$worksheet->write_string("Q':(EXCEL.ROW+1):'", "':ITMMST.REC<1>:'", $keep_leading_zeros);'
  •       END
  •  
  •       GOSUB 8000 ;* Common WRITESEQ logic for PERL.BLOCK
@-Signs
If you have data with the @-sign (like an email address), you may need to escape the data, esp. if you have multi-lines (word wrap). Do not add the data to the PERL.ARRAY. Instead, add its own PERL.BLOCK append. Simply add code something like this:
  •       EMAIL.ADDRESSES = CUSTMST.USR<7>
  •       EMAIL.ADDRESSES = CHANGE(EMAIL.ADDRESSES, "@", "\@") ;* escape the @-sign
  •       EMAIL.ADDRESSES = CHANGE(EMAIL.ADDRESSES, @VM, "\n") ;* convert mv to line break (assumes the cell has text-wrap format)
  •       PERL.BLOCK<-1> = '$worksheet->write_string("E':(EXCEL.ROW+1):'", "':EMAIL.ADDRESSES:'", $wrap_format);'
Double Quotes, Single Quotes & Line Breaks
Sometime, you may need to have data that includes double quotes, single quotes and/or line breaks in a cell (like comments or descriptions). To do this this, we want to leave that field blank in PERL.ARRAY and load it seperately. Assuming the multiple lines are sperated with @VMs, you could write the codes something like this:
  •       TEMP.COMMENTS = Your_original_variable
  •       CALL SYSS9118.10(SCRUBBED.COMMENTS, TEMP.COMMENTS, "")
  •       COMMENTS = SCRUBBED.COMMENTS
  •       COMMENTS = CHANGE(COMMENTS, @VM, "\n")
  •       PERL.BLOCK<-1> = 'my $string = qq(':COMMENTS:');'
  •       PERL.BLOCK<-1> = '$worksheet->write("V':(EXCEL.ROW+1):'", $string, $wrap_format);'
Note that the number of parentheses in COMMENTS must be equal or it will cause issues with the Perl script. SYSS9118.10 will fix this. See below for details.
If you need a 2nd (3rd, 4th, etc.) worksheet, use the following to close out the current worksheet and start a new one:
  • ******
  • 3000 * Build the 2nd worksheet
  • ******
  •       TAB.TITLE = "Zip Codes"
  •       EXTRAS<4>  = 1    ;* null = no size test/1 = size test
  •       EXTRAS<10> = NO.COLS ;* Override the default # of columns used for filters
  •       FUTURE2    = ""
  •       FUTURE2    = ""
  •       CALL SYSS9118.9(F.PERLSEQ, EXCEL.ROW, TAB.TITLE, EXTRAS, FUTURE2, FUTURE3)
  •  
  •       WORKSHEET = 2
  •       GOSUB 6000 ;* Common column and header setup
  •       GOSUB 3100 ;* Select & loop thru the parts for worksheet 2
  •       RETURN
Sometimes, you may need to build an Excel workbook that contains more than 1 worksheet. This is easy to accomplish by adding a few extra steps. When you are done populating the desired worksheet, call SYSS9118.9. This routine will:
  1. Turn on the auto filters for the current workbook
  2. Initiate a new workbook
  3. Freeze the top row (headers) for the new workbook
  4. Reset EXCEL.ROW to zero
Now define the columns properties for the new worksheet and build the headers (see the steps above)
ArgumentIn/OutComment
F.PERLSEQ Input
Output		 Opened seq file handle
unchanged
EXCEL.ROW Input
Output		 The current row of the current worksheet
Re-set to zero for new worksheet
TAB.TITLE Input
Output		 The name of the new worksheet tab
unchanged
EXTRAS<1>

EXTRAS<2>

EXTRAS<3>		 Input
Output
Input
Output
Input
Output		 Border type (used in SYSS9118.2)
Unchanged
Excel format (used in SYSS9118.1)
Unchanged
# of spaces to indent any CRTs
Unchanged
FUTURE2 Input
Output		 For future use
For future use
FUTURE3 Input
Output		 For future use
For future use
Converts a PICK array (the new "row") into a Perl script array with all the special quoting logic. This can get a bit tricky, as there are various ways that Perl considers something quoted. This subroutine is smart enough to use the best method of quoting based on the data in question. The end result is that each element of the resulting PERL.ARRAY will be enclosed with either:
  • Double quotes or
  • q{raw data}
Argument In/Out Comment
PERL.ARRAY Input
Output		 A FM delimited array
A Perl Array
For example:
  •      PERL.ARRAY = COMPANY
  •      PERL.ARRAY<2> = QUHDR.KEY
  •      PERL.ARRAY<3> = HDR.STATUS.DESC
  •      PERL.ARRAY<4> = HDR.CONF.FACTOR.DESC
  •      PERL.ARRAY<5> = CONTACT.NAME
  •      PERL.ARRAY<6> = CONTACT.PHONE
  •      PERL.ARRAY<7> = BILL.TO.CUSTOMER
  •      PERL.ARRAY<8> = BILL.TO.NAME
  •      PERL.ARRAY<9> = QUHDR.REC<3> ;* Ship-to
  •      PERL.ARRAY<10> = QUHDR.REC<4> ;* Ship-to name
  •      CALL SYSS9118.3(PERL.ARRAY)
When control is returned back to the calling program, PERL.ARRAY (which has been converted from a Pick array to a Perl array) would look something like:
  •      q{01}, q{001148362}, "Open", "G - UNKNOWN", "DEE BARFIELD", "601-693-4301", "102232", "MAGNOLIA STEEL CO.", "102232", "MSC"
Note that the values with leading zeros (or anyvalue with a double quote) is enclosed using the 2nd method described above.
This could then be written to disk something like this:
  •      PERL.ARRAY = '@row = (':PERL.ARRAY:');'
  •  
  •      EXCEL.ROW += 1
  •  
  •      PERL.BLOCK = PERL.ARRAY
  •      PERL.BLOCK<-1> = '$array_ref = \@row;'
  •      PERL.BLOCK<-1> = '$worksheet->write_row(':EXCEL.ROW:', 0, $array_ref);'
Remember to increment your row counter (in this case EXCEL.ROW) for each Excel row you write to disk.
Converts a date or time from its raw format to the format expected by Excel. Excel expects the date and/or time to be in a very specific format and this subroutine will take a raw Pick date or time and make that conversion. Since dates and times need special formating, you cannot use the normal write_row method used to write an entire array (row) of data. Instead, you need to use the write_date_time method and put the data into a specific cell. If you had previously written the entire row using write_row, the write_date_time method will replace the contents of the specified cell.
ArgumentIn/OutComment
RTN.VALUEInput
Output		n/a
The exported date or time
ORIG.VALUEInput
Output		The raw date or time in PICK format
unchanged
FLAVORInput
Output		"D" for date, "T" for time or "TXT" for multi-valued1
unchanged
EXTRAS<1>

EXTRAS<2>

EXTRAS<3>		 Input
Output
Input
Output
Input
Output		 Border type (used in SYSS9118.2)
Unchanged
Excel format (used in SYSS9118.1)
Unchanged
# of spaces to indent any CRTs
Unchanged
FUTURE2 Input
Output		 For future use
For future use
FUTURE3 Input
Output		 For future use
For future use
1 Removes lines with only a dot, empty lines and double quotes (may still need some work)
  •      ENTRY.DATE = QUHDR.REC<9>
  •      CALL SYSS9118.4(EXCEL.ENTRY.DATE, ENTRY.DATE, "D", "", "", "") ;* Convert Pick date to Excel date
  •  
  •      PERL.BLOCK<-1> = '$worksheet->write_date_time("AD':(EXCEL.ROW+1):'", "':EXCEL.ENTRY.DATE:'", $date_format);'
Note that we put it in row (EXCEL.ROW + 1) because Perl uses a zero-based array.
This routine will do all the common finish-up steps of any Perl script. This includes:
  1. Turning on Auto Filters
  2. Closing the Excel file
  3. Closing the SEQ file
  4. Makes the Perl script executable
  5. Test the size of the script and warn as needed. The calling program must be written to re-act accordingly.
  6. Executing the Perl script (which technically does not mean it worked)
ArgumentIn/OutComment
EXCEL.ROW
Input
Output
The Excel row # being updated
n/a
PERL.PATH
Input
Output
The relative path to the Perl script
unchanged
F.PERLSEQ
Input
Output
The open seq file handle
unchanged
SHOW
Input
Output
Should the program display things with CRT commands
unchanged
EXTRAS<1>
 
EXTRAS<2>
 
EXTRAS<3>
 
EXTRAS<4,1>
 
EXTRAS<4,2>
 
Input
Output
Input
Output
Input
Output
Input
Output
Input
Output
Border type (used in SYSS9118.2)
Unchanged
Excel format (used in SYSS9118.1)
Unchanged
# of spaces to indent any CRTs
Unchanged
Size issue flag1
If 0 then unchanged, otherwise variable
n/a
Size issue message if EXTRAS<4,1> > 2
FUTURE2
Input
Output
For future use
For future use
FUTURE3
Input
Output
For future use
For future use

1Size Issue Flags
CodeResult
0 or null Never test Perl script size (default - backward compatible).
If the Perl script will never get big, this is the option to use & no additional abort logic is needed.
1 This is the initial setting to use if we wish to test the Perl script size.
It tells SYSS9118.5 (and optionally SYSS9118.11) to test the Perl script size.
If the size exceeds the WARNING or FATAL size, it will display a message as needed.
So long as there are no size issues, this value will not change.
The calling program must be written to cleanly exit if the script is too big.
2 This option is only available if SYSS9118.11 is used to periodically test the Perl script size as it is being built.
If the Perl script exceeds the WARNING size and the user chooses to continue, the flag will be set to "2".
The user will only get 1 WARNING, but it will still test for the FATAL byte size.
This should never be used with an ePortal program.
The calling program must be written to cleanly exit if the script is too big.
8 The Perl script's size has exceeded the WARNING byte size & the user wants to quit.
The calling program must be written to cleanly exit if the script is too big.
9 The Perl script's size has exceeded the FATAL byte size & the program should quit.
The calling program must be written to cleanly exit if the script is too big.
For example:
  •       * EXTRAS<4> is used to tell SYSS9118.5 to test/not test the Perl script size
  •       EXTRAS<4> = 1 ;* null = no size test/1 = size test
  •  
  •       SHOW = @TRUE
  •       CALL SYSS9118.5(EXCEL.ROW, PERL.PATH, F.PERLSEQ, SHOW, EXTRAS, FUTURE2, FUTURE3)
  •  
  •       IF (EXTRAS<4, 1> > 2) THEN
  •          * The process was aborted
  •          ABORTED = @TRUE
  •          ABORTED.TYPE = EXTRAS<4, 1>
  •          ABORTED.MSG = EXTRAS<4, 2>
  •       END
  • Based on the program, you must decide how the program should react if ABORTED.
  • ABORTED.TYPE will be an 8 if the user chose to exit or a 9 if the Perl size was fatally large.
  • ABORTED.MSG will have a detailed message as to why it was aborted.
This subroutine verifies that the Excel file was built correctly and if not, sends an error E-mail. After calling SYSS9118.5 to clean up and execute the Perl script, you should always call this routine and react accordingly.
Argument In/Out Note
SUCCESS Input
Output		 n/a
True or False
MASTER.TITLE Input
Output		 The title of the report (upper and lower case with spaces)
Unchanged
CALLED.BY Input
Output		 The calling program
n/a
EXCEL.PATH Input
Output		 The relative path to the Excel file
n/a
PERL.PATH Input
Output		 The relative path to the Perl file
n/a
SHOW Input
Output		 Should the results of SYSS9118.2 be CRTed to the screen or not (true/false)
Unchanged
EXTRAS<1>

EXTRAS<2>

EXTRAS<3>		 Input
Output
Input
Output
Input
Output		 Border type (used in SYSS9118.2)
Unchanged
Excel format (used in SYSS9118.1)
Unchanged
# of spaces to indent any CRTs
Unchanged
FUTURE2 Input
Output		 For future use
For future use
FUTURE3 Input
Output		 For future use
For future use
For example:
  •       SHOW = @FALSE
  •       CALLING.PGM = THIS.PROGRAM
  •       * Verify that an Excel file was correctly built and if not, send an error E-mail
  •       CALL SYSS9118.6(SUCCESS, MASTER.TITLE, CALLING.PGM, EXCEL.PATH, PERL.PATH, SHOW, EXTRAS, FUTURE2, FUTURE3)
  •  
  •       IF SUCCESS THEN
  •          Do something with the Excel file (E-mail, Samba, etc.)
  •          See a possible example below.
  •       END ELSE
  •          React accordingly
  •       END
Here is an example of how you could E-mail the Excel file.
Since the E-mailer (SYSS9002.1) expects all attachments to have been built in DLDATA, we need to copy and rename the Excel file.
If in an Avante account, do the following:
  •       EMAIL.CTL = 1
  •       CALL SYSS9002.1(EMAIL.CTL, F.SEQ, THIS.PROGRAM)
  •       DLDATA.KEY = EMAIL.CTL<1>
  •       UNIX.STMT = "!cp ":EXCEL.PATH:" ../../dldata/":DLDATA.KEY
  •       IF SHOW THEN
  •          CRT
  •          CRT UNIX.STMT
  •       END
  •       EXECUTE UNIX.STMT CAPTURING JUNK
The above syntax does not work in ePortal because the paths in the UNIX.STMT are relative to Avante. To get this to work from ePortal (or some other non-Avante account), try the following instead:
  •       * EXCEL.PATH is local, but PERL is in the Avante account, so we
  •       * need to get the path to PERL in order for the UNIX cp to work
  •       OPEN "VOC" TO F.VOC ELSE STOP "Unable to open VOC!"
  •       READV PERL.PATH FROM F.VOC, "PERL", 2 ELSE STOP "Unable to read VOC PERL!"
  •       PERL.PATH = CHANGE(PERL.PATH, "PERL", "")
  •       READV DLDATA.PATH FROM F.VOC, "DLDATA", 2 ELSE STOP "Unable to read VOC DLDATA!"
  •  
  •       * Move the file to DLDATA so the Emailer can find it
  •       EMAIL.CTL = 1
  •       CALL SYSS9002.1(EMAIL.CTL, F.SEQ, THIS.PROGRAM)
  •       DLDATA.KEY = EMAIL.CTL<1>
  •       UNIX.STMT = "!cp ":PERL.PATH:EXCEL.PATH:" ":DLDATA.PATH:"/":DLDATA.KEY
  •      IF SHOW THEN
  •         CRT
  •         CRT UNIX.STMT
  •       END
  •       EXECUTE UNIX.STMT CAPTURING JUNK
You can now use the E-mailer (SYSS9002.1) normally to send this file as an attachment.
This subroutine is used by I-Descs in the PERL file to return the size of the file or the date it was built. Chances are you will not call this from another program, though you could if needed.
Argument In/Out Note
RTN.VALUE Input
Output		 n/a
The size or date of the file in question
PATH Input
Output		 The path (filename) of the Perl or Excel file
Unchanged
FLAVOR Input
Output		 "D" of date or "S" for size
n/a
SHOW Input
Output		 Should the results of SYSS9118.2 be CRTed to the screen or not (true/false)
Unchanged
Determine currency conversion between countries based on a primary currency. It is written to be used in an I-Desc, but could be used in any program (or tested with SYSS9118.8.DRIVER).
Argument In/Out Note
RTN.VALUE Input
Output		 n/a
The conversion ration between the primary and other countries
BANK Input



Output		 F1 - mv list of companies - 1st must be the primary company
F2 - mv list of currencies
F3 - mv list of conversion codes or mv/sv list of conversion codes by date
F4 - null or mv list of dates
Unchanged
COMPANY Input
Output		 The company to test
n/a
USE.DATE Input
Output		 null or mv list of dates
Unchanged
In order to load this data with Perl, our data must be scrubbed and written seperately. Fortunately, we have a routine that will do most of this for us.
Argument In/Out Note
SCRUBBED.TECT Input
Output		 n/a
The text after it has been scrubbed, based on OPTIONS
ORIG.TEXT Input
Output		 The original text that needs to be scrubbed
n/a
OPTIONS Input





Output 		F1 -

F2 -

F3 -

n/a 		null or zero - TRIM the text (default)
1 - Do NOT TRIM
null or zero - Remove blank lines (default)
1 - Keep blank lines
null or zero - Do not force to upper case (default)
1 - Force to upper case
Here is an example of how you could write a PERL.BLOCK to disk:
  • ******
  • 8000 * Common WRITESEQ logic for PERL.BLOCK
  • ******
  •       NO.LINES = DCOUNT(PERL.BLOCK, @FM)
  •       FOR XX8 = 1 TO NO.LINES
  •           PERL.LINE = PERL.BLOCK<XX8>
  •           WRITESEQF PERL.LINE APPEND ON F.PERLSEQ ELSE NULL
  •       NEXT XX8
  •       NO.PERL.WRITES += 1
  •  
  •       IF (MOD(NO.PERL.WRITES, 100) = 0) THEN
  •           CALL SYSS9118.11(KEEP.GOING, PERL.PATH, PERL.CTL)
  •           IF (KEEP.GOING<1> > 2) THEN
  •               * The process was aborted
  •               ABORTED = @TRUE
  •               ABORTED.TYPE = KEEP.GOING<1> ;* 8=user choice | 9=program choice
  •               ABORTED.MSG = KEEP.GOING<2>
  •               * Kill the loop
  •               LOOPER1 = @FALSE
  •               CLEARSELECT
  •           END
  •       END
  •       RETURN
This is a stand-alone program that is run from cron every hour. It executes a UNIX ll command against the PERL directory to get the file names, sizes & date/time stamps. It scrubs the results, then loops through the remaining contents. SYSS9149.1 is then used to consistantly determine the age of the file (compares UNIX file date/time vs. system date/time). Any file older than an hour will unconditionally be deleted (since this runs every hour, the oldest file could be just under 2 hours old).
This program is not required but is highly recommended as a clean-up routine to delete the Perl and Excel files after the emailing (or moving) logic has been completed. In general, once the Excel file has been emailed (or moved), there is no reason to keep the files. The exception to this is if there is a problem with the export and we need to investigate the Perl script. In that case, add the user's ID to DSCTBL PERL.CTL<5> and re-run the process. When finished, the user will be prompted if they want to delete the files or not.
Argument In/Out Comments
PERL.KEY Input
Output		 The name of the Perl script
Unchanged
EXCEL.KEY Input
Output		 The name of the Excel file
Unchanged
  • If the user's ID is found in DSCTBL PERL.CTL<5>, the user will be asked if they want ot delete the 2 files or not.
  • If the user's ID is not found in DSCTBL PERL.CTL<5>, the files are automatically deleted.
  • Note that cronuser/SCHED will never be prompted and will always delete the files.
  • Also note that there are various other processes that will eventually delete these files. This is simply a way to clean them up as soon as the driving process is finished.
Here is an example of how you could write the E-mailing logic:
If ABORTED, be sure to alter the body of the email as needed. This will likely include:
  • Do not add the attachment (because it was aborted)
  • Use the ABORTED.MSG as the body of the email (explains why it was aborted)
  • Set the priority to High
  •      EMAIL.CTL = 5
  •      EMAIL.CTL<2> = "Blah Blah Blah"
  •      EMAIL.CTL<3> = ""
  •      EMAIL.CTL<4> = @TRUE
  •      EMAIL.CTL<4,2> = EXCEL.KEY2
  •      EMAIL.CTL<5> = DOWNCASE(EXTENSION)
  •      EMAIL.CTL<6> = EMAIL.BODY
  •      CALL SYSS9002.1(EMAIL.CTL, F.SEQ, THIS.PROGRAM)