Manipulate sparklines in Excel using Free Cloud API

Sparkline in excel image

Charts are one of the great mechanisms to visually represent data and when dealing with computational data inside excel files, graphs and charts play a pivotal role in data representation. However, sometimes we stumble upon a situation where we cannot accommodate a chart/graphs within an excel spreadsheet. Consequently, we require a smarter way to represent the data trend, and here’s when sparklines come into action.

Advantages of using Sparkline

Sparklines in Excel enable you to see trends and patterns within your data at a glance using minimal real estate on your dashboard. Most of the time, you’ll probably create a group of sparklines — one for each row or column of data. A worksheet can hold any number of sparkline groups. Excel remembers each group, and you can work with the group as a single unit.

The sparkline groups provide a very convenient mechanism to deal with a set of sparklines in certain groups. For example, you can select one sparkline in a group and then modify the formatting of all sparklines in the group. In order to facilitate our users programmatically dealing with excel worksheets, the working with sparkline groups has been further optimized in the latest release version of Aspose.Cells Cloud API.

Get sparkline group description

The fastest and easiest way to deal with documents is to use Cloud storage. Once you have uploaded the excel worksheet containing the sparkline group, you may easily get details pertinent to the group.

Getting JSON web authentication token

Our Cloud APIs and Cloud Storage are secured and can only be accessed by authorized users. Once you are authorized, you may easily perform read, upload, updated, or delete operations on documents available over cloud storage or available on a local system. In order to get the JWT token,

  • First signup for an account over Cloud dashboard
  • Then log-in to the Cloud dashboard and click the Applications menu from left
  • By default, you will have the First App on the page. Click to open the details
  • On the First App details page, you will find your personalized Client ID and Client Secret
  • Enter the following command on the command prompt to retrieve your JWT token.
curl -v "https://api.aspose.cloud/connect/token" \
-X POST \
-d "grant_type=client_credentials&client_id=xxxx-xxx-xxx-xxx-xxxx&client_secret=xxxxxxx" \
-H "Accept: application/json"

For further details, please visit JSON Web Token Authentication

cURL command

curl -X GET "https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>"

Try using API Reference

  • Open API Reference page for Aspose.Cells Cloud
  • Authenticate using a Client ID and Client-Server keys
  • Access GetWorksheetSparklineGroups API and click the Try it out button
  • Provide excel workbook name against name field (which is Sparkline.xlsx in our case)
  • Enter the name of the worksheet containing the Sparkline group against sheetName field (Sheet1 in our case)
  • Click the Execute button. The details are fetched and displayed under responses section

Request URL

https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups

The response will display all the details related to color, preset, style, Type, LowPointColor, MarkersColor, etc. Please take a look over the following images showing a preview of an excel worksheet containing sparkline groups as well as information rendered in response.

Sparkline properties preview
Fig 1:- Preview of properties associated with Sparkline group.
Data range for vertical sparkline group
Fig 2:- Preview of data range for vertical sparkline group.
Horizontal sparkline data range
Fig 3:- Preview of data range for Horizontal sparkline group.
Sparkline group type
Fig 4:- Preview of sparkline group type.

Add Sparkline group description

Aspose.Cells Cloud also provides the capabilities to add Sparkline group description to existing excel worksheet. The PostWorksheetSparklineGroup requires name, sheetName, sparklineGroupIndex, sparklineGroup parameters to accomplish this requirement.

Update Sparkline group description

In order to update a sparkline to the existing worksheet, please try using PutWorksheetSparklineGroup API. The API requires the following parameters to fulfill the requirement

  • name :- Name of the workbook stored on cloud storage
  • sheetName :- Worksheet where you need to add the sparkline
  • type :- Sparkline type (Line, Column, Win/Loss)
  • dataRange :- The data range which we need to use as a source for sparkline
  • isVertical :- Is the sparkline vertical or not
  • locationRange :- The location where we need to draw the sparkline

cURL Command

curl -X PUT "https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups?type=Column&dataRange=B3%3AF9&isVertical=false&locationRange=G3%3AG9" \
-H  "accept: application/json" \
-H  "authorization: Bearer <JWT Token>"

In the above scenario, the type of sparkline is specified as Column (updated from Line to Column), dataRange is specified as B3:F9, isVertical is specified as False and locationRange is specified as G3:G9.

Request URL

https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups?type=Column&dataRange=B3%3AF9&isVertical=false&locationRange=G3%3AG9
Sparkline type update preview
Fig 5:- Preview of sparkline type updated.

Delete sparkline group description

Aspose.Cells Cloud also provides a quick mechanism to delete the sparkline group description. In order to remove all the sparkline groups, please try using DeleteWorksheetSparklineGroups API. Whereas if we need to remove the sparkline group based on a specific index ID, we need to use DeleteWorksheetSparklineGroup.

cURL command

curl -X DELETE "https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups/1" \
-H "accept: application/json" \
-H "authorization: Bearer <JWT Token>"

Request URL

https://api.aspose.cloud/v3.0/cells/Sparkline.xlsx/worksheets/Sheet1/sparklineGroups/1

After successful execution, 200 response code is displayed in response body. The following image shows the removed vertical sparkline group from excel worksheet. Whereas the other horizontal sparkline group remains in worksheet because only index 1 group is removed.

Vertical sparkline group removed
Fig 6:- Preview of Vertical sparkline group after deletion.

The article has explained the steps to get, create, update, and delete using Cloud API and you may consider reading a related blog Manipulate Charts Pivot Tables and Sparklines using Cloud API. In case you have any related query, please feel free to contact via Free support forum. The source code of API can also be found over GitHub Free code repository.