Edytuj

Udostępnij za pośrednictwem

What's new in Excel JavaScript API 1.7

  • Artykuł

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.

See also