Datasheet

• 4 minutes to read

This widget is designed to display data as a spreadsheet. You can import a file with data, predefine cells in the config, and let your user fill in the cells.

General info

• type: datasheet

Params

• fileName - a file name with the extension of the file to export.
• data - a table scheme, an array of the IWidgetDataSheetColumnScheme objects with column definitions:
  • name - the field name.
  • value - the default value.
  • type - the field type ("Image" | "ImagePlaceholder" | "Text" | "InString" | "BarcodePlaceholder"), optional.
  • barcodeFormat - defines the format for barcodes, optional.
  • barcodeSubType - defines the sub type for QR-codes ("Phone" | "Url" | "None"), optional.
  • readonly - if true, disables field editing, optional. The default value is false.
• tableData - an array of objects, each representing one table row. The keys in these objects are column names.
• order - an array of strings defining the sort order of the columns. If the data array contains more columns than the order array, they are displayed after in the original order.
• hideButtonsLeft - if true, hides the Export and Import buttons. The default value is false.
• hideButtonsRight - if true, hides the Add row and Delete row buttons. The default value is false.
• updateItem - updates cell information for the specified column in the current row. For fields of the ImagePlaceholder type, a value from the value property will be assigned.
• customTexts - defines custom error messages:
  • tableIsEmpty - the table has no rows.
  • tableNotCreatedText - the table is not created.
• allowedFileTypes - file types that can be selected for downloading.
• itemsPerPage - the number of items displayed on a page. The default value is 50.
• onRowChange - a function ("{{#function <expression>}}") or an array of functions that work after changing a cell.
• onCellTextChange - a function ("{{#function <expression>}}") or an array of functions that work after changing a text cell.

Properties

• value or _ - the selected row.
• selectedRowIndex - the index of the selected row.

Methods

• getSelectedRowData() - gets an array of selected row data, all column-associated data, and the field value.
• getTableData() - gets an array of objects representing rows with all associated data.
• setErrors(errors: DataSheetCellError[]) - defines an error for a table cell.

Examples

Defining fields

In this example, we will create a table of three columns and predefine three rows of data. Your users will be able to create and delete rows in this table.

{
    "name": "table",
    "type": "datasheet",
    "params": {
        "data": [
            { "name": "Name" },
            { "name": "Photo" },
            { "name": "Position" }
        ],
        "tableData": [
            { "Name": "John Wood", "Position": "Designer", "Photo": "https://example.com/images/j.wood.jpg" },
            { "Name": "Cristopher Bennet", "Position": "Manager", "Photo": "https://example.com/images/c.bennet.jpg" },
            { "Name": "Alex Ford", "Position": "Tester", "Photo": "https://example.com/images/a.ford.jpg" }
        ],
        "oerder": [ "Name", "Position", "Photo" ]
    }
}

Importing files

If a larger sample is expected, it will be more convenient to download it from a file. By default, you can open files of any type. In this example, we will create a table with names and emails and allow users to import only files in the CSV and XSLS formats for downloading.

{
    "name": "table",
    "type": "datasheet",
    "params": {
        "data": [ 
            {  "name": "UserName"  },
            {  "name": "Email"  }
        ],
        "allowedFileTypes": [
            ".csv",
            ".xsls"
        ]
    }
}

To disable file import, set the hideButtonsLeft param to false.

Filling with variable data

Using this widget, you can implement a scenario in which a table is created based on variable fields added in the editor, and after filling this table, a proof image will be created for a data set from one of the table rows.

First, you will need to define a design-editor widget. In this example, its initialization is omitted to keep it brief.

Then, define a datasheet widget using the variableData property obtained from design-editor when calling the getVariableData() method. This property will contain an array of all fields defined through double braces {{}}.

To display a preview, you can use any widget displaying images. Now, let's define a slider that uses URLs obtained from ajax widget (image-request-vdp).

{
    "name": "editor",
    "type": "design-editor",
    "params": {
        "initial": { ... }
    }
},
{
    "name": "table",
    "type": "datasheet",
    "params": {
        "data": "{{ $['editor'].variableData }}"
    }
},
{
    "name": "vdp-preview",
    "type": "slider",
    "params": {
        "direction": "tile",
        "rows": 1,
        "columns": 1,
        "images": [
            { "url": "{{ $['image-request-vdp'].response }}" }
        ]
    }
}

For more details about defining and processing requests, refer to the Ajax widget.

Finally, define a step for when both the datasheet and slider widgets will be displayed. Note that getVariableData() is executed when this step is activated.

{
    "name": "Variable data",
    "mainPanel": {
        "name": "vdp-preview"
    },
    "toolPanel": {
        "name": "table",
        "height": "50%"
    },
    "onActivate": [
        "{{ #function $['editor'].saveProduct() }}",
        "{{ #function $['editor'].getVariableData() }}"
    ]
}

---

You can find more details about the properties of this widget in AuWidgetDataSheet.

For more details about configuration parameters, refer to IWidgetDataSheetConfig.