Reference External Data Using the COM

Using Lua and the Microsoft Component Object Model (COM) to interact with external data sources

Visionary Render supports LuaCOM, a library which allows Lua scripts to interact with the COM. In all scripts, the global interface named luacom is available by default, to be accessed as described by the API documentation linked above. Below are a couple of examples of what this can do.

Web Services

To access web data, the MSXML2.XMLHTTP object can be used. In the example below, we start by calling luacom.CreateObject to make an instance of this. Note that if the application will be making multiple HTTP requests, it is more efficient to reuse the same XMLHTTP object than recreating it each time. In this case, the object is local, and will be destroyed when the script terminates.

Next, we call open on the web object, passing it the GET method and desired URL. The third argument, true, is for the async parameter on web:open(). This allows the script to continue running while the server is contacted, as otherwise, the entire application would lock up.

After we have sent the request we take advantage of the Lua coroutines supported by Visionary Render. To block the script from completing until the request has completed, the script calls vrYield() in a loop. This allows Visionary Render to continue updating, checking on the status of the request every frame. When it has finished, we print the result. Note that it is important to use a timeout for web requests, as we can never know how long the server may take to respond, and should handle the case where it never does. This is achieved by checking the __Time register. The readyState variable will be 1 (LOADING) when we initiate the request, and will step through a number of other states until the operation completes on 4 (COMPLETED).

function httpRequest(url)
  -- Create the COM object.
  local web = luacom.CreateObject("MSXML2.XMLHTTP")

  -- Make an asynchronous request.
  web:open("GET", url, true)
  web:send()

  -- Yield until done, or time out after 10 seconds.
  local timeout = 10
  local startTime = __Time
  while web.readyState < 4 and __Time - startTime < timeout do
    vrYield()
  end

  -- Print the result.
  print(web.responseText)
end

Excel Spreadsheets

Important: This requires a valid Microsoft Excel license.

Moreso than contacting web servers, the COM is known for its ability to interface with Microsoft applications. In the next example we will read data from an Excel spreadsheet.

In this case we create an Excel.Application object. As before, this can be a slow operation, so it is beneficial to reuse the object.

Next, we open the desired file with excel.Workbooks:Open(). This demonstrates LuaCOM's partial support for named parameters; the Open() function takes a number of named parameters, which in a language like Visual Basic can be specified in any order. Unfortunately, in Lua, we must pass them in order. This means that we specify the FileName, then leave UpdatedLinks as nil, so we can set ReadOnly to true - for this specific case the arguments can be found here.

After this we examine the first sheet in the workbook. We can read the entire used section of the sheet into a Lua table by taking advantage of the UsedRange property on the sheet. This is much faster than calling the COM for every cell. Value() returns a Lua primitive for a single cell, or a table for a range.

Finally, we Close() the workbook, passing false to say we don't want to save any changes. Unlike the COM objects themselves, which are destroyed when they go out of scope, we must do this manually. The function caller now has access to the data from the spreadsheet.

As a final note, it is not advised to use this method for very large spreadsheets, due to both Lua's memory constraints and performance of the COM. If you have a very large data set, a more suitable approach may be to export from Excel in another format such as csv, and load that incrementally.

function loadExcel(filename)
  -- Create the COM object.
  local excel = luacom.CreateObject("Excel.Application")

  -- Load the file as read-only.
  local workbook = excel.Workbooks:Open(filename, nil, true)
  local sheet = workbook.Sheets(1)

  -- Read the whole sheet in one go.
  local sheetData = sheet.UsedRange:Value()

  -- Close the file.
  workbook:Close(false)

  return sheetData
end