/* Cash12ToExcel.rex */
/*
Lee Peedin
11/18/2006
11/20/2006
Reads in the sample .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'_IncomeExpense12.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 = 9      -- 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 = 4     -- 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~word(1) = '-------' | aline~word(1) = '=======' then iterate i   -- Don't need these line
  if aline~words = 13 & aline~word(aline~words) = 'Total' 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 < 13 then do     -- A "description row"
        column = 1
        xlobj~Cells(row,column)~Value = aline
      end
      when aline~words = 13 then do     -- A row with title data
        column = 2
        aline='Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total'
        do w = 1 to aline~words
          xlobj~Cells(row,column)~Value = aline~word(w)
          column = column + 1
        end
      end
      otherwise do                      -- Must be actual data
        col_a_words = aline~words - 13
        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
          if col_a_data~word(2) = 'Reserve' then ,
           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(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

lastcell = xlobj~ActiveCell~SpecialCells(xlLastCell)~Address    -- Determine last cell used
parse var lastcell '$'max_column'$'max_row                      -- Parse that for column & row

-- 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

-- Right justify all data
xlobj~Range('B7:'lastcell)~Select
mySelection = xlobj~Selection
mySelection~HorizontalAlignment = xlRight
mySelection~NumberFormat = '#0'

-- 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(.0)
xlobj~ActiveSheet~PageSetup~TopMargin         = xlobj~InchesToPoints(.0)
xlobj~ActiveSheet~PageSetup~BottomMargin      = xlobj~InchesToPoints(.0)
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

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"