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]