Tutorial: Managing Rows in Excel with Aspose.Cells Cloud API

Introduction

Welcome to this hands-on tutorial on managing rows in Excel spreadsheets using Aspose.Cells Cloud API. Excel worksheets are organized in rows and columns, and efficient row management is essential for creating well-structured and functional spreadsheets. In this tutorial, you’ll learn how to programmatically add, delete, hide, unhide, group, and format rows in Excel using Aspose.Cells Cloud API.

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 knowledge of REST APIs and HTTP concepts
  4. Familiarity with your programming language of choice
  5. A development environment or API testing tool (like Postman, cURL, etc.)
  6. Completed the Worksheets tutorial (recommended but not required)

Tutorial Steps

Step 1: Authentication with Aspose.Cells Cloud API

Before working with rows, you need to authenticate with the Aspose.Cells Cloud API to get an access token.

Try it yourself:

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" \
-H "Accept: application/json"

Save the access token from the response for use in subsequent API calls.

Step 2: Get All Rows in a Worksheet

Let’s start by retrieving information about all rows in an existing Excel worksheet.

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows" \
-X GET \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This API returns information about all rows in the specified worksheet, including their properties.

Example Response:

{
  "Rows": {
    "MaxRow": 20,
    "RowsCount": 17,
    "RowsList": [
      {
        "link": {
          "Href": "/0",
          "Rel": "self",
          "Title": null,
          "Type": null
        }
      },
      // Additional rows...
    ],
    "link": {
      "Href": "/test.xlsx/worksheets/Sheet1/cells/rows",
      "Rel": "self",
      "Title": null,
      "Type": null
    }
  },
  "Code": 200,
  "Status": "OK"
}

Step 3: Get Information About a Specific Row

Now, let’s retrieve detailed information about a specific row:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/0" \
-X GET \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This request retrieves information about the first row (index 0) in the worksheet.

Example Response:

{
  "Row": {
    "GroupLevel": 0,
    "Height": 13.5,
    "Index": 0,
    "IsBlank": false,
    "IsHeightMatched": true,
    "IsHidden": false,
    "Style": {
      "link": {
        "Href": "/style",
        "Rel": "self"
      }
    },
    "link": {
      "Href": "api.aspose.cloud/v3.0/cells/Book1.xlsx/worksheets/Sheet1/cells/rows/0",
      "Rel": "self"
    }
  },
  "Code": 200,
  "Status": "OK"
}

Step 4: Add a Single Row to a Worksheet

Now let’s learn how to add a new row to a worksheet at a specific position:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/5" \
-X PUT \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example adds a new row at index 5. All existing rows at and below this index will be shifted down.

SDK Example (Python):

# Add a row example
import requests
import json

# Authentication
auth_url = "https://api.aspose.cloud/connect/token"
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"

auth_data = {
    "grant_type": "client_credentials",
    "client_id": client_id,
    "client_secret": client_secret
}

auth_headers = {
    "Content-Type": "application/x-www-form-urlencoded",
    "Accept": "application/json"
}

auth_response = requests.post(auth_url, data=auth_data, headers=auth_headers)
access_token = auth_response.json().get("access_token")

# Add a new row
api_url = "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/5"
headers = {
    "Content-Type": "application/json",
    "Accept": "application/json",
    "Authorization": f"Bearer {access_token}"
}

response = requests.put(api_url, headers=headers)
print(json.dumps(response.json(), indent=2))

if response.status_code == 200:
    print("Row added successfully at index 5!")
else:
    print(f"Error: {response.status_code}")

Step 5: Add Multiple Rows to a Worksheet

For adding multiple rows at once, use this approach:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows?startrow=10&totalRows=5&updateReference=true" \
-X PUT \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example adds 5 new rows starting at index 10.

Step 6: Hide Rows in a Worksheet

You can hide rows to reduce visual clutter or protect information:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/hide?startrow=2&totalRows=3" \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example hides 3 rows starting from index 2 (rows 2, 3, and 4).

SDK Example (C#):

// Hide rows example - Tutorial Code Example
using System;
using System.Collections.Generic;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Threading.Tasks;

namespace AsposeCellsTutorials
{
    class Program
    {
        static async Task Main(string[] args)
        {
            // Authentication
            var client = new HttpClient();
            var authRequest = new HttpRequestMessage(HttpMethod.Post, "https://api.aspose.cloud/connect/token");
            
            var authContent = new FormUrlEncodedContent(new Dictionary<string, string>
            {
                {"grant_type", "client_credentials"},
                {"client_id", "YOUR_CLIENT_ID"},
                {"client_secret", "YOUR_CLIENT_SECRET"}
            });
            
            authRequest.Content = authContent;
            var authResponse = await client.SendAsync(authRequest);
            var authResult = await authResponse.Content.ReadAsStringAsync();
            
            // Parse access token from JSON response
            // For simplicity, using basic string operations
            var accessToken = authResult.Split("\"access_token\":\"")[1].Split("\"")[0];
            
            // Hide rows
            var apiClient = new HttpClient();
            apiClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Bearer", accessToken);
            
            var apiUrl = "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/hide?startrow=2&totalRows=3";
            var response = await apiClient.PostAsync(apiUrl, null);
            
            if (response.IsSuccessStatusCode)
            {
                Console.WriteLine("Rows 2-4 hidden successfully!");
            }
            else
            {
                Console.WriteLine($"Error: {response.StatusCode} - {await response.Content.ReadAsStringAsync()}");
            }
        }
    }
}

Step 7: Unhide Rows

To make hidden rows visible again:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/unhide?startrow=2&totalRows=3&height=15" \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example unhides the same 3 rows we hid in the previous step and sets their height to 15 points.

Step 8: Group Rows for Better Organization

Excel’s row grouping feature helps organize related data. Here’s how to group rows:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/group?firstIndex=1&lastIndex=5&hide=false" \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example groups rows 1 through 5 without hiding them.

SDK Example (Java):

// Group rows example - Tutorial Code Example
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.util.Scanner;

public class GroupRowsExample {
    public static void main(String[] args) {
        try {
            // Authentication
            String clientId = "YOUR_CLIENT_ID";
            String clientSecret = "YOUR_CLIENT_SECRET";
            String authUrl = "https://api.aspose.cloud/connect/token";
            
            URL url = new URL(authUrl);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setRequestMethod("POST");
            conn.setRequestProperty("Content-Type", "application/x-www-form-urlencoded");
            conn.setRequestProperty("Accept", "application/json");
            conn.setDoOutput(true);
            
            String authData = "grant_type=client_credentials&client_id=" + clientId + "&client_secret=" + clientSecret;
            try (OutputStream os = conn.getOutputStream()) {
                byte[] input = authData.getBytes(StandardCharsets.UTF_8);
                os.write(input, 0, input.length);
            }
            
            // Read auth response
            Scanner scanner = new Scanner(conn.getInputStream());
            String authResponse = scanner.useDelimiter("\\A").next();
            scanner.close();
            
            // Extract access token (simple approach for demonstration)
            String accessToken = authResponse.split("\"access_token\":\"")[1].split("\"")[0];
            
            // Group rows
            String apiUrl = "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/group?firstIndex=1&lastIndex=5&hide=false";
            URL groupUrl = new URL(apiUrl);
            HttpURLConnection groupConn = (HttpURLConnection) groupUrl.openConnection();
            groupConn.setRequestMethod("POST");
            groupConn.setRequestProperty("Content-Type", "application/json");
            groupConn.setRequestProperty("Accept", "application/json");
            groupConn.setRequestProperty("Authorization", "Bearer " + accessToken);
            
            int responseCode = groupConn.getResponseCode();
            if (responseCode == HttpURLConnection.HTTP_OK) {
                System.out.println("Rows grouped successfully!");
                
                // Read and print the response for educational purposes
                Scanner responseScanner = new Scanner(groupConn.getInputStream());
                String responseBody = responseScanner.useDelimiter("\\A").next();
                System.out.println("Response: " + responseBody);
                responseScanner.close();
            } else {
                System.out.println("Error: " + responseCode);
                Scanner errorScanner = new Scanner(groupConn.getErrorStream());
                System.out.println(errorScanner.useDelimiter("\\A").next());
                errorScanner.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Step 9: Ungroup Rows

You can also ungroup previously grouped rows:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/ungroup?firstIndex=1&lastIndex=5&isAll=false" \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example ungroups rows 1 through 5.

Step 10: Copy Rows

You can copy rows from one location to another:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/copy?sourceRowIndex=1&destinationRowIndex=10&rowNumber=3" \
-X POST \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example copies 3 rows starting from row 1 to a new location starting at row 10.

Step 11: Delete a Row

To remove a row from a worksheet:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows/1" \
-X DELETE \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example deletes row 1 from the worksheet.

Step 12: Delete Multiple Rows

For bulk row deletion:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows?startrow=5&totalRows=3&updateReference=true" \
-X DELETE \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example deletes 3 rows starting from row 5.

SDK Example (Python):

# Delete multiple rows example - Tutorial Code Example
import requests
import json

# Authentication
auth_url = "https://api.aspose.cloud/connect/token"
client_id = "YOUR_CLIENT_ID"
client_secret = "YOUR_CLIENT_SECRET"

auth_data = {
    "grant_type": "client_credentials",
    "client_id": client_id,
    "client_secret": client_secret
}

auth_headers = {
    "Content-Type": "application/x-www-form-urlencoded",
    "Accept": "application/json"
}

auth_response = requests.post(auth_url, data=auth_data, headers=auth_headers)
access_token = auth_response.json().get("access_token")

# Delete multiple rows
api_url = "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/cells/rows?startrow=5&totalRows=3&updateReference=true"
headers = {
    "Content-Type": "application/json",
    "Accept": "application/json",
    "Authorization": f"Bearer {access_token}"
}

response = requests.delete(api_url, headers=headers)
print(json.dumps(response.json(), indent=2))

if response.status_code == 200:
    print("3 rows starting from row 5 deleted successfully!")
    # Expected output: rows 5, 6, and 7 will be removed
    # and subsequent rows will be shifted up
else:
    print(f"Error: {response.status_code}")

Step 13: Adjust Row Height

You can customize the height of rows to accommodate your data:

curl -v "https://api.aspose.cloud/v3.0/cells/test.xlsx/worksheets/Sheet1/ranges/rowHeight?value=25" \
-X POST \
-d "{ \"ColumnCount\": 5, \"FirstColumn\": 0, \"FirstRow\": 0, \"RowCount\": 5 }" \
-H "Content-Type: application/json" \
-H "Accept: application/json" \
-H "Authorization: Bearer YOUR_ACCESS_TOKEN"

This example sets the height of the first 5 rows to 25 points.

What You’ve Learned

In this tutorial, you’ve learned how to:

  • Retrieve information about rows in a worksheet
  • Add single and multiple rows
  • Hide and unhide rows
  • Group and ungroup rows for better organization
  • Copy rows between locations
  • Delete single and multiple rows
  • Adjust row heights for better presentation

Troubleshooting Tips

  • Row Index Out of Range: Make sure the row indices you specify are within the valid range for the worksheet.
  • Permission Issues: Some operations might fail if the worksheet is protected. Make sure you have the proper permissions.
  • Reference Updates: When adding or deleting rows, consider whether formula references should be updated. The updateReference parameter controls this behavior.
  • API Rate Limiting: If you receive 429 (Too Many Requests) responses, you might have exceeded your API quota.

Further Practice

To reinforce your learning, try these exercises:

  1. Create a script that creates a formatted data entry table with header rows
  2. Build a function that automatically hides empty rows in a data range
  3. Create a reporting template that groups related data rows
  4. Implement a batch operation to adjust the height of rows based on their content
  5. Build a data import utility that adds new rows while preserving existing data

Next Tutorial

Ready to continue your learning journey? Check out our tutorial on Working with Ranges in Excel to learn how to manipulate cell ranges, apply formatting, and more.

Helpful Resources