Highlighted
Absent Member.
Absent Member.
4753 views

Using EXCEL in managed code.

Jump to solution

The following code works in our NET EXPRESS code...

       
      $set ooctrl(+P)
       ENVIRONMENT DIVISION.                                           
       INPUT-OUTPUT SECTION.                                           
       FILE-CONTROL.                                                   
                                                                       
                                                                      
      ******************************************************************
       class-control.
           MSExcel is class "$OLE$Excel.Application".
      ******************************************************************
  
   Procedure Division
  
      *>   Create a new instance of Microsoft Excel
           invoke MSExcel "new" returning ExcelObject.

      *>   Make Excel visible
      *    invoke ExcelObject "setVisible" using by value 1.
      *>   Get the collection of WorkBooks
           invoke ExcelObject "getWorkBooks"
               returning WorkBooksCollection.

      *>   Open File
           invoke WorkBooksCollection "Open"
                                        using by reference WS-CSVFILE
                                        returning Workbook.
           invoke Workbook "getWorkSheets" returning WorkSheets.
           invoke WorkSheets "getItem" using by value 1
                                 returning WorkSheet.

      *    Get rid of the embedded formulae by copying the first 256
      *    columns and then doing a paste special
           invoke WorkSheet "getColumns" using z"A:IV"
                                         returning theRange.
           invoke theRange "Select".
           invoke theRange "Copy".
           invoke theRange "PasteSpecial" using
                                            by value -4163
                                            by value -4142.
           invoke theRange "Autofit".

           invoke theRange "finalize" returning theRange.
           invoke WorkSheet "getRange" using z"A1:A1"
                                         returning theRange.
           invoke theRange "Select".

           MOVE SPACES TO WS-PASSWORD.
           IF REP-PASSWORD(1) NOT= SPACES
               PERFORM GET-PASSWORD.
           STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
                  INTO WS-PASSWORD.

           SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
           invoke WorkSheet "SaveAs" using
                  by reference WS-XLSFILE
                  by value ExcelFileFormat
                  by reference WS-PASSWORD.
           invoke WorkSheet "finalize" returning Worksheet.
           invoke WorkSheets "finalize" returning WorkSheets.
           invoke Workbook "finalize" returning WorkBook.
           invoke WorkbooksCollection "finalize"
                       returning WorkBooksCollection.
           invoke ExcelObject "Quit".
           invoke ExcelObject "finalize" returning ExcelObject.

           STRING WS-MESSAGE-STR, " file " WS-FILENAME,
               " created successfully" DELIMITED BY "  " INTO
                   WS-MESSAGE-STR.
       
       
 When this code is inserted in managed .NET code we get an COBCH0845 error "Unknown Class - "$OLE$Excel.Application" and then a load more errors relating to the various lines of code above. 

How do we get managed .NET code to allow us to work with EXCEL?

0 Likes
1 Solution

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Using EXCEL in managed code.

Jump to solution

Just to add to the sample that Michael gave above, here is a partial conversion of your original program.

I did not run this application and there are no guarantees that it will run 100% correctly but it compiles clean and should help you to get started at least.

The $OLE$ class support in Net Express is only available under native code in Visual COBOL.

If you wish to use Excel in managed code then you need to add a reference to the project using the COM tab to Microsoft.Office.Interop.Excel or use the $set ilref directive as Michael points out.

     $set ilusing"Microsoft.Office.Interop"
      program-id. Program1 as "Excelmanaged.Program1".
      data division.
      working-storage section.
      01 WS-CSVFILE string.
      01 WS-XLSFILE string.
      01 WS-PASSWORD string.
      01 ExcelObject type Microsoft.Office.Interop.Excel.Application.
      01 WorkBooksCollection type Workbooks.
      01 WorkBook type _Workbook.
      01 Worksheets type Sheets.
      01 Worksheet type _Worksheet.
      01 theRange type Range.
      procedure division.
          set ExcelObject to new type Microsoft.Office.Interop.Excel.Application
     *>   Make Excel visible
          set ExcelObject::Visible to true
     *>   Get the collection of WorkBooks
          set WorkBooksCollection to ExcelObject::Workbooks
     *>   Open File
          set Workbook to WorkBooksCollection::Open(WS-CSVFILE, type Type::Missing, type Type::Missing, type Type::Missing,
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)
          set Worksheets to Workbook::Worksheets
          set Worksheet to Worksheets[1] as type _Worksheet
     *    Get rid of the embedded formulae by copying the first 256
     *    columns and then doing a paste special
          set theRange to Worksheet::get_Range("A", "IV")
          invoke theRange::Select
          invoke theRange::Copy(type Type::Missing)
          invoke theRange::PasteSpecial(type XlPasteType::xlPasteValues, type XlPasteSpecialOperation::xlPasteSpecialOperationNone,
            type Type::Missing, type Type::Missing)
          invoke theRange::AutoFit
          set theRange to WorkSheet::get_Range("A1", "A1")
          invoke theRange::Select
     *    MOVE SPACES TO WS-PASSWORD.
     *    IF REP-PASSWORD(1) NOT= SPACES
     *        PERFORM GET-PASSWORD.
     *    STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
     *           INTO WS-PASSWORD.
     *    SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
          invoke WorkSheet::SaveAs(WS-XLSFILE, type XlFileFormat::xlExcel7, WS-PASSWORD, type Type::Missing, type Type::Missing
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)
          invoke ExcelObject::Quit
          goback.

      end program Program1.

View solution in original post

0 Likes
6 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Using EXCEL in managed code.

Jump to solution

How do we get managed .NET code to allow us to work with EXCEL?

Try something along these lines:


      $set ilref "Microsoft.Office.Interop.Excel"
         
       class-id TalkToExcel.
       
       method-id Main static.
       01 excelApp     type Microsoft.Office.Interop.Excel.Application.
       01 targetRange  type Microsoft.Office.Interop.Excel.Range.
       procedure division using by value p_args as string occurs any.
           set excelApp to new Microsoft.Office.Interop.Excel.Application
           set excelApp::Visible to true
           invoke excelApp::Workbooks::Add(type Type::Missing)
           set targetRange to excelApp::get_Range("A1", type Type::Missing)
           invoke targetRange::set_Value(type Type::Missing, "Name")
       end method.
       
       end class.

(For some reason the forum appears to render <pre> text double-spaced. Someone should fix that...)

That's based on a Microsoft example for invoking Excel in C# 2008. (Later versions of C# have additional syntactic sugar that simplifies these operations, notably optional-parameter support that gets rid of the "Type::Missing" parameters, but I don't know of COBOL.NET equivalents.)

That code works for me - it opens Excel, creates a workbook, and sets the contents of cell A1 to the string "Name".

Note that you can get rid of the $set ilref directive if you add a reference to Microsoft.Office.Interop.Excel to your Visual Studio project.

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Using EXCEL in managed code.

Jump to solution

OK, <pre> text is only double-spaced in the post-preview view. Still broken, but less so.

Unfortunately the column size in the form is much too narrow, and <pre> text at the default font size wraps horribly even if the lines aren't very long. (Why is the column width different in the post preview and the final view? Someone wasn't very thoughtful about their CSS settings. Also, the preview view doesn't escape less-than and greater-than characters in the text.)

Let's try that again:

      $set ilref "Microsoft.Office.Interop.Excel"
         
       class-id TalkToExcel.
       
       method-id Main static.
       01 excelApp     type Microsoft.Office.Interop.Excel.Application.
       01 targetRange  type Microsoft.Office.Interop.Excel.Range.
       procedure division using by value p_args as string occurs any.
           set excelApp to new Microsoft.Office.Interop.Excel.Application
           set excelApp::Visible to true
           invoke excelApp::Workbooks::Add(type Type::Missing)
           set targetRange to excelApp::get_Range("A1", type Type::Missing)
           invoke targetRange::set_Value(type Type::Missing, "Name")
       end method.
       
       end class.

 

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Using EXCEL in managed code.

Jump to solution

Just to add to the sample that Michael gave above, here is a partial conversion of your original program.

I did not run this application and there are no guarantees that it will run 100% correctly but it compiles clean and should help you to get started at least.

The $OLE$ class support in Net Express is only available under native code in Visual COBOL.

If you wish to use Excel in managed code then you need to add a reference to the project using the COM tab to Microsoft.Office.Interop.Excel or use the $set ilref directive as Michael points out.

     $set ilusing"Microsoft.Office.Interop"
      program-id. Program1 as "Excelmanaged.Program1".
      data division.
      working-storage section.
      01 WS-CSVFILE string.
      01 WS-XLSFILE string.
      01 WS-PASSWORD string.
      01 ExcelObject type Microsoft.Office.Interop.Excel.Application.
      01 WorkBooksCollection type Workbooks.
      01 WorkBook type _Workbook.
      01 Worksheets type Sheets.
      01 Worksheet type _Worksheet.
      01 theRange type Range.
      procedure division.
          set ExcelObject to new type Microsoft.Office.Interop.Excel.Application
     *>   Make Excel visible
          set ExcelObject::Visible to true
     *>   Get the collection of WorkBooks
          set WorkBooksCollection to ExcelObject::Workbooks
     *>   Open File
          set Workbook to WorkBooksCollection::Open(WS-CSVFILE, type Type::Missing, type Type::Missing, type Type::Missing,
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing,
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)
          set Worksheets to Workbook::Worksheets
          set Worksheet to Worksheets[1] as type _Worksheet
     *    Get rid of the embedded formulae by copying the first 256
     *    columns and then doing a paste special
          set theRange to Worksheet::get_Range("A", "IV")
          invoke theRange::Select
          invoke theRange::Copy(type Type::Missing)
          invoke theRange::PasteSpecial(type XlPasteType::xlPasteValues, type XlPasteSpecialOperation::xlPasteSpecialOperationNone,
            type Type::Missing, type Type::Missing)
          invoke theRange::AutoFit
          set theRange to WorkSheet::get_Range("A1", "A1")
          invoke theRange::Select
     *    MOVE SPACES TO WS-PASSWORD.
     *    IF REP-PASSWORD(1) NOT= SPACES
     *        PERFORM GET-PASSWORD.
     *    STRING WS-PASSWORD DELIMITED BY SPACE, x"00",
     *           INTO WS-PASSWORD.
     *    SET XLEXCEL7 OF ExcelFileFormat TO TRUE.
          invoke WorkSheet::SaveAs(WS-XLSFILE, type XlFileFormat::xlExcel7, WS-PASSWORD, type Type::Missing, type Type::Missing
             type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing, type Type::Missing)
          invoke ExcelObject::Quit
          goback.

      end program Program1.

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Using EXCEL in managed code.

Jump to solution

I'm using EXCEL 2007 and this program, after a couple of minor tweaks, does not seem to want to convert the existing .CSV file into a EXCEL 2007 worksheet although it WILL create an EXCEL 2003 one. Am I missing something?

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

RE: Using EXCEL in managed code.

Jump to solution

Try changing the second parameter to SaveAs to one of the following:

type XlFileFormat::xlOpenXMLWorkbook  *> equals number 51 or default for Excel 2007
   equates to .xlsx extension

type XlFileFormat::xlOpenXMLWorkbookMacrosEnabled  *> equals number 52 or Excel 2007 with macros
  equates to .xlsm extension

type XlFileFormat::xlWorkbookDefault   *> equals number 51 (see above) if running under Excel 2007
  equates to .xlsx extension

0 Likes
Highlighted
Absent Member.
Absent Member.

RE: Using EXCEL in managed code.

Jump to solution

Thank You.

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.