How To Get Cell Data From an Excel Spreadsheet Using APIs in Java

Our Excel spreadsheets hold a lot of valuable data in their dozens, hundreds, or even thousands of cells and rows.  With that much clean, formatted digital data at our disposal, it’s up to us to find programmatic methods for extracting and sharing that data among other important documents in our file ecosystem.

Thankfully, Microsoft made that extremely easy to do when they switched their file representation standard over to OpenXML more than 15 years ago.  This open-source XML-based approach drastically improved the accessibility of all Office document contents by basing their structure on well-known technologies – namely Zip and XML – which most software developers intimately understand.  Before that, Excel (XLS) files were stored in a binary file format known as BIFF (Binary Interchange File Format), and other proprietary binary formats were used to represent additional Office files like Word (DOC).

This change to an open document standard made it possible for developers to build applications that could interact directly with Office documents in meaningful ways. To get information about the structure of a particular Excel workbook, for example, a developer could write code to access xl/workbook.xml in the XLSX file structure and get all the workbook metadata they need. Similarly, to get specific sheet data, they could access xl/worksheets/(sheetname).xml, knowing that each cell and value with that sheet will be represented by simple <c> and <v> elements with all their relevant data nested within.  This is a bit of an oversimplification, but it serves to point out the ease of navigating a series of zipped XML file paths.

Given the global popularity of Excel files, building (or simply expanding) applications to load, manipulate, and extract content from XLSX was a no-brainer. There are dozens of examples of modern applications that can seamlessly load & manipulate XLSX files, and many even provide the option to export files in XLSX format.  

When we set out to build our applications to interact with Excel documents, we have several options at our disposal.  We can elect to write our code to sift through OpenXML document formatting, or we can download a specialized programming library, or we can alternatively call a specially designed web API to take care of a specific document interaction on our behalf.   The former two options can help us keep our code localized, but they’ll chew up a good amount of keyboard time and prove a little more costly to run.  With the latter option, we can offload our coding and processing overhead to an external service, reaping all the benefits with a fraction of the hassle.  Perhaps most beneficially, we can use APIs to save time and rapidly get our application prototypes off the ground.

Demonstration

In the remainder of this article, I’ll quickly demonstrate two free-to-use web APIs that allow us to retrieve content from specific cells in our XLSX spreadsheets in slightly different ways.  Ready-to-run Java code is available below to make structuring our calls straightforward.

Both APIs will return information about our target cell, including the cell path, cell text value, cell identifier, cell style index, and the formula (if any) used within that cell.   With the information in our response object, we can subsequently ask our applications to share data between spreadsheets and other open standard files for myriad purposes.  

Conveniently, both API requests can be authorized with the same free API key. It’s also important to note that both APIs process file data in memory and release that data upon completion of the request.  This makes both requests fast and extremely secure.

The first of these two API solutions will locate the data we want using the row index and cell index in our request.  The second solution will instead use the cell identifier (i.e., A1, B1, C1, etc.) for the same purpose.  While cell index and cell identifier are often regarded interchangeably (both locate a specific cell in a specific location within an Excel worksheet), using the cell index can make it easier for our application to adapt dynamically to any changes within our document, while the cell identifier will always remain static.

To use these APIs, we’ll start by installing the SDK with Maven.  We can first add a reference to the repository in pom.xml:

<repositories>
    <repository>
        <id>jitpack.io</id>
        <url>https://jitpack.io</url>
    </repository>
</repositories>

We can then add a reference to the dependency in pom.xml:

<dependencies>
<dependency>
    <groupId>com.github.Cloudmersive</groupId>
    <artifactId>Cloudmersive.APIClient.Java</artifactId>
    <version>v4.25</version>
</dependency>
</dependencies>

With installation out of the way, we can structure our request parameters and use ready-to-run Java code examples to make our API calls.  To retrieve cell data using the row index and cell index, we can format our request parameters like the application/JSON example below:

{
  "InputFileBytes": "string",
  "InputFileUrl": "string",
  "WorksheetToQuery": {
    "Path": "string",
    "WorksheetName": "string"
  },
  "RowIndex": 0,
  "CellIndex": 0
}

And we can use the below code to call the API once our parameters are set:

// Import classes:
//import com.cloudmersive.client.invoker.ApiClient;
//import com.cloudmersive.client.invoker.ApiException;
//import com.cloudmersive.client.invoker.Configuration;
//import com.cloudmersive.client.invoker.auth.*;
//import com.cloudmersive.client.EditDocumentApi;

ApiClient defaultClient = Configuration.getDefaultApiClient();

// Configure API key authorization: Apikey
ApiKeyAuth Apikey = (ApiKeyAuth) defaultClient.getAuthentication("Apikey");
Apikey.setApiKey("YOUR API KEY");
// Uncomment the following line to set a prefix for the API key, e.g. "Token" (defaults to null)
//Apikey.setApiKeyPrefix("Token");

EditDocumentApi apiInstance = new EditDocumentApi();
GetXlsxCellRequest input = new GetXlsxCellRequest(); // GetXlsxCellRequest | Document input request
try {
    GetXlsxCellResponse result = apiInstance.editDocumentXlsxGetCellByIndex(input);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentXlsxGetCellByIndex");
    e.printStackTrace();
}

To retrieve cell data using the cell identifier, we can format our request parameters like the application/JSON example below:

{
  "InputFileBytes": "string",
  "InputFileUrl": "string",
  "WorksheetToQuery": {
    "Path": "string",
    "WorksheetName": "string"
  },
  "CellIdentifier": "string"
}

We can use the final code examples below to structure our API call once our parameters are set:

// Import classes:
//import com.cloudmersive.client.invoker.ApiClient;
//import com.cloudmersive.client.invoker.ApiException;
//import com.cloudmersive.client.invoker.Configuration;
//import com.cloudmersive.client.invoker.auth.*;
//import com.cloudmersive.client.EditDocumentApi;

ApiClient defaultClient = Configuration.getDefaultApiClient();

// Configure API key authorization: Apikey
ApiKeyAuth Apikey = (ApiKeyAuth) defaultClient.getAuthentication("Apikey");
Apikey.setApiKey("YOUR API KEY");
// Uncomment the following line to set a prefix for the API key, e.g. "Token" (defaults to null)
//Apikey.setApiKeyPrefix("Token");

EditDocumentApi apiInstance = new EditDocumentApi();
GetXlsxCellByIdentifierRequest input = new GetXlsxCellByIdentifierRequest(); // GetXlsxCellByIdentifierRequest | Document input request
try {
    GetXlsxCellByIdentifierResponse result = apiInstance.editDocumentXlsxGetCellByIdentifier(input);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentXlsxGetCellByIdentifier");
    e.printStackTrace();
}

That’s all the code we’ll need.  With utility APIs at our disposal, we’ll have our projects up and running in no time.