Skip to content

Latest commit

 

History

History
297 lines (258 loc) · 10.8 KB

spreadsheet_parse_method.md

File metadata and controls

297 lines (258 loc) · 10.8 KB
sidebar_label parse()
title parse method
description Load data into DHTMLX Spreadsheet from a local data source with the parse() method, including cell values, styles, formats, editors, links and several sheets.

parse()

Description

@short: Loads data into spreadsheet from a local data source

Usage

parse([
    {
        cell: string,
        value: string | number | Date,
        css?: string,
        format?: string,
        editor?: {
            type: string, // type: "select"
            options: string | array
        },
        locked?: boolean,
        link?: {
            text?: string,
            href: string
        }
    },
    // more cell objects
]): void;
parse({
    sheets: [
        {
            name?: string,
            id?: string,
            cols?: [
                {
                    width?: number,
                    hidden?: boolean,
                },
                // more column objects
            ],
            rows?: [
                {
                    height?: number,
                    hidden?: boolean,
                },
                // more row objects
            ],
            data: [
                {
                    cell: string,
                    value: string | number | Date,
                    css: string,
                    format?: string,
                    editor?: {
                        type: string, // type: "select"
                        options: string | array
                    },
                    locked?: boolean,
                    link?: {
                        text?: string,
                        href: string
                    }
                },
                // more cell objects
            ],
            merged?: [
                { 
                    from: { column: index, row: index }, 
                    to: { column: index, row: index }
                },
                // more objects
            ],
            freeze?: {
               col?: number,
               row?: number,
            }
        },
        // more sheet objects
    ]      
}): void;

Parameters

If you need to create a data set for one sheet only, specify data as an array of cell objects. For each cell object you can specify the following parameters:

  • cell - (required) the id of a cell that is formed as "id of the column + id of the row", for example, A1
  • value - (required) the value of a cell
  • css - (optional) the name of the CSS class
  • format - (optional) the name of the default number format or of a custom format that you've added to apply to the cell value
  • editor - (optional) an object with configuration settings for the editor of a cell:
    • type - (required) the type of the cell editor: "select"
    • options - (required) either a range of cells ("A1:B8") or an array of string values
  • locked - (optional) defines whether a cell is locked, false by default
  • link - (optional) an object with configuration settings for the link added into a cell:
    • text - (optional) the text of a link
    • href - (required) the URL that defines the link destination

If you need to create a data set for several sheets at once, specify data as an object with the following parameter:

  • sheets - (required) an array of sheet objects. Each object has the following properties:
    • name - (optional) the sheet name
    • id - (optional) the sheet id
    • rows - (optional) an array of objects with rows configurations. Each object may contain the following properties:
      • height - (optional) the row height. If not specified, rows have a height of 32px
      • hidden - (optional) defines the visibility of a row
    • cols - (optional) an array of objects with columns configurations. Each object may contain the following properties:
      • width - (optional) the column width. If not specified, columns have a width of 120px
      • hidden - (optional) defines the visibility of a column
    • data - (required) an array of cell objects. Each object has the following properties:
      • cell - (required) the id of a cell that is formed as "id of the column + id of the row", for example, A1
      • value - (required) the value of a cell
      • css - (optional) the name of the CSS class
      • format - (optional) the name of the default number format or of a custom format that you've added to apply to the cell value
      • editor - (optional) an object with configuration settings for the editor of a cell:
        • type - (required) the type of the cell editor: "select"
        • options - (required) either a range of cells ("A1:B8") or an array of string values
      • locked - (optional) defines whether a cell is locked, false by default
      • link - (optional) an object with configuration settings for the link added into a cell:
        • text - (optional) the text of a link
        • href - (required) the URL that defines the link destination
    • merged - (optional) an array of objects where each object defines a range of cells which need to be merged. Each object must include the following properties:
      • from - an object which defines the position of the first cell from a range:
        • column - the index of the column
        • row - the index of the row
      • to - an object which defines the position of the last cell from a range:
        • column - the index of the column
        • row - the index of the row
    • freeze - (optional) an object that sets and adjusts fixed columns/rows for particular sheets. It may contain the following properties:
      • col - (optional) specifies the number of fixed columns (for example, 2), 0 by default
      • row - (optional) specifies the number of fixed rows (for example, 2), 0 by default

:::info If the multisheets configuration option is set to false, only one sheet is created. :::

Example

const data = [
    { cell: "A1", value: "Country" },
    { cell: "B1", value: "Product" },
    { cell: "C1", value: "Price" },
    { cell: "D1", value: "Amount" },
    { cell: "E1", value: "Total Price" },

    { cell: "A2", value: "Ecuador" },
    { cell: "B2", value: "Banana" },
    { cell: "C2", value: 6.68, css: "someclass" },
    { cell: "D2", value: 430 },
    { cell: "E2", value: 2872.4 },
    
    // add drop-down lists to cells
    { cell: "A9", value: "Turkey", editor: {type: "select", options: ["Turkey","India","USA","Italy"]} },
    { cell: "B9", value: "", editor: {type: "select", options: "B2:B8" } },

    // more data
];

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {});
spreadsheet.parse(data);
const data = {
    sheets: [
        { 
            name: "sheet 1", 
            id: "sheet_1",
            rows: [
                { height: 50, hidden: true }, // config of the first row
                { height: 50 }, // config of the second row
                // the height of other rows is 32
            ],
            cols: [
                { width: 300 }, // config of the first column
                { width: 300, hidden: true }, // config of the second column
                // the width of other columns is 120
            ],
            data: [
                { cell: "A1", value: "Country" },
                { cell: "B1", value: "Product" }
            ],
            merged: [
                // merge cells A1 and B1
                { from: { column: 0, row: 0 }, to: { column: 1, row: 0 } },
                // merge cells A2, A3, A4, and A5
                { from: { column: 0, row: 1 }, to: { column: 0, row: 4 } }
            ],
            freeze: {
                col: 2,
                row: 2
            },
        }, 
        { 
            name: "sheet 2", 
            id: "sheet_2", 
            data: [
                { cell: "A1", value: "Country" },
                { cell: "B1", value: "Product" },
            ]
        }
    ]
};

spreadsheet.parse(data);

Parsing styled data

You can also add specific styles for cells while preparing a data set. To do this, define data as an object with two parameters:

  • styles - (required) an object with CSS classes to be applied to particular cells. Check the details below
  • data - (required) the data to load
const styledData = {
    styles: {
        someclass: {
            background: "#F2F2F2",
            color: "#F57C00"
        }
    },
    data: [
        { cell: "a1", value: "Country" },
        { cell: "b1", value: "Product" },
        { cell: "c1", value: "Price" },
        { cell: "d1", value: "Amount" },
        { cell: "e1", value: "Total Price" },

        { cell: "a2", value: "Ecuador" },
        { cell: "b2", value: "Banana" },
        { cell: "c2", value: 6.68, css: "someclass" },
        { cell: "d2", value: 430, css: "someclass" },
        { cell: "e2", value: 2872.4 }
    ],
};

const spreadsheet = new dhx.Spreadsheet("spreadsheet_container", {});
spreadsheet.parse(styledData);

:::info Set a CSS class for a cell with the css property. :::

List of properties

The list of properties you can specify in the styles object:

  • background
  • color
  • textAlign
  • verticalAlign
  • textDecoration
  • fontWeight
  • fontStyle
  • multiline: "wrap" (from v5.0.3)
  • border, border-right, border-left, border-top, border-bottom (from v5.2)

:::note You may also use the following properties if needed:

  • fontSize
  • font
  • fontFamily
  • textShadow

but in some cases they may not work as you expect (for example, when applying position: absolute or display: box) :::

Change log:

  • The freeze property and the hidden parameter for the rows and cols properties of the sheets object were added in v5.2
  • The locked and link properties of the cell object were added in v5.1
  • The merged property of the sheets object was added in v5.0
  • The editor property of the cell object was added in v4.3
  • The rows and cols properties of the sheets object were added in v4.2
  • The ability to prepare data for several sheets was added in v4.1

Related article: Data loading and export

Related samples: