Comprehensive Tutorial: CellsObjectOperate Task

Introduction

In this advanced tutorial, you’ll learn how to use the CellsObjectOperate task in Aspose.Cells Cloud API to manipulate various Excel objects programmatically. The CellsObjectOperate task is a powerful feature that allows you to work with worksheets, charts, shapes, pivot tables, and more through simple API calls.

Prerequisites

Before starting this tutorial, make sure you have:

An Aspose Cloud account (sign up for free here)
Your Client ID and Client Secret from the Aspose Cloud dashboard
Intermediate understanding of REST API concepts
Familiarity with Excel objects and their properties
Completed our ImportData Task Tutorial (recommended)

Understanding CellsObjectOperate Structure

The CellsObjectOperate task operates on Excel objects through a structured parameter set:

Main Components

OperateObject - Defines what Excel object you want to work with:
- OperateObjectType: Type of object (Workbook, Worksheet, Chart, etc.)
- Position: Location of the object in the workbook
Operation Parameter - Specific parameters for the operation you want to perform:
- Multiple parameter types exist depending on which object you’re operating on
- Each has an OperateType field (Add, Delete, Update, etc.)

Object Types and Their Parameters

Here are the main object types you can operate on:

Object Type	Parameter Class	Common Operations
Workbook	WorkbookSettingsOperateParameter	Update settings
Worksheet	WorksheetOperateParameter	Add, remove, rename, move
Chart	ChartOperateParameter	Create, update, delete
Shape	ShapeOperateParameter	Add, format, position
ListObject	ListObjectOperateParameter	Create, format tables
PivotTable	PivotTableOperateParameter	Create, configure pivot tables
PageSetup	PageSetupOperateParameter	Configure print settings
PageBreak	PageBreakOperateParameter	Add, remove page breaks

Tutorial: Creating a Chart with CellsObjectOperate

Let’s create a practical example by generating a chart from spreadsheet data.

Step 1: Import Sample Data

First, we’ll import some sample data that we’ll use for our chart:

<TaskDescription>
  <TaskType>ImportData</TaskType>
  <ImportDataTaskParameter>
    <Workbook>
      <FileSourceType>CloudFileSystem</FileSourceType>
      <FilePath>Chart.xlsx</FilePath>
    </Workbook>
    <ImportBatchDataOption>
      <DestinationWorksheet>Sheet1</DestinationWorksheet>
      <IsInsert>true</IsInsert>
      <BatchData>
        <!-- Header row -->
        <CellValue>
          <rowIndex>0</rowIndex>
          <columnIndex>1</columnIndex>
          <type>String</type>
          <value>Sales</value>
        </CellValue>
        <CellValue>
          <rowIndex>0</rowIndex>
          <columnIndex>2</columnIndex>
          <type>String</type>
          <value>Expenses</value>
        </CellValue>
        
        <!-- Data rows -->
        <CellValue>
          <rowIndex>1</rowIndex>
          <columnIndex>0</columnIndex>
          <type>String</type>
          <value>January</value>
        </CellValue>
        <CellValue>
          <rowIndex>1</rowIndex>
          <columnIndex>1</columnIndex>
          <type>Double</type>
          <value>5000</value>
        </CellValue>
        <CellValue>
          <rowIndex>1</rowIndex>
          <columnIndex>2</columnIndex>
          <type>Double</type>
          <value>3200</value>
        </CellValue>
        
        <CellValue>
          <rowIndex>2</rowIndex>
          <columnIndex>0</columnIndex>
          <type>String</type>
          <value>February</value>
        </CellValue>
        <CellValue>
          <rowIndex>2</rowIndex>
          <columnIndex>1</columnIndex>
          <type>Double</type>
          <value>5500</value>
        </CellValue>
        <CellValue>
          <rowIndex>2</rowIndex>
          <columnIndex>2</columnIndex>
          <type>Double</type>
          <value>3300</value>
        </CellValue>
        
        <CellValue>
          <rowIndex>3</rowIndex>
          <columnIndex>0</columnIndex>
          <type>String</type>
          <value>March</value>
        </CellValue>
        <CellValue>
          <rowIndex>3</rowIndex>
          <columnIndex>1</columnIndex>
          <type>Double</type>
          <value>6200</value>
        </CellValue>
        <CellValue>
          <rowIndex>3</rowIndex>
          <columnIndex>2</columnIndex>
          <type>Double</type>
          <value>3800</value>
        </CellValue>
      </BatchData>
    </ImportBatchDataOption>
  </ImportDataTaskParameter>
</TaskDescription>

Step 2: Create a Chart Using CellsObjectOperate

Now, let’s add a task to create a chart based on this data:

<TaskDescription>
  <TaskType>CellsObjectOperate</TaskType>
  <CellsObjectOperateTaskParameter>
    <OperateObject>
      <OperateObjectType>Chart</OperateObjectType>
      <Position>
        <Workbook>
          <FileSourceType>InMemoryFiles</FileSourceType>
          <FilePath>Chart.xlsx</FilePath>
        </Workbook>
        <SheetName>Sheet1</SheetName>
      </Position>
    </OperateObject>
    <ChartOperateParameter>
      <OperateType>Add</OperateType>
      <ChartType>Column</ChartType>
      <UpperLeftRow>5</UpperLeftRow>
      <UpperLeftColumn>0</UpperLeftColumn>
      <LowerRightRow>20</LowerRightRow>
      <LowerRightColumn>10</LowerRightColumn>
      <Area>=Sheet1!A1:C4</Area>
      <IsVertical>false</IsVertical>
      <Title>Monthly Financial Performance</Title>
    </ChartOperateParameter>
    <DestinationWorkbook>
      <FileSourceType>InMemoryFiles</FileSourceType>
      <FilePath>ChartResult.xlsx</FilePath>
    </DestinationWorkbook>
  </CellsObjectOperateTaskParameter>
</TaskDescription>

<!-- Save the result -->
<TaskDescription>
  <TaskType>SaveResult</TaskType>
  <SaveResultTaskParameter>
    <ResultSource>InMemoryFiles</ResultSource>
    <ResultDestination>
      <DestinationType>OutputStream</DestinationType>
      <InputFile>ChartResult.xlsx</InputFile>
      <OutputFile>FinalChart.xlsx</OutputFile>
    </ResultDestination>
  </SaveResultTaskParameter>
</TaskDescription>

Step 3: Understanding the Chart Parameters

Let’s break down the key chart settings:

ChartType: Column specifies a column chart
Upper/Lower Positions: Define where the chart will be placed on the sheet
Area: =Sheet1!A1:C4 specifies the data range for the chart
IsVertical: false means data series are in columns (not rows)
Title: Sets the chart title

Step 4: Execute the Complete Request

Combine both tasks (ImportData and CellsObjectOperate) into a single API request:

<TaskData>
  <Tasks>
    <!-- Task 1: Import sample data (from Step 1) -->
    <!-- Task 2: Create chart (from Step 2) -->
    <!-- Task 3: Save the result (from Step 2) -->
  </Tasks>
</TaskData>

Using cURL

curl -v "https://api.aspose.cloud/v3.0/cells/task/runtask" \
-X POST \
-H "accept: application/xml" \
-H "Content-Type: application/xml" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN" \
-d "YOUR_XML_REQUEST"

Tutorial: Working with Worksheets

Let’s explore another common operation: managing worksheets.

Adding a New Worksheet

<TaskDescription>
  <TaskType>CellsObjectOperate</TaskType>
  <CellsObjectOperateTaskParameter>
    <OperateObject>
      <OperateObjectType>Worksheet</OperateObjectType>
      <Position>
        <Workbook>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>MyWorkbook.xlsx</FilePath>
        </Workbook>
      </Position>
    </OperateObject>
    <WorksheetOperateParameter>
      <OperateType>Add</OperateType>
      <Name>NewSheet</Name>
      <SheetType>Worksheet</SheetType>
    </WorksheetOperateParameter>
    <DestinationWorkbook>
      <FileSourceType>InMemoryFiles</FileSourceType>
      <FilePath>UpdatedWorkbook.xlsx</FilePath>
    </DestinationWorkbook>
  </CellsObjectOperateTaskParameter>
</TaskDescription>

Renaming a Worksheet

<TaskDescription>
  <TaskType>CellsObjectOperate</TaskType>
  <CellsObjectOperateTaskParameter>
    <OperateObject>
      <OperateObjectType>Worksheet</OperateObjectType>
      <Position>
        <Workbook>
          <FileSourceType>InMemoryFiles</FileSourceType>
          <FilePath>UpdatedWorkbook.xlsx</FilePath>
        </Workbook>
        <SheetName>NewSheet</SheetName>
      </Position>
    </OperateObject>
    <WorksheetOperateParameter>
      <OperateType>Update</OperateType>
      <NewName>RenamedSheet</NewName>
    </WorksheetOperateParameter>
    <DestinationWorkbook>
      <FileSourceType>InMemoryFiles</FileSourceType>
      <FilePath>FinalWorkbook.xlsx</FilePath>
    </DestinationWorkbook>
  </CellsObjectOperateTaskParameter>
</TaskDescription>

Try It Yourself

Create a request that adds two new worksheets to a workbook
Create a pie chart from a data range
Try moving a worksheet to a different position in the workbook

Advanced: Working with Shapes

Let’s look at adding a shape to a worksheet:

<TaskDescription>
  <TaskType>CellsObjectOperate</TaskType>
  <CellsObjectOperateTaskParameter>
    <OperateObject>
      <OperateObjectType>Shape</OperateObjectType>
      <Position>
        <Workbook>
          <FileSourceType>CloudFileSystem</FileSourceType>
          <FilePath>Shapes.xlsx</FilePath>
        </Workbook>
        <SheetName>Sheet1</SheetName>
      </Position>
    </OperateObject>
    <ShapeOperateParameter>
      <OperateType>Add</OperateType>
      <Shape>
        <ShapeType>Rectangle</ShapeType>
        <Left>100</Left>
        <Top>100</Top>
        <Width>200</Width>
        <Height>100</Height>
        <Text>Sample Shape</Text>
        <TextHorizontalAlignment>Center</TextHorizontalAlignment>
        <TextVerticalAlignment>Center</TextVerticalAlignment>
        <FillFormat>
          <FillType>Solid</FillType>
          <ForegroundColor>
            <A>255</A>
            <R>220</R>
            <G>230</G>
            <B>240</B>
          </ForegroundColor>
        </FillFormat>
      </Shape>
    </ShapeOperateParameter>
    <DestinationWorkbook>
      <FileSourceType>InMemoryFiles</FileSourceType>
      <FilePath>ShapesResult.xlsx</FilePath>
    </DestinationWorkbook>
  </CellsObjectOperateTaskParameter>
</TaskDescription>

Common Issues and Troubleshooting

Issue: Object Not Found

If you get an error that the object can’t be found, check:

The sheet name is spelled correctly and exists
The object index (if used) is correct
The file path points to an existing file

Issue: Invalid Operation

If you get an “invalid operation” error:

Verify that the operation you’re trying to perform is valid for that object type
Check the required parameters for that specific operation
Ensure the parameter values are within valid ranges

Multiple Operations Example

You can chain multiple operations together in a single request:

<TaskData>
  <Tasks>
    <TaskDescription>
      <!-- First operation: Import data -->
    </TaskDescription>
    <TaskDescription>
      <!-- Second operation: Create a chart -->
    </TaskDescription>
    <TaskDescription>
      <!-- Third operation: Add a shape -->
    </TaskDescription>
    <TaskDescription>
      <!-- Fourth operation: Save the result -->
    </TaskDescription>
  </Tasks>
</TaskData>

What You’ve Learned

In this tutorial, you’ve learned:

How to structure CellsObjectOperate tasks for different Excel objects
Techniques for creating charts from spreadsheet data
Methods for adding and manipulating worksheets
How to work with shapes and other Excel objects
Approaches for chaining multiple operations in a single request

Further Practice

Create a workbook with multiple charts analyzing different data ranges
Add a table with conditional formatting using CellsObjectOperate
Create a dashboard combining charts, tables, and shapes
Experiment with the different chart types available