What's new in Excel JavaScript API 1.7
The Excel JavaScript API requirement set 1.7 features include APIs for charts, events, worksheets, ranges, document properties, named items, protection options and styles.
Customize charts
With the new chart APIs, you can create additional chart types, add a data series to a chart, set the chart title, add an axis title, add display unit, add a trendline with moving average, change a trendline to linear, and more. The following are some examples.
- Chart axis - get, set, format and remove axis unit, label and title in a chart.
- Chart series - add, set, and delete a series in a chart. Change series markers, plot orders and sizing.
- Chart trendlines - add, get, and format trendlines in a chart.
- Chart legend - format the legend font in a chart.
- Chart point - set chart point color.
- Chart title substring - get and set title substring for a chart.
- Chart type - option to create more chart types.
Events
Excel events APIs provide a variety of event handlers that allow your add-in to automatically run a designated function when a specific event occurs. You can design that function to perform whatever actions your scenario requires. For a list of events that are currently available, see Work with Events using the Excel JavaScript API.
Customize the appearance of worksheets and ranges
Using the new APIs, you can customize the appearance of worksheets in multiple ways:
- Freeze panes to keep specific rows or columns visible when you scroll in the worksheet. For example, if the first row in your worksheet contains headers, you might freeze that row so that the column headers will remain visible as you scroll down the worksheet.
- Modify the worksheet tab color.
- Add worksheet headings.
You can customize the appearance of ranges in multiple ways:
- Set the cell style for a range to ensure sure that all cells in the range have consistent formatting. A cell style is a defined set of formatting characteristics, such as fonts and font sizes, number formats, cell borders, and cell shading. Use any of Excel's built-in cell styles or create your own custom cell style.
- Set the text orientation for a range.
- Add or modify a hyperlink on a range that links to another location in the workbook or to an external location.
Manage document properties
Using the document properties APIs, you can access built-in document properties and also create and manage custom document properties to store state of the workbook and drive workflow and business logic.
Copy worksheets
Using the worksheet copy APIs, you can copy the data and format from one worksheet to a new worksheet within the same workbook and reduce the amount of data transfer needed.
Handle ranges with ease
Using the various range APIs, you can do things such as get the surrounding region, get a resized range, and more. These APIs should make tasks like range manipulation and addressing much more efficient.
In addition:
- Workbook and worksheet protection options - use these APIs to protect data in a worksheet and the workbook structure.
- Update a named item - use this API to update a named item.
- Get active cell - use this API to get the active cell of a workbook.
API list
The following table lists the APIs in Excel JavaScript API requirement set 1.7. To view API reference documentation for all APIs supported by Excel JavaScript API requirement set 1.7 or earlier, see Excel APIs in requirement set 1.7 or earlier.
| Class | Fields | Description |
|---|---|---|
| Chart | chartType | Specifies the type of the chart. |
| id | The unique ID of chart. | |
| showAllFieldButtons | Specifies whether to display all field buttons on a PivotChart. | |
| ChartAreaFormat | border | Represents the border format of chart area, which includes color, linestyle, and weight. |
| ChartAxes | getItem(type: Excel.ChartAxisType, group?: Excel.ChartAxisGroup) | Returns the specific axis identified by type and group. |
| ChartAxis | axisGroup | Specifies the group for the specified axis. |
| baseTimeUnit | Specifies the base unit for the specified category axis. | |
| categoryType | Specifies the category axis type. | |
| customDisplayUnit | Specifies the custom axis display unit value. | |
| displayUnit | Represents the axis display unit. | |
| height | Specifies the height, in points, of the chart axis. | |
| left | Specifies the distance, in points, from the left edge of the axis to the left of chart area. | |
| logBase | Specifies the base of the logarithm when using logarithmic scales. | |
| majorTickMark | Specifies the type of major tick mark for the specified axis. | |
| majorTimeUnitScale | Specifies the major unit scale value for the category axis when the categoryType property is set to dateAxis. |
|
| minorTickMark | Specifies the type of minor tick mark for the specified axis. | |
| minorTimeUnitScale | Specifies the minor unit scale value for the category axis when the categoryType property is set to dateAxis. |
|
| reversePlotOrder | Specifies if Excel plots data points from last to first. | |
| scaleType | Specifies the value axis scale type. | |
| setCategoryNames(sourceData: Range) | Sets all the category names for the specified axis. | |
| setCustomDisplayUnit(value: number) | Sets the axis display unit to a custom value. | |
| showDisplayUnitLabel | Specifies if the axis display unit label is visible. | |
| tickLabelPosition | Specifies the position of tick-mark labels on the specified axis. | |
| tickLabelSpacing | Specifies the number of categories or series between tick-mark labels. | |
| tickMarkSpacing | Specifies the number of categories or series between tick marks. | |
| top | Specifies the distance, in points, from the top edge of the axis to the top of chart area. | |
| type | Specifies the axis type. | |
| visible | Specifies if the axis is visible. | |
| width | Specifies the width, in points, of the chart axis. | |
| ChartBorder | color | HTML color code representing the color of borders in the chart. |
| lineStyle | Represents the line style of the border. | |
| weight | Represents weight of the border, in points. | |
| ChartDataLabel | position | Value that represents the position of the data label. |
| separator | String representing the separator used for the data label on a chart. | |
| showBubbleSize | Specifies if the data label bubble size is visible. | |
| showCategoryName | Specifies if the data label category name is visible. | |
| showLegendKey | Specifies if the data label legend key is visible. | |
| showPercentage | Specifies if the data label percentage is visible. | |
| showSeriesName | Specifies if the data label series name is visible. | |
| showValue | Specifies if the data label value is visible. | |
| ChartFormatString | font | Represents the font attributes, such as font name, font size, and color of a chart characters object. |
| ChartLegend | height | Specifies the height, in points, of the legend on the chart. |
| left | Specifies the left value, in points, of the legend on the chart. | |
| legendEntries | Represents a collection of legendEntries in the legend. | |
| showShadow | Specifies if the legend has a shadow on the chart. | |
| top | Specifies the top of a chart legend. | |
| width | Specifies the width, in points, of the legend on the chart. | |
| ChartLegendEntry | visible | Represents the visibility of a chart legend entry. |
| ChartLegendEntryCollection | getCount() | Returns the number of legend entries in the collection. |
| getItemAt(index: number) | Returns a legend entry at the given index. | |
| items | Gets the loaded child items in this collection. | |
| ChartLineFormat | lineStyle | Represents the line style. |
| weight | Represents weight of the line, in points. | |
| ChartPoint | dataLabel | Returns the data label of a chart point. |
| hasDataLabel | Represents whether a data point has a data label. | |
| markerBackgroundColor | HTML color code representation of the marker background color of a data point (e.g., #FF0000 represents Red). | |
| markerForegroundColor | HTML color code representation of the marker foreground color of a data point (e.g., #FF0000 represents Red). | |
| markerSize | Represents marker size of a data point. | |
| markerStyle | Represents marker style of a chart data point. | |
| ChartPointFormat | border | Represents the border format of a chart data point, which includes color, style, and weight information. |
| ChartSeries | chartType | Represents the chart type of a series. |
| delete() | Deletes the chart series. | |
| doughnutHoleSize | Represents the doughnut hole size of a chart series. | |
| filtered | Specifies if the series is filtered. | |
| gapWidth | Represents the gap width of a chart series. | |
| hasDataLabels | Specifies if the series has data labels. | |
| markerBackgroundColor | Specifies the marker background color of a chart series. | |
| markerForegroundColor | Specifies the marker foreground color of a chart series. | |
| markerSize | Specifies the marker size of a chart series. | |
| markerStyle | Specifies the marker style of a chart series. | |
| plotOrder | Specifies the plot order of a chart series within the chart group. | |
| setBubbleSizes(sourceData: Range) | Sets the bubble sizes for a chart series. | |
| setValues(sourceData: Range) | Sets the values for a chart series. | |
| setXAxisValues(sourceData: Range) | Sets the values of the x-axis for a chart series. | |
| showShadow | Specifies if the series has a shadow. | |
| smooth | Specifies if the series is smooth. | |
| trendlines | The collection of trendlines in the series. | |
| ChartSeriesCollection | add(name?: string, index?: number) | Add a new series to the collection. |
| ChartTitle | getSubstring(start: number, length: number) | Get the substring of a chart title. |
| height | Returns the height, in points, of the chart title. | |
| horizontalAlignment | Specifies the horizontal alignment for chart title. | |
| left | Specifies the distance, in points, from the left edge of chart title to the left edge of chart area. | |
| position | Represents the position of chart title. | |
| setFormula(formula: string) | Sets a string value that represents the formula of chart title using A1-style notation. | |
| showShadow | Represents a boolean value that determines if the chart title has a shadow. | |
| textOrientation | Specifies the angle to which the text is oriented for the chart title. | |
| top | Specifies the distance, in points, from the top edge of chart title to the top of chart area. | |
| verticalAlignment | Specifies the vertical alignment of chart title. | |
| width | Specifies the width, in points, of the chart title. | |
| ChartTitleFormat | border | Represents the border format of chart title, which includes color, linestyle, and weight. |
| ChartTrendline | delete() | Delete the trendline object. |
| format | Represents the formatting of a chart trendline. | |
| intercept | Represents the intercept value of the trendline. | |
| movingAveragePeriod | Represents the period of a chart trendline. | |
| name | Represents the name of the trendline. | |
| polynomialOrder | Represents the order of a chart trendline. | |
| type | Represents the type of a chart trendline. | |
| ChartTrendlineCollection | add(type?: Excel.ChartTrendlineType) | Adds a new trendline to trendline collection. |
| getCount() | Returns the number of trendlines in the collection. | |
| getItem(index: number) | Gets a trendline object by index, which is the insertion order in the items array. | |
| items | Gets the loaded child items in this collection. | |
| ChartTrendlineFormat | line | Represents chart line formatting. |
| CustomProperty | delete() | Deletes the custom property. |
| key | The key of the custom property. | |
| type | The type of the value used for the custom property. | |
| value | The value of the custom property. | |
| CustomPropertyCollection | add(key: string, value: any) | Creates a new or sets an existing custom property. |
| deleteAll() | Deletes all custom properties in this collection. | |
| getCount() | Gets the count of custom properties. | |
| getItem(key: string) | Gets a custom property object by its key, which is case-insensitive. | |
| getItemOrNullObject(key: string) | Gets a custom property object by its key, which is case-insensitive. | |
| items | Gets the loaded child items in this collection. | |
| DataConnectionCollection | refreshAll() | Refreshes data connections in the collection, such as from a PivotTable to a Power BI dataset, or a Data Model to a table or range in the same workbook. |
| DocumentProperties | author | The author of the workbook. |
| category | The category of the workbook. | |
| comments | The Comments field in the metadata of the workbook. | |
| company | The company of the workbook. | |
| creationDate | Gets the creation date of the workbook. | |
| custom | Gets the collection of custom properties of the workbook. | |
| keywords | The keywords of the workbook. | |
| lastAuthor | Gets the last author of the workbook. | |
| manager | The manager of the workbook. | |
| revisionNumber | Gets the revision number of the workbook. | |
| subject | The subject of the workbook. | |
| title | The title of the workbook. | |
| NamedItem | arrayValues | Returns an object containing values and types of the named item. |
| formula | The formula of the named item. | |
| NamedItemArrayValues | types | Represents the types for each item in the named item array |
| values | Represents the values of each item in the named item array. | |
| Range | getAbsoluteResizedRange(numRows: number, numColumns: number) | Gets a Range object with the same top-left cell as the current Range object, but with the specified numbers of rows and columns. |
| getImage() | Renders the range as a Base64-encoded PNG image. | |
| getSurroundingRegion() | Returns a Range object that represents the surrounding region for the top-left cell in this range. |
|
| hyperlink | Represents the hyperlink for the current range. | |
| isEntireColumn | Represents if the current range is an entire column. | |
| isEntireRow | Represents if the current range is an entire row. | |
| numberFormatLocal | Represents Excel's number format code for the given range, based on the language settings of the user. | |
| showCard() | Displays the card for an active cell if it has rich value content. | |
| style | Represents the style of the current range. | |
| RangeFormat | textOrientation | The text orientation of all the cells within the range. |
| useStandardHeight | Determines if the row height of the Range object equals the standard height of the sheet. |
|
| useStandardWidth | Specifies if the column width of the Range object equals the standard width of the sheet. |
|
| RangeHyperlink | address | Represents the URL target for the hyperlink. |
| documentReference | Represents the document reference target for the hyperlink. | |
| screenTip | Represents the string displayed when hovering over the hyperlink. | |
| textToDisplay | Represents the string that is displayed in the top left most cell in the range. | |
| Style | borders | A collection of four border objects that represent the style of the four borders. |
| builtIn | Specifies if the style is a built-in style. | |
| delete() | Deletes this style. | |
| fill | The fill of the style. | |
| font | A Font object that represents the font of the style. |
|
| formulaHidden | Specifies if the formula will be hidden when the worksheet is protected. | |
| horizontalAlignment | Represents the horizontal alignment for the style. | |
| includeAlignment | Specifies if the style includes the auto indent, horizontal alignment, vertical alignment, wrap text, indent level, and text orientation properties. | |
| includeBorder | Specifies if the style includes the color, color index, line style, and weight border properties. | |
| includeFont | Specifies if the style includes the background, bold, color, color index, font style, italic, name, size, strikethrough, subscript, superscript, and underline font properties. | |
| includeNumber | Specifies if the style includes the number format property. | |
| includePatterns | Specifies if the style includes the color, color index, invert if negative, pattern, pattern color, and pattern color index interior properties. | |
| includeProtection | Specifies if the style includes the formula hidden and locked protection properties. | |
| indentLevel | An integer from 0 to 250 that indicates the indent level for the style. | |
| locked | Specifies if the object is locked when the worksheet is protected. | |
| name | The name of the style. | |
| numberFormat | The format code of the number format for the style. | |
| numberFormatLocal | The localized format code of the number format for the style. | |
| readingOrder | The reading order for the style. | |
| shrinkToFit | Specifies if text automatically shrinks to fit in the available column width. | |
| verticalAlignment | Specifies the vertical alignment for the style. | |
| wrapText | Specifies if Excel wraps the text in the object. | |
| StyleCollection | add(name: string) | Adds a new style to the collection. |
| getItem(name: string) | Gets a Style by name. |
|
| items | Gets the loaded child items in this collection. | |
| Table | onChanged | Occurs when data in cells changes on a specific table. |
| onSelectionChanged | Occurs when the selection changes on a specific table. | |
| TableChangedEventArgs | address | Gets the address that represents the changed area of a table on a specific worksheet. |
| changeType | Gets the change type that represents how the changed event is triggered. | |
| source | Gets the source of the event. | |
| tableId | Gets the ID of the table in which the data changed. | |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet in which the data changed. | |
| TableCollection | onChanged | Occurs when data changes on any table in a workbook, or a worksheet. |
| TableSelectionChangedEventArgs | address | Gets the range address that represents the selected area of the table on a specific worksheet. |
| isInsideTable | Specifies if the selection is inside a table. | |
| tableId | Gets the ID of the table in which the selection changed. | |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet in which the selection changed. | |
| Workbook | dataConnections | Represents all data connections in the workbook. |
| getActiveCell() | Gets the currently active cell from the workbook. | |
| name | Gets the workbook name. | |
| properties | Gets the workbook properties. | |
| protection | Returns the protection object for a workbook. | |
| styles | Represents a collection of styles associated with the workbook. | |
| WorkbookProtection | protect(password?: string) | Protects the workbook. |
| protected | Specifies if the workbook is protected. | |
| unprotect(password?: string) | Unprotects the workbook. | |
| Worksheet | copy(positionType?: Excel.WorksheetPositionType, relativeTo?: Excel.Worksheet) | Copies a worksheet and places it at the specified position. |
| freezePanes | Gets an object that can be used to manipulate frozen panes on the worksheet. | |
| getRangeByIndexes(startRow: number, startColumn: number, rowCount: number, columnCount: number) | Gets the Range object beginning at a particular row index and column index, and spanning a certain number of rows and columns. |
|
| onActivated | Occurs when the worksheet is activated. | |
| onChanged | Occurs when data changes in a specific worksheet. | |
| onDeactivated | Occurs when the worksheet is deactivated. | |
| onSelectionChanged | Occurs when the selection changes on a specific worksheet. | |
| standardHeight | Returns the standard (default) height of all the rows in the worksheet, in points. | |
| standardWidth | Specifies the standard (default) width of all the columns in the worksheet. | |
| tabColor | The tab color of the worksheet. | |
| WorksheetActivatedEventArgs | type | Gets the type of the event. |
| worksheetId | Gets the ID of the worksheet that is activated. | |
| WorksheetAddedEventArgs | source | Gets the source of the event. |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet that is added to the workbook. | |
| WorksheetChangedEventArgs | address | Gets the range address that represents the changed area of a specific worksheet. |
| changeType | Gets the change type that represents how the changed event is triggered. | |
| source | Gets the source of the event. | |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet in which the data changed. | |
| WorksheetCollection | onActivated | Occurs when any worksheet in the workbook is activated. |
| onAdded | Occurs when a new worksheet is added to the workbook. | |
| onDeactivated | Occurs when any worksheet in the workbook is deactivated. | |
| onDeleted | Occurs when a worksheet is deleted from the workbook. | |
| WorksheetDeactivatedEventArgs | type | Gets the type of the event. |
| worksheetId | Gets the ID of the worksheet that is deactivated. | |
| WorksheetDeletedEventArgs | source | Gets the source of the event. |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet that is deleted from the workbook. | |
| WorksheetFreezePanes | freezeAt(frozenRange: Range | string) | Sets the frozen cells in the active worksheet view. |
| freezeColumns(count?: number) | Freeze the first column or columns of the worksheet in place. | |
| freezeRows(count?: number) | Freeze the top row or rows of the worksheet in place. | |
| getLocation() | Gets a range that describes the frozen cells in the active worksheet view. | |
| getLocationOrNullObject() | Gets a range that describes the frozen cells in the active worksheet view. | |
| unfreeze() | Removes all frozen panes in the worksheet. | |
| WorksheetProtection | unprotect(password?: string) | Unprotects a worksheet. |
| WorksheetProtectionOptions | allowEditObjects | Represents the worksheet protection option allowing editing of objects. |
| allowEditScenarios | Represents the worksheet protection option allowing editing of scenarios. | |
| selectionMode | Represents the worksheet protection option of selection mode. | |
| WorksheetSelectionChangedEventArgs | address | Gets the range address that represents the selected area of a specific worksheet. |
| type | Gets the type of the event. | |
| worksheetId | Gets the ID of the worksheet in which the selection changed. |