converter

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);
	}
}