/* IncomeExpenseToExcel.rex */
/*
Les Koehler
9Jan2006
Based on code provided by Lee Peedin
Reads in the monthly Income/Expense .prt file and places data in Excel
*/
Arg monyy '('print
Call Time('r')                     /* Just to see how long this takes */
print?=Abbrev(print,'PR')
/* Define location of the .prt file  WILL HAVE TO BE CHANGED DEPENDENT ON SYSTEM ITS RUNNING ON */
file_path = 'c:\Documents and Settings\Les\My Documents\HOA\Financials\'
Trace or
parse value hoadate(monyy) with err? mon yy rest
if err? then do
  say mon yy rest
  exit

end
months='JanFebMarAprMayJunJulAugSepOctNovDecJan'
file_name=mon||yy'_IncomeExpense.prt'

infile = file_path||file_name
Parse Var file_name fname'.'.
outfile = file_path||fname'.xls'
Call syscls
Call killexcel
Signal On SYNTAX

xlobj = .oleObject~new('Excel.Application') /* Create an Excel object */
xlobj~Visible = .true                           /* Make Excel visible */
xlobj~Application~SheetsInNewWorkBook = 1   /*Create a new workbook with 1 worksheet */
xlobj~WorkBooks~Add
font_name = 'Arial Rounded MT Bold' /* Define the font attributes we'll use later */
font_size = 8      /* Leave the font_size BIG - the printing to a single page will */
                    /* take care of it fitting on the sheet */

/* Need this to convert column numbers to column letters */
col_letters = 'A B C D E F G H I J K L M N O P Q R S T U V W X Y Z'

/* Retrieve some constant values we'll need later (they're stored within Excel) */
xllastcell    = xlobj~GetConstant('xlLastCell')
xlright       = xlobj~GetConstant('xlRight')
xledgeleft    = xlobj~GetConstant('xlEdgeLeft')
xledgetop     = xlobj~GetConstant('xlEdgeTop')
xledgebottom  = xlobj~GetConstant('xlEdgeBottom')
xledgeright   = xlobj~GetConstant('xlEdgeRight')
xlcontinuous  = xlobj~GetConstant('xlContinuous')
xlcenter      = xlobj~GetConstant('xlCenter')
xllandscape   = xlobj~GetConstant('xlLandScape')
xlpaperletter = xlobj~GetConstant('xlPaperLetter')

/* Fastest method in ooRexx to read in an entire file and convert it to an array */
istream = .stream~new(infile)
iarray  = istream~charin(,istream~chars)~makearray
istream~close

/* Define some boolean values (you'll see their use as the code progresses) */
found_title = .false
skip_line = .false

/* Define an array to store our header lines - need to do all formatting before insert of headers */
header_lines = .array~new

row = 3                             /* Starting point for report data */
Do i = 1 To iarray~items    /* Process each array element (rows in our text file) */
  aline = iarray[i]~strip
  If aline='' Then Iterate i
  If Left(aline~word(1),1) = '_' | Left(aline~word(1),1) = '=' Then Iterate i   /* Don't need these line */

  If aline~word(1) = 'REVENUE:' Then found_title = .true
  If \found_title Then header_lines[header_lines~items + 1] = aline         /* These must be header lines */
  Else Do
    row = row + 1
    Select    /* Based on word count, determine where to put our data */
      When aline~words < 9 Then Do             /* A "description row" */
        column = 1
        xlobj~Cells(row,column)~Value = aline
      End
      Otherwise Do                             /* Must be actual data */
        col_a_words = aline~words - 7
        col_a_data  = aline~subword(1,col_a_words)
        column = 1
        If col_a_data~word(1) = 'Total' Then Do
          col_a_data = '       'col_a_data
/*          skip_line = .true             -- Set a boolean to pretty up the print */
        End
        If col_a_data~word(1)='CURRENT' Then col_a_data='NET INCOME/(LOSS)'
        xlobj~Cells(row,column)~Value = Left(Space(titles(col_a_data)),25)
        column = 2
        Do w = col_a_words + 1 To aline~words
          If aline~word(w)~reverse~left(1) = '-' Then Do
            temp = aline~word(w)
            temp='-'temp~left(temp~length-1)
            xlobj~Cells(row,column)~Value=temp
          End
          Else xlobj~Cells(row,column)~Value = aline~word(w)
          column = column + 1
        End
      End
    End                                           /* Select statement */
    If skip_line Then Do
      row = row + 1
      skip_line = .false
    End
  End                                                  /* else clause */
End                                               /* 1 to array~items */
/* All the data is there, now add our stuff */
/* Insert rows between the existing rows 3 & 4 */
insert_before = 4
xlobj~Rows(insert_before':'insert_before)~Insert
xlobj~Rows(insert_before':'insert_before)~Insert
xlobj~Rows(insert_before':'insert_before)~Insert
/* Insert columns */
insert_before = 'H'                                   /* For YTD %var */
xlobj~Columns(insert_before':'insert_before)~Insert
insert_before = 'E'                                   /* For MTD %var */
xlobj~Columns(insert_before':'insert_before)~Insert
t.1='___________ <-------- Current -------- -------->  <-------- --YTD---  -------- --------> Yearly'
t.2='Description <-Actual- -Budget --$Var-- ---%Var-> <--Actual- -Budget- ---$Var-- --%Var--> Budget'
row=4

Do r=1 To 2
  Do c=1 To Words(t.r)
    xlobj~Cells(row+r,c)~Value = Translate(Word(t.r,c),' ','_')
  End
End
row=row+3
formula='=IF(RC[-2]>0,(RC[-1]/RC[-2])*100,0)'

lastcell = xlobj~ActiveCell~SpecialCells(xllastcell)~Address    /* Determine last cell used */
Parse Var lastcell '$'max_column'$'max_row                      /* Parse that for column & row */
--mark
/* Set the font name and size for the used range */
xlobj~Range('A5:'lastcell)~Select
myselection = xlobj~Selection
mySelection~Font~Name = font_name
mySelection~Font~Size = font_size

/* Autofit the column widths to match our data */
xlobj~Columns('A:'max_column)~select
xlobj~Selection~Columns~AutoFit

cols='5 9'
Do cc=1 To Words(cols) /* Put the %Var formula in our cols */
  c=Word(cols,cc)      /* We do this AFTER autofit because */
  Do r=row To max_row  /* the formula is so long           */
    If examine_cell(r,c-1)\='EMPTY' Then ,
     xlobj~Cells(r,c)~formula=formula
  End
End

/* Right justify all data */
/*xlobj~Range('B8:'max_column||8)~Select */
xlobj~Range('B8:'lastcell)~Select
myselection = xlobj~Selection
mySelection~HorizontalAlignment = xlright
mySelection~NumberFormat = '#0.00'
/*
xlobj~Range('B8:'max_column||max_row)~Select
xlobj~Selection~NumberFormat = '#0.00'
*/
/* Draw boxes around only the cells that are not .nil */
max_column_number = col_letters~wordpos(max_column)
Do r = 5 To max_row
  Do c = 1 To max_column_number
    column_letter = col_letters~word(c)
    xlobj~Range(column_letter||r)~Select
    myselection = xlobj~Selection
    box?=1
    If c=1 Then Do
      next_column_letter=col_letters~word(2)
      xlobj~Range(next_column_letter||r)~Select
      myselection_next = xlobj~Selection
      If mySelection_next~Value = .nil Then box?=0
    End
    If mySelection~Value \= .nil & box? Then Do
      mySelection~Borders(xledgeleft)~LineStyle   = xlcontinuous
      mySelection~Borders(xledgetop)~LineStyle    = xlcontinuous
      mySelection~Borders(xledgebottom)~LineStyle = xlcontinuous
      mySelection~Borders(xledgeright)~LineStyle  = xlcontinuous
    End
  End
End

/* Now that we are done formatting, go back and insert our header line data */
Do i = 1 To header_lines~items
  xlobj~Cells(i,1)~Value          = header_lines[i]
  xlobj~Cells(i,1)~Font~Name      = font_name /* Set the font name and size for the header line */
  xlobj~Cells(i,1)~Font~Size      = font_size
  xlobj~Range('A'i':'max_column||i)~Select
  myselection                     = xlobj~Selection
  mySelection~HorizontalAlignment = xlcenter  /* Center and merge the header line */
  mySelection~Merge
End

xlobj~DisplayAlerts = .false        /* Don't alert us if the document already exists */
xlobj~ActiveSheet~SaveAs(outfile)                /* Save the document */

/* Set up information to print */
xlobj~ActiveSheet~PageSetup~PrintArea         = '$A$1:'lastcell
xlobj~ActiveSheet~PageSetup~LeftMargin        = xlobj~InchesToPoints(.7)
xlobj~ActiveSheet~PageSetup~RightMargin       = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~TopMargin         = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~BottomMargin      = xlobj~InchesToPoints(.5)
xlobj~ActiveSheet~PageSetup~HeaderMargin      = xlobj~InchesToPoints(0)
xlobj~ActiveSheet~PageSetup~FooterMargin      = xlobj~InchesToPoints(0)
/*
xlobj~ActiveSheet~PageSetup~Orientation       = xlLandscape
xlobj~ActiveSheet~PageSetup~Orientation       = xlPortrait
*/
xlobj~ActiveSheet~PageSetup~PaperSize         = xlpaperletter
xlobj~ActiveSheet~PageSetup~Zoom              = .false
xlobj~ActiveSheet~PageSetup~FitToPagesWide    = 1
xlobj~ActiveSheet~PageSetup~FitToPagesTall    = 1
if print? then do
  xlobj~ActiveWindow~SelectedSheets~PrintOut  /* Comment out this line to NOT print */
end
xlobj~Range('A1')~Select                    /* Be nice and place the cursor at the top of the document */
xlobj~WorkBooks(1)~Close(saveall)               /* Close the document */
xlobj~Quit                                              /* Quit Excel */
Say 'Your Completed Excel Document Was Saved As:' outfile
Say 'Elasped Time To Convert To Excel Was:' Time('e') 'Seconds'
Exit
EXAMINE_CELL: Procedure Expose xlobj
Parse Arg row,col .
cell_value = xlobj~Cells(row,col)~Value
cell_formula = xlobj~Cells(row,col)~Formula
Select
  When cell_formula = '' Then c = 'EMPTY'
  When cell_value \= cell_formula Then c = 'FORMULA'
  Otherwise c = 'VALUE'
End
Return c
SYNTAX:
Say 'SYNTAX Error On Line' sigl 'ERRORTEXT'(rc)
/* If we have an error, we want to be sure to quit Excel, don't it will be an orphaned process */
xlobj~Quit
Exit
::requires "KillExcel.rex"
::requires "titles.rex"
::requires "hoadate.rex"