Search

excelGet

Version 23.4.8839

excelGet

Get values from an Excel workbook. This operation is commonly used in template files for the Excel connector when Translation Mode is set to Template. However, this operation can also be used in dedicated script scenarios such as the Script connector.

Required Parameters

  • sheet: The sheet in the workbook from which to retrieve values.

Optional Parameters

  • version: The Excel version of the target workbook. The allowed values are AUTO, 95, 97-2003, 2007. The default is AUTO.
  • file: The full path on disk, including the filename, of the Excel workbook. You must specify either the file or handle parameter.
  • handle: A readable handle reference to the Excel data created by excelOpen. If you specify the file parameter, handle is not required.
  • map:*: A set of one or more inputs where the name of the mapped parameter precedes the colon (for example, map:foo), and the value is the name or range of cells in the sheet. For example, the attribute map:foo with a value of C1 creates a foo attribute on the specified output item of the operation, and its value is the value found in cell C1 of the target sheet.

    You can also specify a range of cells to retrieve values from all cells in the range. For example, C1:C10 retrieves all values from cell C1 to C10. You can use the wildcard * character to get the cell values for the length of the column. For example, C1:C* retrieves all values from C1 until the end of the column.

Output Attributes

  • *: Dependent on the content of the sheet and the query specified. If column headers are present, they are used to name the output attributes. In addition, if you set the map:* parameter on the input item, the output item contains attributes with the same name(s). See the Examples for additional context.

Examples

For additional information and more examples, see the Excel to XML section of the Excel connector documentation.

Get Values and Map to a New Item

This example uses the excelGet operation to get specific and ranged values from cells and columns in the specified sheet and map them to a new item. The new item can be used later in the template to populate specific XML elements of an XML output file as part of the Template Mode of the Excel connector. See arc:map for details on the arc:map keyword used in arc:call.

<!-- Read the Excel -->
<arc:set attr="excel.file" value="[FilePath]" />
<arc:set attr="excel.sheet" value="Sheet1" />
<arc:set attr="excel.version" value="2007" />

<!-- Map the specific and ranged values to various attributes -->
<arc:setm item="excel">
  map:PONumber = "C5"
  map:PODate = "C6"
  map:DeliveryDate = "C7"
  map:ShipDate = "C8"
  map:BillerName = "I11"
  map:ShipperName = "C11"
  map:ShipperAddressLine1 = "C12"
  map:ShipperCity = "C13"
  map:ShipperState = "C14"
  map:ShipperZip = "C15"
  map:LineUPC = "B19:B*"
  map:LineQty = "C19:C*"
  map:LineUnit = "D19:D*"
  map:LinePrice = "E19:E*"
  map:LineDesc = "F19:F*"
  map:LineAllowanceRate = "I19:I*"
  map:LineAllowanceType = "J19:J*"
</arc:setm>

<!-- Calling the excelGet operation and using the arc:map keyword to map the output of the operation from the "result" item to a new "data" item that can be used later -->
<arc:call op="excelGet" in="excel" out="result">
  <arc:map from="result" to="data" map="*=*" />
</arc:call>

excelGet in a Script Connector

This example uses the excelGet operation in a Script connector to open an existing Excel workbook on disk and read data from specific cells. The Excel workbook is then closed and the script pushes that data as a new output file.

<!-- Creating the input item for the operation and passing it in -->
<arc:set attr="excel.file" value="C:\Temp\movies.xlsx" />
<arc:call op="excelOpen" in="excel" out="result" >
  <!-- Resolving the handle from excelOpen to use in excelGet -->
  <arc:set attr="get.handle" value="[result.handle]" />
  <arc:set attr="get.sheet" value="film" />
  <arc:set attr="get.version" value="2007" />
  <arc:set attr="get.map:favoritemovie" value="A2" />
  <arc:set attr="get.map:favoritemovieyear" value="B2" />
  <!-- Calling excelGet inside excelOpen to use the handle -->
  <arc:call op="excelGet" in="get" out="out">
    <!-- Creating some output data and file from the data read from the excel sheet -->
    <arc:set attr="output.data" value="My favorite movie is [out.favoritemovie] and it came out in [out.favoritemovieyear]." />
    <arc:set attr="output.filename" value="results.txt" />
    <!-- Using the arc:finally keyword to execute the closing of the handle last -->
    <arc:finally>
      <!-- Calling excelClose to close the handle -->
      <arc:call op="excelClose" in="excel" out="close">
        <!-- Check to ensure the handle was closed and throw an error if it was not -->
        <arc:exists attr="close.success" >
          <arc:else>
            <arc:throw code="CloseFailed" desc="The handle was not closed successfully." />
          </arc:else>
        </arc:exists>
      </arc:call>
    </arc:finally>
  </arc:call>
</arc:call>
<!-- Push the output item out as a file -->
<arc:push item="output" />