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.

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>

Properties

Property	Type	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.

Description

Represents a change to a row

Properties

Property	Type	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

Properties

Property	Type	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

Properties

Property	Type	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.

Properties

Property	Type	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

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[]

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.

Properties

Property	Type	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.

Properties

Property	Type	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.

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.

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.

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

Reference Documentation

Excel

APIobject

Properties

Methods

onAddinStatusChangedmethod

Signature

Description

Parameters

Example

openSheetmethod

Signature

Description

Parameters

Example

readymethod

Signature

ColumnConfigobject

Properties

DeltaItemobject

Description

Properties

OpenSheetConfigobject

Properties

OpenSheetOptionsobject

Properties

Sheetobject

Properties

Methods

changeColumnConfigmethod

Signature

Description

Parameters

onChangedmethod

Signature

Description

Parameters

Example

onChangingmethod

Signature

Description

Parameters

updatemethod

Signature

Description

Parameters

SheetChangingInfoobject

Properties

Validationobject

Properties

ValidationErrorobject

Properties

TriggerTypeenumeration

Description

ValidationAlertenumeration

Description

ValidationTypeenumeration

Description