package poi;

import java.io.FileInputStream;
import java.util.Calendar;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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.xssf.usermodel.XSSFWorkbook;

public class FormatAndReadCell {
	//Q: How to format the cell data in excel file for reading?
	
	public static void main(String[] args) {
		readFromExcel();
	}

	
	static void readFromExcel() {
		Workbook wb = null;
		Sheet sh = null;
		Row row = null;
		Cell cell = null;
		int rowCount = 0;
		int colCount = 0;
		String strData = null;
		FileInputStream fin = null;
		Calendar cal = null;
		String sDate = null;
		String sMonth = null;
		String sYear = null;
		try {
			fin = new FileInputStream("C:\\MorningBatch_Combined\\Sample.xlsx");
			wb = new XSSFWorkbook(fin);
			sh = wb.getSheet("MixedTypes");
			
			rowCount = sh.getPhysicalNumberOfRows();
			
			for(int r=0; r<rowCount; r++) {
				row = sh.getRow(r);
				
				colCount = row.getLastCellNum();
				
				for(int c=0; c<colCount; c++) {
					cell = row.getCell(c);
					
					//format the cell data
					if(cell==null || cell.getCellType()==CellType.BLANK) {
						strData = "";
					}
					else if(cell.getCellType()==CellType.STRING) {
						strData = cell.getStringCellValue();
					}
					else if(cell.getCellType()==CellType.BOOLEAN) {
						strData = String.valueOf(cell.getBooleanCellValue());
					}
					else if(cell.getCellType()==CellType.NUMERIC) {
						if(DateUtil.isCellDateFormatted(cell) == true) {
							double dt = cell.getNumericCellValue();
							cal = Calendar.getInstance();
							cal.setTime(DateUtil.getJavaDate(dt));
							
							//If day is <10, then prefix with zero
							if(cal.get(Calendar.DAY_OF_MONTH) <10) {
								sDate = "0" + cal.get(Calendar.DAY_OF_MONTH); 
							}else {
								sDate = String.valueOf(cal.get(Calendar.DAY_OF_MONTH));
							}
							
							//If month is <10, then prefix with zero
							if(cal.get(Calendar.DAY_OF_MONTH) <10) {
								sMonth = "0" + (cal.get(Calendar.MONTH)+1); 
							}else {
								sMonth = String.valueOf((cal.get(Calendar.MONTH)+1));
							}
							
							sYear = String.valueOf(cal.get(Calendar.YEAR));
							
							strData = sDate + "/" + sMonth +"/"+ sYear;
						}else {
							strData = String.valueOf(cell.getNumericCellValue());
						}
					}
					System.out.print(strData + "     ");
				}
				System.out.println();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally
		{
			try {
				fin.close();
				fin = null;
				cell = null;
				row = null;
				sh = null;
				wb.close();
				wb = null;
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}
