How to Convert XLSX to CSV in Java
Convert any Excel document to CSV with support for both XLSX and XLSB file formats, as well as legacy formats such as XLS.
CORE
·
Join the DZone community and get the full member experience.
Join For FreeWith design-focused formatting capabilities and a recognizable, approachable interface, it is no wonder that Excel is the leading spreadsheet software. Because it possesses the functionality to create both complex tables and graphs, users often utilize the application as a one-stop-shop for data entry and graphic design.
While many choose to use Excel as their main resource for data input, it is not always the ideal format when compiling and sharing that data with other programs. This is partially caused by the in-depth formatting that is so appealing to other users, as it cannot easily be converted to non-compatible applications. In these instances, it would be more useful to use a plain text version of your input data. This is where the Comma-Separated Values (CSV) format is most useful. CSV will show the data in stacked rows to represent each column in your average Excel file. This can then be easily read by other applications with no need to work around difficult formatting. For example, if a budgets officer receives an Excel spreadsheet tracking invoices, they can then convert this data to CSV to input more easily into their company databases. This will not only accelerate processing speeds but increase overall productivity as a result.
The following Convert APIs will allow you to instantly convert any Excel file to CSV, with support for the legacy format XLS. We will also show you how to convert multiple Excel worksheets at once to return separate CSV files. In performing this conversion process, our main goal is to preserve the organization of the data and remove clunky formatting that may hinder your data entry.
Our first API will show you how to convert a single XLSX spreadsheet to CSV in Java. We will need to install our library with a repository reference to Jitpack, as seen here:
xxxxxxxxxx
<repositories>
<repository>
<id>jitpack.io</id>
<url>https://jitpack.io</url>
</repository>
</repositories>
Then, we will add a reference to the dependency:
xxxxxxxxxx
<dependencies>
<dependency>
<groupId>com.github.Cloudmersive</groupId>
<artifactId>Cloudmersive.APIClient.Java</artifactId>
<version>v3.54</version>
</dependency>
</dependencies>
To install with Gradle, add it into your root build.gradle at the end of repositories:
xxxxxxxxxx
allprojects {
repositories {
...
maven { url 'https://jitpack.io' }
}
}
Then, add the dependency in build.gradle:
xxxxxxxxxx
dependencies {
implementation 'com.github.Cloudmersive:Cloudmersive.APIClient.Java:v3.54'
}
After this, we can add our imports to the top of the file and call our function, ConvertDocumentXlsxToCsv:
xxxxxxxxxx
// 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;
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");
ConvertDocumentApi apiInstance = new ConvertDocumentApi();
File inputFile = new File("/path/to/inputfile"); // File | Input file to perform the operation on.
String outputEncoding = "outputEncoding_example"; // String | Optional, set the output text encoding for the result; possible values are UTF-8, ASCII and UTF-32. Default is UTF-8.
try {
byte[] result = apiInstance.convertDocumentXlsxToCsv(inputFile, outputEncoding);
System.out.println(result);
} catch (ApiException e) {
System.err.println("Exception when calling ConvertDocumentApi#convertDocumentXlsxToCsv");
e.printStackTrace();
}
Your main input for this action should be an input file. Optionally, you can also set the output text encoding for the result. This can be UTF-8, ASCII and UTF-32, with a default of UTF-8. This will automatically take your specified spreadsheet and convert it to plain text in CSV.
To ensure that the API functions properly, you will need to verify that:
- Your input file is valid
- Your API Key has been properly inserted into the code block. This can be retrieved at no cost on the Cloudmersive website and will provide access to 800 monthly calls across our library of APIs.
If you need to convert multiple worksheets from one Excel file, the following API will be your best option. This can handle Excel documents of any size and will maintain the returned CSV files in the same order of the input file worksheets.
As shown above, you will need to first install the library using the same references. Then we can once again place our imports at the top of the file and call our function, ConvertDocumentXlsxToCsvMulti:
xxxxxxxxxx
// 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;
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");
ConvertDocumentApi apiInstance = new ConvertDocumentApi();
File inputFile = new File("/path/to/inputfile"); // File | Input file to perform the operation on.
String outputEncoding = "outputEncoding_example"; // String | Optional, set the output text encoding for the result; possible values are UTF-8, ASCII and UTF-32. Default is UTF-8.
try {
CsvCollection result = apiInstance.convertDocumentXlsxToCsvMulti(inputFile, outputEncoding);
System.out.println(result);
} catch (ApiException e) {
System.err.println("Exception when calling ConvertDocumentApi#convertDocumentXlsxToCsvMulti");
e.printStackTrace();
}
If you are working with a legacy version of Excel or need to convert an older file for current databasing, this last API will allow you to convert your XLS files to CSV. It follows the same initial steps as our two previous examples: install the library with a Jitpack reference in Maven and input our imports at the top of the file. After this, you can call our function, ConvertDocumentXlsToCsv:
// 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;
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");
ConvertDocumentApi apiInstance = new ConvertDocumentApi();
File inputFile = new File("/path/to/inputfile"); // File | Input file to perform the operation on.
try {
byte[] result = apiInstance.convertDocumentXlsToCsv(inputFile);
System.out.println(result);
} catch (ApiException e) {
System.err.println("Exception when calling ConvertDocumentApi#convertDocumentXlsToCsv");
e.printStackTrace();
}
If you have any questions about using these APIs or inquiries concerning other API solutions, you can visit the Cloudmersive website where our team is happy to help with anything you might need.
Opinions expressed by DZone contributors are their own.
Comments