How to Convert CSV to Excel in Java: Complete Guide

 CSV (Comma-Separated Values) files are commonly used for data exchange and storage due to their simplicity and lightweight nature. However, when it comes to data analysis, reporting, or sharing information, Excel files often offer more advantages. Excel provides enhanced formatting options and makes it easier to manipulate and present data effectively.

This article will guide you through several methods of converting CSV files to Excel using Java, ensuring a more efficient and streamlined approach for your development projects.

Prerequisites: Installing Required Dependencies

Before diving into the code, you’ll need to include the necessary Java libraries. For this guide, we will use the popular Spire.XLS library to perform CSV to Excel conversion. Here's the Maven dependency configuration:

<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>16.1.3</version>
    </dependency>
</dependencies>

Once you’ve added this dependency to your project, you can begin utilizing the provided API to manage Excel files.

Example 1: Simple CSV to Excel Conversion

For many cases, simply converting a CSV file to an Excel file is enough. This basic approach involves just a few lines of code and is perfect for quick tasks.

import com.spire.xls.*;

public class CsvToXlsx {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.csv", ",");  // Load the CSV file
        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);  // Save as Excel file
    }
}

Code Breakdown

• workbook.loadFromFile("data.csv", ",");: Loads the CSV file and specifies the comma delimiter.

• workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);: Saves the content into an Excel file.

This method is ideal for quickly converting CSV files to Excel without any additional formatting or features.

Example 2: Formatting the Excel Output

If you need to apply some formatting, such as customizing the header style or adjusting the column widths, this example demonstrates a more advanced conversion approach.

import com.spire.xls.*;

public class CsvToXlsx {
    public static void main(String[] args) {
        Workbook workbook = new Workbook();
        workbook.loadFromFile("data.csv", ",");  // Load the CSV file

        Worksheet sheet = workbook.getWorksheets().get(0);

        // Format the header row
        CellStyle headerStyle = workbook.getStyles().addStyle("Header");
        headerStyle.getFont().isBold(true);
        headerStyle.setKnownColor(ExcelColors.LightYellow);
        for (int col = 1; col <= sheet.getLastColumn(); col++) {
            sheet.getCellRange(1, col).setStyle(headerStyle);  // Apply header style
        }

        // Format the numeric column
        CellStyle numStyle = workbook.getStyles().addStyle("Numbers");
        numStyle.setNumberFormat("#,##0.00");
        sheet.getCellRange("B2:B100").setStyle(numStyle);  // Apply number format

        // Auto-fit all columns
        for (int i = 1; i <= sheet.getLastRow(); i++) {
            sheet.autoFitColumn(i);  // Auto-fit column width
        }

        workbook.saveToFile("formatted_output.xlsx", ExcelVersion.Version2013);  // Save as Excel file
    }
}

Code Breakdown

• Header Formatting: The header row is set to bold, and the background color is changed to light yellow to make it stand out.

• Numeric Column Formatting: The numeric column is formatted with thousand separators and two decimal places for easier readability.

• Auto-fit Columns: Column widths are automatically adjusted to fit the content, ensuring that data is displayed neatly in Excel.

This method is great for situations where data presentation is important, and you need to ensure that the output looks professional.

Example 3: Batch Processing Multiple CSV Files

If you have multiple CSV files that need to be merged into one Excel file, this example shows how to process several CSV files at once and combine them into a single Excel workbook.

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

public class CsvToXlsx {
    public static void main(String[] args) {
        // Get all CSV files from the folder
        File[] csvFiles = new File("CSVs/").listFiles((dir, name) -> name.endsWith(".csv"));
        // Create a new 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(), ",");
            // Add the CSV file to the workbook as a new worksheet
            workbook.getWorksheets().addCopy(temp.getWorksheets().get(0));
        }

        // Save the merged Excel file
        workbook.saveToFile("merged_output.xlsx", ExcelVersion.Version2016);
    }
}

Code Breakdown

• Batch Processing: The program scans the specified folder for all CSV files, loading each one individually.

• Merging Worksheets: Each CSV file’s data is added as a new worksheet to the same workbook.

• Saving the Merged File: Once all CSV files are processed, the combined data is saved into one Excel file.

This method is perfect for handling multiple CSV files and merging them into a single Excel workbook.

Conclusion: Why Convert CSV to Excel?

By now, you’ve seen how to easily convert CSV files into Excel files using Java. Whether you’re simply changing file formats, formatting the data, or processing multiple files, Java provides all the tools you need to efficiently handle CSV to Excel conversions.

You can choose the right method depending on your project’s requirements, ensuring seamless data processing and presentation. For further questions or suggestions, feel free to share your thoughts in the comments section!