Edit this page

kendo.ui.Spreadsheet

Represents the Kendo UI Spreadsheet widget. Inherits from Widget.

Configuration

activeSheet String

The name of the currently active sheet.

Must match one of the (sheet names)[#configuration-sheets.name] exactly.

columnWidth Number (default: 64)

The default column width in pixels.

columns Number (default: 50)

The number of columns in the document.

defaultCellStyle Object

The default cell styles to be applyied to sheet cells.

defaultCellStyle.background String

The background CSS color of the cell.

defaultCellStyle.color String

The text CSS color of the cell.

defaultCellStyle.fontFamily String

The font family for the cell.

defaultCellStyle.fontSize String

The font size of the cell in pixels.

defaultCellStyle.Italic Boolean

Sets the cell font to italic, if set to true.

defaultCellStyle.bold Boolean

Sets the cell font to bold, if set to true.

defaultCellStyle.underline Boolean

Sets the cell font to underline, if set to true.

defaultCellStyle.wrap Boolean

Sets the cell wrap, if set to true.

headerHeight Number (default: 20)

The height of the header row in pixels.

headerWidth Number (default: 32)

The width of the header column in pixels.

excel Object

Configures the Kendo UI Spreadsheet Excel export settings.

excel.fileName String (default: "Spreasheet.xslx")

Specifies the file name of the exported Excel file.

Example - set the default Excel file name

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }],
            }],
            excel: {
                fileName: "Order.xlsx"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsExcel();
    </script>

excel.forceProxy Boolean (default: false)

If set to true, the content will be forwarded to proxyURL even if the browser supports saving files locally.

excel.proxyURL String (default: null)

The URL of the server side proxy which will stream the file to the end user.

A proxy will be used when the browser isn't capable of saving files locally. Such browsers are IE version 9 and lower and Safari.

The developer is responsible for implementing the server-side proxy.

The proxy will receive a POST request with the following parameters in the request body:

  • contentType: The MIME type of the file
  • base64: The base-64 encoded file content
  • fileName: The file name, as requested by the caller.

The proxy should return the decoded file with the "Content-Disposition" header set to attachment; filename="<fileName.xslx>".

Example - set the server proxy URL

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }],
            }],
            excel: {
                proxyURL: "/save"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsExcel();
    </script>

pdf Object

Configures the Kendo UI Spreadsheet PDF export settings.

pdf.area String

The area to export. Possible values:

  • "workbook": Exports the full workbook, including all sheets.
  • "sheet": Exports the active sheet.
  • "selection": Exports the selected area on the active sheet.

Example - set export area

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                area: "selection"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.author String (default: null)

The author of the PDF document.

Example - set the author

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                author: "John Doe"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.creator String (default: "Kendo UI PDF Generator")

The creator of the PDF document.

Example - set the creator

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                creator: "John Doe"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.date Date

The date when the PDF document is created. Defaults to new Date().

Example - set the date

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                date: new Date("2014/10/10")
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.fileName String (default: "Export.pdf")

Specifies the file name of the exported PDF file.

Example - set the default PDF file name

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                fileName: "Foo.pdf"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.fitWidth Boolean (default: false)

An option indicating whether to fit the spreadsheet content to page width.

Example - fit spreadsheet to page width

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [
                        { value: "A" }, { value: "B" }, { value: "C" },
                        { value: "1" }, { value: "2" }, { value: "3" },
                        { value: "4" }, { value: "5" }, { value: "6" },
                        { value: "7" }, { value: "8" }, { value: "9" }
                    ]
                }]
            }],
            pdf: {
                landscape: false,
                fitWidth: true
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.forceProxy Boolean (default: false)

If set to true, the content will be forwarded to proxyURL even if the browser supports saving files locally.

pdf.guidelines Boolean (default: false)

An option indicating whether to export the cell guidelines.

Example - disable guidelines

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                guidelines: false
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.hCenter Boolean (default: false)

An option indicating whether to center the content horizontally.

See also vCenter.

Example - center content horizontally

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                hCenter: true
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.keywords String (default: null)

Specifies the keywords of the exported PDF file.

Example - set the keywords

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                keywords: "food order"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.landscape Boolean (default: true)

Set to true to reverse the paper dimensions if needed such that width is the larger edge.

Example - set portrait mode

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                landscape: false
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.margin Object

Specifies the margins of the page (numbers or strings with units). Supported units are "mm", "cm", "in" and "pt" (default).

Example - set the margins

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                margin: {
                    left: 10,
                    right: "10pt",
                    top: "10mm",
                    bottom: "1in"
                }
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.margin.bottom Number |String (default: 0)

The bottom margin. Numbers are considered as "pt" units.

pdf.margin.left Number |String (default: 0)

The left margin. Numbers are considered as "pt" units.

pdf.margin.right Number |String (default: 0)

The right margin. Numbers are considered as "pt" units.

pdf.margin.top Number |String (default: 0)

The top margin. Numbers are considered as "pt" units.

pdf.paperSize String |Array (default: "auto")

Specifies the paper size of the PDF document. The default "auto" means paper size is determined by content.

The size of the content in pixels will match the size of the output in points (1 pixel = 1/72 inch).

Supported values:

  • A predefined size: "A4", "A3" etc
  • An array of two numbers specifying the width and height in points (1pt = 1/72in)
  • An array of two strings specifying the width and height in units. Supported units are "mm", "cm", "in" and "pt".

Example - set custom paper size

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                paperSize: ["100mm", "50mm"]
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.proxyURL String (default: null)

The URL of the server side proxy which will stream the file to the end user.

A proxy will be used when the browser is not capable of saving files locally, for example, Internet Explorer 9 and Safari.

The developer is responsible for implementing the server-side proxy.

The proxy will receive a POST request with the following parameters in the request body:

  • contentType: The MIME type of the file
  • base64: The base-64 encoded file content
  • fileName: The file name, as requested by the caller.

The proxy should return the decoded file with the "Content-Disposition" header set to attachment; filename="<fileName.pdf>".

Example - set the server proxy URL

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                proxyURL: "/save"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.proxyTarget String (default: "_self")

A name or keyword indicating where to display the document returned from the proxy.

If you want to display the document in a new window or iframe, the proxy should set the "Content-Disposition" header to inline; filename="<fileName.pdf>".

Example - open the generated document in a new window

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                forceProxy: true,
                proxyURL: "/save",
                proxyTarget: "_blank"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.subject String (default: null)

Sets the subject of the PDF file.

Example - set the subject

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                subject: "Products"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.title String (default: null)

Sets the title of the PDF file.

Example - set the title

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                title: "Products"
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

pdf.vCenter Boolean (default: false)

An option indicating whether to center the content vertically.

See also hCenter.

Example - center content vertically

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                vCenter: true
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>

rowHeight Number (default: 20)

The default row height in pixels.

rows Number (default: 200)

The number of rows in the document.

sheets Array

An array defining the document sheets and their content.

sheets.activeCell String

The active cell in the sheet, e.g. "A1".

sheets.name String

The name of the sheet.

sheets.columns Array

An array defining the columns in this sheet and their content.

sheets.columns.index Number

The zero-based index of the column. Required to ensure correct positioning.

sheets.columns.width Number

The width of the column in pixels. Defaults to columnWidth.

sheets.dataSource kendo.data.DataSource

The data source instance for this sheet.

See Bind to Data Source.

sheets.filter Object

Defines the filtering criteria for this sheet, if any.

sheets.filter.columns Array

An array defining the filter configuration of individual columns.

sheets.filter.columns.criteria Array

An array of filter criteria for custom filters.

sheets.filter.columns.criteria.operator String

The criterion operator type.

Supported types vary based on the inferred column data type (inferred):

  • Text
    • contains - the text contains the value
    • doesnotcontain - text does not contain the value
    • startswith - text starts with the value
    • endswith - text ends with the value
  • Date
    • eq - date is the same as the value
    • neq - date is not the same as the value
    • lt - date is before the value
    • gt - date is after the value
  • Number
    • eq - is equal to the value
    • neq - is not equal to the value
    • gte - is greater than or equal to the value
    • gt - is greater than the value
    • lte - is less than or equal to the value
    • lt - is less than the value

sheets.filter.columns.criteria.value String

The value for the criteria operator.

sheets.filter.columns.filter String

The filter to apply to this column.

The supported filters are:

  • value - filters based on unique values
  • custom - applies custom filtering criteria
  • top - filters the top or bottom records
  • dynamic - filters based on dynamic criteria

sheets.filter.columns.index Number

The index of the column relative to the filter range.

sheets.filter.columns.logic String

The logical operator to apply to filter criteria.

Possible values are and, or.

sheets.filter.columns.type String

The filter sub-type, if any.

Applicable types according to the main filter.

  • top
    • topNumber
    • topPercent
    • bottomNumber
    • bottomPercent
  • dynamic
    • aboveAverage
    • belowAverage
    • tomorrow
    • today
    • yesterday
    • nextWeek
    • thisWeek
    • lastWeek
    • nextMonth
    • thisMonth
    • lastMonth
    • nextQuarter
    • thisQuarter
    • lastQuarter
    • nextYear
    • thisYear
    • lastYear
    • yearToDate

sheets.filter.columns.value Number |String |Date

The filter value for filters that require a single value, e.g. "top".

sheets.filter.columns.values Array

The filter values for filters that support multiple values.

sheets.filter.ref String

The active range for the filter, e.g. "B1:D8".

sheets.frozenColumns Number

The number of frozen columns in this sheet.

sheets.frozenRows Number

The number of frozen rows in this sheet.

sheets.mergedCells Array

An array of merged cell ranges, e.g. "B1:D2".

sheets.rows Array

The row data for this sheet.

sheets.rows.cells Array

The cells for this row.

sheets.rows.cells.background String

The background color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.borderBottom Object

The style information for the bottom border of the cell.

sheets.rows.cells.borderBottom.color String

The bottom border color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.borderBottom.size String

The width of the border in pixels.

sheets.rows.cells.borderLeft Object

The style information for the left border of the cell.

sheets.rows.cells.borderLeft.color String

The left border color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.borderLeft.size String

The width of the border in pixels.

sheets.rows.cells.borderTop Object

The style information for the top border of the cell.

sheets.rows.cells.borderTop.color String

The top border color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.borderTop.size String

The width of the border in pixels.

sheets.rows.cells.borderRight Object

The style information for the right border of the cell.

sheets.rows.cells.borderRight.color String

The right border color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.borderRight.size String

The width of the border in pixels.

sheets.rows.cells.color String

The font color of the cell.

Many standard CSS formats are supported, but the canonical form is "#ccff00".

sheets.rows.cells.fontFamily String

The font family for the cell.

sheets.rows.cells.fontSize Number

The font size of the cell in pixels.

sheets.rows.cells.italic Boolean

Sets the cell font to italic, if set to true.

sheets.rows.cells.bold Boolean

Sets the cell font to bold, if set to true.

sheets.rows.cells.enable Boolean

Disables the cell, if set to false.

sheets.rows.cells.format String

The format of the cell text.

See Create or delete a custom number format on MS Office.

sheets.rows.cells.formula String

The cell formula without the leading equals sign, e.g. A1 * 10.

sheets.rows.cells.index Number

The zero-based index of the cell. Required to ensure correct positioning.

The hyperlink (URL) of the cell.

sheets.rows.cells.textAlign String

The text align setting for the cell content.

Available options are:

  • left
  • center
  • right
  • justify

sheets.rows.cells.underline Boolean

Sets the cell font to underline, if set to true.

sheets.rows.cells.value Number |String |Boolean |Date

The cell value.

sheets.rows.cells.validation Object

The validation rule applied to the cell.

Initialize Spreadsheet with validation data, using sheets.rows configuration option

    <div id="spreadsheet"></div>

    <script type="text/javascript" charset="utf-8">
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [
                {
                    name: "Populated" ,
                    rows: [
                        {
                            index: 2,
                            cells: [
                                { index: 3, background: "red", color: "green", value: "D3" },
                                { index: 10, color: "blue", value: "a value" }
                            ]
                        },
                        {
                            index: 5,
                            cells: [
                                {
                                    index: 0,
                                    color: "red",
                                    value: "A6",
                                    validation: {
                                        from: "1",
                                        to: "2",
                                        comparerType: "between",
                                        dataType: "number",
                                        messageTemplate: "Number should match the validation."
                                    }
                                }
                            ]
                        }
                    ]
                }
            ]
        });
    </script>

sheets.rows.cells.validation.type String

Defines the validation type. The acceptable options are reject or warning

sheets.rows.cells.validation.comparerType String

Defines the comparer type used to validate the cell value, e.g. "greaterThan", "between" and etc.

sheets.rows.cells.validation.dataType String

Defines the data type of the cell value.

sheets.rows.cells.validation.from String

Defines a formula or value used for the comparison process. Used as only if comparer type does not require second argument.

sheets.rows.cells.validation.showButton Boolean (default: false)

A boolean value indicating if a button for selecting list items (dataType set to list) should be displayed.

sheets.rows.cells.validation.to String

Defines a formula or value used for the comparison process. Will be used if comparer type requires second argument.

sheets.rows.cells.validation.allowNulls Boolean

Specifies whether to allow nulls.

sheets.rows.cells.validation.messageTemplate String

Defines the hint message that will be displayed if value is invalid.

The template is giving an access to the following variables: from{0}, to{1}, fromFormula{2}, toFormula{3}, dataType{4}, type{5} and comparerType{6}.

Example - use validation template variables

<div id="example">
      <div id="spreadsheet" style="width: 100%;"></div>
      <script>
        $(function() {
          $("#spreadsheet").kendoSpreadsheet({
            columns: 26,
            rows: 30,
            sheetsbar: false,
            excel: {
              // Required to enable Excel Export in some browsers
              proxyURL: "//demos.telerik.com/kendo-ui/service/export"
            },
            sheets: [
              {
                name: "Validation Template",

                rows: [
                  {
                    height: 25,
                    cells: [
                      {
                        value: "15",
                        validation: {
                          dataType: "number",
                          from: "B1",
                          to:"C1",
                          allowNulls: true,
                          comparerType:"between" ,
                          type: "reject",
                          titleTemplate: "Number validation error",
                          messageTemplate: "The number have to be between {0} and {1}"
                        }

                      },
                      {
                        value: "10",

                      },
                      {
                        value: "20",

                      },

                    ]
                  },
                ],
                columns: [
                  {
                    width: 100
                  },
                  {
                    width: 215
                  },
                  {
                    width: 115
                  },
                  {
                    width: 115
                  },
                  {
                    width: 115
                  }
                ]
              },
              {
                name: "ListValues",
                rows: [ //A1:B1
                  {
                    cells: [
                      {
                        value: true
                      },
                      {
                        value: false
                      }
                    ]
                  }
                ]
              }
            ]
          });
        });
      </script>
    </div>

sheets.rows.cells.validation.titleTemplate String

Defines the hint title that will be displayed if value is invalid.

sheets.rows.cells.verticalAlign String

The vertical align setting for the cell content.

Available options are:

  • top
  • center
  • bottom

sheets.rows.cells.wrap Boolean

Will wrap the cell content if set to true.

sheets.rows.height Number

The row height in pixels. Defaults to rowHeight.

sheets.rows.index Number

The absolute row index. Required to ensure correct positioning.

sheets.rows.type String

The table row element role, in the context of the Grid table structure.

sheets.selection String

The selected range in the sheet, e.g. "A1:B10".

sheets.showGridLines Boolean (default: true)

A boolean value indicating if the sheet grid lines should be displayed.

sheets.sort Object

Defines the sort criteria for the sheet.

sheets.sort.columns Array

Specifies the sort options for individual columns.

sheets.sort.columns.ascending Boolean

Indicates if the data in the cell should be sorted ascending (true) or descending or (false).

sheets.sort.columns.index Number

The index of the column within the sheet.

For example, column C will have index 2.

sheets.sort.ref String

The sorted range, e.g. "A1:D5".

sheetsbar Boolean (default: true)

A boolean value indicating if the sheetsbar should be displayed.

toolbar Boolean |Object (default: true)

A boolean value indicating if the toolbar should be displayed.

toolbar.home Boolean |Array (default: true)

A boolean value indicating if the "home" tab should be displayed or a collection of tools that will be shown in the "home" tab.

The available tools are:

  • open
  • exportAs
  • [cut, copy, paste]
  • [bold, italic, underline]
  • backgroundColor, textColor
  • borders
  • fontSize, fontFamily
  • alignment
  • textWrap
  • [formatDecreaseDecimal, formatIncreaseDecimal]
  • format
  • merge
  • freeze
  • filter

Those tools which are part of a tool group are defined as array. For example ["bold", "italic", "underline"]

Example - customize home tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                home: [ ["bold", "italic"], "format" ]
            }
        });
    </script>

Example - disable home tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                home: false
            }
        });
    </script>

Example - show a custom tool

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                home: [
                    // for all available options, see the toolbar items configuration
                    // http://docs.telerik.com/kendo-ui/api/javascript/ui/toolbar#configuration-items
                    {
                        type: "button",
                        text: "Custom",
                        spriteCssClass: "k-icon k-font-icon k-i-cog",
                        click: function() {
                            window.alert("custom tool");
                        }
                    }
                ]
            }
        });
    </script>

toolbar.insert Boolean |Array (default: true)

A boolean value indicating if the "insert" tab should be displayed or a collection of tools that will be shown in the "insert" tab.

The available tools are:

  • [ addColumnLeft, addColumnRight, addRowBelow, addRowAbove ]
  • [ deleteColumn, deleteRow ]

Those tools which are part of a tool group are defined as array. For example ["deleteColumn", "deleteRow"]

Example - customize insert tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                insert: [ ["deleteColumn", "deleteRow"] ]
            }
        });
    </script>

Example - disable insert tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                insert: false
            }
        });
    </script>

toolbar.data Boolean |Array (default: true)

A boolean value indicating if the "insert" tab should be displayed or a collection of tools that will be shown in the "insert" tab.

The available tools are:

  • sort
  • filter
  • validation

Example - customize data tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                data: ["validation"]
            }
        });
    </script>

Example - disable data tab

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            toolbar: {
                data: false
            }
        });
    </script>

Methods

activeSheet

Gets or sets the active sheet.

Parameters

sheet kendo.spreadsheet.Sheet optional

The sheet to set as active.

Returns

kendo.spreadsheet.Sheet the active sheet.

Example - change the active Sheet

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{ name: "Sheet1" }, { name: "Sheet2" }]
        });
        var sheets = $("#spreadsheet").data("kendoSpreadsheet").sheets();
        $("#spreadsheet").data("kendoSpreadsheet").activeSheet(sheets[1]);
    </script>

cellContextMenu

Gets the cell contextMenu instance.

Returns

kendo.ui.ContextMenu The menu instance.

Example - dynamically adding a context menu item and associating a selection command

    <div id="spreadsheet"></div>

    <script>      
        $(function() {            
            var spreadsheet = $("#spreadsheet").kendoSpreadsheet().data("kendoSpreadsheet"),
                cellContextMenu = spreadsheet.cellContextMenu(); 

            cellContextMenu.append([{ text: "Highlight", cssClass: "highlight" }]);    

            cellContextMenu.bind("select", function(e) {
               var command = $(e.item).text();

              if(command == "Highlight") {
                var sheet = spreadsheet.activeSheet(),
                    selection = sheet.selection();   

                selection.background("green");
              }
           });
        });
    </script>

rowHeaderContextMenu

Gets the row header contextMenu instance.

Returns

kendo.ui.ContextMenu The menu instance.

colHeaderContextMenu

Gets the column header contextMenu instance.

Returns

kendo.ui.ContextMenu The menu instance.

sheets

Returns an array with the sheets in the workbook.

Returns

Array the available sheets.

fromFile

Clears the spreadsheets and populates it with data from the specified Excel (.xlsx) file.

Requires Internet Explorer 10 or a recent version of other browsers. The JSZip library is a prerequisite for the import from file functionality.

Parameters

blob Blob |File

The file or blob, usually obtained through a file input.

Returns

Promise a promise that will be resolved when the import operation completes.

Example - import file

    <input id="file" type="file" />
    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet();

        $("#file").on("change", function() {
            var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
            spreadsheet.fromFile(this.files[0]);
        });
    </script>

saveAsExcel

Initiates the Excel export. Also fires the excelExport event.

Calling this method could trigger the browser built-in popup blocker in some cases. To avoid that, always call it as a response to an end-user action e.g. button click.

Example - manually initiate Excel export

    <button id="export">Export to Excel</button>
    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }],
            }]
        });
        $("#export").click(function(e) {
            var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
            spreadsheet.saveAsExcel();
        });
    </script>

    <!-- Load JSZIP library to enable Excel export -->
    <script src="http://kendo.cdn.telerik.com/2017.2.621/js/jszip.min.js"></script>

saveAsPDF

Initiates the PDF export. Also fires the pdfExport event.

Calling this method could trigger the browser built-in popup blocker in some cases. To avoid that, always call it as a response to an end-user action e.g. button click.

Parameters

options Object

An options object with the same structure as the pdf options.

Returns

Promise A promise that will be resolved when the export completes. The same promise is available in the pdfExport event arguments.

Example - manually initiate PDF export

    <button id="export">Export to PDF</button>
    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdf: {
                fileName: "Test.pdf"
            }
        });
        $("#export").click(function(e) {
            var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
            spreadsheet.saveAsPDF({ area: "selection" });
        });
    </script>

    <!-- Load Pako library to enable PDF compression -->
    <script src="http://kendo.cdn.telerik.com/2017.2.621/js/pako_deflate.min.js"></script>

sheetByName

Returns a sheet matching the specified name, if any.

Parameters

name String

The name of the sheet to locate.

Returns

kendo.spreadsheet.Sheet the sheet that match the name.

sheetIndex

Returns the index of the specified sheet.

Parameters

sheet kendo.spreadsheet.Sheet

The sheet to determine the index of.

Returns

Number the sheet index.

sheetByIndex

Locates a sheet by its index in the workbook.

Parameters

index Number

The index of the sheet to locate.

Returns

kendo.spreadsheet.Sheet the sheet that match the index.

insertSheet

Inserts a sheet with the specified options.

Parameters

options Object

The configuration options for the sheet.

options.rows Number

The number of rows in this sheet.

options.columns Number

The number of columns in this sheet.

options.rowHeight Number

The row height in this sheet in pixels.

options.columnWidth Number

The column width in this sheet in pixels.

options.headerHeight Number

The header row height in pixels.

options.headerWidth Number

The header column width in pixels.

options.dataSource kendo.data.DataSource

The data source for this sheet.

options.data Object

The sheet state and data as Object. The schema follows the same structure as the widget configuration.

Returns

kendo.spreadsheet.Sheet the inserted sheet.

moveSheetToIndex

Moves the sheet to the specified index.

Parameters

sheet kendo.spreadsheet.Sheet

The sheet instance to move.

index Number

The new zero-based index of the sheet.

removeSheet

Removes the specified sheet.

Parameters

sheet kendo.spreadsheet.Sheet

The sheet instance to remove.

renameSheet

Renames the specified sheet.

Parameters

sheet kendo.spreadsheet.Sheet

The sheet instance to rename.

newSheetName String

The new name of the sheet.

Returns

kendo.spreadsheet.Sheet the renamed sheet.

toJSON

Serializes the workbook in the format defined in the configuration.

Example - Store spreadsheet to JSON

<div id="spreadsheet"></div>
<pre id="result"></pre>
<script>
    $("#spreadsheet").kendoSpreadsheet({
        sheets: [{
            name: "Food Order",
            mergedCells: [
                "A1:G1"
            ],
            rows: [{
                height: 70,
                cells: [{
                    value: "My Company", fontSize: 32, textAlign: "center"
                }]
            }]
        }]
    });

    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    var data = spreadsheet.toJSON();
    var json = JSON.stringify(data, null, 2);

    $("#spreadsheet").remove();
    $("#result").text(json);
</script>

Returns

Object the serialized workbook.

fromJSON

Loads the workbook data from an object with the format defined in the configuration.

All existing sheets and their data will be lost.

Parameters

data Object

The object to load data from. This should be the deserialized object, not the JSON string.

Example - Load spreadsheet from JSON

<div id="spreadsheet"></div>
<script>
    $("#spreadsheet").kendoSpreadsheet();

    var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    spreadsheet.fromJSON({
        sheets: [{
            name: "Food Order",
            mergedCells: [
                "A1:G1"
            ],
            rows: [{
                height: 70,
                cells: [{
                    value: "My Company", fontSize: 32, textAlign: "center"
                }]
            }]
        }]
    });
</script>

defineName

Define a custom name to be available and used in formulas. If the function is not able to parse the name of the value, it will throw an error.

Parameters

name String

A new name to be defined. The names are case-insensitive.

It is possible to provide a name that already exists. In such cases, the value is silently updated.

To make the name available only in one sheet, qualify it in the way demonstrated in the next example.

value String

The value has to be a valid formula in the form of a string, that is, without a leading = sign.

Generally, a name points to a reference. For maximum compatibility, it is recommended for you to use references here that are fully qualified (include the name of the sheet they refer to) and absolute (prefix both row and column with the $ sign).

hidden Boolean (default: false)

To hide this name from the custom names dropdown in the toolbar, pass true. Even if hidden is set to false, only reference names will be displayed in the dropdown.

Example - define a few names

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet();
        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");

        // cell reference
        spreadsheet.defineName("MyCell", "Sheet1!$A$1");

        // range reference
        spreadsheet.defineName("MyRange", "Sheet1!$A$1:$C$3");

        // qualified name
        spreadsheet.defineName("Sheet1!Foo", "Sheet1!$B$2");

        // relative reference (incompatible with other programs).
        // relative refs in A1 notation are ambiguous, unless we know
        // the cell where they are used, so we use the RC notation here:
        spreadsheet.defineName("CellsAbove", "R1C[0]:R[-1]C[0]");

        // arbitrary formula
        spreadsheet.defineName("GoldenRatio", "(1+SQRT(5))/2");
    </script>

After that, it is possible to use any of those names in formulas. For example, a formula like =SUM(CellsAbove) will return the sum of the cells above it, no matter where it sits. Relative references, such as the CellsAbove example, are not compatible with other spreadsheets, such as Excel, LibreOffice, or Google Sheets.

The "qualified" Sheet1!Foo name is visible without qualification only in formulas in Sheet1 and, for example, you can type =Foo * Foo. If you need the name in formulas from other sheets, you have to refer to it as =Sheet1!Foo.

undefineName

Delete a name.

Parameters

name String - the name to remove

To delete a fully qualified name, prefix the name of the sheet - for example, spreadsheet.undefineName("Sheet1!Foo").

Events

insertSheet

Triggered when sheet is inserted. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.preventDefault Function

If invoked the spreadsheet will not insert the sheet.

removeSheet

Triggered when sheet will be removed. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be removed.

e.preventDefault Function

If invoked the spreadsheet will not remove the sheet.

renameSheet

Triggered when sheet will be renamed. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be renamed.

e.newSheetName String

The new sheet name.

e.preventDefault Function

If invoked the spreadsheet will not rename the sheet.

selectSheet

Triggered when sheet will be activated. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be activated.

e.preventDefault Function

If invoked the spreadsheet will not activate the sheet.

unhideColumn

Triggered when column will be shown. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the column.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

unhideRow

Triggered when row will be shown. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the row.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

hideColumn

Triggered when column will be hidden. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the column.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

hideRow

Triggered when row will be hidden. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the row.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

deleteColumn

Triggered when column will be deleted. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the column.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

deleteRow

Triggered when row will be deleted. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the row.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

insertColumn

Triggered when column will be inserted. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the column.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

insertRow

Triggered when row will be inserted. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.sheet kendo.spreadsheet.Sheet

The sheet instance which will be affected.

e.index Number

The index of the row.

e.preventDefault Function

If invoked the spreadsheet will execute the change.

select

Triggered when spreadsheet selection is changed. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.range kendo.spreadsheet.Range

The Range that is selected.

changeFormat

Triggered when range format is changed from the UI. Introduced in 2017 Q1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.range kendo.spreadsheet.Range

The Range which format is changed.

change

Triggered when a value in the spreadsheet has been changed. Introduced in 2016.Q1.SP1.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.range kendo.spreadsheet.Range

The Range that has triggered the change.

render

Triggered after the widget has completed rendering.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

excelExport

Fired when the user clicks the "Export to Excel" toolbar button.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.data Array

The array of data items used to create the Excel workbook.

e.workbook kendo.ooxml.Workbook

The Excel workbook configuration object. Used to initialize a kendo.ooxml.Workbook class. Modifications of the workbook will reflect in the output Excel document.

e.preventDefault Function

If invoked the spreadsheet will not save the generated file.

Example - subscribe to the "excelExport" event during initialization

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }],
            }],
            excelExport: function(e) {
            e.workbook.fileName = "Spreadsheet1.xlsx";
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsExcel();
    </script>

Example - subscribe to the "excelExport" event after initialization

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                name: "Food Order",
                mergedCells: [
                    "A1:G1"
                ],
                rows: [{
                    height: 70,
                    cells: [{
                        value: "My Company", fontSize: 32, textAlign: "center"
                    }]
                }],
            }]
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.bind("excelExport", function(e) {
            e.workbook.fileName = "Spreadsheet1.xlsx";
        });

        spreadsheet.saveAsExcel();
    </script>

excelImport

Fired when the user clicks the "Open" toolbar button.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.file Blob |File

The file that is being imported.

e.preventDefault Function

If invoked the spreadsheet will not import the file.

e.progress Promise

A promise that will be resolved when the import operation completes.

The promise progress handler will be called periodically with the following arguments:

  • sheet - The current sheet. An instance of kendo.spreadsheet.Sheet
  • progress - Number if the range 0 to 1, indicating the progress of the current import operation

Example - monitor the progress of an import operation

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            excelImport: function(e) {
                e.promise
                .progress(function(e) {
                    console.log(kendo.format("{0:P} complete", e.progress));
                })
                .done(function() {
                    alert("Export completed!");
                });
            }
        });

        // Click the Open command and select a file to import
    </script>

pdfExport

Fired when the user initiates PDF export.

Event Data

e.sender kendo.ui.Spreadsheet

The widget instance which fired the event.

e.preventDefault Function

If invoked the spreadsheet will not save the generated file.

e.promise Promise

A promise that will be resolved when the export completes.

Example - Monitor export progress

    <div id="spreadsheet"></div>
    <script>
        $("#spreadsheet").kendoSpreadsheet({
            sheets: [{
                rows: [{
                    cells: [{ value: "A" }, { value: "B" }, { value: "C" }]
                }, {
                    cells: [{ value: "1" }, { value: "2" }, { value: "3" }]
                }, {
                    cells: [{ value: "4" }, { value: "5" }, { value: "6" }]
                }]
            }],
            pdfExport: function(e) {
                e.promise.done(function() {
                    alert("Export completed!");
                });
            }
        });

        var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
        spreadsheet.saveAsPDF();
    </script>
Is this article helpful? Yes / No
Thank you for your feedback!

Give article feedback

Tell us how we can improve this article

close
Dummy