How to get Microsoft Excel Spreadsheet Named Range data using Aspose.Cells REST API

Aspose.Cells Cloud Product Family
1

Hi,

I’m trying to read out the values of a range using the Cloud API.

How do I iterate through the range to get all the corresponding values of a name range, ex. =Sheet1!$A$3:$A$4 using the API?

API response -
{ "Ranges": { "RangeList": [ { "ColumnCount": 1, "ColumnWidth": 13.72, "FirstColumn": 0, "FirstRow": 2, "Name": "NamedRange1", "RefersTo": "=Sheet1!$A$3:$A$4", "RowCount": 2, "RowHeight": 15.75, "Worksheet": "Sheet1" } ] }, "Code": 200, "Status": "OK" }

Sample spreadsheet -

Thanks!

2

@alpeware

Thank you for contacting Aspose Support.

At present, there is no Cloud API that returns Cells data based on Named Range. We have an API that returns cell value based on Cell Name Aspose.Cells Cloud - API References

We have logged a request (CELLSCLOUD-10130) for our Development team to implement this API. We will update you here as soon as the requested API is implemented.

3

Great, thanks for your response!

As a more general question, is there any API call allowing me to retrieve multiple values besides the API call to get the value of a single cell you mentioned?

Thanks!

4

@alpeware

Please check the following API, it returns values of all Cells in the Excel file:
https://apireference.aspose.cloud/cells/#!/CellsWorkbook/CellsWorkbook_GetWorkBookTextItems

I asked API to return values of this Excel file Book1.xlsx.zip (22.8 KB) and it returns following response:

{
  "TextItems": {
    "TextItemList": [
      {
        "Text": "12",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A1",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "12",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B1",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "A",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C1",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "23",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A2",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "23",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B2",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "B",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C2",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "34",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A3",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "34",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B3",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "C",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C3",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "45",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A4",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "45",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B4",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "D",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C4",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "56",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A5",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "56",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B5",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "E",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C5",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "67",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A6",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "67",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B6",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "F",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C6",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "78",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A7",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "78",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B7",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "G",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C7",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "89",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A8",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "89",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B8",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "H",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C8",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "90",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/A9",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "90",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/B9",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      },
      {
        "Text": "I",
        "link": {
          "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/worksheets/Sheet1/cells/C9",
          "Rel": "parent",
          "Title": null,
          "Type": null
        }
      }
    ],
    "link": {
      "Href": "http://api.aspose.cloud/v1.1/cells/Book1.xlsx/textitems",
      "Rel": "self",
      "Title": null,
      "Type": null
    }
  },
  "Code": 200,
  "Status": "OK"
}

P.S. As you can see from the attached JIRA task, our Development team is working on your requested API, we will update you soon as the requested API is implemented.

5

Thanks!

I’m dealing with large spreadsheets so looking for ways to limit number of text items returned.

Can you support either i) getting text items per sheet or ii) support pagination when returning the results?

6

@alpeware

Our Development team is working to return Cells Data based on Named Range. We believe this would serve your purpose. Or you want us to log a request for getting cells data per sheet too?

7

Data based on Named Range would satisfy my current use case.

Pagination was a general suggestion for dealing with calls returning potentially large data sets in general.

8

Thanks for sharing your suggestion. We have logged a request for our Development team to group cells data per sheet or per page.

9

@alpeware

We are pleased to inform you that we have added an API for getting Cells Data based on Named Range.

GET /cells/{name}/worksheets/{sheetName}/ranges/value

Please check API Reference guide for parameters details.

I have written following Postman example to help you understand the API better:

firstRow and firstColumn parameters are zero-indexed.