Convert Multiple CSV Files into a Single Excel XLSX Spreadsheet in Java

Assemble multi-worksheet Excel files from a series of CSV documents using a free API.

Comma Separated Value (CSV) files offer a rudimentary representation of tabular data - especially in comparison with Microsoft Excel’s XLSX, which offers anything but. XLSX files are zipped archives composed of rich text and media assets stored and referenced within a set of XML files and metadata files, whereas CSV files are nothing more than plain, unformatted text broken into rows by a comma delimiter.

Given the different advantages CSV and XLSX offer, converting data between these formats is common. We convert CSV to XLSX for access to robust data manipulation features and fine-tuned presentation capabilities, and we perform the opposite conversion to reduce the complexity of our data, diminishing its size and ensuring its compatibility with a much wider range of applications/systems.

In this tutorial, we’ll learn how to make efficient, multi-file CSV to XLSX conversions with a specialized API in Java. Using this API, we’ll be able to convert 10+ CSV files at a once, instantly generating an Excel document containing exactly one worksheet per each original CSV file.

Step 1: Install the SDK

To handle SDK installation, we’ll begin by adding the repositories block into our pom.xml file to fetch dependencies from JitPack:

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

Right after that, we’ll place the dependencies block within our pom.xml:

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

Step 2: Add the Import Classes

Next, at the top of our file, we’ll add the import statements provided below:

// 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.ConvertDocumentApi;

Step 3: Initialize the API Client & Configure API Key Authentication

With our next block of code, we’ll create an instance of the ApiClient, and we’ll obtain the ApiKeyAuth object before setting our API key. If we don’t currently have a Cloudmersive API key, we can get one for free by visiting the Cloudmersive website and creating a free account. Free accounts allow a limit of 800 API calls per month with zero commitments (that total resets at the beginning of each month).

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");

Step 4: Call the API Method

In our final step, we’ll use the below code to create an instance of the API, prepare our input files, specify worksheet names for our output XLSX document (this is optional), and pass the prepared file objects to the endpoint.

ConvertDocumentApi apiInstance = new ConvertDocumentApi();
File inputFile1 = new File("/path/to/inputfile"); // File | First input file to perform the operation on.
File inputFile2 = new File("/path/to/inputfile"); // File | Second input file to perform the operation on.
File inputFile3 = new File("/path/to/inputfile"); // File | Third input file to perform the operation on.
File inputFile4 = new File("/path/to/inputfile"); // File | Fourth input file to perform the operation on.
File inputFile5 = new File("/path/to/inputfile"); // File | Fifth input file to perform the operation on.
File inputFile6 = new File("/path/to/inputfile"); // File | Sixth input file to perform the operation on.
File inputFile7 = new File("/path/to/inputfile"); // File | Seventh input file to perform the operation on.
File inputFile8 = new File("/path/to/inputfile"); // File | Eighth input file to perform the operation on.
File inputFile9 = new File("/path/to/inputfile"); // File | Ninth input file to perform the operation on.
File inputFile10 = new File("/path/to/inputfile"); // File | Tenth input file to perform the operation on.
String worksheetNames = "worksheetNames_example"; // String | Optional; Specify the name of each CSV's worksheet in order, separated with commas (e.g. \"worksheet1,worksheet2,worksheet3\"). Defaults to the names of the input CSV files. Recommended when inputting the files directly, without file names.
try {
    byte[] result = apiInstance.convertDocumentCsvMultiToXlsx(inputFile1, inputFile2, inputFile3, inputFile4, inputFile5, inputFile6, inputFile7, inputFile8, inputFile9, inputFile10, worksheetNames);
    System.out.println(result);
} catch (ApiException e) {
    System.err.println("Exception when calling ConvertDocumentApi#convertDocumentCsvMultiToXlsx");
    e.printStackTrace();
}

We’ll then capture the returned byte array and write that to a new XLSX file. The try-catch block will handle exceptions for us and print detailed error messages (including stack traces) to the console.

Now we’re all done - no more code required! We can now easily automate batch CSV to XLSX conversions and consolidate that content into a single XLSX file.