XLSX to CSV Convert in Java

XLSX to CSV Convert in Java:

We have realtime business scenarios to convert xlsx file to csv file using Java. When I searched for the existing solutions for this usecase I could find some program in github, but the solution can not be used directly, it requires some modifications. I have mentioned the github source code link below for reference. On top of the program taken from github I have modified little bit to make it accurate as possible and it works well for me without any issues.

Dependencies / Requirements:

commons-io-1.3.2.jar:
requires for:
[plain]
import org.apache.commons.io.FileUtils;
[/plain]

poi-3.9.jar:
requires for:
[plain]
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
[/plain]

poi-ooxml-3.9.jar:
requires for:
[plain]
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
[/plain]
Above poi jars internally requires/depends on below jars:
xmlbeans-2.3.0.jar [org/apache/xmlbeans/XmlOptions]
poi-ooxml-schemas-3.9.jar [org/openxmlformats/schemas/spreadsheetml/x2006/main/CTSheet]
dom4j-1.6.1.jar [org/dom4j/DocumentException]

Overall we require all the below jars:

  • commons-io-1.3.2.jar
  • poi-3.9.jar
  • poi-ooxml-3.9.jar
  • xmlbeans-2.3.0.jar
  • poi-ooxml-schemas-3.9.jar
  • dom4j-1.6.1.jar

Github Base source code Link: XLSX TO CSV CONVERT

XLSX to CSV Convert using Java Source code:

[java]
/*
* Dependencies: Apache POI Library from http://poi.apache.org/
*/
package in.javadomain;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.io.FileUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
*
* @author Munawwar + Javadomain.in
*/
public class Xlsx2CsvConvert {
static String COMMA_SEPARATED = “,”;
public static void echoAsCSV(Sheet sheet,String filePath) {
String fullString = “”;
Row row = null;
for (int i = 0; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);

// with double quotes – eg: “row a2″,”row b2″,”row c2″,”row d2”
/* for (int j = 0; j < row.getLastCellNum(); j++) {
if(j==row.getLastCellNum()-1){
if(fullString.isEmpty()){
fullString = “\”” + row.getCell(j)+”\””;
}else{
fullString = fullString + “\”” + row.getCell(j)+”\””+”\n”;
}
}
else{
if(fullString.isEmpty()){
fullString = “\”” + row.getCell(j) +”\””+ COMMA_SEPARATED;
}else{
fullString = fullString +”\”” + row.getCell(j) +”\””+ COMMA_SEPARATED;
}
}
}*/

// Without double quotes – eg: row a2,row b2,row c2,row d2 –> Recommended
for (int j = 0; j < row.getLastCellNum(); j++) {
if(j==row.getLastCellNum()-1){
if(fullString.isEmpty()){
fullString = “” + row.getCell(j)+””;
}else{
fullString = fullString + “” + row.getCell(j)+””+”\n”;
}
}
else{
if(fullString.isEmpty()){
fullString = “” + row.getCell(j) +””+ COMMA_SEPARATED;
}else{
fullString = fullString +”” + row.getCell(j) +””+ COMMA_SEPARATED;
}
}
}

}

try {
FileUtils.writeStringToFile(new File(filePath.replaceAll(“.xlsx”, “.csv”)), fullString);
} catch (IOException e) {
e.printStackTrace();
}
}

/**
* @param args the command line arguments
*/
//public static void main(String[] args) {
public static void main(String[] args) {
InputStream inp = null;
String filePath = “C:\\sample.xlsx”;
try {
inp = new FileInputStream(filePath);
Workbook wb = WorkbookFactory.create(inp);

for(int i=0;i<wb.getNumberOfSheets();i++) {
System.out.println(wb.getSheetAt(i).getSheetName());
echoAsCSV(wb.getSheetAt(i),filePath);
}
} catch (InvalidFormatException ex) {
Logger.getLogger(Xlsx2CsvConvert.class.getName()).log(Level.SEVERE, null, ex);
} catch (FileNotFoundException ex) {
Logger.getLogger(Xlsx2CsvConvert.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(Xlsx2CsvConvert.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
inp.close();
} catch (IOException ex) {
Logger.getLogger(Xlsx2CsvConvert.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
[/java]

Output:

Through java program: (Without double quotes)

[plain]
Column 1,Column 2,Column 3,Column 4
row a2,row b2,row c2,row d2
row a3,row b3,row c3,row d3
[/plain]

Through Java Program: (With double quotes)

[plain]
“Column 1″,”Column 2″,”Column 3″,”Column 4”
“row a2″,”row b2″,”row c2″,”row d2”
“row a3″,”row b3″,”row c3″,”row d3”
[/plain]

 

Through manual way: (.csv comma delimited, save as from .xlsx):

[plain]
Column 1,Column 2,Column 3,Column 4
row a2,row b2,row c2,row d2
row a3,row b3,row c3,row d3
[/plain]

 

 

One comment

  • This is an excellent article, it is clear and informative. It provides a clear explanation of how to use the Apache POI library to convert XLSX files to CSV files using Java. The code examples provided in the article are easy to follow and understand, making it a great resource for anyone looking to learn how to convert XLSX files to CSV. Thanks for the great article!

Leave a Reply