Introduction: Why Convert Excel to CSV in Java?
Modern enterprise data processing demands seamless interoperability between various file formats. While spreadsheets are convenient for end-users, they are notoriously difficult for automated systems to parse efficiently. Excel files (.xlsx and .xls) are actually compressed zip containers packed with complex XML schemas, styling guidelines, formula chains, and heavy metadata. When parsing large-scale datasets, importing transactional files into database architectures, or supplying clean tables to analytical pipelines, raw tabular formats are significantly preferred.
This is why developing a robust excel to csv java parser is a vital skill. Converting complex workbook archives into simple, comma-separated values (CSV) strips away unnecessary presentation layers, leaving behind only pure, structured database-friendly entries. This results in faster processing speeds, vastly reduced memory footprints, and near-universal system compatibility.
However, writing a reliable converter is not as simple as dumping cell values into a text file. A production-grade tool must account for formatted cell values (like preserving specific dates, percentages, or localized currency symbols), properly escape commas and quotes within strings, and protect host servers from devastating memory crashes (OutOfMemoryError) when loading massive spreadsheets.
In this comprehensive guide, we will design a robust, clean, and highly optimized converter using the industry-standard Apache POI library. You will learn to navigate the core DOM parser APIs, build a high-performance event-driven streaming architecture for exceptionally large files, and establish a clean reverse processing utility.
1. Project Setup and Maven Dependencies
To build our java excel to csv utility, we will leverage the open-source Apache POI (Poor Obfuscation Implementation) framework. It is the most robust and widely supported library for reading, manipulating, and writing Microsoft Office documents within the Java ecosystem.
To process both older binary formats (.xls) and modern XML-based OpenXML formats (.xlsx), you need to declare the core poi and poi-ooxml modules. Add the following dependency declarations to your Maven project's pom.xml file:
<dependencies>
<!-- Core Apache POI for .xls (HSSF) format -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.3.0</version>
</dependency>
<!-- POI OOXML for .xlsx (XSSF) format -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.3.0</version>
</dependency>
<!-- Core Logging requirement for Apache POI -->
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.23.1</version>
</dependency>
</dependencies>
If you prefer Gradle for your project builds, append these lines directly to your build.gradle dependency block:
dependencies {
implementation 'org.apache.poi:poi:5.3.0'
implementation 'org.apache.poi:poi-ooxml:5.3.0'
implementation 'org.apache.logging.log4j:log4j-core:2.23.1'
}
With our development workspace configured, let's explore how to design a general-purpose, DOM-based excel to csv converter java utility.
2. Converting Excel to CSV (DOM Approach for Small to Medium Files)
For spreadsheets smaller than 10 to 15 megabytes, loading the file using the DOM (Document Object Model) pattern is highly efficient. Using Apache POI’s WorkbookFactory, we can transparently load both legacy .xls binary formats and modern .xlsx XML collections into memory without needing separate, hardcoded conditional checks.
The Critical Pitfall of Raw Formatting
A very common developer mistake is parsing cell values using raw string conversions (like calling cell.toString()) or querying strict types directly (such as cell.getNumericCellValue()). This introduces several broken behaviors in your output:
- Dates are printed as plain floating-point offsets (representing Excel serial days, e.g., showing
46161.0instead of2026-05-23). - Currencies lose their formatting symbols (e.g., losing the primary symbol identifier).
- Floats and Integers acquire unwanted decimal zeroes (e.g., a simple user ID of
23412is exported as23412.0).
To prevent this format degradation, we employ POI's DataFormatter class. This component reads the cell's underlying format configuration and generates a string representation matching exactly what a human editor sees inside the Excel application UI.
Architectural Tip: Files vs. InputStreams
When instantiating a workbook, always prefer passing a File object over an InputStream to the WorkbookFactory.create() method. When you load a standard stream, POI is forced to buffer the entire file payload into heap memory. By using a local File reference, Apache POI can memory-map parts of the underlying file package directly, lowering the memory footprint significantly.
Correctly Escaping CSV Columns
Standard CSV specification dictates that if any column value contains a comma (,), double quotes ("), or line breaks (\n or \r), the entire entry must be wrapped inside double quotes. Furthermore, any literal double quotes located within that text must be escaped by doubling them up (""). We will implement a highly optimized, character-level escaping helper to make our exporter standard-compliant.
Code Implementation: Standard Excel to CSV Exporter
Here is a complete, production-ready class designed to convert excel to csv in java:
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.nio.charset.StandardCharsets;
public class StandardExcelToCsv {
/**
* Converts a specific index sheet of an Excel workbook to a clean CSV file.
* Supports both .xls and .xlsx formats seamlessly.
*/
public static void convertExcelToCsv(String excelFilePath, String csvFilePath, int sheetIndex) {
File inputFile = new File(excelFilePath);
File outputFile = new File(csvFilePath);
DataFormatter formatter = new DataFormatter();
try (Workbook workbook = WorkbookFactory.create(inputFile);
BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(
new FileOutputStream(outputFile), StandardCharsets.UTF_8))) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
int maxColumns = getMaxColumns(sheet);
for (Row row : sheet) {
StringBuilder rowBuilder = new StringBuilder();
// Loop through all column cells up to maxColumns to guarantee flat table structures
for (int colIndex = 0; colIndex < maxColumns; colIndex++) {
Cell cell = row.getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
// Retrieve cell value formatted precisely as presented in Excel
String cellValue = formatter.formatCellValue(cell);
rowBuilder.append(escapeCsvField(cellValue));
if (colIndex < maxColumns - 1) {
rowBuilder.append(",");
}
}
writer.write(rowBuilder.toString());
writer.newLine();
}
System.out.println("Successfully converted sheet: " + sheet.getSheetName());
} catch (IOException e) {
System.err.println("Failed to convert Excel to CSV: " + e.getMessage());
e.printStackTrace();
}
}
/**
* Analyzes the active sheet to find the maximum horizontal column index.
*/
private static int getMaxColumns(Sheet sheet) {
int maxColumns = 0;
for (Row row : sheet) {
if (row.getLastCellNum() > maxColumns) {
maxColumns = row.getLastCellNum();
}
}
return maxColumns;
}
/**
* Clean, robust character-level CSV escaping to handle commas, quotes, and newlines.
*/
private static String escapeCsvField(String field) {
if (field == null || field.isEmpty()) {
return "";
}
boolean mustQuote = false;
for (int i = 0; i < field.length(); i++) {
char c = field.charAt(i);
if (c == ',' || c == '"' || c == '\n' || c == '\r') {
mustQuote = true;
break;
}
}
StringBuilder sb = new StringBuilder();
if (mustQuote) {
sb.append('"');
}
for (int i = 0; i < field.length(); i++) {
char c = field.charAt(i);
if (c == '"') {
sb.append('"').append('"');
} else {
sb.append(c);
}
}
if (mustQuote) {
sb.append('"');
}
return sb.toString();
}
public static void main(String[] args) {
String inputPath = "data/quarterly_report.xlsx";
String outputPath = "data/quarterly_report.csv";
// Convert the very first tab of our sheet workbook
convertExcelToCsv(inputPath, outputPath, 0);
}
}
This simple DOM-based approach performs brilliantly for the vast majority of standard office documents, implementing java convert xls to csv and java convert xlsx to csv cleanly. However, if your cloud environments need to process 100MB+ spreadsheets containing hundreds of thousands of entries, this script will quickly exhaust your server's RAM. Let's look at how to scale this safely.
3. High-Performance Streaming for Massive XLSX Files (Avoiding Memory Crashes)
To construct a bulletproof excel to csv in java pipeline, developers must understand the structure of .xlsx files. The XML elements inside modern OpenXML formats are highly compressed. When you read a standard spreadsheet using XSSFWorkbook, Apache POI parses the compressed file, generating millions of Java objects to represent cells, styles, formulas, and formatting tables in memory. A 50MB file on disk can easily expand to use over 1.5GB of RAM in the Java Virtual Machine.
To safely process massive datasets, we must completely avoid loading the DOM tree. Instead, we must read the raw XML streams sequentially using event-driven SAX parsing. This keeps the JVM memory footprint steady at around 15MB, regardless of whether the file size is 1MB or 2GB.
The Architecture of Excel Streaming
In an .xlsx archive, actual cell data is decoupled from styles. To parse the file efficiently:
- ReadOnlySharedStringsTable: In modern Excel documents, strings are stored uniquely in a master XML table to prevent duplicate values. The row sheets only hold numeric pointers referencing this table. We must load this lightweight index to translate cell values on the fly.
- XSSFReader: This component allows us to open the zip package and retrieve direct, low-level XML input streams for separate worksheets.
- XSSFSheetXMLHandler: A SAX parser handler that processes rows element-by-element, firing callbacks as cells begin, complete, or finalize. We map these triggers directly into an outgoing file stream.
Code Implementation: Memory-Efficient XLSX Streaming
Below is an enterprise-ready implementation demonstrating how to convert xlsx to csv using java without memory issues:
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler.SheetContentsHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFComment;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.apache.poi.util.XMLHelper;
import java.io.*;
import java.nio.charset.StandardCharsets;
public class HighPerformanceXlsxToCsv {
private final OPCPackage xlsxPackage;
private final PrintStream output;
private final int minColumns;
public HighPerformanceXlsxToCsv(OPCPackage pkg, PrintStream output, int minColumns) {
this.xlsxPackage = pkg;
this.output = output;
this.minColumns = minColumns;
}
/**
* Inner class implementing the SheetContentsHandler interface to capture SAX XML tags.
*/
private class SheetToCSV implements SheetContentsHandler {
private boolean firstCellOfRow;
private int currentRow = -1;
private int currentCol = -1;
@Override
public void startRow(int rowNum) {
// Handle any completely skipped blank rows in the source file
outputMissingRows(rowNum - currentRow - 1);
firstCellOfRow = true;
currentRow = rowNum;
currentCol = -1;
}
@Override
public void endRow(int rowNum) {
// Add final commas for empty trailing columns
for (int i = currentCol; i < minColumns - 1; i++) {
output.print(',');
}
output.println();
}
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
if (firstCellOfRow) {
firstCellOfRow = false;
} else {
output.print(',');
}
// Resolve absolute column location to reconstruct skipped cells
int thisCol = (new org.apache.poi.ss.util.CellReference(cellReference)).getCol();
int missedCols = thisCol - currentCol - 1;
for (int i = 0; i < missedCols; i++) {
output.print(',');
}
currentCol = thisCol;
output.print(escapeCsvField(formattedValue));
}
private void outputMissingRows(int number) {
for (int i = 0; i < number; i++) {
for (int j = 0; j < minColumns; j++) {
output.print(',');
}
output.println();
}
}
private String escapeCsvField(String field) {
if (field == null) return "";
boolean mustQuote = false;
for (int i = 0; i < field.length(); i++) {
char c = field.charAt(i);
if (c == ',' || c == '"' || c == '\n' || c == '\r') {
mustQuote = true;
break;
}
}
StringBuilder sb = new StringBuilder();
if (mustQuote) sb.append('"');
for (int i = 0; i < field.length(); i++) {
char c = field.charAt(i);
if (c == '"') {
sb.append('"').append('"');
} else {
sb.append(c);
}
}
if (mustQuote) sb.append('"');
return sb.toString();
}
}
/**
* Triggers the sequential parsing of the workbook's primary worksheet data.
*/
public void process() throws Exception {
ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
StylesTable styles = xssfReader.getStylesTable();
XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
// In this implementation, we parse the very first sheet
if (iter.hasNext()) {
try (InputStream stream = iter.next()) {
InputSource sheetSource = new InputSource(stream);
XMLReader sheetParser = XMLHelper.newXMLReader();
DataFormatter formatter = new DataFormatter();
XSSFSheetXMLHandler handler = new XSSFSheetXMLHandler(
styles,
null,
strings,
new SheetToCSV(),
formatter,
false
);
sheetParser.setContentHandler(handler);
sheetParser.parse(sheetSource);
}
}
}
public static void main(String[] args) {
String inputFilePath = "data/extremely_large_file.xlsx";
String outputFilePath = "data/extremely_large_file.csv";
try (OPCPackage pkg = OPCPackage.open(inputFilePath, PackageAccess.READ);
PrintStream out = new PrintStream(new FileOutputStream(outputFilePath), true, StandardCharsets.UTF_8)) {
// Enforce a minimum width format to pad empty grid columns correctly
HighPerformanceXlsxToCsv converter = new HighPerformanceXlsxToCsv(pkg, out, 12);
converter.process();
System.out.println("Extremely large Excel file parsed and streamed to CSV successfully!");
} catch (Exception e) {
System.err.println("Conversion failed: " + e.getMessage());
e.printStackTrace();
}
}
}
This streaming class reads XML nodes sequentially, immediately formatting and writing completed lines to disk. Because JVM heap memory does not accumulate parsed cell representations, this provides a highly stable approach for enterprise data ingest systems.
4. Reverse Pipeline: Converting CSV to Excel Using Apache POI
In many applications, data pipelines must support two-way conversion. Converting modern flat files back into structured Excel files is just as critical. While we could use XSSFWorkbook for simple CSV imports, writing massive datasets can still trigger memory issues.
To address this, Apache POI offers SXSSFWorkbook (Streaming Extension of XSSFWorkbook). This architecture keeps a small, configurable sliding window of rows in active memory (e.g., retaining 100 rows). As new rows are added, older rows are automatically formatted and written directly to temporary disk files, allowing you to build large sheets with a minimal memory footprint.
Code Implementation: Memory-Efficient CSV to Excel Importer
Below is a highly performant utility designed to convert csv to excel using java poi:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;
public class CsvToExcelConverter {
/**
* Reads a CSV file and converts it into a structured modern .xlsx worksheet.
*/
public static void convertCsvToExcel(String csvPath, String excelPath) {
// SXSSFWorkbook restricts active RAM consumption by streaming rows directly to disk files
try (BufferedReader reader = new BufferedReader(new InputStreamReader(
new FileInputStream(csvPath), StandardCharsets.UTF_8));
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
FileOutputStream out = new FileOutputStream(excelPath)) {
Sheet sheet = workbook.createSheet("Imported CSV Data");
String line;
int rowNum = 0;
while ((line = reader.readLine()) != null) {
Row row = sheet.createRow(rowNum++);
List<String> fields = parseCsvLine(line);
for (int colNum = 0; colNum < fields.size(); colNum++) {
Cell cell = row.createCell(colNum);
String val = fields.get(colNum);
// Convert numeric text entries to actual doubles so Excel calculations work
if (isNumeric(val)) {
cell.setCellValue(Double.parseDouble(val));
} else {
cell.setCellValue(val);
}
}
}
workbook.write(out);
// Always invoke dispose to clear the temporary streaming XML files from the local OS storage
workbook.dispose();
System.out.println("CSV to Excel conversion completed successfully: " + excelPath);
} catch (IOException e) {
System.err.println("Conversion process experienced errors: " + e.getMessage());
e.printStackTrace();
}
}
private static boolean isNumeric(String str) {
if (str == null || str.trim().isEmpty()) return false;
try {
Double.parseDouble(str);
return true;
} catch (NumberFormatException e) {
return false;
}
}
/**
* A lightweight, robust parser to extract CSV values, handling internal quotes and commas.
*/
private static List<String> parseCsvLine(String line) {
List<String> fields = new ArrayList<>();
StringBuilder field = new StringBuilder();
boolean inQuotes = false;
for (int i = 0; i < line.length(); i++) {
char c = line.charAt(i);
if (c == '"') {
if (inQuotes && i + 1 < line.length() && line.charAt(i + 1) == '"') {
field.append('"');
i++; // Skip second escaped double quote
} else {
inQuotes = !inQuotes;
}
} else if (c == ',') {
if (inQuotes) {
field.append(c);
} else {
fields.add(field.toString());
field.setLength(0);
}
} else {
field.append(c);
}
}
fields.add(field.toString());
return fields;
}
public static void main(String[] args) {
String csvInput = "data/extremely_large_file.csv";
String excelOutput = "data/extremely_large_file_restored.xlsx";
convertCsvToExcel(csvInput, excelOutput);
}
}
Architectural Warning: Always call dispose()
When executing streaming writes using SXSSFWorkbook, POI writes row information to encrypted temporary disk files. These files are not cleaned up automatically by Java's Garbage Collector. Always invoke workbook.dispose() in a finally block (or via try-with-resources) to explicitly remove these temporary files and prevent server storage issues.
5. Frequently Asked Questions (FAQ)
How do I handle multiple sheets when converting Excel to CSV?
Because the CSV format is flat and single-sheet by design, you cannot export multi-tab Excel files into a single standard CSV file. The standard approach is to loop over each sheet index in your workbook (using workbook.getNumberOfSheets()) and write a separate, uniquely named CSV file for each (e.g., report_q1.csv, report_q2.csv).
Why are some dates formatting as numbers in my output CSV?
Excel stores dates as floating-point numbers representing the number of days elapsed since January 1, 1900. If you try to extract a cell value directly without formatting, you will retrieve this raw serial number (e.g., 46161.0). To preserve human-readable date formats, always use POI's DataFormatter class, which applies the cell's design styles automatically.
Can I customize the column separator (e.g., semicolon instead of comma)?
Yes, this is very easy to do. Simply replace the comma character in the string builder logic with your target character (such as a semicolon ; or a tab \t). Ensure you also adjust your escaping helper logic to check for this custom character instead of a comma before wrapping the field in double quotes.
Should I choose FastExcel over Apache POI?
For simple operations where raw execution speed is your only target, lightweight libraries like FastExcel can be faster because they skip the complex formatting rules of Excel. However, Apache POI remains the industry standard because of its comprehensive support for older binary formats (.xls), dynamic mathematical formulas, chart configurations, and extensive streaming libraries.
Is SAX XML streaming safe against XXE injection vulnerabilities?
Older versions of parser libraries were sometimes vulnerable to XML External Entity (XXE) attacks if a malicious Excel file was parsed (CVE-2016-5000). To avoid this, always use Apache POI version 5.0.0 or higher. Modern versions automatically disable external DTDs and entities in their internal parser configurations, securing your application by default.
Conclusion
Building a production-grade excel to csv java utility requires balancing formatting accuracy, file format support, and resource constraints. While standard DOM-based loaders are ideal for small files, event-driven SAX parsing is essential for handling massive datasets cleanly without memory issues.
By leveraging modern Apache POI APIs (WorkbookFactory and event-driven XSSFReader handlers), you can protect your enterprise applications from scaling bottlenecks. Use these optimized classes in your data pipelines, configure your delimiters, and ensure reliable, lightning-fast spreadsheet parsing.
![How to Convert XLSX to CSV Without Opening [5 Fast Ways]](https://blog.assetly.work/image/covers/_generic/04.webp)
