Search and Replace Text in Excel Files using Aspose.Cells Cloud API

Text operations are complex processes for Excel files. Many factors contribute to this complexity and should be considered during processing. Aspose.Cells Cloud provides a reliable way to search for and replace text across a variety of spreadsheet formats.

Working with text in Excel workbooks often requires locating specific strings and updating them across multiple sheets. The Aspose.Cells Cloud API simplifies this task by providing a unified search and replace operation that works on any supported spreadsheet format.

Overview

Search and replace lets you locate specific strings in a workbook or a particular worksheet and substitute them with new values. The operation works with all formats supported by Aspose.Cells Cloud, such as XLS, XLSX, XLSM, XLSB, ODS, CSV, and others. Using the search and replace feature you can quickly clean up data, correct repeated typos, or apply bulk naming conventions throughout an entire workbook.

Prerequisites

  • An active Aspose.Cloud account with a valid Client‑Id and Client‑Secret.
  • Access token obtained via the OAuth 2.0 authentication flow.
  • The target workbook must be stored in Aspose Cloud storage or accessible via a public URL.
  • Required SDK installed (e.g., Aspose.Cells‑Cloud for .NET, Java, or Python).

API Reference

Method: POST
Endpoint

POST https://api.aspose.cloud/v3.0/cells/{fileName}/searchreplace
Parameter Type Required Description
fileName string Yes Name of the workbook (including extension).
folder string No Cloud storage folder path.
storage string No Storage name if not the default.
sheetName string No Specific worksheet name; if omitted, the operation applies to the whole workbook.
searchString string Yes Text to search for.
replaceString string Yes Text to replace the found occurrences with.
ignoreCase boolean No Set to true to perform a case‑insensitive search.
matchWholeCell boolean No Set to true to replace only whole‑cell matches.

Headers

  • Authorization: Bearer {access_token}
  • Content-Type: application/json

Request Body (JSON)

{
  "searchString": "OldValue",
  "replaceString": "NewValue",
  "ignoreCase": false,
  "matchWholeCell": false,
  "sheetName": "Sheet1"
}

Successful Response (JSON)

{
  "status": "OK",
  "replacedCount": 3,
  "updatedFileUrl": "https://api.aspose.cloud/v3.0/storage/file/updatedWorkbook.xlsx"
}

Supported Formats

Format Extension
Excel Workbook .xls, .xlsx, .xlsm, .xlsb
OpenDocument Spreadsheet .ods
CSV .csv
Others (as supported by Aspose.Cells) —

Code Samples

Below are minimal examples for three popular SDKs. Replace {clientId}, {clientSecret}, and other placeholders with your actual values. These samples demonstrate how to perform a search and replace operation programmatically.

Error Handling & Edge Cases

HTTP Code Meaning Recommended Action
400 Bad Request – missing or invalid parameters Verify required fields and data types.
401 Unauthorized – invalid or expired token Refresh the access token.
404 Not Found – workbook or worksheet does not exist Check the file name, folder path, and sheetName.
415 Unsupported Media Type – invalid file format Ensure the uploaded file is in a supported Excel or CSV format.
202 Accepted – request accepted for processing Poll the operation status if asynchronous processing is used.
204 No Content – operation succeeded with no body The replacement was applied; no additional data returned.
500 Internal Server Error – unexpected failure Retry after a short delay; contact Aspose support if the issue persists.

Notes:

  • Large workbooks may exceed request size limits; consider uploading the file to cloud storage first.
  • When ignoreCase is set to true, be aware that locale‑specific case mappings can affect results.
  • Using matchWholeCell with formulas will not replace partial matches inside the formula text.

Search and replace in Excel files

Merge and Split Excel Files Import Data into Excel Files and Export Data from Excel Files