Java: Convert Excel to CSV and Vice Versa

As its name suggests, a Comma Separated Values (CSV) file is a plain text file containing only numbers and letters, usually separated by commas. It can be used for exchanging data between applications. This article demonstrates how to convert Excel to CSV and convert CSV to Excel using Spire.XLS for Java.

• Convert Excel to CSV
• Convert CSV to Excel

Install Spire.XLS for Java

First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's 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>14.11.0</version>
    </dependency>
</dependencies>
    

Convert Excel to CSV

Spire.XLS for Java supports converting Excel to CSV with only several lines of codes. To get started, follow these steps.

• Create a Workbook instance.
• Load a sample Excel document using Workbook.loadFromFile() method.
• Get a specific worksheet of the document using Workbook.getWorksheets().get() method.
• Save the worksheet to CSV using Worksheet.saveToFile() method.

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

Convert CSV to Excel

The following are detailed steps to convert CSV to Excel.

• Create a Workbook instance and load a sample CSV file using Workbook.loadFromFile() method.
• Get a specific worksheet using Workbook.getWorksheets().get() method.
• Specify the cell range using Worksheet.getCellRange() method and ignore errors when setting numbers in the cells as text using CellRange.setIgnoreErrorOptions (java.util.EnumSet ignoreErrorOptions) method.
• Automatically adjust the height of rows and width of columns using methods provided by CellRange class.
• Save the document to an XLSX file using Workbook.saveToFile() method.

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

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.