EXCEL XML FILE GENERATOR  - Version 00
For Unisys Enterprise Servers
(It generates Excel Spreadsheet XML Files that can be opened by either Excel 2003 or Excel 2007)

(See Version 01 with Additional Features)
(See Version 02 to generate EXCEL Data XML Files)

WRITING LESS TO GET MORE!
 SUPPORTING THE APPLICATIONS' DEVELOPPING LYFE CYCLE
CONTROLLING THE TECHNOLOGY AND NOT ALLOWING TO BE CONTROLLED BY IT.  

By Fabio Alves on July 3rd 2007

 

  • The use of Microsoft Excel has become spread among both skilled and non-skilled workers. It is a powerful tool and data can be easily maintained and read for the ones using the software. However,  it is a difficult task to make COBOL programs to read and to generate Excel spreadsheets. But now, we have 50% of the road built. 
  • Using the XML Standard, we can generate Excel Xml Files that, once exported to Windows platform, can be opened using Excel. Using Windows Explorer, open the folder where the file is located, right click the file and click "Open With" Microsoft Office Excel. 
  • This page shows how to use a kit of three COBOL copylibs to be invoked by programs with the purpose of generating Excel Files.
  • The copylibs are available under HEPPS's Sure inventory with the names ahead described:

           HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX (For File Section)
           HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX (For Working-Storage Section)
           HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX (For Procedure Division)

  • Focusing the principle of  "WRITING LESS FOR GETTING MORE", and using simple commands (SET, PERFORM and MOVE), files can be easily generated minimizing the programmers' efforts for doing the task.
  • An Excel File is composed of rows and columns and the codification to be used follows that principle. The limits are 256 columns and 65536 rows;
  • We can build a program for generating more than one file per program.  But first, I am going to show how to do it generating just one EXCEL XML file per program and that is simpler.

 

Syntax rules for the generation of just one Excel Xml File per program:

  • In the Environment Division, Input-Output Section, File-Control the programmer must declare the file following the example below:

          SELECT EXCEL-FILE ASSIGN TO DISK.

  • The use of another internal name implies in the use of replacing clauses in the copy commands ahead described.
  • External filenames can be set or changed in three ways:

          a) Using WFL Language File Equation's declaration:

          FILE EXCEL-FILE = MYEXCELFILE ON DEV;

          b) Using WFL Language File Equation's commands:

         EXCEL-FILE(TITLE=MYEXCELFILE ON DEV);

          c) Inside the COBOL program, before file initialization perform command (To be described ahead);

          CHANGE ATTRIBUTE TITLE OF EXCEL-FILE TO "MYEXCELFILE ON DEV.".

  • In the Data Division, File Section the programmer must insert the following command:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX".

  • In the Data Division, Working-Storage Section the programmer must insert the following one:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX".

  • In the end of Procedure Division, the programmer must insert:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX".

  • At this point the program should compile ok, but no file will be generated if the object code runs.

 

  • The Excel Xml File must me initialized and the programmer must insert the following perform command in the Procedure Division to do it. Note that the EXCEL File will be opened by this command's execution. If you want to change the Excel File's name in the COBOL code, the change COBOL command must be executed before this one:

          PERFORM INITIATE-EXCEL-FILE.

 

  • In the same way, a perform command must be inserted  to terminate the file's generation process:

          PERFORM TERMINATE-EXCEL-FILE.

 

  • The Excel File is composed of rows and cells. For starting writing a row the following command must be used:

          PERFORM OPEN-EXCEL-ROW.

 

  • For finishing writing a row, use the following command:

         PERFORM CLOSE-EXCEL-ROW.

 

  • For writing cells, the programmers may select a data type for building them. A cell may contain a string, a number (Integer, real or double, signed or unsigned), a currency, a date, a time, a datetime or a boolean. If the programmer does not specify the data type, the string one will be assumed by default.
  • The programmer must also use the input parameter provided by the kit for informing the value to be stored in the cell. And, of course, to perform the routine for writing the file's record.
  • Below is the syntax to specify the data type is showed. As said, the programmer should choose one of them, otherwise string will be assumed by default.

            SET [EXCEL-DATATYPE-STRING,
                       EXCEL-DATATYPE-NUMBER,
                       EXCEL-DATATYPE-INTEGER,
                       EXCEL-DATATYPE-REAL,
                       EXCEL-DATATYPE-DOUBLE,
                       EXCEL-DATATYPE-DATE,
                       EXCEL-DATATYPE-BOOLEAN,
                       EXCEL-DATATYPE-DATETIME,
                       EXCEL-DATATYPE-TIME,
                       EXCEL-DATATYPE-CURRENCY] TO TRUE.

  • For specifying the value to be stored in the cell, a value must be moved to the input parameter provided by the kit:

             MOVE "MYVALUE" TO EXCEL-VALUE. 

  • Some limitations and defaults for strings apply:

           All the values will be trimmed.

           String's size is limited to 180 characters.

           High-Values (@FF@), Low-Values (@00@), quotations,"<" and ">" are forbidden characters and they will be replaced by spaces (@40@). 

           If quotations,  "<" or ">" are desired to be in strings, the programmer must respectively use the meta-characters (&quot;) , (&lt;) and (&gt;).   

           If the value informed is not a string, it will undergo to validation and if the validation fails it will be transformed to string. 

 

  • Below the rules for number, integer, real, double and currency values are presented.The values should be informed as edited strings with decimal-points (Real and double) and with left sign (If negative).The number's size and precision are limited by the limits imposed by UNISYS. The number of numeric digits must not exceed 21 and the decimal-point floats.

          Examples:

             ...
             SET EXCEL-DATATYPE-NUMBER TO TRUE.
             MOVE "656" TO EXCEL-VALUE.
             PERFORM WRITE-EXCEL-CELL.
             ...
            SET EXCEL-DATATYPE--REAL TO TRUE.
            MOVE "-656.83248" TO EXCEL-VALUE.
            PERFORM WRITE-EXCEL-CELL.
            ...
            SET EXCEL-DATATYPE-CURRENCY TO TRUE.
            MOVE "+19997656.88" TO EXCEL-VALUE.
            PERFORM WRITE-EXCEL-CELL.
            ...

  

  • Here are the rules for informing DATE cell values. The date value must be informed using the "CCYYMMDD" where CC=Century, YY=year, MM=Month and DD=day:

            Example:

             ...
            SET EXCEL-DATATYPE-DATE TO TRUE.
            MOVE "20070828" TO EXCEL-VALUE. *> 08/28/2007
            PERFORM WRITE-EXCEL-CELL.
            ...   

  • Here are the rules for informing TIME cell values. The time value must be informed using the format "HHMMSSLLL" where HH=Hour, MM= Minutes, SS= Seconds and LLL= Thousands of seconds.

           Example:

            ...
            SET EXCEL-DATATYPE-TIME TO TRUE.
            MOVE "105923888" TO EXCEL-VALUE. *> 10:59:23.888
            PERFORM WRITE-EXCEL-CELL.
            ...

   

  • Here are the rules for informing a DATETIME cell value. It uses a combination of the two last ones. The datetime value must be informed using the "CCYYMMDDHHMMSSLLL"  where CC= Century, YY= year, MM=Month, DD=day, HH= Hour, MM= Minutes, SS= Seconds and LLL=Thousands of seconds.

          Example:

             ...
             SET EXCEL-DATATYPE-DATE TO TRUE.
             MOVE "20060527224533555" TO EXCEL-VALUE. *> 05/27/2006 22:45:33.555
             PERFORM WRITE-EXCEL-CELL.  
             ...

   

  • Here are the rules for informing BOOLEAN cell values. The boolean value may be informed using synonyms. "0","FALSE" and "false" mean FALSE.  "1","TRUE" and "true" mean TRUE. 

           Example:    

          ... 
            SET EXCEL-DATATYPE-BOOLEAN TO TRUE.
            MOVE "false" TO EXCEL-VALUE. *> FALSE
            PERFORM WRITE-EXCEL-CELL.      
            ...

  • Following, there is an example of a COBOL program that can be cut and pasted in a NX Edit's empty one for testing purposes.

   IDENTIFICATION DIVISION.
   ENVIRONMENT DIVISION.

   INPUT-OUTPUT SECTION.
  FILE-CONTROL.

          SELECT EXCEL-FILE       ASSIGN TO DISK.

   DATA DIVISION.
   FILE SECTION.

   COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX".
/
   WORKING-STORAGE SECTION. 
  COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX". 
/
   PROCEDURE DIVISION.
   MAIN SECTION.
   ENTRANCE.     

             CHANGE ATTRIBUTE FILENAME OF EXCEL-FILE TO "MYEXCELFILENAME.".

            PERFORM INITIATE-EXCEL-FILE.

*===> WRITING FIRST ROW

           PERFORM OPEN-EXCEL-ROW.

            SET EXCEL-DATATYPE-BOOLEAN TO TRUE.
           MOVE "false" TO EXCEL-VALUE.
           PERFORM WRITE-EXCEL-CELL.               PERFORM CLOSE-EXCEL-ROW.

*====> WRITING SECOND ROW.

           PERFORM OPEN-EXCEL-ROW. 

           SET EXCEL-DATATYPE-DATE TO TRUE.            
           MOVE "20060527224533555" TO EXCEL-VALUE. 
           PERFORM WRITE-EXCEL-CELL.

           PERFORM CLOSE-EXCEL-ROW. 

           PERFORM TERMINATE-EXCEL-FILE. 

           STOP RUN.
/
COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX".

 

 

Syntax rules for the generation of more than one Excel Xml File per program:

  • In the Environment Division, Input-Output Section, File-Control, the programmer must declare the files following the example below:

          SELECT EXCEL-FILE1 ASSIGN TO DISK.
          SELECT EXCEL-FILE2 ASSIGN TO DISK.
          SELECT EXCEL-FILE3 ASSIGN TO DISK.

  • External filenames can be set or changed in three ways:

          a) Using either WFL Language File Equation's declarations:

          FILE EXCEL-FILE1 = MYEXCELFILE1 ON DEV;
          FILE EXCEL-FILE2 = MYEXCELFILE2 ON DEV;
          FILE EXCEL-FILE3 = MYEXCELFILE3 ON DEV;

          b) Using either WFL Language File Equation's commands:

         EXCEL-FILE1(TITLE=MYEXCELFILE1 ON DEV);
         EXCEL-FILE2(TITLE=MYEXCELFILE2 ON DEV);
         EXCEL-FILE3(TITLE=MYEXCELFILE3 ON DEV);

          c) Inside  the  COBOL program, before file initialization perform commands (To be described ahead);

          CHANGE ATTRIBUTE TITLE OF EXCEL-FILE1 TO "MYEXCELFILE1 ON DEV.".
          CHANGE ATTRIBUTE TITLE OF EXCEL-FILE2 TO "MYEXCELFILE2 ON DEV.".
          CHANGE ATTRIBUTE TITLE OF EXCEL-FILE3 TO "MYEXCELFILE3 ON DEV.".

  • In the Data Division, File Section,  the programmer must insert the following ones:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE1==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
                        REPLACING  ==EXCEL-FILE== BY ==EXCEL-FILE2==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
                        REPLACING  ==EXCEL-FILE== BY ==EXCEL-FILE3==.

  • In the Data Division, Working-Storage Section the programmer must insert:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
                        REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO1== .        

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
                       REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO2==.        

         COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
                       REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO3==.

  • In the end of Procedure Division, the programmer must insert:

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE1== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO1==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE1==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE2== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO2==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE2==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE3== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO3==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE3==.

  • At this point the program should compile ok, but no file will be generated when the object code runs.

 

  • The Excel File must me initialized and the program must insert the following perform commands in the Procedure Division for doing it. Note that  EXCEL Files will be opened by these command's execution.  If you want to programmatically change one the Excel File' names in the COBOL code, the change COBOL commands must be executed before the respective file's ones.

          PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE1.
          ...
          PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE2.
          ...
          PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE3.

         

 

  • In the same way, a perform command must be inserted in the COBOL code to terminate the file's generation process:

          PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE1.
          ...
          PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE2.
          ...
          PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE3.

 

  • The Excel Files are composed of rows and cells. For starting writing a row the following commands must be used:

          PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1.
          ...
          PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2.
          ...
          PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3.

 

  • For finishing writing a row, use the following command:

         PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1.
         ...
         PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2.
         ...
         PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3.
 

  • For writing cells, the programmers may select a data type for building them. A cell may contain a string, a number (Integer, real, double or currency, signed or unsigned), a date, a time, a datetime or a boolean. If the programmer does not specify the data type, the string one will be assumed by default.
  • The programmer must also use the input parameter provided by the kit for informing the content of the cell. And, of course, to perform the routine for writing the file.
  • Below is the syntax to specify the data type is showed. As already said, the programmer should choose one of them, otherwise string will be assumed by default.

            SET [EXCEL-DATATYPE-STRING OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-NUMBER OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-INTEGER OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-REAL OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-DOUBLE OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-DATETIME OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO1,
                       EXCEL-DATATYPE-CURRENCY OF EXCEL-FILE-INFO1] TO TRUE.           
             ...
             SET [EXCEL-DATATYPE-STRING OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-NUMBER OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-INTEGER OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-REAL OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-DOUBLE OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-DATETIME OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO2,
                       EXCEL-DATATYPE-CURRENCY OF EXCEL-FILE-INFO2] TO TRUE.           
             ...
             SET [EXCEL-DATATYPE-STRING OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-NUMBER OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-INTEGER OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-REAL OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-DOUBLE OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-DATETIME OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO3,
                       EXCEL-DATATYPE-CURRENCY OF EXCEL-FILE-INFO3] TO TRUE.

  • For specifying the value to be stored in the cells, the value must be moved to the input parameters provided by the kit:

              MOVE "MYVALUE1" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. 
              ...
              MOVE "MYVALUE2" TO EXCEL-VALUE OF EXCEL-FILE-INFO2. 
              ...
              MOVE "MYVALUE3" TO EXCEL-VALUE OF EXCEL-FILE-INFO3. 

  • Some limitations and defaults for strings apply:

           All the values will be trimmed.

           String's size is limited to 180 characters.

           High-Values (@FF@), Low-Values (@00@), quotations, "<" and ">" are forbidden characters and they will be replaced by spaces (@40@). 

           If quotations, "<" or ">" are desired to appear in the strings, the respective meta-characters (&quot;) , (&lt;) and (&gt;)  must be inform instead.

           If the value informed is not a string, it will undergo to validation and if the validation fails it will be transformed to string. 

 

  • Below the rules for number, integer, real, double and currency are presented.They be should be informed as edited strings with decimal-points (Real and double) and with left sign (Negative).The number's size and precision are limited by the limits imposed by UNISYS. The number of numeric digits must not exceed 21 and the decimal-point floats.

          Examples:

             ...
             SET EXCEL-DATATYPE-NUMBER OF EXCEL-FILE-INFO1 TO TRUE.
             MOVE "656" TO EXCEL-VALUE OF EXCEL-FILE-INFO1.
             PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.
             ... 
            SET EXCEL-DATATYPE--REALOF EXCEL-FILE-INFO2 TO TRUE.
            MOVE "-656.888858" TO EXCEL-VALUEOF EXCEL-FILE-INFO2.
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE2.
            ... 
            SET EXCEL-DATATYPE-CURRENCY OF EXCEL-FILE-INFO3 TO TRUE.
            MOVE "+19997656.88" TO EXCEL-VALUE OF EXCEL-FILE-INFO3.
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE3.
            ...

  

  • Here are the rules for informing DATE cell values. The date value must be informed using the "CCYYMMDD" where CC=Century, YY=year, MM=Month and DD=day;

            Example:

             ...
            SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO1TO TRUE.
            MOVE "20071125" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 11/25/2007
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.
            ...   
            SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO3TO TRUE.
            MOVE "20071228" TO EXCEL-VALUE OF EXCEL-FILE-INFO3. *> 12/28/2007
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE3.
            ...  
            SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO2TO TRUE.
            MOVE "20060828" TO EXCEL-VALUE OF EXCEL-FILE-INFO2. *> 08/28/2006
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE2.
            ...  

  • Here are the rules for informing TIME cell values. The time value must be informed using the format "HHMMSSLLL" where HH=Hour, MM= Minutes, SS= Seconds and LLL= Thousands of seconds.

           Example:

            ...
            SET EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO1TO TRUE.
            MOVE "105923888" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 10:59:23.888
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.
            ...
               SET EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO1TO TRUE.
            MOVE "105923888" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 10:59:23.888
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.
            ...
            SET EXCEL-DATATYPE-TIME OF EXCEL-FILE-INFO1TO TRUE.
            MOVE "105923338" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 10:59:23.888
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.
            ...

  • Here are the rules for informing DATETIME cell values. It uses a combination of the two last ones. The datetime value must be informed using the "CCYYMMDDHHMMSSLLL"  where CC= Century, YY= year, MM=Month, DD=day, HH= Hour, MM= Minutes, SS= Seconds and LLL=Thousands of seconds.

          Example:

             ...
             SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO1 TO TRUE.
             MOVE "20060527224533553" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 05/27/2006 22:45:33.555
             PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.  
             ...
             SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO1 TO TRUE.
             MOVE "20060527224533552" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 05/27/2006 22:45:33.555
             PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.  
             ...
            SET EXCEL-DATATYPE-DATE OF EXCEL-FILE-INFO1 TO TRUE.
             MOVE "20060527224533551" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> 05/27/2006 22:45:33.555
             PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.  
             ...

  • Here are the rules for informing BOOLEAN cell values. The boolean value may be informed using synonyms: "0","FALSE" and "false" mean FALSE. "1","TRUE" and "true" mean TRUE. 

           Example:    

            ... 
            SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO1TO TRUE.
            MOVE "false" TO EXCEL-VALUE OF EXCEL-FILE-INFO1. *> FALSE
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.      
            ...
            SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO2 TO TRUE.
            MOVE "1" TO EXCEL-VALUE OF EXCEL-FILE-INFO2. *> TRUE
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE2.      
            ...
            SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO3 TO TRUE.
            MOVE "1" TO EXCEL-VALUE OF EXCEL-FILE-INFO3. *> TRUE
            PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE3.      
            ...

Following, there is an example of a COBOL program that can be cut and pasted in NX Edit for testing purposes.

   IDENTIFICATION DIVISION.
   ENVIRONMENT DIVISION.

   INPUT-OUTPUT SECTION.
  FILE-CONTROL.

          SELECT EXCEL-FILE1       ASSIGN TO DISK.
          SELECT EXCEL-FILE2       ASSIGN TO DISK. 
          SELECT EXCEL-FILE3       ASSIGN TO DISK. 

   DATA DIVISION.
   FILE SECTION. 

   COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
               REPLACING   ==EXCEL-FILE== BY ==EXCEL-FILE1==.

   COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
               REPLACING  ==EXCEL-FILE== BY ==EXCEL-FILE2==.

   COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPFDX"
               REPLACING  ==EXCEL-FILE== BY ==EXCEL-FILE3==.
/
   WORKING-STORAGE SECTION.  

   COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
               REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO1== .        

  COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
              REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO2==.        

 COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPWSX"
              REPLACING ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO3==.
/
   PROCEDURE DIVISION.
   MAIN SECTION.
   ENTRANCE.      

            CHANGE ATTRIBUTE FILENAME OF EXCEL-FILE1 TO "MYEXCELFILENAME1.".

            PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE1.

            CHANGE ATTRIBUTE FILENAME OF EXCEL-FILE2 TO "MYEXCELFILENAME2.".

            PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE2.

            CHANGE ATTRIBUTE FILENAME OF EXCEL-FILE3 TO "MYEXCELFILENAME3.".

            PERFORM INITIATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE3.



*===> WRITING FIRST ROW (1)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1. 

           SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO1 TO TRUE.
           MOVE "false" TO EXCEL-VALUE OF EXCEL-FILE-INFO1.
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.              
           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1.

*====> WRITING SECOND ROW.  (1)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1. 

           SET EXCEL-DATATYPE-DATE EXCEL-FILE-INFO1 TO TRUE.           
           MOVE "20060527224533555" TO EXCEL-VALUE EXCEL-FILE-INFO1. 
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE1.

           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE1.  




*===> WRITING FIRST ROW (2)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2. 

           SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO2 TO TRUE.
           MOVE "false" TO EXCEL-VALUE OF EXCEL-FILE-INFO2.
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE2.              
           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2.

*====> WRITING SECOND ROW.  (2)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2. 

           SET EXCEL-DATATYPE-DATE EXCEL-FILE-INFO2 TO TRUE.           
           MOVE "20060527224533555" TO EXCEL-VALUE EXCEL-FILE-INFO2. 
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE2.

           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE2.   




*===> WRITING FIRST ROW (3)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3. 

           SET EXCEL-DATATYPE-BOOLEAN OF EXCEL-FILE-INFO3 TO TRUE.
           MOVE "false" TO EXCEL-VALUE OF EXCEL-FILE-INFO3.
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE3.              
           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3.

*====> WRITING SECOND ROW.  (3)

           PERFORM OPEN-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3. 

           SET EXCEL-DATATYPE-DATE EXCEL-FILE-INFO3 TO TRUE.           
           MOVE "20060527224533555" TO EXCEL-VALUE EXCEL-FILE-INFO3. 
           PERFORM WRITE-EXCEL-CELL OF EXCEL-FILE-PROCEDURE3.

           PERFORM CLOSE-EXCEL-ROW OF EXCEL-FILE-PROCEDURE3.   

           PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE1.              

           PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE2.   

           PERFORM TERMINATE-EXCEL-FILE OF EXCEL-FILE-PROCEDURE3.  

           STOP RUN.
/ 
           COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE1== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO1==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE1==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE2== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO2==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE2==.

          COPY "HEPPS/COPYLIB/EXCEL2003/XMLGEN/V00/CPPDX"
                        REPLACING    ==EXCEL-FILE== BY ==EXCEL-FILE3== 
                                                    ==EXCEL-FILE-INFO== BY ==EXCEL-FILE-INFO3==
                                                    ==EXCEL-FILE-PROCEDURE== BY == EXCEL-FILE-PROCEDURE3==.

 

By Fabio Alves on July 3rd 2007
Personal Picture

Powered by
Fabio Alves