Edytuj

Udostępnij za pośrednictwem

What's new in Excel JavaScript API 1.8

  • Artykuł

The Excel JavaScript API requirement set 1.8 features include APIs for PivotTables, data validation, charts, events for charts, performance options, and workbook creation.

PivotTable

Wave 2 of the PivotTable APIs lets add-ins set the hierarchies of a PivotTable. You can now control the data and how it is aggregated. Our PivotTable article has more on the new PivotTable functionality.

Data Validation

Data validation gives you control of what a user enters in a worksheet. You can limit cells to pre-defined answer sets or give pop-up warnings about undesirable input. Learn more about adding data validation to ranges today.

Charts

Another round of Chart APIs brings even greater programmatic control over chart elements. You now have greater access to the legend, axes, trendline, and plot area.

Events

More events have been added for charts. Have your add-in react to users interacting with the chart. You can also toggle events firing across the entire workbook.

API list

The following table lists the APIs in Excel JavaScript API requirement set 1.8. To view API reference documentation for all APIs supported by Excel JavaScript API requirement set 1.8 or earlier, see Excel APIs in requirement set 1.8 or earlier.

Class Fields Description
BasicDataValidation formula1 Specifies the right-hand operand when the operator property is set to a binary operator such as GreaterThan (the left-hand operand is the value the user tries to enter in the cell).
formula2 With the ternary operators Between and NotBetween, specifies the upper bound operand.
operator The operator to use for validating the data.
Chart categoryLabelLevel Specifies a chart category label level enumeration constant, referring to the level of the source category labels.
displayBlanksAs Specifies the way that blank cells are plotted on a chart.
onActivated Occurs when the chart is activated.
onDeactivated Occurs when the chart is deactivated.
plotArea Represents the plot area for the chart.
plotBy Specifies the way columns or rows are used as data series on the chart.
plotVisibleOnly True if only visible cells are plotted.
seriesNameLevel Specifies a chart series name level enumeration constant, referring to the level of the source series names.
showDataLabelsOverMaximum Specifies whether to show the data labels when the value is greater than the maximum value on the value axis.
style Specifies the chart style for the chart.
ChartActivatedEventArgs chartId Gets the ID of the chart that is activated.
type Gets the type of the event.
worksheetId Gets the ID of the worksheet in which the chart is activated.
ChartAddedEventArgs chartId Gets the ID of the chart that is added to the worksheet.
source Gets the source of the event.
type Gets the type of the event.
worksheetId Gets the ID of the worksheet in which the chart is added.
ChartAxis alignment Specifies the alignment for the specified axis tick label.
isBetweenCategories Specifies if the value axis crosses the category axis between categories.
multiLevel Specifies if an axis is multilevel.
numberFormat Specifies the format code for the axis tick label.
offset Specifies the distance between the levels of labels, and the distance between the first level and the axis line.
position Specifies the specified axis position where the other axis crosses.
positionAt Specifies the axis position where the other axis crosses.
setPositionAt(value: number) Sets the specified axis position where the other axis crosses.
textOrientation Specifies the angle to which the text is oriented for the chart axis tick label.
ChartAxisFormat fill Specifies chart fill formatting.
ChartAxisTitle setFormula(formula: string) A string value that represents the formula of chart axis title using A1-style notation.
ChartAxisTitleFormat border Specifies the chart axis title's border format, which includes color, linestyle, and weight.
fill Specifies the chart axis title's fill formatting.
ChartBorder clear() Clear the border format of a chart element.
ChartCollection onActivated Occurs when a chart is activated.
onAdded Occurs when a new chart is added to the worksheet.
onDeactivated Occurs when a chart is deactivated.
onDeleted Occurs when a chart is deleted.
ChartDataLabel autoText Specifies if the data label automatically generates appropriate text based on context.
format Represents the format of chart data label.
formula String value that represents the formula of chart data label using A1-style notation.
height Returns the height, in points, of the chart data label.
horizontalAlignment Represents the horizontal alignment for chart data label.
left Represents the distance, in points, from the left edge of chart data label to the left edge of chart area.
numberFormat String value that represents the format code for data label.
text String representing the text of the data label on a chart.
textOrientation Represents the angle to which the text is oriented for the chart data label.
top Represents the distance, in points, from the top edge of chart data label to the top of chart area.
verticalAlignment Represents the vertical alignment of chart data label.
width Returns the width, in points, of the chart data label.
ChartDataLabelFormat border Represents the border format, which includes color, linestyle, and weight.
ChartDataLabels autoText Specifies if data labels automatically generate appropriate text based on context.
horizontalAlignment Specifies the horizontal alignment for chart data label.
numberFormat Specifies the format code for data labels.
textOrientation Represents the angle to which the text is oriented for data labels.
verticalAlignment Represents the vertical alignment of chart data label.
ChartDeactivatedEventArgs chartId Gets the ID of the chart that is deactivated.
type Gets the type of the event.
worksheetId Gets the ID of the worksheet in which the chart is deactivated.
ChartDeletedEventArgs chartId Gets the ID of the chart that is deleted from the worksheet.
source Gets the source of the event.
type Gets the type of the event.
worksheetId Gets the ID of the worksheet in which the chart is deleted.
ChartLegendEntry height Specifies the height of the legend entry on the chart legend.
index Specifies the index of the legend entry in the chart legend.
left Specifies the left value of a chart legend entry.
top Specifies the top of a chart legend entry.
width Represents the width of the legend entry on the chart Legend.
ChartLegendFormat border Represents the border format, which includes color, linestyle, and weight.
ChartPlotArea format Specifies the formatting of a chart plot area.
height Specifies the height value of a plot area.
insideHeight Specifies the inside height value of a plot area.
insideLeft Specifies the inside left value of a plot area.
insideTop Specifies the inside top value of a plot area.
insideWidth Specifies the inside width value of a plot area.
left Specifies the left value of a plot area.
position Specifies the position of a plot area.
top Specifies the top value of a plot area.
width Specifies the width value of a plot area.
ChartPlotAreaFormat border Specifies the border attributes of a chart plot area.
fill Specifies the fill format of an object, which includes background formatting information.
ChartSeries axisGroup Specifies the group for the specified series.
dataLabels Represents a collection of all data labels in the series.
explosion Specifies the explosion value for a pie-chart or doughnut-chart slice.
firstSliceAngle Specifies the angle of the first pie-chart or doughnut-chart slice, in degrees (clockwise from vertical).
invertIfNegative True if Excel inverts the pattern in the item when it corresponds to a negative number.
overlap Specifies how bars and columns are positioned.
secondPlotSize Specifies the size of the secondary section of either a pie-of-pie chart or a bar-of-pie chart, as a percentage of the size of the primary pie.
splitType Specifies the way the two sections of either a pie-of-pie chart or a bar-of-pie chart are split.
varyByCategories True if Excel assigns a different color or pattern to each data marker.
ChartTrendline backwardPeriod Represents the number of periods that the trendline extends backward.
forwardPeriod Represents the number of periods that the trendline extends forward.
label Represents the label of a chart trendline.
showEquation True if the equation for the trendline is displayed on the chart.
showRSquared True if the r-squared value for the trendline is displayed on the chart.
ChartTrendlineLabel autoText Specifies if the trendline label automatically generates appropriate text based on context.
format The format of the chart trendline label.
formula String value that represents the formula of the chart trendline label using A1-style notation.
height Returns the height, in points, of the chart trendline label.
horizontalAlignment Represents the horizontal alignment of the chart trendline label.
left Represents the distance, in points, from the left edge of the chart trendline label to the left edge of the chart area.
numberFormat String value that represents the format code for the trendline label.
text String representing the text of the trendline label on a chart.
textOrientation Represents the angle to which the text is oriented for the chart trendline label.
top Represents the distance, in points, from the top edge of the chart trendline label to the top of the chart area.
verticalAlignment Represents the vertical alignment of the chart trendline label.
width Returns the width, in points, of the chart trendline label.
ChartTrendlineLabelFormat border Specifies the border format, which includes color, linestyle, and weight.
fill Specifies the fill format of the current chart trendline label.
font Specifies the font attributes (such as font name, font size, and color) for a chart trendline label.
CustomDataValidation formula A custom data validation formula.
DataPivotHierarchy field Returns the PivotFields associated with the DataPivotHierarchy.
id ID of the DataPivotHierarchy.
name Name of the DataPivotHierarchy.
numberFormat Number format of the DataPivotHierarchy.
position Position of the DataPivotHierarchy.
setToDefault() Reset the DataPivotHierarchy back to its default values.
showAs Specifies if the data should be shown as a specific summary calculation.
summarizeBy Specifies if all items of the DataPivotHierarchy are shown.
DataPivotHierarchyCollection add(pivotHierarchy: Excel.PivotHierarchy) Adds the PivotHierarchy to the current axis.
getCount() Gets the number of pivot hierarchies in the collection.
getItem(name: string) Gets a DataPivotHierarchy by its name or ID.
getItemOrNullObject(name: string) Gets a DataPivotHierarchy by name.
items Gets the loaded child items in this collection.
remove(DataPivotHierarchy: Excel.DataPivotHierarchy) Removes the PivotHierarchy from the current axis.
DataValidation clear() Clears the data validation from the current range.
errorAlert Error alert when user enters invalid data.
ignoreBlanks Specifies if data validation will be performed on blank cells.
prompt Prompt when users select a cell.
rule Data validation rule that contains different type of data validation criteria.
type Type of the data validation, see Excel.DataValidationType for details.
valid Represents if all cell values are valid according to the data validation rules.
DataValidationErrorAlert message Represents the error alert message.
showAlert Specifies whether to show an error alert dialog when a user enters invalid data.
style The data validation alert type, please see Excel.DataValidationAlertStyle for details.
title Represents the error alert dialog title.
DataValidationPrompt message Specifies the message of the prompt.
showPrompt Specifies if a prompt is shown when a user selects a cell with data validation.
title Specifies the title for the prompt.
DataValidationRule custom Custom data validation criteria.
date Date data validation criteria.
decimal Decimal data validation criteria.
list List data validation criteria.
textLength Text length data validation criteria.
time Time data validation criteria.
wholeNumber Whole number data validation criteria.
DateTimeDataValidation formula1 Specifies the right-hand operand when the operator property is set to a binary operator such as GreaterThan (the left-hand operand is the value the user tries to enter in the cell).
formula2 With the ternary operators Between and NotBetween, specifies the upper bound operand.
operator The operator to use for validating the data.
FilterPivotHierarchy enableMultipleFilterItems Determines whether to allow multiple filter items.
fields Returns the PivotFields associated with the FilterPivotHierarchy.
id ID of the FilterPivotHierarchy.
name Name of the FilterPivotHierarchy.
position Position of the FilterPivotHierarchy.
setToDefault() Reset the FilterPivotHierarchy back to its default values.
FilterPivotHierarchyCollection add(pivotHierarchy: Excel.PivotHierarchy) Adds the PivotHierarchy to the current axis.
getCount() Gets the number of pivot hierarchies in the collection.
getItem(name: string) Gets a FilterPivotHierarchy by its name or ID.
getItemOrNullObject(name: string) Gets a FilterPivotHierarchy by name.
items Gets the loaded child items in this collection.
remove(filterPivotHierarchy: Excel.FilterPivotHierarchy) Removes the PivotHierarchy from the current axis.
ListDataValidation inCellDropDown Specifies whether to display the list in a cell drop-down.
source Source of the list for data validation
PivotField id ID of the PivotField.
items Returns the PivotItems associated with the PivotField.
name Name of the PivotField.
showAllItems Determines whether to show all items of the PivotField.
sortByLabels(sortBy: SortBy) Sorts the PivotField.
subtotals Subtotals of the PivotField.
PivotFieldCollection getCount() Gets the number of pivot fields in the collection.
getItem(name: string) Gets a PivotField by its name or ID.
getItemOrNullObject(name: string) Gets a PivotField by name.
items Gets the loaded child items in this collection.
PivotHierarchy fields Returns the PivotFields associated with the PivotHierarchy.
id ID of the PivotHierarchy.
name Name of the PivotHierarchy.
PivotHierarchyCollection getCount() Gets the number of pivot hierarchies in the collection.
getItem(name: string) Gets a PivotHierarchy by its name or ID.
getItemOrNullObject(name: string) Gets a PivotHierarchy by name.
items Gets the loaded child items in this collection.
PivotItem id ID of the PivotItem.
isExpanded Determines whether the item is expanded to show child items or if it's collapsed and child items are hidden.
name Name of the PivotItem.
visible Specifies if the PivotItem is visible.
PivotItemCollection getCount() Gets the number of PivotItems in the collection.
getItem(name: string) Gets a PivotItem by its name or ID.
getItemOrNullObject(name: string) Gets a PivotItem by name.
items Gets the loaded child items in this collection.
PivotLayout getColumnLabelRange() Returns the range where the PivotTable's column labels reside.
getDataBodyRange() Returns the range where the PivotTable's data values reside.
getFilterAxisRange() Returns the range of the PivotTable's filter area.
getRange() Returns the range the PivotTable exists on, excluding the filter area.
getRowLabelRange() Returns the range where the PivotTable's row labels reside.
layoutType This property indicates the PivotLayoutType of all fields on the PivotTable.
showColumnGrandTotals Specifies if the PivotTable report shows grand totals for columns.
showRowGrandTotals Specifies if the PivotTable report shows grand totals for rows.
subtotalLocation This property indicates the SubtotalLocationType of all fields on the PivotTable.
PivotTable columnHierarchies The Column Pivot Hierarchies of the PivotTable.
dataHierarchies The Data Pivot Hierarchies of the PivotTable.
delete() Deletes the PivotTable.
filterHierarchies The Filter Pivot Hierarchies of the PivotTable.
hierarchies The Pivot Hierarchies of the PivotTable.
layout The PivotLayout describing the layout and visual structure of the PivotTable.
rowHierarchies The Row Pivot Hierarchies of the PivotTable.
PivotTableCollection add(name: string, source: Range | string | Table, destination: Range | string) Add a PivotTable based on the specified source data and insert it at the top-left cell of the destination range.
Range dataValidation Returns a data validation object.
RowColumnPivotHierarchy fields Returns the PivotFields associated with the RowColumnPivotHierarchy.
id ID of the RowColumnPivotHierarchy.
name Name of the RowColumnPivotHierarchy.
position Position of the RowColumnPivotHierarchy.
setToDefault() Reset the RowColumnPivotHierarchy back to its default values.
RowColumnPivotHierarchyCollection add(pivotHierarchy: Excel.PivotHierarchy) Adds the PivotHierarchy to the current axis.
getCount() Gets the number of pivot hierarchies in the collection.
getItem(name: string) Gets a RowColumnPivotHierarchy by its name or ID.
getItemOrNullObject(name: string) Gets a RowColumnPivotHierarchy by name.
items Gets the loaded child items in this collection.
remove(rowColumnPivotHierarchy: Excel.RowColumnPivotHierarchy) Removes the PivotHierarchy from the current axis.
Runtime enableEvents Toggle JavaScript events in the current task pane or content add-in.
ShowAsRule baseField The PivotField to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.
baseItem The item to base the ShowAs calculation on, if applicable according to the ShowAsCalculation type, else null.
calculation The ShowAs calculation to use for the PivotField.
Style autoIndent Specifies if text is automatically indented when the text alignment in a cell is set to equal distribution.
textOrientation The text orientation for the style.
Subtotals automatic If Automatic is set to true, then all other values will be ignored when setting the Subtotals.
average
count
countNumbers
max
min
product
standardDeviation
standardDeviationP
sum
variance
varianceP
Table legacyId Returns a numeric ID.
TableChangedEventArgs getRange(ctx: Excel.RequestContext) Gets the range that represents the changed area of a table on a specific worksheet.
getRangeOrNullObject(ctx: Excel.RequestContext) Gets the range that represents the changed area of a table on a specific worksheet.
Workbook readOnly Returns true if the workbook is open in read-only mode.
WorkbookCreated
Worksheet onCalculated Occurs when the worksheet is calculated.
showGridlines Specifies if gridlines are visible to the user.
showHeadings Specifies if headings are visible to the user.
WorksheetCalculatedEventArgs type Gets the type of the event.
worksheetId Gets the ID of the worksheet in which the calculation occurred.
WorksheetChangedEventArgs getRange(ctx: Excel.RequestContext) Gets the range that represents the changed area of a specific worksheet.
getRangeOrNullObject(ctx: Excel.RequestContext) Gets the range that represents the changed area of a specific worksheet.
WorksheetCollection onCalculated Occurs when any worksheet in the workbook is calculated.

See also