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:
import org.apache.commons.io.FileUtils;
poi-3.9.jar:
requires for:
import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook;
poi-ooxml-3.9.jar:
requires for:
import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.WorkbookFactory;
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:
/* * 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); } } } }
Output:
Through java program: (Without double quotes)
Column 1,Column 2,Column 3,Column 4 row a2,row b2,row c2,row d2 row a3,row b3,row c3,row d3
Through Java Program: (With double quotes)
"Column 1","Column 2","Column 3","Column 4" "row a2","row b2","row c2","row d2" "row a3","row b3","row c3","row d3"
Through manual way: (.csv comma delimited, save as from .xlsx):
Column 1,Column 2,Column 3,Column 4 row a2,row b2,row c2,row d2 row a3,row b3,row c3,row d3
1,004 total views, 3 views today
Leave a Reply