Reflection .NET API
Walkthrough / 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);      
              }
          }
      }