{"_id":"57fcc4870312b20e00ac64e4","parentDoc":null,"version":{"_id":"57fcc4860312b20e00ac64c0","project":"5435687035740020002a1c04","__v":1,"createdAt":"2016-10-11T10:52:54.637Z","releaseDate":"2016-10-11T10:52:54.637Z","categories":["57fcc4860312b20e00ac64c1","57fcc4860312b20e00ac64c2","57fcc4860312b20e00ac64c3","57fcc4860312b20e00ac64c4","57fcc4860312b20e00ac64c5","57fcc4860312b20e00ac64c6","57fcc4860312b20e00ac64c7","57fcc4860312b20e00ac64c8","57fcc4860312b20e00ac64c9","57fcc4860312b20e00ac64ca","57fcc4860312b20e00ac64cb"],"is_deprecated":false,"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"[APP-1265], [APP-1035]","version_clean":"5.2.0","version":"5.2"},"project":"5435687035740020002a1c04","user":"5589fde775eaf50d004e4b0c","__v":0,"category":{"_id":"57fcc4860312b20e00ac64c8","__v":0,"version":"57fcc4860312b20e00ac64c0","project":"5435687035740020002a1c04","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2015-11-04T23:13:08.376Z","from_sync":false,"order":7,"slug":"lua-scripting","title":"Agent Lua Scripting"},"updates":[],"next":{"pages":[],"description":""},"createdAt":"2015-11-26T19:44:13.606Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":6,"body":"The Telemetry Agent supports Excel spreadsheet file importing using the Lua scripting language. Imported Excel files will be accessible as a Lua table object.\n\n# Importing\n\nThe `telemetry/excel` library exposes a single function called `import`. This function expects one argument:\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Name\",\n    \"h-1\": \"Type\",\n    \"h-2\": \"Description\",\n    \"0-0\": \"`filepath`\",\n    \"0-1\": \"string\",\n    \"0-2\": \"The complete path and filename to the Excel document that you wish to import. The path is relative to the directory from where the Agent is being executed  (required).\"\n  },\n  \"cols\": 3,\n  \"rows\": 1\n}\n[/block]\nThe import function creates a three level indexed array within a Lua table object. Each array corresponds with the sheet, row, and column `spreadsheet[SHEET_NUMBER][ROW_NUMBER][COLUMN_NUMBER]`  for example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local excel = require(\\\"telemetry/excel\\\")\\n\\n-- The import function takes a path relative to the directory from where the Agent is being executed\\nlocal spreadsheet = excel.import(\\\"/path/to/file/spreadsheet.xlsx\\\")\\n\\n-- Output the value of cell B1 within sheet 1 \\noutput.number = {value = spreadsheet[1][1][2]}\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"warning\",\n  \"title\": \"Lua arrays begin at index 1\",\n  \"body\": \"The convention for Lua is to begin arrays at index 1 instead of index 0 like in many other languages. For all Telemetry libraries, such as `telemetry/excel`, we begin arrays at index 1.\"\n}\n[/block]\n# Outputting a spreadsheet as a Telemetry table\n\nOne of the most common use cases for Excel importing is to represent a spreadsheet's contents as a table within Telemetry. The following code sample can be used for this purpose:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"local excel = require(\\\"telemetry/excel\\\")\\n\\nlocal spreadsheet = excel.import(\\\"/path/to/file/spreadsheet.xlsx\\\")\\n\\n-- Create a variable for our output to Telemetry\\nlocal tableCells = array({})\\n\\n-- Iterate through the spreadsheet rows for sheet 1\\nfor rowKey, row in pairs(spreadsheet[1]) do\\n\\t-- Create a new array in our variable to contain each row of cells\\n\\ttableCells[rowKey] = array({})\\n\\n\\tfor cellKey, cell in pairs(row) do\\n\\t\\t-- Table values must be explicitly defined as either type \\\"number\\\" or type \\\"text\\\"\\n\\t\\tif type(cell) == \\\"number\\\" then\\n\\t\\t\\ttableCells[rowKey][cellKey] = {number = cell}\\n\\t\\telse\\n\\t\\t\\ttableCells[rowKey][cellKey] = {text = cell}\\n\\t\\tend\\n\\tend\\n\\nend\\n\\n-- Send the data to Telemetry\\noutput.cells = tableCells\",\n      \"language\": \"lua\"\n    }\n  ]\n}\n[/block]","excerpt":"","slug":"excel-files","type":"basic","title":"Excel Files"}
The Telemetry Agent supports Excel spreadsheet file importing using the Lua scripting language. Imported Excel files will be accessible as a Lua table object. # Importing The `telemetry/excel` library exposes a single function called `import`. This function expects one argument: [block:parameters] { "data": { "h-0": "Name", "h-1": "Type", "h-2": "Description", "0-0": "`filepath`", "0-1": "string", "0-2": "The complete path and filename to the Excel document that you wish to import. The path is relative to the directory from where the Agent is being executed (required)." }, "cols": 3, "rows": 1 } [/block] The import function creates a three level indexed array within a Lua table object. Each array corresponds with the sheet, row, and column `spreadsheet[SHEET_NUMBER][ROW_NUMBER][COLUMN_NUMBER]` for example: [block:code] { "codes": [ { "code": "local excel = require(\"telemetry/excel\")\n\n-- The import function takes a path relative to the directory from where the Agent is being executed\nlocal spreadsheet = excel.import(\"/path/to/file/spreadsheet.xlsx\")\n\n-- Output the value of cell B1 within sheet 1 \noutput.number = {value = spreadsheet[1][1][2]}", "language": "lua" } ] } [/block] [block:callout] { "type": "warning", "title": "Lua arrays begin at index 1", "body": "The convention for Lua is to begin arrays at index 1 instead of index 0 like in many other languages. For all Telemetry libraries, such as `telemetry/excel`, we begin arrays at index 1." } [/block] # Outputting a spreadsheet as a Telemetry table One of the most common use cases for Excel importing is to represent a spreadsheet's contents as a table within Telemetry. The following code sample can be used for this purpose: [block:code] { "codes": [ { "code": "local excel = require(\"telemetry/excel\")\n\nlocal spreadsheet = excel.import(\"/path/to/file/spreadsheet.xlsx\")\n\n-- Create a variable for our output to Telemetry\nlocal tableCells = array({})\n\n-- Iterate through the spreadsheet rows for sheet 1\nfor rowKey, row in pairs(spreadsheet[1]) do\n\t-- Create a new array in our variable to contain each row of cells\n\ttableCells[rowKey] = array({})\n\n\tfor cellKey, cell in pairs(row) do\n\t\t-- Table values must be explicitly defined as either type \"number\" or type \"text\"\n\t\tif type(cell) == \"number\" then\n\t\t\ttableCells[rowKey][cellKey] = {number = cell}\n\t\telse\n\t\t\ttableCells[rowKey][cellKey] = {text = cell}\n\t\tend\n\tend\n\nend\n\n-- Send the data to Telemetry\noutput.cells = tableCells", "language": "lua" } ] } [/block]