Excel Adapter

1. Import the Excel Adapter:

   • Click the +Component button in Iguana and import the Excel Adapter.

2. Start the Adapter:

   • Start the adapter. It will automatically begin listening on port 6456.

3. Access the Adapter in Your Browser:

   • If you’re on your local machine, open the URL http://localhost:6456 in your web browser.

   • Log in using the same username and password you use for Iguana. The adapter uses Iguana’s user credentials.

4. Download the Sample Spreadsheet:

   • Once logged in, the page will offer a link to download a sample spreadsheet. Click the link to download it.

5. Enable Macros in Excel:

   • When opening the spreadsheet, Excel will ask if you want to Enable Macros. Click YES to proceed.

6. Go to the GetData Tab:

   • The spreadsheet will open to the GetData tab.

7. Adjust User Settings if Needed:

   • If you don’t have the password for the default "admin" user, change the USER field to your own Iguana username.

8. Retrieve Data from Iguana:

   • Click the button labeled Get Data From Iguana.

   • Enter your Iguana password when prompted.

   • The adapter will create a new tab called PulledData, which will contain the data retrieved from Iguana.

9. Send Data to Iguana (Optional):

   • To send data to Iguana, click the button labeled Send Data to Iguana.

   • The adapter will send data from the table in the Accounts tab (named "Accounts").

By following these steps, you can easily fetch and send data between Excel and Iguana using the adapter.

1. Open the Translator Project:

   • Start by opening the Translator project in Iguana.

2. Select the “Pull” Sample Data:

   • Choose the Pull example to see how data retrieval is implemented.

3. Understand the Pull Process:

   • Navigate to the APP/APPpull.lua source file.

   • This file shows how:

     • CSV data is generated and stored as a Lua table.

     • The data is formatted and sent in response to the /pull web request.

   • The annotations in the code should make it easy to understand how the data flows from the source system to the web request response.

4. Explore the Push Process:

   • Switch to the Push example to see how Iguana handles incoming data.

   • Navigate to the APP/APPpush.lua source file.

   • This file demonstrates how Iguana receives and processes data sent from the spreadsheet.

   • The annotations in this file clearly explain how data flows from the incoming request into Iguana's processing logic.

By reviewing the annotated code in APPpull.lua and APPpush.lua, you can easily understand how data flows through Iguana for both pulling data into Lua tables and pushing data from external sources.

1. Code Language:

   • The code that handles communication with Excel is written in Visual Basic for Applications (VBA).

2. Communication Process:

   • Data is sent and received using HTTP requests.

   • The adapter parses incoming data and formats outgoing data to work with Excel.

3. Configuration Table:

   • A configuration table in Excel determines which web services to query and where to store the data. It contains the following key columns:

     • Enabled - true if the call is executed when pressing the Get Data from Iguana button

     • Table: Specifies where the data will be stored.

       • The adapter will create or update an Excel table object with this name in a tab of the same name.

       • You can change this on the fly simply by editing the name in the Table column. The adapter will create a new tab and a new table when you click on the Get Data from Iguana button.

     • URL: Provides the URL of the Iguana-powered web service to fetch the data.

     • ParamName and ParamValue: Optional parameters that can be used for more advanced customization when querying the web service.

By leveraging Excel table objects, the adapter ensures that data is dynamically updated and formatted properly. This setup provides flexibility for storing and customizing data retrieval and storage processes.

Open

Similarly for sending data we have the following configuration table:

Open

Excel Tables: A Unique Feature

Excel table objects are a powerful feature that simplify the management of tabular data. They allow addition of columns and rows, and automatically update dependent features like PivotTables. When a PivotTable references a table object as its data source, it stays synchronized with any updates to the table.

How the Iguana Excel Adapter Works

The Excel adapter code integrates with Iguana's webservice to efficiently populate Excel tables with data:

1. Data Fetching: The adapter retrieves data from a specified webservice URL.

2. Table Creation:

   • If the named table doesn’t exist, the adapter:

     • Creates a new worksheet using the table name.

     • Generates a new table object with the same name on that worksheet.

     • Dynamically creates the required columns based on the data structure.

3. Data Insertion: If the table already exists, the adapter inserts the data into the table, updating existing rows and columns as needed.

Key Advantage: Preserving Customizations

The algorithm is designed to preserve existing tables, including any user-added columns. For example, if a user adds custom columns with formulas referencing other columns in the table, these customizations remain intact even when the table is refreshed with new data.

This makes the adapter particularly user-friendly, enabling dynamic updates without losing manual enhancements or custom calculations.

Look at the APP/APPspreadSheet.lua and exercise it with the GetSpreadsheet sample data.

This shows how the APP/APPiguanaFeed.xlsm is modified to have the VBA code.

Excel .xlsm files are essentially zipped bundles of XML files. You can unzip them, modify the content programmatically, and zip them back up.

They also include a vbaProject.bin file, which stores VBA code. The adapter uses this approach to add my VBA code for the adaptor.

You can see this piece in action by open the translator project and looking how how GetSpreadsheet sample data request is implemented in APP/APPspreadSheet.