Api

new Api()

Base class.

Properties

Name Type Description
Sheets Array

Returns the Sheets collection that represents all the sheets in the active workbook.

ActiveSheet ApiWorksheet

Returns an object that represents the active sheet.

Selection ApiRange

Returns an object that represents the selected range.

Comments Array.

Returns an array of ApiComment objects.

FreezePanes FreezePaneType

Returns or sets the type of freeze panes.

Methods

Name Description
AddComment

Returns an array of ApiComment objects.

AddDefName

Adds a new name to a range of cells.

AddSheet

Creates a new worksheet. The new worksheet becomes the active sheet.

attachEvent

Subscribes to the specified event and calls the callback function when the event fires.

CreateBlipFill

Creates a blip fill to apply to the object using the selected image as the object background.

CreateBullet

Creates a bullet for a paragraph with the character or symbol specified with the sSymbol parameter.

CreateColorByName

Creates a color selecting it from one of the available color presets.

CreateColorFromRGB

Creates an RGB color setting the appropriate values for the red, green and blue color components.

CreateGradientStop

Creates a gradient stop used for different types of gradients.

CreateLinearGradientFill

Creates a linear gradient fill to apply to the object using the selected linear gradient as the object background.

CreateNewHistoryPoint

Creates a new history point.

CreateNoFill

Creates no fill and removes the fill from the element.

CreateNumbering

Creates a bullet for a paragraph with the numbering character or symbol specified with the sType parameter.

CreateParagraph

Creates a new paragraph.

CreatePatternFill

Creates a pattern fill to apply to the object using the selected pattern as the object background.

CreatePresetColor

Creates a color selecting it from one of the available color presets.

CreateRadialGradientFill

Creates a radial gradient fill to apply to the object using the selected radial gradient as the object background.

CreateRGBColor

Creates an RGB color setting the appropriate values for the red, green and blue color components.

CreateRun

Creates a new smaller text block to be inserted to the current paragraph or table.

CreateSchemeColor

Creates a complex color scheme selecting from one of the available schemes.

CreateSolidFill

Creates a solid fill to apply to the object using a selected solid color as the object background.

CreateStroke

Creates a stroke adding shadows to the element.

CreateTextPr

Creates the empty text properties.

detachEvent

Unsubscribes from the specified event.

Format

Returns a class formatted according to the instructions contained in the format expression.

GetActiveSheet

Returns an object that represents the active sheet.

GetCommentById

Returns a comment from the current document by its ID.

GetComments

Returns an array of ApiComment objects.

GetDefName

Returns the ApiName object by the range name.

GetFreezePanesType

Returns the freeze panes type.

GetFullName

Returns the full name of the currently opened file.

GetLocale

Returns the current locale ID.

GetMailMergeData

Returns the mail merge data.

GetRange

Returns the ApiRange object by the range reference.

GetSelection

Returns an object that represents the selected range.

GetSheet

Returns an object that represents a sheet.

GetSheets

Returns a sheet collection that represents all the sheets in the active workbook.

GetThemesColors

Returns a list of all the available theme colors for the spreadsheet.

Intersect

Returns the ApiRange object that represents the rectangular intersection of two or more ranges. If one or more ranges from a different worksheet are specified, an error will be returned.

RecalculateAllFormulas

Recalculates all formulas in the active workbook.

ReplaceTextSmart

Replaces each paragraph (or text in cell) in the select with the corresponding text from an array of strings.

Save

Saves changes to the specified document.

SetFreezePanesType

Sets a type to the freeze panes.

SetLocale

Sets a locale to the document.

SetThemeColors

Sets the theme colors to the current spreadsheet.

Events

Name Description
onWorksheetChange

The callback function which is called when the specified range of the current sheet changes. Please note that the event is not called for the undo/redo operations.

Example

Copy code
builder.CreateFile("xlsx");
var oWorksheet = Api.GetActiveSheet();
var oFillColor = Api.CreateColorFromRGB(201, 222, 255);
oWorksheet.GetRange("A1:M6").SetFillColor(oFillColor);
oWorksheet.GetRange("A1:M6").SetBold(true);
oWorksheet.GetRange("A7:A11").SetBold(true);
oWorksheet.GetRange("A21:M22").SetFillColor(oFillColor);

oFillColor = Api.CreateColorFromRGB(232, 240, 252);
oWorksheet.GetRange("A1").SetColumnWidth(14);
oWorksheet.GetRange("B1:M1").SetColumnWidth(7);
oWorksheet.GetRange("A1:A4").SetRowHeight(27);
var oImage = oWorksheet.AddImage("https://api.onlyoffice.com/content/img/docbuilder/examples/api-cell.png", 28 * 36000, 28 * 36000, 5, 0, 0, 0);

var oRange = oWorksheet.GetRange("C4:J4");
oRange.Merge(false);
oRange.SetValue("Annual Results");
oRange.SetFontSize(24);
oRange = oWorksheet.GetRange("C5:J5");
oRange.Merge(false);
oRange.SetValue("(in thousands of USD)");
oRange.SetFontSize(12);
oRange = oWorksheet.GetRange("C4:J5");
oRange.SetAlignHorizontal("center");
oRange.SetFontColor(Api.CreateColorFromRGB(64, 135, 247));
oRange.SetBorders("Top", "Thick", Api.CreateColorFromRGB(64, 135, 247));
oWorksheet.GetRange("A4:M5").SetBorders("Bottom", "Thick", Api.CreateColorFromRGB(64, 135, 247));

for (var i = 8; i <= 10; i++) {
  if (i%2 === 0) {
    oRange = oWorksheet.GetRange(`A${i}:M${i}`);
    oRange.SetFillColor(oFillColor);
  }
}

oWorksheet.GetRange("A6:M10").SetValue([
  ["City/Month", "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec"],
  ["Beijing", 361.06, 484.37, 318.96, 230.41, 219.15, 229.92, 431.04, 224.24, 396.48, 373.76, 361.06, 419.46],
  ["Jinan", 382.77, 375.40, 439.87, 238.76, 138.84, 240.92, 388.12, 299.67, 326.89, 379.88, 382.77, 481.45],
  ["Haujou", 348.05, 399.41, 303.55, 311.43, 148.70, 219.52, 450.96, 270.40, 371.74, 378.89, 348.05, 501.98],
  ["Macau", 354.52, 366.52, 342.87, 382.48, 222.50, 304.50, 388.09, 233.51, 346.43, 392.74, 354.52, 468.08],
]);

oWorksheet.GetRange("B6:M10").SetAlignHorizontal("center");
oWorksheet.GetRange("B6:M10").SetNumberFormat("$#,##0");

oWorksheet.GetRange("G11:I15").SetValue([
  ["","=B6","=M6"],
  ["=A7","=B7","=M7"],
  ["=A8","=B8","=M8"],
  ["=A9","=B9","=M9"],
  ["=A10","=B10","=M10"]
]);

var sheetName = oWorksheet.GetName();
oWorksheet.GetRange("G11:I15").SetNumberFormat("$#,##0");
var oChart = oWorksheet.AddChart(`'${sheetName}'!$A$6:$M$10`, true, "lineNormal", 1, 100 * 36000, 50 * 36000, 0, 0, 10, 0);
oChart.SetTitle("Financial Overview (in thousands of USD)", 10);
oChart.SetLegendPos("left");

oChart = oWorksheet.AddChart(`'${sheetName}'!$G$11:$I$15`, true, "bar", 2, 100 * 36000, 50 * 36000, 6, 0, 10, 0);
oChart.SetTitle("Year start VS Year End (in thousands of USD)", 10);
oChart.SetLegendPos("left");
Api.Save();
builder.SaveFile("xlsx", "Api.xlsx");
builder.CloseFile();

Resulting document