DEV Community

lu liu
lu liu

Posted on

Converting CSV to Excel in Java: A Comprehensive Tutorial with Spire.XLS

Converting data between formats is a common task in software development, and one recurring need is transforming Comma Separated Values (CSV) files into the more structured and versatile Excel format. Developers often face challenges with data manipulation, reporting, and ensuring compatibility across various systems. This tutorial will guide you through the process of converting CSV to Excel in Java, leveraging the capabilities of the Spire.XLS for Java library. We'll explore practical examples, from single file conversions to consolidating multiple CSVs into a single, multi-sheet Excel workbook.

Introduction to Spire.XLS for Java and Setup

Spire.XLS for Java is a powerful and efficient library designed for creating, reading, editing, and converting Excel files in Java applications. It offers a comprehensive set of features for handling various Excel operations without requiring Microsoft Office or any external installations. From basic data manipulation to complex charting and formatting, Spire.XLS provides developers with robust tools. Before we dive into the conversion process, let's set up our Java project to include the Spire.XLS dependency.

To integrate Spire.XLS for Java into your project, you'll need to add its dependency. If you're using Maven, include the following in 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.12.15</version>
    </dependency>
</dependencies>
Enter fullscreen mode Exit fullscreen mode

After adding the dependency, synchronize your project to download the necessary libraries. You are now ready to start converting your CSV files.

Converting a Single CSV File to Excel

Converting a single CSV file to an Excel workbook is a straightforward process with Spire.XLS. The library handles the parsing of CSV data and writes it directly into an Excel worksheet. Let's look at a detailed example.

Here's the Java code to convert a CSV into an Excel file named output.xlsx:

import com.spire.xls.*;

public class CsvToXlsx {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.csv", ",");
        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);
    }
}
Enter fullscreen mode Exit fullscreen mode

In this code:

  • new Workbook() creates an empty Excel workbook object.
  • workbook.loadFromFile(inputCsvFile, ",", 1, 1) is the core method that reads the CSV. The "," specifies the comma as the delimiter.

- workbook.saveToFile(outputExcelFile, ExcelVersion.Version2016) saves the workbook as an .xlsx file. You can choose different ExcelVersion enumerations based on your requirements (e.g., ExcelVersion.Version97To2003 for .xls).

Converting Multiple CSV Files into a Single Excel File (with multiple sheets)

A more advanced scenario involves consolidating data from several CSV files into a single Excel workbook, with each CSV file occupying its own sheet. This is particularly useful for reporting or data aggregation.

Here's the Java code to combine these into yearly_sales.xlsx, with each CSV on a separate sheet:

import com.spire.xls.*;
import java.io.File;

public class CsvToXlsx {
    public static void main(String[] args) {
        // Get the CSV file names
        File[] csvFiles = new File("CSVs/").listFiles((dir, name) -> name.endsWith(".csv"));
        // Create a workbook and clear all worksheets
        Workbook workbook = new Workbook();
        workbook.getWorksheets().clear();

        for (File csv : csvFiles) {
            // Load the CSV file
            Workbook temp = new Workbook();
            temp.loadFromFile(csv.getAbsolutePath(), ",");
            // Append the CSV file to the workbook as a worksheet
            workbook.getWorksheets().addCopy(temp.getWorksheets().get(0));
        }

        // Save the workbook
        workbook.saveToFile("merged.xlsx", ExcelVersion.Version2016);
    }
}
Enter fullscreen mode Exit fullscreen mode

In this example:

  • We initialize an empty Workbook and immediately clear() its default sheet.
  • We iterate through an array of CSV file paths.
  • For each CSV, workbook.getWorksheets().addCopy() creates a new sheet, using the CSV file name (without .csv extension) as the sheet name.
  • Finally, call the saveToFile() method to save the Excel file.

This method provides a robust way to aggregate data from disparate CSV sources into a single, organized Excel document, making data analysis and sharing much simpler.

Advanced Considerations: Styling and Data Types

While Spire.XLS for Java efficiently handles basic CSV to Excel conversion, you might encounter scenarios where more control over styling and data types is required.

For instance, to make the header row bold in our single CSV conversion example, you could add the following lines after loading the CSV:

// Access the first row (header) 
CellRange headerRow = sheet.getCellRange(1, 1, 1, sheet.getLastColumn()); headerRow.getStyle().getFont().isBold(true);
Enter fullscreen mode Exit fullscreen mode

Regarding data types, Spire.XLS generally infers data types during CSV loading. However, if you need to explicitly set a column to a specific format (e.g., a number column that might be loaded as text), you can do so:

// Assuming column B contains numbers that should be formatted as currency 
sheet.getCellRange("B2:B" + sheet.getLastRow()).setNumberFormat("$#,##0.00");
Enter fullscreen mode Exit fullscreen mode

These advanced features allow for greater customization, ensuring that your converted Excel files are not just data containers but also well-formatted and easy to interpret.

Conclusion

In summary, converting CSV files to Excel in Java is a common and essential task for many developers. The Spire.XLS for Java library provides a comprehensive and efficient solution for this, simplifying complex data transformations into just a few lines of code. We've explored how to set up the library, convert a single CSV file, and even consolidate multiple CSVs into a single Excel workbook with distinct sheets. By leveraging Spire.XLS, developers can effectively manage data compatibility and reporting needs, saving valuable development time and ensuring data integrity. We encourage you to explore the extensive capabilities of Spire.XLS further to unlock its full potential for your Java-based Excel manipulation tasks.

Top comments (0)