Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for HCL Domino provides formulas that can query HCL Domino data. The following three steps show how you can automate the following task: Search HCL Domino data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the HCL Domino data records you want to retrieve, written in standard SQL.

• Connection: Either the connection name, such as DominoConnection1, or a connection string. The connection string consists of the required properties for connecting to HCL Domino data, separated by semicolons.

  Prerequisites

  The connector requires the Proton component to be installed. Normally, Proton is distributed as part of the AppDev pack. See the HCL documentation for instructions on acquiring and installing Proton or the AppDev pack.

  Once the Proton service is installed and running, you will also need to create a user account and download its Internet certificate. This certificate can be used to set the connector certificate connection properties.

  Authenticating to Domino

  • Server: The name or IP address of the server running Domino with the Proton service.
  • Port: The port number that the Proton service is listening on.
  • Database: The name of the database file, including the .nsf extension.
  • SSLClientCertType: This must match the format of the certificate file. Typically this will be either PEMKEY_FILE for .pem certificates or PFXFILE for .pfx certificates.
  • SSLClientCert: The path to the certificate file.
  • SSLServerCert: This can be set to (*) if you trust the server. This is usually the case, but if you want to perform SSL validation, you may provide a certificate or thumbprint instead. See the documentation for SSLServerCert for details.

  Additional Server Configuration

  The connector supports querying Domino views if any are defined. Before views can be queried by the connector they must be registered with the design catalog.

  Please refer to the Catalog Administration section of the AppDev pack documentation for details on how to do this.

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search HCL Domino data, such as City.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM ByName WHERE City = '"&B7&"'","Server="&B1&";Database="&B2&";Port="&B3&";SSLClientCertType="&B4&";SSLClientCert="&B5&";SSLServerCert="&B6&";Provider=Domino",B8)
3. Change the filter to change the data.