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.

     • 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.

Similarly for sending data we have the following configuration table:

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.

1. Minimal Library:

   • The webservice example uses a lightweight library to serve HTML and CSS assets to the browser.

2. Library Location:

   • This library is located in the WFIL directory.

3. Source Code Directory:

   • The APPW directory contains the source code for the example's:

     • HTML files

     • CSS stylesheets

     • Icons

4. Editable Files:

   • You can customize the example by editing the files in the APPW directory as needed.

Look at APP/APPpull.lua to see how the data needs to be formatted.

In a real application you would most likely be querying the data from a SQL database and then formatting it in the same way as the application.

If you want to add additional web services then edit this code from main:

local APPaction={}
APPaction["/"] = APProot
APPaction["/ExcelExport.xlsm"] = APPspreadSheet
APPaction["/push"]             = APPpush
APPaction["/pull"]             = APPpull     

And define additional functions.

You can specify which table to populate in excel by editing the Table column entry in the Query table. That can be done purely in excel without altering the back end code.

Writing code in Visual Basic for Applications is challenging, so I chose a format that’s simple to parse and generate in VB, avoiding the need for a complex parser.

Since we control both ends of the ‘pipe’ in this case it makes sense to do it this way.

Currently it’s invoking the session API of Iguana itself so every login is done with an Iguana login. I was a little lazy with how I did it - it doesn’t maintain a session.

The authentication is just done using HTTP basic authentication.

One could use #hash_tags on users if one wanted to restrict the data being given to particular users but for this example the code doesn’t do this for simplicity.

Built-in reporting tools because, while they’re quick to set up, they often hit limits and can’t combine data from different systems, like customer management and accounting.

By extracting data from the source systems and store it in separate 'shadow' tables for easy querying gives more power and flexibility.

It’s a first-principles method to give more flexibility and power when building reports.

Good reporting helps organizations communicate better, but it often falls short because teams don’t focus on the right things.

The Key Steps:

1. Decide What Matters: Work with your team to agree on the most important data to track.

2. Keep Improving: Give the team ways to see the results, learn what’s working, and adjust what they focus on.

When everyone is clear on what’s important and keeps learning from the data, reporting becomes a valuable tool for success.

Pivot Tables are a feature in spreadsheet tools like Excel that allow you to quickly summarize, analyze, and organize large amounts of data in a clear and dynamic way.

Why Are Pivot Tables Excellent for Presenting Data?

1. Flexibility: They let you rearrange, filter, and sort data dynamically to focus on specific insights.

2. Summarization: You can easily calculate totals, averages, counts, and other statistics without needing complex formulas.

3. Visualization: Pivot Tables make it simple to turn raw data into a readable, organized format, highlighting trends and patterns.

4. Interactivity: They allow users to drill down into data for deeper insights or adjust the presentation to answer specific questions.

In short, Pivot Tables transform complex data into easy-to-understand summaries, making them a powerful tool for data presentation and decision-making.

Getting data is all about making stories map to codes. Coding stories is difficult because it essentially means getting a team to negotiate and create a shared language to see common trends in the conversations they are having.

The same idea applies whether you are a medical team dealing with patients or company dealing with customers.

Over formalized traditional IT systems often make it difficult for a team to evolve a language to talk about the problems they are seeing.

One clever ‘hack’ is to use the practice of putting #hash_tags into notes. Almost all applications support free form text and thus you put hash tags into this text as a simple fast way to ‘code’ data.