Intelligent Media Management:Sheet

Sheets

ActiveWorkbook.Sheets

Obtains all worksheets in the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // Obtain all worksheets in the workbook.
    const sheets = await app.ActiveWorkbook.Sheets;
  }

Methods

ActiveWorkbook.Sheets.Add()

Adds a new worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Add({ Before, After, Count, Type, Name })

  expression: an Application object.

• Parameters

  Parameter	Type	Required	Description
  Before	String/Number	Yes	An object that specifies the worksheet before which the new worksheet is added.
  After	String/Number	No	An object that specifies the worksheet after which the new worksheet is added.
  Count	Number	No	The number of worksheets to be added. Default value: 1.
  Type	Enum	No	The type of the new worksheet. For more information, see XlSheetType.
  Name	Name	No	The name of the new worksheet.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // Obtain all worksheets in the workbook.
    const sheets = await app.ActiveWorkbook.Sheets;
  
    // Add a worksheet.
    await worksheets.Add(null, null, 1, app.Enum.XlSheetType.xlWorksheet, 'new worksheet');
  }

Properties

ActiveWorkbook.Sheets.Count

Returns the number of worksheets in the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Count

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // Obtain all worksheets in the workbook.
    const sheets = await app.ActiveWorkbook.Sheets;
  
    // Obtain the number of worksheets.
    const count = await sheets.Count;
    console.log(count);
  }

Sheet

ActiveWorkbook.Sheets.Item(Index)

Obtains a single worksheet in the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Item

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    // Obtain all worksheets in the workbook.
    const sheet = await app.ActiveWorkbook.Sheets.Item(1)
  }

Methods

ActiveWorkbook.Sheets.Item(Index).Delete()

Deletes a worksheet from the active workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Delete()

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    await activeSheet.Delete();
  } 

ActiveWorkbook.Sheets.Item(Index).ExportAsFixedFormat()

Exports the specified worksheet as a PDF or IMG file and returns the URL of the exported file.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).ExportAsFixedFormat({ Type })

  expression: an Application object.

• Parameters

  Parameter	Type	Required	Description
  Type	Enum	No	The type of the file format to export as. For more information, see XlFixedFormatType. Note Only IMG and PDF files are supported.

• Return values

  Parameter	Type	Description
  url	string	The URL of the exported file.

• Example

  • Export the worksheet as PDF

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Export the entire worksheet.
      const workbookPdfUrl = await app.ActiveWorkbook.Sheets.Item(1).ExportAsFixedFormat();
      console.log(workbookPdfUrl);
    }

  • Export the worksheet as an image

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Export the entire worksheet.
      const workbookPdfUrl = await app.ActiveWorkbook.Sheets.Item(1).ExportAsFixedFormat();
      console.log(workbookPdfUrl);
    }

ActiveWorkbook.Sheets.Item(Index).Protect()

Protects the specified worksheet from modifications.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Protect({ Password, DrawingObjects, Scenarios, AllowSelLockedCells, AllowSelUnlockedCells, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables })

  expression: an Application object.

• Parameters

  Parameter	Type	Default value	Required	Remarks
  Password	String		No	A string that specifies a case-sensitive password for the worksheet or workbook. If this parameter is omitted, you can remove protection from the worksheet or workbook without using a password. Otherwise, you must specify the password to remove protection from the worksheet or workbook. If you forget the password, you cannot remove protection from the worksheet or workbook.
  DrawingObjects	Boolean	false	No	If you set this parameter to true, shapes are protected.
  Scenarios	Boolean	true	No	If you set this parameter to true, scenarios are protected. This parameter applies only to worksheets.
  AllowSelLockedCells	Boolean	true	If you set this parameter to true, locked cells cannot be selected.
  AllowSelUnLockedCells	Boolean	true	If you set this parameter to true, unlocked cells cannot be selected.
  AllowFormattingCells	Boolean	false	No	If you set this parameter to true, users are allowed to format any cell on the protected worksheet.
  AllowFormattingColumns	Boolean	false	No	If you set this parameter to true, users are allowed to format any column on the protected worksheet.
  AllowFormattingRows	Boolean	false	No	If you set this parameter to true, users are allowed to format any row on the protected worksheet.
  AllowInsertingColumns	Boolean	false	No	If you set this parameter to true, users are allowed to insert columns on the protected worksheet.
  AllowInsertingRows	Boolean	false	No	If you set this parameter to true, users are allowed to insert rows on the protected worksheet.
  AllowInsertingHyperlinks	Boolean	false	No	If you set this parameter to true, users are allowed to insert hyperlinks on the protected worksheet.
  AllowDeletingColumns	Boolean	false	No	If you set this parameter to true, users are allowed to delete columns from the protected worksheet, where every cell in the columns is unlocked.
  AllowDeletingRows	Boolean	false	No	If you set this parameter to true, users are allowed to delete rows from the protected worksheet, where every cell in the rows is unlocked.
  AllowSorting	Boolean	false	No	If you set this parameter to true, users are allowed to sort on the protected worksheet. Every cell in the sort range must be unlocked or unprotected.
  AllowFiltering	Boolean	false	No	If you set this parameter to true, users are allowed to set filters on the protected worksheet. Users can change filter criteria but cannot enable or disable an auto filter. Users can set filters on an existing auto filter.
  AllowUsingPivotTables	Boolean	false	No	If you set this parameter to true, users are allowed to use PivotTable reports on the protected worksheet.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    activeSheet.Protect('123456');
  }

ActiveWorkbook.Sheets.Item(Index).Unprotect()

Removes protection from a worksheet. This method has no effect if the worksheet is not protected.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Unprotect({ Password })

  expression: an Application object.

• Parameters

  Parameter	Type	Required	Description
  Password	String	No	The password that is used to protect the worksheet.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    activeSheet.Unprotect('123456');
  }

ActiveWorkbook.Sheets.Item(Index).Activate()

Makes the specified worksheet the active worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Activate()

  or expression.Sheets(Index).Activate()

  expression: an Application object.

• Parameters

  Parameter	Type	Required	Description
  Index	Number	Yes	The index number of the worksheet.

• Examples

  • Example 1

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Make the specified worksheet the active worksheet.
      const worksheetIndex = 2; // The index number of the worksheet. The number starts from 1.
      app.ActiveWorkbook.Sheets.Item(sheetIndex).Activate();
    }

  • Example 2

    async function example() {
      await instance.ready();
    
      const app = instance.Application;
    
      // Make the specified worksheet the active worksheet.
      const worksheetIndex = 2; // The index number of the worksheet. The number starts from 1.
      app.Sheets(sheetIndex).Activate();
    }

ActiveWorkbook.Sheets.Item(Index).Move()

Moves the worksheet to another location in the workbook.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Move({ Before, After })

  expression: an Application object.

• Parameters

  Parameter	Type	Required	Description
  Before	number	No	The ID of the worksheet before which the moved worksheet is placed. You cannot specify Before if you specify After.
  After	number	No	The ID of the worksheet after which the moved worksheet is placed. You cannot specify After if you specify Before.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    await app.ActiveWorkbook.Sheets(1).Move({
      Before: null,
      After: await app.ActiveWorkbook.Sheets(2).Id,
    })
  }

Properties

ActiveWorkbook.Sheets.Item(Index).Type

Returns the type of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Type

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // Obtain all worksheets in the workbook.
    const worksheetIndex = 2; // The index number of the worksheet. The number starts from 1.
    const sheet = await app.ActiveWorkbook.Sheets.Item(sheetIndex)
  
    // Obtain the type of the worksheet.
    const Type = await sheet.Type;
    console.log(Type);
  }

ActiveWorkbook.Sheets.Item(Index).Columns

Returns all columns on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Columns

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const chartObjects = await activeSheet.Columns;
  }

ActiveWorkbook.Sheets.Item(Index).Range

Returns a cell or a range of cells.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Range

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // Obtain all worksheets in the workbook.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // Obtain a cell or a range of cells in the worksheet.
    const Range = await Sheet.Range;
  
    // Specify the cell.
    const range = await Range.Item("D2");
    // You can also use range=await Range("D2") to specify the cell.
  
    // Select cell D2.
    await range.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Cells

Returns all cells on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Cells

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // Obtain all worksheets in the workbook.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // Obtain the cell objects.
    const cells = await Sheet.Cells;
  
    // Specify the cell.
    const cell = await cells.Item(1);
    // Alternatively, use cell = await cells(1)
  
    // Select the first cell.
    await cell.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Name

Returns the name of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Name

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
    
    const name = await app.ActiveWorkbook.Sheets.Item(1).Name;
    console.log(name);
  }

ActiveWorkbook.Sheets.Item(Index).Names

Returns all the worksheet-specific names.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Names

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const names = await activeSheet.Names;
  }

ActiveWorkbook.Sheets.Item(Index).Rows

Returns all rows on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Rows

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // Obtain all worksheets in the workbook.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // Obtain rows.
    const Rows = await Sheet.Rows;
  
    // Specify the row.
    const row = await Rows.Item(1);
    // Alternatively, use row = await Rows(1)
  
    // Select the first row.
    await row.Select();
  }

ActiveWorkbook.Sheets.Item(Index).UsedRange

Returns the used range on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).UsedRange

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    // Obtain all worksheets in the workbook.
    const Sheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    // Obtain the used range on the worksheet.
    const UsedRange = await Sheet.UsedRange;
  
    // Select the used range.
    UsedRange.Select();
  }

ActiveWorkbook.Sheets.Item(Index).Index

Returns the index number of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Index

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const index = await app.ActiveWorkbook.Sheets.Item(1).Index;
    console.log(index);
  }

ActiveWorkbook.Sheets.Item(Index).Shapes

Returns all shapes on the specified worksheet.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Shapes

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const activeSheet = await app.ActiveWorkbook.Sheets.Item(1);
  
    const shapes = await activeSheet.Shapes;
  }

ActiveWorkbook.Sheets.Item(Index).Visible

By using the Visible property, you can specify and obtain whether a worksheet is visible.

• Syntax

  expression.ActiveWorkbook.Sheets.Item(Index).Visible = Boolean

  expression: an Application object.

  If you specify true for Boolean, the worksheet is visible. If you specify false, the worksheet is hidden.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const visible = await app.ActiveWorkbook.Sheets.Item(1).Visible;
    console.log(visible);
  
    app.ActiveWorkbook.Sheets.Item(1).Visible = false;
  }

ActiveWorkbook.Sheets.Item(Index).Id

Returns the ID of the specified worksheet.

• Syntax

  expression.ActiveWorkbook.ActiveSheet.Shapes.Item(Index).ID

  expression: an Application object.

• Example

  async function example() {
    await instance.ready();
  
    const app = instance.Application;
  
    const id = await app.ActiveWorkbook.Sheets.Item(1).Id;
    console.log(id);
  }