excel-ole.cbl

excel ole for your reference

       IDENTIFICATION DIVISION.
       PROGRAM-ID.                      EXCEL-OLE.
       AUTHOR.                          Dan Maltes, AST.
       DATE-WRITTEN.                    2002/12/15 - 12:00:00.
       DATE-COMPILED.                   2002/12/15 - 12:00:00.
       REMARKS.  Example of controlling excel 2002(office xp) through ole automation.
           The excel object model states that an Application object
           contains Workbooks which contains Worksheets which contains
           a Range(cells).
           Excel Object Model
            - Application
              + Workbooks(Workbook) collection
                + Worksheets(Worksheet) collection
                  - Range(cells)

           Note: You will need to generate your own excel.def file with
                 the AXDEFGEN utility.  This sample was tested with an
                 excel.def file generated for the
                 Microsoft Excel 10.0 Object Library(Ver 1.4)
                 which is part of MS Office XP.
                 You may need to make changes to this program for
                 compatibility with office 2000 and office 97.

       ENVIRONMENT DIVISION.
       CONFIGURATION SECTION.
       SPECIAL-NAMES.
       COPY "EXCEL.DEF".
            .
       INPUT-OUTPUT SECTION.
       FILE SECTION.
       WORKING-STORAGE SECTION.
       77 EX-APP            HANDLE OF Application.
       77 EX-WORKBOOK       HANDLE OF Workbook.
       77 EX-WORKSHEET      HANDLE OF Worksheet.
       77 EX-RANGE          HANDLE OF Range.
       77 CELL-RANGE        PIC X(04) VALUE SPACES.
       77 TXT-VALUE         PIC X(50) VALUE SPACES.
       77 NUM-VALUE         PIC 9(09) VALUE ZEROS.
       77 FORMULA-VALUE     PIC X(50) VALUE SPACES.
       77 WKBK-NAME         PIC X(100) VALUE SPACES.

       PROCEDURE DIVISION.

       MAIN SECTION.
      * Create excel application object
           CREATE @Application OF @Excel HANDLE IN EX-APP.
      * Open existing sheet logic:
      *     MODIFY EX-APP @Visible = 1.
      *     MODIFY EX-APP @GetOpenFilename() GIVING WKBK-NAME.
      *     MODIFY EX-APP @Workbooks::Open(WKBK-NAME) GIVING EX-WORKBOOK.

      * (optional)Specify default number of sheets(can't be zero).
      * If not specified, assigns excel default(usually 3), unless
      * user has changed this in excel settings.
           MODIFY EX-APP @SheetsInNewWorkBook = 1.
      * Add a new workbook, do not name it yet, will be done when saved.
           MODIFY EX-APP @Workbooks::Add() GIVING EX-WORKBOOK.

      * Can reference Sheet1 and change the name.
           INQUIRE EX-WORKBOOK @Worksheets::Item(1)
               IN EX-WORKSHEET.
           MODIFY EX-WORKSHEET @Name = "Yearly".
      *Cleanup
           DESTROY EX-WORKSHEET.

      * Add a new worksheet to workbook and name it.
           MODIFY EX-WORKBOOK @Worksheets::Add() GIVING EX-WORKSHEET.
           MODIFY EX-WORKSHEET @Name = "Quarterly".

      * Report name at top of worksheet in blue
           MOVE "A1" TO CELL-RANGE.
           MOVE "Quarterly Sales Figures for 2002" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           INQUIRE EX-WORKSHEET @Range("A1") IN EX-RANGE.
           MODIFY EX-RANGE @Font::ColorIndex = 5.
           DESTROY EX-RANGE.

      * Column Headers
           MOVE "A3" TO CELL-RANGE.
           MOVE "QUARTER" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B3" TO CELL-RANGE.
           MOVE "AMOUNT" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
      * Make headers bold with light gray background
           INQUIRE EX-WORKSHEET @Range("A3:B3") IN EX-RANGE.
           MODIFY EX-RANGE @Font::Bold = 1.
           MODIFY EX-RANGE @Interior::ColorIndex = 15.
           MODIFY EX-RANGE @Interior::Pattern = @xlSolid.
           DESTROY EX-RANGE.
      * Quarter 1
           MOVE "A4" TO CELL-RANGE.
           MOVE "1" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B4" TO CELL-RANGE.
           MOVE "45554" TO NUM-VALUE.
           PERFORM UPDATE-CELL-VALUE.
      * Quarter 2
           MOVE "A5" TO CELL-RANGE.
           MOVE "2" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B5" TO CELL-RANGE.
           MOVE "78990" TO NUM-VALUE.
           PERFORM UPDATE-CELL-VALUE.
      * Quarter 3
           MOVE "A6" TO CELL-RANGE.
           MOVE "3" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B6" TO CELL-RANGE.
           MOVE "98807" TO NUM-VALUE.
           PERFORM UPDATE-CELL-VALUE.
      * Quarter 4
           MOVE "A7" TO CELL-RANGE.
           MOVE "4" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B7" TO CELL-RANGE.
           MOVE "121764" TO NUM-VALUE.
           PERFORM UPDATE-CELL-VALUE.
      *Total
           MOVE "B8" TO CELL-RANGE.
           MOVE "'==========" TO TXT-VALUE.
           PERFORM UPDATE-CELL-TEXT.
           MOVE "B9" TO CELL-RANGE.
           MOVE "=Sum(B4:B7)" TO FORMULA-VALUE.
           PERFORM UPDATE-CELL-FORMULA.
      * Make total green with a boxed border and currency format
           INQUIRE EX-WORKSHEET @Range("B9") IN EX-RANGE.
           MODIFY EX-RANGE @Font::ColorIndex = 10.
           MODIFY EX-RANGE @BorderAround(@xlContinuous, @xlMedium,
               @xlColorIndexAutomatic).
           MODIFY EX-RANGE @Style = "Currency".
           MODIFY EX-RANGE @NumberFormat =
               "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)".
           DESTROY EX-RANGE.

      * Save workbook and close it
           MODIFY EX-APP @GetSaveAsFileName() GIVING WKBK-NAME.
           MODIFY EX-WORKBOOK @SaveAs(
               BY NAME @Filename WKBK-NAME,
               BY NAME @FileFormat @xlNormal,
               BY NAME @Password NULL,
               BY NAME @WriteResPassword NULL,
               BY NAME @ReadOnlyRecommended 0,
               BY NAME @CreateBackup 0
               BY NAME @AccessMode @xlNoChange).
           MODIFY EX-WORKBOOK @Close().

      * Cleanup and exit.  Handle destruction should be done in reverse
      * object heirarchy order or Excel may be left open in background
      * waiting for dangling handles to be destroyed.
      * Quit the Excel automation server before destroying its handle.
           DESTROY EX-WORKSHEET.
           DESTROY EX-WORKBOOK.
           MODIFY EX-APP @Quit().
           DESTROY EX-APP.

           EXIT PROGRAM.
           STOP RUN.

       UPDATE-CELL SECTION.
       UPDATE-CELL-VALUE.
      * Format numeric with commas and no decimals
           MODIFY EX-WORKSHEET @Range(CELL-RANGE)::Value = NUM-VALUE.
           MODIFY EX-WORKSHEET @Range(CELL-RANGE)::Style = "Comma".
           MODIFY EX-WORKSHEET @Range(CELL-RANGE)::NumberFormat =
               "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)".
       UPDATE-CELL-TEXT.
           MODIFY EX-WORKSHEET @Range(CELL-RANGE)::Value = TXT-VALUE.
       UPDATE-CELL-FORMULA.
           MODIFY EX-WORKSHEET
               @Range(CELL-RANGE)::Formula = FORMULA-VALUE.