Reflection .NET API
Walkthroughs / Retrieve and Enter Data / Get Data With a Visual Studio Office App
In This Topic
    Get Data With a Visual Studio Office App
    In This Topic

    This walkthrough shows how to create a Microsoft Visual Studio Office solution that can be used to open Reflection and get data from a host application. You can use this process to develop an Excel application level add-in that includes these features for any Excel workbook or to customize an Excel workbook so that they are available only when that workbook is open.

    Create an Excel workbook add-in

    1. Create a Microsoft Project and select the Office Add-ins Excel Workbook template or the Excel Add-in template.
    2. When prompted, select to Create a New Document.
    3. In Visual Studio, create a new Console Application project and add references for the following Reflection assemblies. (Depending on your version of Visual Studio, these can be found either on the .NET tab or under Assemblies | Extensions.)
      Attachmate.Reflection
      Attachmate.Reflection.Framework
      Attachmate.Reflection.Emulation.IbmHosts  (if using an IBM host)
      Attachmate.Reflection.Emulation.OpenSystems (if using an Open Systems host)

    Add Controls to the Excel Ribbon

    Next, add the controls you want users to see in the Excel Ribbon when they open the document.

    1. In Solution Explorer, right click on the project and select Add a new item.
    2. In the Add new item dialog box, select to add a Ribbon (Visual Designer) item to the project.
      The Ribbon1.cs file should open in Design mode.
    3. Open the Toolbox and add a Tab control to the Ribbon.
    4. In the Properties window, change the tab label to INTERNAL APPS.
    5. Add a Group and change its label to Sales Data.
    6. Add a Button to this group and change its label to Get Data.
      When you're done, the Ribbon shown in design view should look like this:

    Add the Code for the Controls to the Ribbon1.cs file

    Now that you have designed the Ribbon for the workbook, set up the Ribbon1.cs file to handle the button click event for the Get Data button. This code does the work of opening Reflection, navigating to the data, and copying the data from the host to Excel.

    You can follow along with this section or skip to Ribbon1.cs code at the end of this article and copy all of the code to Ribbon1.cs.

    1. Double click the Get Data button and replace the code in the Ribbon1.cs file with the following code.

      This code starts Reflection and creates an event handler for the NewScreenReady event, which navigates to the screen that has the data. When that screen is ready, the event handler calls methods to get the data from the screen and add the data to Excel.

      Create a session, navigate to a screen, and call methods to get and enter data
      Copy Code 
      using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
using Attachmate.Reflection.UserInterface;
using Attachmate.Reflection;
using Attachmate.Reflection.Framework;
using Attachmate.Reflection.Emulation.IbmHosts;
namespace VSTOGetScreenData
{
    public partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
            
        }

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            //Start a visible instance of Reflection or get the instance running at the given channel name
            Application app = MyReflection.CreateApplication("MyWorkspace", true);

            //Create a terminal control, configure, and connect
            IIbmTerminal terminal = (IIbmTerminal)app.CreateControl(new Guid("{09E5A1B4-0BA6-4546-A27D-FE4762B7ACE1}"));
            terminal.HostAddress = "demo:ibm3270.sim";
            terminal.Port = 623;
            terminal.Connect();

            //Make the session visible and then get a handle to the screen
            IFrame frame = (IFrame) app.GetObject("Frame");
            frame.CreateView(terminal);
            IIbmScreen screen = terminal.Screen;
           
            //Add an event handler to navigate screens
            screen.NewScreenReady += screen_NewScreenReady;       
        }

        void screen_NewScreenReady(object sender, EventArgs e)
        {
            String screenID1, screenID2, screenID3;   
            IIbmScreen screen = (IIbmScreen) sender;

            //Get text at specific locations and compare with known text to identify screen
            screenID1 = screen.GetText(1, 2, 6);
            screenID2 = screen.GetText(1, 7, 4);
            screenID3 = screen.GetText(1, 25, 13);

            if (screenID1 == "ATM VM")
            {
                screen.SendControlKey(Attachmate.Reflection.Emulation.IbmHosts.ControlKeyCode.Transmit);
            }
            if (screenID2 == "ATM5")
            {
                screen.PutText("kayak", 23, 1);
                screen.SendControlKey(Attachmate.Reflection.Emulation.IbmHosts.ControlKeyCode.Transmit);
            }
            if (screenID3 == "INTERNATIONAL")
            {
                //Get the screen data from the application
                int[,]screenData = GetScreenData(screen);

                //Put the data into Excel
                putInData(screenData); 
            }
        }
       }
   }

      This code starts Reflection, opens a session document file, and navigates to the screen that has the data. Then it calls the GetScreenData() method to get the data from the screen and the PutDataIntoExcel() method to put it into an Excel worksheet.

      Open session, navigate to a screen, and call methods to get and enter data
      Copy Code 
      using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
using Attachmate.Reflection.UserInterface;
using Attachmate.Reflection;
using Attachmate.Reflection.Framework;
using Attachmate.Reflection.Emulation.OpenSystems;

namespace ExcelWorkBookOS
{
    public  partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
        }
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            //Start a visible instance of Reflection or get the instance running at the given channel name
            Application reflectionApplication = MyReflection.CreateApplication("MyWorkspace", true);

            //Create a terminal from the session document file
            string sessionPath = Environment.GetEnvironmentVariable("USERPROFILE") + @"\Documents\Attachmate\Reflection\demoSession.rdox";
            ITerminal terminal = (ITerminal)reflectionApplication.CreateControl(sessionPath);

            //Make the session visible in the workspace and get a handle to the screen
            IFrame frame = (IFrame)reflectionApplication.GetObject("Frame");
            frame.CreateView(terminal);
            IScreen screen = terminal.Screen;

            //Navigate to the screen that has the data
            screen.SendKeys("un");
            screen.SendControlKey(ControlKeyCode.Enter);
            screen.SendKeys("pw");
            screen.SendControlKey(ControlKeyCode.Enter);

            //wait for the host before entering more data
            screen.Wait(3000);
            screen.SendKeys("demodata");
            screen.SendControlKey(ControlKeyCode.Enter);
            screen.Wait(3000);

            //Get the starting point for the data
            ScreenPoint point = screen.SearchText("Jan", 1, 1, FindOptions.Forward);
            int row = point.Row;
            int col = point.Column + 3;

            //Get the Data
            int[,] dataRange =  GetScreenData(row, col, screen);

            //Put the data into Excel
            PutDataIntoExcel(dataRange);      
        }
    }
}
    2.  Add the GetScreenData method to the Ribbon1 partial class. This method gets screen data row by row until it gets a delimiter, indicating the end of the data. Using the resizable List containers allows us to gather any amount of data and resize the containers as we add each row. After the lists are populated, we convert them to a two dimensional array so we can use Excel's method for adding arrays to a worksheet.
      Get the data
      Copy Code 
      public int[,] GetScreenData(IIbmScreen screen)
{
    //Start on row 8 and get the first row of text from the screen
    int row = 8;
    string rowText = screen.GetText(row, 26, 50);

    //Create a list of lists to hold the data for each row
    List<List<string>> list = new List<List<string>>();

    //Gather data until an empty row is encountered
    while (!string.IsNullOrWhiteSpace(rowText))
    {
        //Replace spaces between compound words in first column and remove extra spaces
        while (rowText.Contains("  "))
        {
            rowText = rowText.Replace("  ", " ");
        }
        rowText = rowText.Replace(",", "");
        rowText = rowText.Trim();
        List<string> rowList = rowText.Split(' ').ToList();
        list.Add(rowList);
        row++;

        //Get the new row of text from the screen
        rowText = screen.GetText(row, 26, 50);
    }

    //Convert list of lists into a two dimensional array
    var realDataRange = new int[list.Count, list[0].Count];
    for (int i = 0; i < list.Count; i++)
    {
        for (int j = 0; j < list[0].Count; j++)
        {
            realDataRange[i, j] = int.Parse(list[i][j]);
        }
    }
    return realDataRange;
}
      Get the data
      Copy Code 
      public  int[,] GetScreenData(int row, int col, IScreen screen)
{
    string rowText = screen.GetText(row, col, 50);
    List<List<string>> list = new List<List<string>>();

    //Gather data until an empty row is encountered
    while (!rowText.Contains("-"))
    {
        //Replace spaces between compound words in first column and remove extra spaces
        rowText = rowText.Replace("|", " ");
        while (rowText.Contains("  "))
        {
            rowText = rowText.Replace("  ", " ");
        }
        rowText = rowText.Trim();
        List<string> rowList = rowText.Split(' ').ToList();
        list.Add(rowList);
        row++;

        //Get the new row of text from the screen
        rowText = screen.GetText(row, col, 50);
    }

    //Convert list of lists into a two dimenisional array and return the array
    var data = new int[list.Count, list[0].Count];
    for (int i = 0; i < list.Count; i++)
    {
        for (int j = 0; j < list[0].Count; j++)
        {
            data[i, j] = int.Parse(list[i][j]);
        }
    }
    return data;
}
    3. Finally, add the PutDataIntoExcel method to the Ribbon.cs partial class. This method enters the data from the host into a spreadsheet. It creates a range, based on the size of the array that contains the data, and then assigns the array to the range.
      Put the data into Excel
      Copy Code 
      public void putInData(int[,] screenData)
{
        Excel.Worksheet myWorksheet = (Excel.Worksheet)VSTOGetScreenData.Globals.ThisWorkbook.ActiveSheet;

        // Create a Range of the correct size:
        int rows = screenData.GetLength(0);
        int columns = screenData.GetLength(1);
        Excel.Range range = myWorksheet.get_Range("B3", Type.Missing);
        range = range.get_Resize(rows, columns);
        range.NumberFormat = "#,##0.00";

        // Assign the Array to the Range
        range.set_Value(Type.Missing, screenData);
}
      Put the data into Excel
      Copy Code 
      public  void PutDataIntoExcel(int[,] realDataRange)
{
    Excel.Worksheet myWorksheet =  (Excel.Worksheet)ExcelWorkBookOS.Globals.ThisWorkbook.ActiveSheet;

    // Create a Range of the correct size:
    int rows = realDataRange.GetLength(0);
    int columns = realDataRange.GetLength(1);
    Excel.Range range = myWorksheet.get_Range("B3", Type.Missing);
    range = range.get_Resize(rows, columns);
    range.NumberFormat = "#,##0.00";

    // Assign the Array to the Range
    range.set_Value(Type.Missing, realDataRange);
}

      Test the Project

      1. If you are using an Open Systems terminal, create a new VT session using a host name of "demo:UNIX" and save it in the default location (...Documents\Attachmate\Reflection) as demoSession.rdox.
      2. Press F5 to compile and run the sample.
      3. When the Excel workbook opens, open the INTERNAL APPS tab and then click the Get Data button.
      4. Verify that Reflection opens and navigates to the screen with the data and that the data is entered in the spreadsheet.

      The full sample for the Ribbon1.cs file

      Get data with Visual Studio for Office App
      Copy Code 
      using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
using Attachmate.Reflection.UserInterface;
using Attachmate.Reflection;
using Attachmate.Reflection.Framework;
using Attachmate.Reflection.Emulation.IbmHosts;
namespace VSTOGetScreenData
{
    public partial class Ribbon1
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
            
        }

        public int[,] GetScreenData(IIbmScreen screen)
        {
            //Start on row 8 and get the first row of text from the screen
            int row = 8;
            string rowText = screen.GetText(row, 26, 50);

            //Create a list of lists to hold the data for each row
            List<List<string>> list = new List<List<string>>();

            //Gather data until an empty row is encountered
            while (!string.IsNullOrWhiteSpace(rowText))
            {
                //Replace spaces between compound words in first column and remove extra spaces
                while (rowText.Contains("  "))
                {
                    rowText = rowText.Replace("  ", " ");
                }
                rowText = rowText.Replace(",", "");
                rowText = rowText.Trim();
                List<string> rowList = rowText.Split(' ').ToList();
                list.Add(rowList);
                row++;

                //Get the new row of text from the screen
                rowText = screen.GetText(row, 26, 50);
            }

            //Convert list of lists into a two dimenisional array
            var realDataRange = new int[list.Count, list[0].Count];
            for (int i = 0; i < list.Count; i++)
            {
                for (int j = 0; j < list[0].Count; j++)
                {
                    realDataRange[i, j] = int.Parse(list[i][j]);
                }
            }
            return realDataRange;
        }
        public void putInData(int[,] screenData)
        {
                Excel.Worksheet myWorksheet = (Excel.Worksheet)VSTOGetScreenData.Globals.ThisWorkbook.ActiveSheet;

                // Create a Range of the correct size:
                int rows = screenData.GetLength(0);
                int columns = screenData.GetLength(1);
                Excel.Range range = myWorksheet.get_Range("B3", Type.Missing);
                range = range.get_Resize(rows, columns);
                range.NumberFormat = "#,##0.00";

                // Assign the Array to the Range
                range.set_Value(Type.Missing, screenData);
        }

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            //Start a visible instance of Reflection or get the instance running at the given channel name
            Application app = MyReflection.CreateApplication("MyWorkspace", true);

            //Create a terminal control, configure, and connect
            IIbmTerminal terminal = (IIbmTerminal)app.CreateControl(new Guid("{09E5A1B4-0BA6-4546-A27D-FE4762B7ACE1}"));
            terminal.HostAddress = "demo:ibm3270.sim";
            terminal.Port = 623;
            terminal.Connect();

            //Make the session visible and then get a handle to the screen
            IFrame frame = (IFrame) app.GetObject("Frame");
            frame.CreateView(terminal);
            IIbmScreen screen = terminal.Screen;
           
            //Add an event handler to navigate screens
            screen.NewScreenReady += screen_NewScreenReady;       
        }

        void screen_NewScreenReady(object sender, EventArgs e)
        {
            String screenID1, screenID2, screenID3;   
            IIbmScreen screen = (IIbmScreen) sender;

            //Get text at specific locations and compare with known text to identify screen
            screenID1 = screen.GetText(1, 2, 6);
            screenID2 = screen.GetText(1, 7, 4);
            screenID3 = screen.GetText(1, 25, 13);
            if (screenID1 == "ATM VM")
            {
                screen.SendControlKey(Attachmate.Reflection.Emulation.IbmHosts.ControlKeyCode.Transmit);
            }
            if (screenID2 == "ATM5")
            {
                screen.PutText("kayak", 23, 1);
                screen.SendControlKey(Attachmate.Reflection.Emulation.IbmHosts.ControlKeyCode.Transmit);
            }
            if (screenID3 == "INTERNATIONAL")
            {
                //Get the screen data from the application
                int[,]screenData = GetScreenData(screen);

                //Put the data into Excel
                putInData(screenData); 
            }
        }
       }
   }
      Get data with Visual Studio for Office App
      Copy Code 
      using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using Excel = Microsoft.Office.Interop.Excel;
using Attachmate.Reflection.UserInterface;
using Attachmate.Reflection;
using Attachmate.Reflection.Framework;
using Attachmate.Reflection.Emulation.OpenSystems;

namespace ExcelWorkBookOS
{
    public  partial class Ribbon1
    {
        public  int[,] GetScreenData(int row, int col, IScreen screen)
        {
            string rowText = screen.GetText(row, col, 50);
            List<List<string>> list = new List<List<string>>();

            //Gather data until an empty row is encountered
            while (!rowText.Contains("-"))
            {
                //Replace spaces between compound words in first column and remove extra spaces
                rowText = rowText.Replace("|", " ");
                while (rowText.Contains("  "))
                {
                    rowText = rowText.Replace("  ", " ");
                }
                rowText = rowText.Trim();
                List<string> rowList = rowText.Split(' ').ToList();
                list.Add(rowList);
                row++;

                //Get the new row of text from the screen
                rowText = screen.GetText(row, col, 50);
            }

            //Convert list of lists into a two dimenisional array and return the array
            var data = new int[list.Count, list[0].Count];
            for (int i = 0; i < list.Count; i++)
            {
                for (int j = 0; j < list[0].Count; j++)
                {
                    data[i, j] = int.Parse(list[i][j]);
                }
            }
            return data;
        }

        public  void PutDataIntoExcel(int[,] realDataRange)
        {
            Excel.Worksheet myWorksheet =  (Excel.Worksheet)ExcelWorkBookOS.Globals.ThisWorkbook.ActiveSheet;

            // Create a Range of the correct size:
            int rows = realDataRange.GetLength(0);
            int columns = realDataRange.GetLength(1);
            Excel.Range range = myWorksheet.get_Range("B3", Type.Missing);
            range = range.get_Resize(rows, columns);
            range.NumberFormat = "#,##0.00";

            // Assign the Array to the Range
            range.set_Value(Type.Missing, realDataRange);
        }
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
        }
        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            //Start a visible instance of Reflection or get the instance running at the given channel name         
            Application reflectionApplication = MyReflection.CreateApplication("MyWorkspace", true);
                               
            //Create a terminal from the session document file
            string sessionPath = Environment.GetEnvironmentVariable("USERPROFILE") + @"\Documents\Attachmate\Reflection\demoSession.rdox";
            ITerminal terminal = (ITerminal)reflectionApplication.CreateControl(sessionPath);

            //Make the session visible in the workspace and get a handle to the screen
            IFrame frame = (IFrame)reflectionApplication.GetObject("Frame");
            frame.CreateView(terminal);
            IScreen screen = terminal.Screen;

            //Navigate to the screen that has the data
            screen.SendKeys("un");
            screen.SendControlKey(ControlKeyCode.Enter);
            screen.SendKeys("pw");
            screen.SendControlKey(ControlKeyCode.Enter);

            //wait for the host before entering more data
            screen.Wait(3000);
            screen.SendKeys("demodata");
            screen.SendControlKey(ControlKeyCode.Enter);
            screen.Wait(3000);

            //Get the starting point for the data
            ScreenPoint point = screen.SearchText("Jan", 1, 1, FindOptions.Forward);
            int row = point.Row;
            int col = point.Column + 3;

            //Get the Data
            int[,] dataRange =  GetScreenData(row, col, screen);

            //Put the data into Excel
            PutDataIntoExcel(dataRange);      
        }
    }
}