Convert Excel to CSV and Vice Versa in Java

CSV files are delimited text files in which values are separated by commas. They can be used with most spreadsheet programs, such as Microsoft Excel and Google Spreadsheets. Sometimes, you might need to do conversions between Excel and CSV. In this article, I will demonstrate how to convert Excel to CSV and CSV to Excel in Java using Spire.XLS for Java library.

Add Dependencies

To begin with, you need to add needed dependencies for including Spire.XLS for Java library into your Java project.
You can either download the library’s jar from the official website or install it from Maven by adding the following code to your maven-based 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>4.12.2</version>    
    </dependency>    
</dependencies>

Convert Excel to CSV using Java

You can convert an Excel worksheet to CSV or convert multiple worksheets to separate CSV files as per your requirement.

The following are the steps to convert an Excel worksheet to CSV:

• Create an instance of Workbook class.
• Load an Excel file using Workbook.loadFromFile() method.
• Get the desired worksheet by its index using Workbook.getWorksheets().get(index) method.

• Save the worksheet as CSV using XlsWorksheet.saveToFile(String fileName, String separator, Charset encoding) method. You may also choose one of the other two overloaded methods below:

  a. saveToFile(String fileName, String separator)

  b. saveToFile(String fileName, String separator, boolean retainHiddenData)
  

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.nio.charset.Charset;

public class ConvertAWorksheetToCsv {
    public static void main(String []args){
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

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

        //Save the worksheet as CSV
        sheet.saveToFile("ExcelToCSV.csv", ",", Charset.forName("UTF-8"));
    }
}

The above code saves only the first worksheet in the Excel file as CSV. If you want to save multiple worksheets in the Excel file as separate CSV files, use the following code.

import com.spire.xls.Workbook;
import com.spire.xls.Worksheet;

import java.nio.charset.Charset;

public class ConvertMultipleWorksheetsToCsv {
    public static void main(String []args) {
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();
        //Load an Excel file
        workbook.loadFromFile("Sample.xlsx");

        //Loop through the worksheets in the Excel file
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
            Worksheet sheet = workbook.getWorksheets().get(i);
            //Save each worksheet as a separate CSV
            sheet.saveToFile("Output/ExcelToCSV_" + i + ".csv", ",", Charset.forName("UTF-8"));
        }
    }
}

Convert CSV to Excel using Java

The following are the steps to convert a CSV to Excel:

• Create an instance of Workbook class.
• Load a CSV file using Workbook.loadFromFile(String fileName, String separator, int row, int column) method.
• Loop through the worksheets in the CSV file.
• Within the loop, access the used range of the current worksheet using Worksheet.getAllocatedRange() method. Then set ignore error option using XlsRange.setIgnoreErrorOptions() method. After that, autofit columns and rows using XlsRange.autoFitColumns() and XlsRange.autoFitRows() method.
• Save the CSV to Excel using Workbook.saveToFile(string fileName, ExcelVersion version) method.

import com.spire.xls.*;

import java.util.EnumSet;

public class ConvertCsvToExcel {
    public static void main(String []args) {
        //Create an instance of Workbook class
        Workbook workbook = new Workbook();

        //Load a CSV file
        workbook.loadFromFile("ExcelToCSV.csv", ",", 1, 1);

        //Loop through the worksheets in the CSV file
        for (int i = 0; i < workbook.getWorksheets().getCount(); i++)
        {
            Worksheet sheet = workbook.getWorksheets().get(i);
            //Access the used range in each worksheet
            CellRange usedRange = sheet.getAllocatedRange();
            //Ignore errors when saving numbers in the used range with text
            usedRange.setIgnoreErrorOptions(EnumSet.of(IgnoreErrorType.NumberAsText));
            //Autofit columns and rows
            usedRange.autoFitColumns();
            usedRange.autoFitRows();
        }

        //Save the result file
        workbook.saveToFile("CSVToExcel.xlsx", ExcelVersion.Version2013);
    }
}

See More

Product Page | Documentation | Forum |