How To Create and Edit Excel XLSX Documents in Java

For more than three decades – practically an eon in tech years – Excel has greased the wheels of businesses and aspiring individuals around the world.  In the US alone, more than 700,000 companies are estimated to use Excel, and worldwide the number of unique Excel users hovers at just over a billion – about 1/8^th of the global population.

However, with a fast-developing era of subscription-based, point-and-click enterprise SaaS data solutions well underway, it’s becoming clear that using Excel in the “traditional” sense is less obviously efficient than it once was.  Clicking on the Excel desktop icon, selecting “New Workbook” and formatting a unique document from a blank page or template is just enough of a resource commitment to put you behind the eight ball by today’s lightning-fast project delivery standards. The mundane, predictable nature of Excel’s usage has, for well over a decade, begged for and received the same form of intervention which much of the restless tech community constantly marches toward: automation.  

Thankfully, automating the creation and manipulation of standard Excel documents requires simple programmatic intervention.  Just about any operation you can perform within Excel documents manually can be performed programmatically with access to the right libraries.  Once those doors are opened, the process of customizing Excel documents and integrating data within them at scale becomes instantly more efficient (this approach reduces the natural element of human input error that we expect from manual data entry and spreadsheet formatting). If we know, for example, that a monthly report spreadsheet only requires up-to-date information from a consistent set of data sources, it’s ideal if we can configure those sources to write information directly to the file without a human being acting as the middleman.  

We don’t have to look far to find examples of modern, commonly used Excel editing libraries – the extremely popular Pandas library in Python, for example, natively supports programmatic Excel editing with a variety of useful functions. Since not all programming languages (like Java, for example) natively support Excel manipulation the way Python does, leveraging an external API is one great way to achieve Excel automation.  Especially for applications processing Excel files at scale, external API calls can deliver the required libraries for Excel file manipulation along with a welcome boost to overall efficiency by offloading the processing power for that task to a separate server.  

Demonstration

In the remainder of this article, I’ll demonstrate several free-to-use API solutions which are collectively designed for programmatically creating and editing Excel files securely and with ease. All require just a few lines of complementary, ready-to-run Java code (supplied below) along with a free-tier API key to complete your API call. The API solutions demonstrated in this article include the following:

1. Begin editing a document: This API initiates document upload to begin editing operations on existing Excel documents, returning a secure editing URL (which will cache for 30 minutes and cannot be used to directly access the document).
2. Create a blank Excel XLSX spreadsheet: This API returns the encoding for a blank Excel XLSX format file, along with its own editing URL.
3. Insert a new worksheet into an Excel XLSX spreadsheet: This API creates and inserts a new worksheet using the temporary editing URL from either an existing or programmatically generated XLSX file.
4. Set and update cell contents in an Excel XLSX spreadsheet, worksheet by cell identifier: This API allows content to be placed and formatted within specified cells in an Excel worksheet using a temporary editing URL.
5. Finish editing a document: This API finalizes document editing by processing the temporary editing URL and providing the encoding for the final, edited Excel file in return.

To summarize the above, you can securely edit existing Excel documents programmatically using the temporary editing URL generated via the “Begin Editing a Document” API call.  To create a new Excel document from scratch, you can call the “Create a Blank Excel XLSX Spreadsheet” API instead, which also generates its own version of a temporary editing URL.  

Once a temporary editing URL is generated in either scenario, you can subsequently insert new worksheets into your document or write information to specific cells by passing the URL through each API’s input request parameters.   

For both pre-existing and programmatically generated Excel documents, once the editing process is complete, the temporary editing URL must be processed in the “Finish Editing” stage to return the updated XLSX file encoding.  This final step is necessary to ensure your document’s security; the temporary editing URL cannot be used to access the document directly, and it will only stay cached for 30 minutes.

Below, I will provide code to structure each individual API call in Java.  To begin, however, you’ll first need to follow a few steps to install the Java SDK.

To install with Maven, first, add a reference to the repository in poml.xml:

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

And 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>

Alternatively, to install with Gradle, add it to your root build.gradle at the end of repositories:

allprojects {
	repositories {
		...
		maven { url 'https://jitpack.io' }
	}
}

And then add the dependency in build.gradle:

dependencies {
        implementation 'com.github.Cloudmersive:Cloudmersive.APIClient.Java:v4.25'
}

With installation complete, you can turn your attention to structuring each individual API call.

1. Begin Editing a Document

To begin the process of uploading and editing an existing Excel document based on its file path, first, call the “Begin Editing” API using the following code examples.  This will return a temporary editing URL:

// 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();
File inputFile = new File("/path/to/inputfile"); // File | Input file to perform the operation on.
try {
    String result = apiInstance.editDocumentBeginEditing(inputFile);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentBeginEditing");
    e.printStackTrace();
}

2. Create a Blank Excel XLSX Spreadsheet

To begin your programmatic editing process with a brand-new file, use the following code instead.   This will automatically generate a blank XLSX file and return a temporary URL:

// 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();
CreateBlankSpreadsheetRequest input = new CreateBlankSpreadsheetRequest(); // CreateBlankSpreadsheetRequest | Document input request
try {
    CreateBlankSpreadsheetResponse result = apiInstance.editDocumentXlsxCreateBlankSpreadsheet(input);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentXlsxCreateBlankSpreadsheet");
    e.printStackTrace();
}

3. Insert a New Worksheet Into an Excel Spreadsheet

To insert a new worksheet into your Excel document, use the code below to make your API call.  You will need to pass the temporary editing URL through the InputFileUrl request parameter, and specify the path and name of your new worksheet:

// 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();
InsertXlsxWorksheetRequest input = new InsertXlsxWorksheetRequest(); // InsertXlsxWorksheetRequest | Document input request
try {
    InsertXlsxWorksheetResponse result = apiInstance.editDocumentXlsxInsertWorksheet(input);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentXlsxInsertWorksheet");
    e.printStackTrace();
}

4. Set, Update Cell Contents in an Excel Spreadsheet, Worksheet by Cell Identifier

To go about setting cell contents in your Excel file, use the code below, and once again include the original file’s temporary editing URL when you make your API call.  The request parameters for this API include the option to specify worksheet, cell identifier, cell value, and more (enumerated in an example request body below). Within the cell value specification, you can include text contents in your specified cell(s), format styles based on style index, and/or configure formulas for that specific cell.

// 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();
SetXlsxCellByIdentifierRequest input = new SetXlsxCellByIdentifierRequest(); // SetXlsxCellByIdentifierRequest | Document input request
try {
    SetXlsxCellByIdentifierResponse result = apiInstance.editDocumentXlsxSetCellByIdentifier(input);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentXlsxSetCellByIdentifier");
    e.printStackTrace();
}

Please refer to the below example to properly structure your input request:

{
  "InputFileBytes": "string",
  "InputFileUrl": "string",
  "WorksheetToUpdate": {
    "Path": "string",
    "WorksheetName": "string"
  },
  "CellIdentifier": "string",
  "CellValue": {
    "Path": "string",
    "TextValue": "string",
    "CellIdentifier": "string",
    "StyleIndex": 0,
    "Formula": "string"
  }
}

5. Finish Editing a Document, Download Result From Document Editing

When your editing process is complete, call the “finish editing” operation using the code provided below.  You only need your file’s temporary editing URL for this operation, and it will return proper XLSX file encoding:

// 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();
FinishEditingRequest reqConfig = new FinishEditingRequest(); // FinishEditingRequest | Cloudmersive Document URL to complete editing on
try {
    byte[] result = apiInstance.editDocumentFinishEditing(reqConfig);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling EditDocumentApi#editDocumentFinishEditing");
    e.printStackTrace();
}

Once you’ve completed your “Finish Editing” API call, you can use the output XLSX file encoding to generate your new file.  

For your reference, the API solutions provided above are only a few of many document editing API iterations available through the Cloudmersive Convert API.  You may also programmatically rename worksheets, get specific columns from a worksheet, clear contents, get macro information, and much more using additional APIs (starting and ending with the “Begin Editing” and “Finish Editing” API calls).