Created
August 27, 2018 12:39
-
-
Save derweise/4287d958af83d22aec982d640ccfb2dc to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
package excelcsvconverter; | |
import java.io.BufferedReader; | |
import java.io.File; | |
import java.io.FileInputStream; | |
import java.io.FileOutputStream; | |
import java.io.InputStreamReader; | |
import java.math.BigDecimal; | |
import java.text.SimpleDateFormat; | |
import java.util.Iterator; | |
import org.apache.poi.hssf.usermodel.HSSFDateUtil; | |
import org.apache.poi.poifs.filesystem.POIFSFileSystem; | |
import org.apache.poi.xssf.usermodel.XSSFCell; | |
import org.apache.poi.xssf.usermodel.XSSFRow; | |
import org.apache.poi.xssf.usermodel.XSSFSheet; | |
import org.apache.poi.xssf.usermodel.XSSFWorkbook; | |
public class ExcelCSVConverter { | |
/*** | |
* Date format used to convert excel cell date value | |
*/ | |
private static final String OUTPUT_DATE_FORMAT = "yyyy-MM-dd"; | |
/** | |
* Comma separated characters | |
*/ | |
private static final String CSV_SEPERATOR_CHAR = ","; | |
/** | |
* New line character for CSV file | |
*/ | |
private static final String NEW_LINE_CHARACTER = "\r\n"; | |
/** | |
* Convert CSV file to Excel file | |
* | |
* @param csvFileName | |
* @param excelFileName | |
* @throws Exception | |
*/ | |
public static void csvToEXCEL(String csvFileName, String excelFileName) throws Exception { | |
checkValidFile(csvFileName); | |
BufferedReader reader = new BufferedReader(new InputStreamReader(new FileInputStream(csvFileName))); | |
XSSFWorkbook myWorkBook = new XSSFWorkbook(); | |
FileOutputStream writer = new FileOutputStream(new File(excelFileName)); | |
XSSFSheet mySheet = myWorkBook.createSheet(); | |
String line = ""; | |
int rowNo = 0; | |
while ((line = reader.readLine()) != null) { | |
String[] columns = line.split(CSV_SEPERATOR_CHAR); | |
XSSFRow myRow = mySheet.createRow(rowNo); | |
for (int i = 0; i < columns.length; i++) { | |
XSSFCell myCell = myRow.createCell(i); | |
myCell.setCellValue(columns[i]); | |
} | |
rowNo++; | |
} | |
myWorkBook.write(writer); | |
myWorkBook.close(); | |
reader.close(); | |
writer.close(); | |
} | |
/** | |
* Convert the Excel file data into CSV file | |
* | |
* @param excelFileName | |
* @param csvFileName | |
* @throws Exception | |
*/ | |
public static void excelToCSV(String excelFileName, String csvFileName) throws Exception { | |
checkValidFile(csvFileName); | |
XSSFWorkbook myWorkBook = new XSSFWorkbook(new FileInputStream(excelFileName)); | |
XSSFSheet mySheet = myWorkBook.getSheetAt(0); | |
Iterator rowIter = mySheet.rowIterator(); | |
String csvData = ""; | |
while (rowIter.hasNext()) { | |
XSSFRow myRow = (XSSFRow) rowIter.next(); | |
for (int i = 0; i < myRow.getLastCellNum(); i++) { | |
csvData += getCellData(myRow.getCell(i)); | |
} | |
csvData += NEW_LINE_CHARACTER; | |
} | |
writeCSV(csvFileName, csvData); | |
myWorkBook.close(); | |
} | |
/** | |
* Write the string into a text file | |
* | |
* @param csvFileName | |
* @param csvData | |
* @throws Exception | |
*/ | |
private static void writeCSV(String csvFileName, String csvData) throws Exception { | |
FileOutputStream writer = new FileOutputStream(csvFileName); | |
writer.write(csvData.getBytes()); | |
writer.close(); | |
} | |
/** | |
* Get cell value based on the excel column data type | |
* | |
* @param myCell | |
* @return | |
*/ | |
private static String getCellData(XSSFCell myCell) throws Exception { | |
String cellData = ""; | |
if (myCell == null) { | |
cellData += CSV_SEPERATOR_CHAR; | |
; | |
} else { | |
switch (myCell.getCellTypeEnum()) { | |
case STRING: | |
case BOOLEAN: | |
cellData += myCell.getRichStringCellValue() + CSV_SEPERATOR_CHAR; | |
break; | |
case NUMERIC: | |
cellData += getNumericValue(myCell); | |
break; | |
case FORMULA: | |
cellData += getFormulaValue(myCell); | |
default: | |
cellData += CSV_SEPERATOR_CHAR; | |
} | |
} | |
return cellData; | |
} | |
/** | |
* Get the formula value from a cell | |
* | |
* @param myCell | |
* @return | |
* @throws Exception | |
*/ | |
private static String getFormulaValue(XSSFCell myCell) throws Exception { | |
String cellData = ""; | |
if (myCell.getCachedFormulaResultType() == XSSFCell.CELL_TYPE_STRING || myCell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) { | |
cellData += myCell.getRichStringCellValue() + CSV_SEPERATOR_CHAR; | |
} else if (myCell.getCachedFormulaResultType() == XSSFCell.CELL_TYPE_NUMERIC) { | |
cellData += getNumericValue(myCell) + CSV_SEPERATOR_CHAR; | |
} | |
return cellData; | |
} | |
/** | |
* Get the date or number value from a cell | |
* | |
* @param myCell | |
* @return | |
* @throws Exception | |
*/ | |
private static String getNumericValue(XSSFCell myCell) throws Exception { | |
String cellData = ""; | |
if (HSSFDateUtil.isCellDateFormatted(myCell)) { | |
cellData += new SimpleDateFormat(OUTPUT_DATE_FORMAT).format(myCell.getDateCellValue()) + CSV_SEPERATOR_CHAR; | |
} else { | |
cellData += new BigDecimal(myCell.getNumericCellValue()).toString() + CSV_SEPERATOR_CHAR; | |
} | |
return cellData; | |
} | |
private static void checkValidFile(String fileName) { | |
boolean valid = true; | |
try { | |
File f = new File(fileName); | |
if (!f.exists() || f.isDirectory()) { | |
valid = false; | |
} | |
} catch (Exception e) { | |
valid = false; | |
} | |
if (!valid) { | |
System.out.println("File doesn't exist: " + fileName); | |
System.exit(0); | |
} | |
} | |
public static void main(String[] args) throws Exception { | |
String excelfileName1 = "/Users/u2007750/Downloads/20171206_M101_Gemeinde_Höchst_per_Liste.xlsx"; | |
String csvFileName1 = "/Users/u2007750/Downloads/20171206_M101_Gemeinde_Höchst_per_Liste.csv"; | |
//String excelfileName2 = "D:\\stephen\\files\\excel-file2.xls"; | |
//String csvFileName2 = "D:\\stephen\\files\\csv-file2.csv"; | |
excelToCSV(excelfileName1, csvFileName1); | |
//csvToEXCEL(csvFileName2, excelfileName2); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment