Seamless Data Transformation: Converting CSV to Excel and Excel to CSV in Java

Data management often involves moving information between different formats. Two of the most common are Comma Separated Values (CSV) and Microsoft Excel (XLSX/XLS). For Java developers, the need to convert CSV to Excel and convert Excel to CSV is a frequent requirement in applications ranging from reporting tools to data integration pipelines. This article provides a practical, step-by-step guide to tackling these java conversion challenges using a powerful and efficient library.

Introduction to Spire.XLS for Java & Setup

To effectively handle Excel and CSV conversions in Java, we'll leverage Spire.XLS for Java. This library is a robust and feature-rich API designed for creating, reading, writing, and converting Excel documents. It supports a wide range of Excel features, including charts, formulas, pivot tables, and, crucially for our purpose, seamless interaction with CSV files.

Adding Spire.XLS for Java Dependency

Integrating Spire.XLS into your Java project is straightforward. If you're using Maven, add the following dependency to your pom.xml file:

<repositories>
    <repository>
        <id>com.e-iceblue</id>
        <name>e-iceblue</name>
        <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
    </repository>
</repositories>
<dependencies>
    <dependency>
        <groupId>e-iceblue</groupId>
        <artifactId>spire.xls</artifactId>
        <version>15.9.1</version>
    </dependency>
</dependencies>

Once the dependency is added, you're ready to start converting!

Converting CSV to Excel in Java

Converting a CSV file to an Excel workbook is a common task when you need to apply formatting, use formulas, or share data in a more structured and interactive format. Spire.XLS for Java simplifies this process significantly.

Step-by-Step Guide: CSV to Excel

• Create a Workbook object: This object represents the Excel file.
• Load the CSV file: Use the loadFromFile() method, specifying the file path, delimiter (e.g., comma), and optionally, the starting row and column.
• Access the worksheet: The loaded CSV data will reside in the first worksheet by default.
• Save as Excel: Use the saveToFile() method, specifying the output file path and the desired Excel version.

Here’s a code example demonstrating how to convert CSV to Excel:

import com.spire.xls.*;
import java.util.EnumSet;

public class CSVToExcel {
    public static void main(String[] args) {
        //Create a workbook
        Workbook workbook = new Workbook();
        //Load a sample CSV file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\test.csv", ",", 1, 1);

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Specify the cell range and ignore errors when setting numbers in the cells as text
        sheet.getCellRange("A1:D6").setIgnoreErrorOptions(EnumSet.of(IgnoreErrorType.NumberAsText));

        //Automatically adjust the height of the rows and width of the columns
        sheet.getAllocatedRange().autoFitColumns();
        sheet.getAllocatedRange().autoFitRows();

        //Save the document to an XLSX file
        workbook.saveToFile("output/CSVToExcel_out.xlsx", ExcelVersion.Version2013);
    }
}

Considerations:

1. Delimiter: Ensure the delimiter provided to loadFromFile() matches your CSV file (e.g., , for comma, ; for semicolon).
2. Encoding: For CSV files with special characters, ensure the correct character encoding is used during loading if not UTF-8 by default.
3. Sheet Naming: By default, the CSV data will be loaded into the first sheet. You can rename it after loading if needed.

Converting Excel to CSV in Java

Converting Excel files to CSV is often necessary for data export, integration with other systems that prefer plain text, or simply to reduce file size. Spire.XLS for Java provides flexible options for this java conversion.

Step-by-Step Guide: Excel to CSV

• Create a Workbook object: Load your existing Excel file using loadFromFile().
• Select the worksheet: If your Excel file has multiple sheets, specify which one you want to convert.
• Save as CSV: Use the saveToFile() method on the Worksheet object, specifying the output file path, delimiter, and optionally, the character encoding.

Here’s a code example demonstrating how to convert Excel to CSV:

import com.spire.xls.*;
import java.nio.charset.Charset;

public class ExcelToCSV {
    public static void main(String[] args) {

        //Create a workbook
        Workbook workbook = new Workbook();

        //Load a sample excel file
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\sample.xlsx");

        //Calculate formulas if any 
        workbook.calculateAllValue();

        //Get the first sheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        //Save the document to CSV
        sheet.saveToFile("output/ToCSV_out.csv", ",", Charset.forName("UTF-8"));
    }
}

Considerations:

1. Selecting Specific Sheets: If you only need to convert a particular sheet, access it by workbook.getWorksheets().get(index) or workbook.getWorksheets().get(name).
2. Delimiter: Similar to CSV to Excel, ensure your output delimiter is correct.
3. Encoding: Specifying Charset.forName("UTF-8") is good practice for universal compatibility.
4. Quoting: Sometimes, you might need to enclose values in double quotes, especially if they contain delimiters. Spire.XLS allows controlling this behavior:

// Convert to CSV file with double quotes around values
// The third parameter 'true' indicates that values should be quoted
workbook.saveToFile("ToCSVAddQuotation.csv", ",", true);
System.out.println("Excel converted to CSV with double quotes.");

Conclusion

Efficiently handling data conversions between CSV and Excel is a fundamental skill for Java developers. With Spire.XLS for Java, these complex tasks become remarkably simple and robust. This article has guided you through the process of how to convert CSV to Excel and convert Excel to CSV with practical code examples. By leveraging this powerful library, you can streamline your data processing workflows and build more versatile Java applications. Explore the Spire.XLS documentation further for advanced features like formatting, styling, and handling larger datasets.