Highlighted
Absent Member.
Absent Member.
294 views

[archive] xls file...

[Migrated content. Thread originally posted on 16 June 2008]

Hello,

Is it possible to create excel file( xls ) from a vision data file directly from a cobol program?
If yes, can you share a program please?

Regards
0 Likes
4 Replies
Highlighted
Absent Member.
Absent Member.

RE: [archive] xls file...

Here's a bit to get you going.
You'll need an excel .def file which can be created using axdefgen.

A heading


           CREATE  @Application OF @Excel HANDLE IN olExcel
           MODIFY  olExcel @Visible = 0
           MODIFY  olExcel Workbooks::Add() GIVING olWrkBk
           INQUIRE olWrkBk Worksheets::Item(1) IN olWrkSh
           MODIFY  olWrkSh Range("A1")::Value = "Code"
                           Range("B1")::Value = "Description"
                           Range("C1")::Value = "This Month"
                           Range("D1")::Value = "Quantity"
                           Range("E1")::Value = "Margin"
                           Range("F1")::Value = "This YTD"
                           Range("G1")::Value = "Quantity"
                           Range("H1")::Value = "Margin"
                           Range("I1")::Value = "This Month"
                           Range("J1")::Value = "Quantity"
                           Range("K1")::Value = "Margin"
                           Range("L1")::Value = "This YTD"
                           Range("M1")::Value = "Quantity"
                           Range("N1")::Value = "Margin"


Some data

           MOVE "A"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-CODE
           MOVE "B"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-DESC
           MOVE "C"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-NETT-TM
           MOVE "D"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-QTY-TM
           MOVE "E"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-MARGIN-TM
           MOVE "F"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-NETT-YTD
           MOVE "G"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-QTY-YTD
           MOVE "H"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-MARGIN-YTD
           MOVE "I"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-NETT-LM
           MOVE "J"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-QTY-LM
           MOVE "K"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-MARGIN-LM
           MOVE "L"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-NETT-LYTD
           MOVE "M"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-QTY-LYTD
           MOVE "N"  TO WS-EXL-CELL(1:1)
           MOVE WS-Y TO WS-EXL-CELL(2:4)
           MODIFY  olWrkSh Range(WS-EXL-CELL)::Value = S-ISM-MARGIN-LYTD




Some very basic formatting


           INQUIRE olWrkSh @Range("A1:N1") in olRange
           MODIFY  olRange @Font::Bold = 1
           DESTROY olRange
           MODIFY  olExcel Range("A1:N65536")::Select()
           INQUIRE olExcel Range("A1:N65536")in olRange
           MODIFY  olRange Columns::AutoFit()
           DESTROY olRange
           MODIFY  olExcel Range("A1")::Select()
           DESTROY olRange                           
           MODIFY  olExcel @Visible = 1                   
           DESTROY olWrkBk
           DESTROY olExcel
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] xls file...

Thanks, and if I may ask...

.def file, do you mean to create a kind of active-x control?
If yes, I have seen some application which allow the user to choose some fields from their database and with one click, all selected fileds are created in a nice xls file.

Would you mind helping a bit more?

Regards...
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] xls file...

If you use the utility axdefgen and find the microsoft office excel control you can then create the .def file required.

I could post my excel definations file, but its probably best if you generate your own with your own version of excel
0 Likes
Highlighted
Absent Member.
Absent Member.

RE: [archive] xls file...

Thanks for your help...
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.