Java: Convert Excel to CSV and Vice Versa

A CSV (Comma Separated Values) file is a plain text file that contains only text and numbers and you can create and edit it in Excel. Rather than storing information in columns, A CSV file stores information separated by commas. This article will show you how to convert Excel to CSV and convert CSV to Excel using Java codes.

DEPENDENCY

In this code sample, we’ll use a third-party library called Spire.XLS for Java. First of all, we can get the product package from the link, and add the Spire.Xls.jar file as a dependency in the Java program. Or create a Maven project and then add the following code to the pom.xml file to add the JAR file to the Java application.

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

Convert Excel to CSV

Spire.XLS for Java supports converting Excel to CSV with only several lines of codes. Here are steps to follow.

l  Create a Workbook instance.

l  Load a sample Excel document using Workbook.loadFromFile() method.

l  Get a specific worksheet of the document using Workbook.getWorksheets().get() method.

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

        //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

In addition to converting Excel to CSV, Free Spire.XLS for Java also supports converting CSV to Excel by using Workbook.saveToFile() method. In this process, we can set ignore error options when setting numbers in cells as text and can adjust the height of rows and width of columns too. The following are detailed steps for your reference.

l  Create a Workbook instance and load a sample CSV file using Workbook.loadFromFile() method.

l  Get a specific worksheet using Workbook.getWorksheets().get() method.

l  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<IgnoreErrorType> ignoreErrorOptions) method.

l  Automatically adjust the height of rows and width of columns using methods provided by CellRange class.

l  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 Xlsx
        workbook.saveToFile("output/CSVToExcel_out.xlsx", ExcelVersion.Version2013);
    }
}