Tutorial: Finding and Replacing Text in Excel Files

Prerequisites

Before starting this tutorial, make sure you have:

  • An Aspose Cloud account with an active subscription or free trial
  • Your Client ID and Client Secret from the Aspose Cloud Dashboard
  • Basic understanding of REST API concepts
  • An Excel file for testing text operations
  • Familiarity with your preferred programming language

Understanding Excel Text Operations

Finding and replacing text in Excel files is crucial for various tasks such as:

  • Updating company names or product information
  • Correcting misspelled data across large datasets
  • Standardizing terminology throughout reports
  • Personalizing template-based documents
  • Sanitizing sensitive information

Aspose.Cells Cloud provides multiple approaches to search and manipulate text:

  1. Finding text in workbooks or specific worksheets
  2. Replacing text in workbooks or specific worksheets
  3. Direct searching and replacing without using cloud storage

Let’s explore each approach step by step.

1. Finding Text in a Workbook

This method allows you to search for text throughout an entire workbook.

Try It Yourself: Finding Text in a Workbook

Follow these steps to find text in an Excel file:

  1. Upload your workbook to cloud storage
  2. Make the API request to search for the text
  3. Process the search results

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/sample.xlsx/findText?text=Revenue" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN"

SDK Examples

Python

# Tutorial Code Example - Finding Text in a Workbook
import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

# Configure authentication
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"
api = CellsApi(client_id, client_secret)

# Specify file name and search text
name = "sample.xlsx"
text_to_find = "Revenue"

# Create a request to find text
request = PostWorkbooksTextSearchRequest(
    name=name,
    text=text_to_find
)

# Execute the request
response = api.post_workbooks_text_search(request)

# Check if the search was successful
if response.status == "OK":
    print(f"Search completed for '{text_to_find}' in '{name}'.")
    
    # Process the search results
    if response.text_items and response.text_items.text_item_list:
        print(f"Found {len(response.text_items.text_item_list)} matches:")
        for i, item in enumerate(response.text_items.text_item_list, 1):
            print(f"  {i}. Text: '{item.text}'")
    else:
        print(f"No matches found for '{text_to_find}'.")
else:
    print(f"Error searching text: {response.status}")

Java

// Tutorial Code Example - Finding Text in a Workbook
import com.aspose.cells.cloud.api.CellsApi;
import com.aspose.cells.cloud.model.*;
import com.aspose.cells.cloud.request.*;

public class FindTextInWorkbook {
    public static void main(String[] args) {
        // Configure authentication
        String clientId = "YOUR_CLIENT_ID";
        String clientSecret = "YOUR_CLIENT_SECRET";
        CellsApi api = new CellsApi(clientId, clientSecret);
        
        try {
            // Specify file name and search text
            String name = "sample.xlsx";
            String textToFind = "Revenue";
            
            // Create a request to find text
            PostWorkbooksTextSearchRequest request = new PostWorkbooksTextSearchRequest();
            request.setName(name);
            request.setText(textToFind);
            
            // Execute the request
            TextItemsResponse response = api.postWorkbooksTextSearch(request);
            
            // Process the search results
            System.out.println("Search completed for '" + textToFind + "' in '" + name + "'.");
            
            if (response.getTextItems() != null && response.getTextItems().getTextItemList() != null) {
                int count = response.getTextItems().getTextItemList().size();
                System.out.println("Found " + count + " matches:");
                
                for (int i = 0; i < count; i++) {
                    TextItem item = response.getTextItems().getTextItemList().get(i);
                    System.out.println("  " + (i+1) + ". Text: '" + item.getText() + "'");
                }
            } else {
                System.out.println("No matches found for '" + textToFind + "'.");
            }
        } catch (Exception e) {
            System.out.println("Error searching text: " + e.getMessage());
        }
    }
}

2. Finding Text in a Specific Worksheet

If you want to limit your search to a particular worksheet, you can use the worksheet-specific endpoint.

Try It Yourself: Finding Text in a Worksheet

  1. Upload your workbook to cloud storage
  2. Make the API request to search for text in a specific worksheet
  3. Process the search results

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/sample.xlsx/worksheets/Sheet1/findText?text=Revenue" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN"

Python Example

# Tutorial Code Example - Finding Text in a Worksheet
import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

# Configure authentication
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"
api = CellsApi(client_id, client_secret)

# Specify file name, worksheet name, and search text
name = "sample.xlsx"
sheet_name = "Sheet1"
text_to_find = "Revenue"

# Create a request to find text in a specific worksheet
request = PostWorksheetTextSearchRequest(
    name=name,
    sheet_name=sheet_name,
    text=text_to_find
)

# Execute the request
response = api.post_worksheet_text_search(request)

# Check if the search was successful
if response.status == "OK":
    print(f"Search completed for '{text_to_find}' in worksheet '{sheet_name}'.")
    
    # Process the search results
    if response.text_items and response.text_items.text_item_list:
        print(f"Found {len(response.text_items.text_item_list)} matches:")
        for i, item in enumerate(response.text_items.text_item_list, 1):
            print(f"  {i}. Text: '{item.text}'")
    else:
        print(f"No matches found for '{text_to_find}' in worksheet '{sheet_name}'.")
else:
    print(f"Error searching text: {response.status}")

3. Replacing Text in a Workbook

This method allows you to find and replace text throughout an entire workbook.

Try It Yourself: Replacing Text in a Workbook

Follow these steps to replace text in an Excel file:

  1. Upload your workbook to cloud storage
  2. Make the API request to replace the specified text
  3. Verify the replacements were made

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/sample.xlsx/replaceText?oldValue=2022&newValue=2023" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN"

Python Example

# Tutorial Code Example - Replacing Text in a Workbook
import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

# Configure authentication
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"
api = CellsApi(client_id, client_secret)

# Specify file name, old text, and new text
name = "sample.xlsx"
old_value = "2022"
new_value = "2023"

# Create a request to replace text
request = PostWorkbooksTextReplaceRequest(
    name=name,
    old_value=old_value,
    new_value=new_value
)

# Execute the request
response = api.post_workbooks_text_replace(request)

# Check if the replacement was successful
if response.code == 200:
    print(f"Replacement completed: '{old_value}' → '{new_value}' in '{name}'.")
    print(f"Number of replacements made: {response.matches}")
else:
    print(f"Error replacing text: {response.status}")

4. Replacing Text in a Specific Worksheet

If you want to limit your text replacement to a particular worksheet, you can use the worksheet-specific endpoint.

Try It Yourself: Replacing Text in a Worksheet

  1. Upload your workbook to cloud storage
  2. Make the API request to replace text in a specific worksheet
  3. Verify the replacements were made

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/sample.xlsx/worksheets/Sheet1/replaceText?oldValue=2022&newValue=2023" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN"

Python Example

# Tutorial Code Example - Replacing Text in a Worksheet
import asposecellscloud
from asposecellscloud.apis.cells_api import CellsApi
from asposecellscloud.models import *
from asposecellscloud.requests import *

# Configure authentication
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"
api = CellsApi(client_id, client_secret)

# Specify file name, worksheet name, old text, and new text
name = "sample.xlsx"
sheet_name = "Sheet1"
old_value = "2022"
new_value = "2023"

# Create a request to replace text in a specific worksheet
request = PostWorsheetTextReplaceRequest(
    name=name,
    sheet_name=sheet_name,
    old_value=old_value,
    new_value=new_value
)

# Execute the request
response = api.post_worsheet_text_replace(request)

# Check if the replacement was successful
if response.code == 200:
    print(f"Replacement completed: '{old_value}' → '{new_value}' in worksheet '{sheet_name}'.")
    print(f"Number of replacements made: {response.matches}")
else:
    print(f"Error replacing text: {response.status}")

5. Searching Without Using Storage

For scenarios where you want to search for text without first uploading files to cloud storage, you can use the direct search approach.

Try It Yourself: Direct Searching

  1. Prepare your Excel file locally
  2. Make the API request to search directly
  3. Process the search results

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/search?text=Revenue" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN" \
-H "Content-Type: multipart/form-data" \
-F "file=@sample.xlsx"

Python Example

# Tutorial Code Example - Searching Without Using Storage
import requests
import json

# Configure authentication (get your own bearer token using client credentials)
bearer_token = "YOUR_ACCESS_TOKEN"

# API endpoint
url = "https://api.aspose.cloud/v3.0/cells/search"

# Search text
text_to_find = "Revenue"

# Local file path
file_path = "sample.xlsx"

# Prepare the request
headers = {
    "accept": "application/json",
    "authorization": f"Bearer {bearer_token}"
}

# Prepare the file
files = {
    "file": open(file_path, "rb")
}

# Submit the request
response = requests.post(
    f"{url}?text={text_to_find}",
    headers=headers,
    files=files
)

# Remember to close the file handle
files["file"].close()

# Process the response
if response.status_code == 200:
    results = response.json()
    
    # Display the search results
    if results:
        print(f"Found {len(results)} matches:")
        for i, result in enumerate(results, 1):
            text = result.get("Text", "N/A")
            location = "N/A"
            
            # Get the location from the link
            if "link" in result and "Href" in result["link"]:
                location = result["link"]["Href"]
            
            print(f"  {i}. Text: '{text}', Location: {location}")
    else:
        print(f"No matches found for '{text_to_find}'.")
else:
    print(f"Error searching text: {response.status_code}")
    print(response.text)

6. Replacing Without Using Storage

Similarly, you can replace text in Excel files without using cloud storage.

Try It Yourself: Direct Replacing

  1. Prepare your Excel file locally
  2. Make the API request to replace text directly
  3. Save the returned modified file

Using cURL

curl -X POST "https://api.aspose.cloud/v3.0/cells/replace?text=2022&newtext=2023" \
-H "accept: application/json" \
-H "authorization: Bearer YOUR_ACCESS_TOKEN" \
-H "Content-Type: multipart/form-data" \
-F "file=@sample.xlsx"

Python Example

# Tutorial Code Example - Replacing Without Using Storage
import requests
import json
import base64

# Configure authentication (get your own bearer token using client credentials)
bearer_token = "YOUR_ACCESS_TOKEN"

# API endpoint
url = "https://api.aspose.cloud/v3.0/cells/replace"

# Replace parameters
old_text = "2022"
new_text = "2023"

# Local file path
file_path = "sample.xlsx"

# Prepare the request
headers = {
    "accept": "application/json",
    "authorization": f"Bearer {bearer_token}"
}

# Prepare the file
files = {
    "file": open(file_path, "rb")
}

# Submit the request
response = requests.post(
    f"{url}?text={old_text}&newtext={new_text}",
    headers=headers,
    files=files
)

# Remember to close the file handle
files["file"].close()

# Process the response
if response.status_code == 200:
    result = response.json()
    
    # The API returns the modified files in the response
    if "Files" in result:
        for file_info in result["Files"]:
            filename = file_info["Filename"]
            file_content_base64 = file_info["FileContent"]
            
            # Decode the file content
            file_content = base64.b64decode(file_content_base64)
            
            # Save the modified file
            with open(f"modified_{filename}", "wb") as f:
                f.write(file_content)
            
            print(f"Modified file saved as modified_{filename}")
    else:
        print("No files returned in the response.")
else:
    print(f"Error replacing text: {response.status_code}")
    print(response.text)

Troubleshooting Tips

When working with text operations in Excel files, you might encounter these common issues:

  1. Case sensitivity:
    • By default, text search is case-sensitive
    • Be precise with your search terms

, &, + might need URL encoding in API requests

  • Test search terms with simple text first, then add complexity

  1. Formatting and hidden content:

    • The search operation includes hidden cells and sheets
    • Search also applies to formulas, not just displayed values
    • Some formatting (like superscript) may affect text searching

  2. Cell data types:

    • Be aware that numbers stored as text and actual numbers are different
    • Dates might be stored in various formats but displayed uniformly

What You’ve Learned

In this tutorial, you’ve learned how to:

  • Find specific text across an entire Excel workbook
  • Search for text within a particular worksheet
  • Replace text throughout a workbook to update information
  • Make targeted text replacements in specific worksheets
  • Perform text operations without using cloud storage
  • Handle common issues that arise during text operations

Further Practice

To reinforce your learning, try these exercises:

  1. Create a script that searches for multiple terms and reports their locations
  2. Develop a batch text replacement utility for updating annual reports
  3. Implement a case-insensitive search function using the API
  4. Write a program that extracts all text matching a specific pattern (like emails or phone numbers)

Next Steps

Now that you’ve learned to find and replace text, you might want to explore:

Additional Resources