Excel

1.6.2

The Glue42 Excel Connector allows applications to use Excel as a local data editor. The application uploads tabular data into Excel, so that the user may view it and edit it in a familiar environment. Any changes can then be transmitted back to the application for validation, processing, auditing and/or long-term storage.

See also the Excel Connector documentation for more details.

object

methodopenSheet
(sheetData: OpenSheetConfig) => Promise<Sheet>

Description

Opens a new sheet

Parameters

Name
Type
Required
Description

sheetData

yes

Returns

Promise<Sheet>

Example

var options = {
  columnConfig: [
    { header: "Symbol", fieldName: "symbol" },
    { header: "Price", fieldName: "price" }
  ],
  data: [
    { price: 100, symbol: "AAPL" },
    { price: 200, symbol: "GOOG" }
  ],
  options: {
    workbook: "MyWorkbook",
    worksheet: "MyWorksheet"
  }
};

glue4office.excel
  .openSheet(options)
  .then(sheet => {
    // see Sheet
  })
  .catch(err => {});
methodready
() => Promise<API>

Returns

Promise<API>

(callback: (connected: boolean) => void) => UnsubscribeFunction

Description

Whether the Excel add-in is working or not

Parameters

Name
Type
Required
Description

callback

(connected: boolean) => void

yes

Returns

UnsubscribeFunction

Example

glue4office.excel.onAddinStatusChanged(connected => {
  if (connected) {
    // yahoo - we're connected to excel
  } else {
    console.log("Excel is closed or addin is not working");
  }
});

Properties

boolean

Excel addin status

Returns all sheets opened by the current application

object

methodupdate
(data: object[]) => Promise<void>

Description

Updates the data in the sheet.

Parameters

Name
Type
Required
Description

data

object[]

yes

Returns

Promise<void>

(columnConfig: ColumnConfig[], data: object[]) => Promise<void>

Description

Change the column configuration of the sheet. Optionally pass new data (if not passed it will keep the existing data)

Parameters

Name
Type
Required
Description

columnConfig

yes

data

object[]

no

Returns

Promise<void>

methodonChanged
(callback: (data: object[], errorCallback: (errors: ValidationError[]) => void, doneCallback: () => void, delta: DeltaItem[]) => void) => UnsubscribeFunction

Description

Executes when the sheet is changed in Excel, after all chunks have arrived.

Parameters

Name
Type
Required
Description

callback

(data: object[], errorCallback: (errors: ValidationError[]) => void, doneCallback: () => void, delta: DeltaItem[]) => void

yes

Returns

UnsubscribeFunction

Example

glue4office.excel
  .openSheet(options)
  .then(sheet => {
    sheet.onChanged((data, errorCb, doneCb, delta) => {
      console.log(data);
      // if no errors - call doneCb()
      // else  errorCb([{
      //     row:0,
      //     column:1,
      //     description: "Error!"
      //}])
    });
  })
  .catch(console.warn);
methodonChanging
(callback: (info: SheetChangingInfo) => void) => UnsubscribeFunction

Description

Executes when data starts coming in from Excel, once for every chunk. Useful for showing progress indication, etc.

Parameters

Name
Type
Required
Description

callback

(info: SheetChangingInfo) => void

yes

Returns

UnsubscribeFunction

Properties

Current column configuration

Sheet options

string

The name of the sheet

string

The name of the workbook

object[]

The current data in the sheet

object

Properties

string

Name of the sheet to receive the data; else uses the first sheet in the workbook.

string

Name of the workbook to reuse; otherwise a new temporary workbook will be created.

string

Name of a template workbook to use when creating new workbooks.

string

Name of the worksheet to display; ignored if there is no template.

Trigger conditions control tell ExcelPad when to invoke the validation method; default is never to return data.

boolean

Set to true to prevent the user from saving the temporary workbook.

boolean

Remove all existing rows before applying the new data; default true.

string

The trigger button is placed over a range of cells; default is A1.

string

The caption to use for a trigger button.

string

The top-left address of the data in the dataWorksheet; default is A1.

string

ExcelPad will create an Excel Named Range that defines the extent of the data written to the worksheet.

"hide" | "show" | "normal" | "restore" | "max" | "min" | "top" | "bottom"

Excel window activation/state options

number

How long to wait for success response from Excel when opening sheet

number

The maximum number of rows of changes to send in each invocation of the Validation method; default 1000.

boolean

If true, send before and after images of each row as it’s sent for validation. Defaults to true.

object

Properties

Defines the column headers to use for the grid of data (the first row).

object[]

Defines the data itself.

object

Properties

string

The name of the property that is used to transmit this value to/from Excel

string

A string that forms the Column header text in Excel. If not specified fieldName is used.

string

A color value for the text colour of the cells in this column

string

A color value for the background colour of the cells in this column

number

Width of the column in Excel units of 0 (zero) to 255. This value represents the number of characters that can be displayed in a cell that is formatted with the standard font. The default column width is 8.43 characters. If a column has a width of 0 (zero), the column is hidden.

The type and possible values for cells in the column.

boolean

If true, will cause numeric data in this column to be stored as text in Excel.

boolean

If true, will cause empty strings in this column to be stored quoted in Excel, enabling round trip.

object

Properties

The type of data that can be used in the cells.

Affects the type of warning when the validation fails.

string[]

An array of strings giving the possible values for the cells, when the type is set to List. ExcelPad uses this to build a drop-down combo box allowing the user to select one from the possible values.

object

Properties

number

The location of the error (row 0 is the row containing the column headers)

number

The location of the error (column 0 is the left-most column in the grid)

number

An error code that defines the problem. This will be displayed in the cell’s comment.

string

A human-readable description of the problem. This will be displayed in the cell’s comment

string

The cell text will be replaced by this value; default is not to change the value in the cell.

string

The new colour for the cell. default is to use the column colour.

string

The new background colour for the cell. default is to use the column colour.

object

Properties

number

In case of large updates, GlueXL sends the update data in chunks. This field indicates which chunk has been received. Between 0 to (totalChunks - 1)

number

In case of large updates, GlueXL sends the update data in chunks. This field indicates the total number of chunks.

object

Description

Represents a change to a row

Properties

"modified" | "inserted" | "unchanged" | "deleted"

any[]

any[]

number

any[]

number

number

Count of deleted rows in case ‘action’ is “deleted”

enumeration

Description

Trigger conditions control when ExcelPad will to invoke the validation method

  • save

    When the user tries to save the worksheet

  • button

    When the user clicks the ExcelPad button (see the buttonText and buttonRange arguments).

  • row

    When the user changes one or more cells in a given row and then selects a cell in a different row.

enumeration

Description

  • Stop

    Prevent users from entering invalid data in a cell. A Stop alert message has two options: Retry or Cancel.

  • Warning

    Warn users that the data they entered is invalid, without preventing them from entering it. When a Warning alert message appears, users can click Yes to accept the invalid entry, No to edit the invalid entry, or Cancel to remove the invalid entry.

  • Information

    Inform users that the data they entered is invalid, without preventing them from entering it. This type of error alert is the most flexible. When an Information alert message appears, users can click OK to accept the invalid value or Cancel to reject it.

enumeration

Description

  • WholeNumber

    only whole numbers are allowed. Once the whole number option is selected, other options become available to further limit input.

  • Decimal

    only decimal numbers are allowed. Once the decimal number option is selected, other options become available to further limit input.

  • Date

    only dates are allowed.

  • Time

    only times are allowed.

  • TextLength

    validates input based on number of characters or digits

  • List

    only values from a predefined list are allowed. The values are presented to the user as a dropdown menu control