Working with OLE Objects: A Comprehensive Tutorial

Learning Objectives

In this tutorial, you’ll learn how to:

  • Retrieve OLE objects from an Excel worksheet
  • Add new OLE objects to a worksheet
  • Update existing OLE objects
  • Convert OLE objects to images
  • Delete specific OLE objects
  • Remove all OLE objects from a worksheet

Prerequisites

Before starting this tutorial, make sure you have:

  1. An Aspose Cloud account (sign up for a free trial if you don’t have one)
  2. Your Client ID and Client Secret from the Aspose Cloud Dashboard
  3. Basic understanding of REST APIs
  4. Familiarity with your chosen programming language
  5. Source files for OLE objects (e.g., a Word document, Excel file, or image)

Introduction to OLE Objects in Excel

OLE (Object Linking and Embedding) objects in Excel allow you to embed content from other applications directly into your spreadsheets. These can include documents from other Office applications, images, or other file types. When embedded, the content becomes part of the Excel file and can be edited using the source application.

Using Aspose.Cells Cloud API, you can programmatically manage OLE objects in your Excel files without requiring Microsoft Excel, providing a powerful way to automate document creation and maintenance.

Tutorial Steps

1. Authentication

First, let’s set up authentication to access the Aspose.Cells Cloud API:

# Base request URL
BASE_URL="https://api.aspose.cloud/v3.0/cells"

# Get JWT token
TOKEN=$(curl -v "https://api.aspose.cloud/connect/token" \
 -X POST \
 -d "grant_type=client_credentials&client_id=YOUR_CLIENT_ID&client_secret=YOUR_CLIENT_SECRET" \
 -H "Content-Type: application/x-www-form-urlencoded" \
 | jq -r '.access_token')

Remember to replace YOUR_CLIENT_ID and YOUR_CLIENT_SECRET with your actual credentials.

2. Getting OLE Objects from a Worksheet

Let’s start by retrieving OLE objects from a specific worksheet:

Using cURL:

curl -X GET "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

Using Python SDK:

import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.api_client import ApiClient

# Configure API key authorization
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"

# Create API client
api_instance = CellsApi(client_id, client_secret)

try:
    # Get all OLE objects
    response = api_instance.cells_ole_objects_get_worksheet_ole_objects(
        name="myworkbook.xlsx", 
        sheet_name="Sheet1")
    
    # Print OLE object information
    if response.ole_objects and response.ole_objects.ole_object_list:
        print(f"Number of OLE objects: {len(response.ole_objects.ole_object_list)}")
        
        # Print each OLE object's details
        for idx, ole_object in enumerate(response.ole_objects.ole_object_list):
            print(f"OLE Object {idx}:")
            print(f"  Image source: {ole_object.image_source_full_name}")
            print(f"  Source: {ole_object.source_full_name}")
            print(f"  Position: Row {ole_object.upper_left_row}, Column {ole_object.upper_left_column}")
            print(f"  Size: {ole_object.width} x {ole_object.height}")
    else:
        print("No OLE objects found.")
        
except Exception as e:
    print("Exception when calling CellsApi->cells_ole_objects_get_worksheet_ole_objects: %s\n" % e)

3. Getting a Specific OLE Object

To retrieve a specific OLE object by its index:

Using cURL:

curl -X GET "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects/0" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

Using C# SDK:

using System;
using Aspose.Cells.Cloud.SDK.Api;
using Aspose.Cells.Cloud.SDK.Model;

namespace AsposeCellsCloudTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            // Configure API credentials
            var cellsApi = new CellsApi("YOUR_CLIENT_ID", "YOUR_CLIENT_SECRET");
            
            // Specify parameters
            string fileName = "myworkbook.xlsx";
            string sheetName = "Sheet1";
            int objectNumber = 0; // First OLE object
            
            try
            {
                // Get the OLE object
                var response = cellsApi.CellsOleObjectsGetWorksheetOleObject(
                    fileName, 
                    sheetName, 
                    objectNumber);
                
                // Display OLE object details
                if (response.OleObject != null)
                {
                    Console.WriteLine("OLE Object Details:");
                    Console.WriteLine($"Image Source: {response.OleObject.ImageSourceFullName}");
                    Console.WriteLine($"Source: {response.OleObject.SourceFullName}");
                    Console.WriteLine($"Position: Row {response.OleObject.UpperLeftRow}, Column {response.OleObject.UpperLeftColumn}");
                    Console.WriteLine($"Size: {response.OleObject.Width} x {response.OleObject.Height}");
                    Console.WriteLine($"Auto Size: {response.OleObject.IsAutoSize}");
                }
                else
                {
                    Console.WriteLine("OLE object not found.");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
    }
}

4. Getting an OLE Object as Image

You can also retrieve an OLE object as an image in various formats:

Using cURL:

curl -X GET "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects/0?format=png" \
-H "Authorization: Bearer $TOKEN" \
-H "Accept: image/png" \
-o "ole_object_image.png"

Using Java SDK:

import com.aspose.cells.cloud.api.CellsApi;
import com.aspose.cells.cloud.model.*;
import com.aspose.cells.cloud.request.*;
import java.io.File;
import java.nio.file.Files;
import java.nio.file.StandardCopyOption;

public class GetOleObjectAsImageExample {
    public static void main(String[] args) {
        // Configure API credentials
        CellsApi cellsApi = new CellsApi("YOUR_CLIENT_ID", "YOUR_CLIENT_SECRET");
        
        try {
            // Specify parameters
            String fileName = "myworkbook.xlsx";
            String sheetName = "Sheet1";
            Integer objectNumber = 0; // First OLE object
            String format = "png"; // Output image format
            
            // Create request
            GetWorksheetOleObjectRequest request = new GetWorksheetOleObjectRequest(
                fileName,
                sheetName,
                objectNumber,
                format,
                null, // folder
                null  // storage name
            );
            
            // Get the OLE object as image
            File result = cellsApi.getWorksheetOleObject(request);
            
            // Save the image to a file
            File outputFile = new File("ole_object_image.png");
            Files.copy(result.toPath(), outputFile.toPath(), StandardCopyOption.REPLACE_EXISTING);
            
            System.out.println("OLE object saved as image: " + outputFile.getAbsolutePath());
        } catch (Exception e) {
            System.err.println("Error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

5. Adding an OLE Object to a Worksheet

Now, let’s add a new OLE object to a worksheet:

Using cURL:

curl -X PUT "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-d '{"ImageSourceFullName":"aspose-logo.png", "IsAutoSize":true, "SourceFullName":"sample.docx", "UpperLeftRow":5, "Top":10, "UpperLeftColumn":2, "Left":10, "Width":300, "Height":200}'

Using Node.js SDK:

const { CellsApi } = require('asposecellscloud');

// Configure API credentials
const cellsApi = new CellsApi('YOUR_CLIENT_ID', 'YOUR_CLIENT_SECRET');

// Specify parameters
const fileName = "myworkbook.xlsx";
const sheetName = "Sheet1";

// Create OLE object definition
const oleObject = {
    ImageSourceFullName: "aspose-logo.png",
    IsAutoSize: true,
    SourceFullName: "sample.docx",
    UpperLeftRow: 5,
    Top: 10,
    UpperLeftColumn: 2,
    Left: 10,
    Width: 300,
    Height: 200
};

// Add the OLE object
cellsApi.cellsOleObjectsPutWorksheetOleObject(fileName, sheetName, oleObject)
    .then(() => {
        console.log("OLE object added successfully.");
    })
    .catch(error => {
        console.error("Error:", error);
    });

6. Updating an Existing OLE Object

To update an existing OLE object:

Using cURL:

curl -X POST "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects/0" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-d '{"ImageSourceFullName":"new-logo.png", "UpperLeftRow":10, "UpperLeftColumn":3, "Width":400, "Height":250}'

Using Python SDK:

import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.api_client import ApiClient

# Configure API key authorization
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"

# Create API client
api_instance = CellsApi(client_id, client_secret)

try:
    # Specify parameters
    name = "myworkbook.xlsx"
    sheet_name = "Sheet1"
    ole_object_index = 0  # First OLE object
    
    # Create OLE object with updated properties
    ole_object = {
        "ImageSourceFullName": "new-logo.png",
        "UpperLeftRow": 10,
        "UpperLeftColumn": 3,
        "Width": 400,
        "Height": 250
    }
    
    # Update the OLE object
    response = api_instance.cells_ole_objects_post_update_worksheet_ole_object(
        name=name,
        sheet_name=sheet_name,
        ole_object_index=ole_object_index,
        ole=ole_object
    )
    
    print("OLE object updated successfully.")
    
except Exception as e:
    print("Exception when calling CellsApi->cells_ole_objects_post_update_worksheet_ole_object: %s\n" % e)

7. Deleting a Specific OLE Object

To delete a specific OLE object by its index:

Using cURL:

curl -X DELETE "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects/0" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

Using C# SDK:

using System;
using Aspose.Cells.Cloud.SDK.Api;
using Aspose.Cells.Cloud.SDK.Model;

namespace AsposeCellsCloudTutorial
{
    class Program
    {
        static void Main(string[] args)
        {
            // Configure API credentials
            var cellsApi = new CellsApi("YOUR_CLIENT_ID", "YOUR_CLIENT_SECRET");
            
            // Specify parameters
            string fileName = "myworkbook.xlsx";
            string sheetName = "Sheet1";
            int oleObjectIndex = 0; // First OLE object
            
            try
            {
                // Delete the OLE object
                var response = cellsApi.CellsOleObjectsDeleteWorksheetOleObject(
                    fileName, 
                    sheetName, 
                    oleObjectIndex);
                
                Console.WriteLine("OLE object deleted successfully.");
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: " + e.Message);
            }
        }
    }
}

8. Deleting All OLE Objects from a Worksheet

To remove all OLE objects from a specific worksheet:

Using cURL:

curl -X DELETE "$BASE_URL/myworkbook.xlsx/worksheets/Sheet1/oleobjects" \
-H "Authorization: Bearer $TOKEN" \
-H "Content-Type: application/json" \
-H "Accept: application/json"

Using Java SDK:

import com.aspose.cells.cloud.api.CellsApi;
import com.aspose.cells.cloud.model.*;
import com.aspose.cells.cloud.request.*;

public class DeleteAllOleObjectsExample {
    public static void main(String[] args) {
        // Configure API credentials
        CellsApi cellsApi = new CellsApi("YOUR_CLIENT_ID", "YOUR_CLIENT_SECRET");
        
        try {
            // Specify parameters
            String fileName = "myworkbook.xlsx";
            String sheetName = "Sheet1";
            
            // Create request
            DeleteWorksheetOleObjectsRequest request = new DeleteWorksheetOleObjectsRequest(
                fileName,
                sheetName,
                null, // folder
                null  // storage name
            );
            
            // Delete all OLE objects
            cellsApi.deleteWorksheetOleObjects(request);
            
            System.out.println("All OLE objects deleted successfully.");
        } catch (Exception e) {
            System.err.println("Error: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

Try It Yourself

Now it’s time to practice what you’ve learned:

  1. Create a new Excel file
  2. Add an OLE object to the worksheet (e.g., a Word document or another Excel file)
  3. Retrieve the OLE object and save it as an image
  4. Update the OLE object’s properties (size, position)
  5. Delete the OLE object

Troubleshooting Tips

  • Error 401 (Unauthorized): Make sure your Client ID and Client Secret are correct and that your token hasn’t expired.
  • Error 404 (Not Found): Check that your file name, worksheet name, and OLE object index are correct.
  • OLE object not appearing correctly: Ensure the source file exists and is accessible. Also verify the image file for the OLE object representation is valid.
  • Format issues when converting OLE objects to images: Not all OLE objects can be converted to all image formats. Try different formats (PNG, JPG, GIF) if you encounter problems.
  • Size and position issues: Excel uses a coordinate system that can be confusing. Make sure to check your values for upperLeftRow, upperLeftColumn, width, and height.

What You’ve Learned

In this tutorial, you’ve learned how to:

  • Retrieve OLE objects from an Excel worksheet
  • Get a specific OLE object by its index
  • Convert OLE objects to different image formats
  • Add new OLE objects to a worksheet
  • Update existing OLE objects
  • Delete specific OLE objects
  • Remove all OLE objects from a worksheet

You now have the skills to programmatically manage OLE objects in Excel files using the Aspose.Cells Cloud API.

Next Steps

Consider exploring these related tutorials:

Helpful Resources