• Back to Glue42 Enterprise Docs
Glue42 enterprise documentation

Reference Documentation

  • Back to Glue42 Enterprise Docs
Press/
  • Glue4Office
  • Bus
  • Connection
  • Excel
  • Interop
  • Logger
  • Metrics
  • Outlook
  • Shared Contexts
  • Word

Excel

1.6.5

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.

APIobject

Properties

Property Type Default Required Description
addinStatus boolean

Excel addin status

sheets Sheet[]

Returns all sheets opened by the current application

Methods

  • onAddinStatusChanged
  • openSheet
  • ready

onAddinStatusChangedmethod

Signature

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

Description

Whether the Excel add-in is working or not

Parameters

Name Type Required Description
callback (connected: boolean) => void

Example

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

openSheetmethod

Signature

(sheetData: OpenSheetConfig) => Promise<Sheet>

Description

Opens a new sheet

Parameters

Name Type Required Description
sheetData OpenSheetConfig

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 => {});

readymethod

Signature

() => Promise<API>

ColumnConfigobject

Properties

Property Type Default Required Description
backgroundColor string

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

fieldName string

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

forceText boolean

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

foregroundColor string

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

header string

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

preserveEmptyString boolean

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

validation Validation

The type and possible values for cells in the column.

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

DeltaItemobject

Description

Represents a change to a row

Properties

Property Type Default Required Description
action "modified" | "inserted" | "unchanged" | "deleted"
count number

Count of deleted rows in case 'action' is "deleted"

row any[]
rowAfter any[]
rowAfterIndex number
rowBefore any[]
rowBeforeIndex number

OpenSheetConfigobject

Properties

Property Type Default Required Description
columnConfig ColumnConfig[]

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

data object[]

Defines the data itself.

options OpenSheetOptions

OpenSheetOptionsobject

Properties

Property Type Default Required Description
buttonRange string

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

buttonText string

The caption to use for a trigger button.

chunkSize number

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

clearGrid boolean

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

dataRangeName string

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

inhibitLocalSave boolean

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

response "image" | "delta"

delta response will return only the delta change and image will return the current data after the change. Default: delta (=== row)

sendBeforeAndAfter boolean

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

templateWorkbook string

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

templateWorksheet string

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

timeoutMs number

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

topLeft string

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

updateTrigger TriggerType[]

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

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

Excel window activation/state options

workbook string

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

worksheet string

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

Sheetobject

Properties

Property Type Default Required Description
columnConfig ColumnConfig[]

Current column configuration

data object[]

The current data in the sheet

name string

The name of the sheet

options OpenSheetOptions

Sheet options

workbook string

The name of the workbook

Methods

  • changeColumnConfig
  • onChanged
  • onChanging
  • update

changeColumnConfigmethod

Signature

(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 ColumnConfig[]
data object[]

onChangedmethod

Signature

(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

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);

onChangingmethod

Signature

(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

updatemethod

Signature

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

Description

Updates the data in the sheet.

Parameters

Name Type Required Description
data object[]

SheetChangingInfoobject

Properties

Property Type Default Required Description
chunkIndex 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)

totalChunks number

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

Validationobject

Properties

Property Type Default Required Description
alert ValidationAlert

Affects the type of warning when the validation fails.

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

type ValidationType

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

ValidationErrorobject

Properties

Property Type Default Required Description
backgroundColor string

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

column number

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

description string

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

error number

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

foregroundColor string

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

row number

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

text string

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

TriggerTypeenumeration

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.

ValidationAlertenumeration

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.

ValidationTypeenumeration

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

  • © 2023 Glue42
  • Home
  • Privacy policy
  • Contact Sales
  • Glue42.com
  • Tick42.com
  • Overview
  • API
  • ColumnConfig
  • DeltaItem
  • OpenSheetConfig
  • OpenSheetOptions
  • Sheet
  • SheetChangingInfo
  • Validation
  • ValidationError
  • TriggerType
  • ValidationAlert
  • ValidationType
Navigate
Go