package util;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.io.InputStream;

import java.util.ArrayList;

import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFRow;

import org.apache.poi.hssf.usermodel.HSSFSheet;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadExcel {

	public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";

    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static final String EMPTY = "";

    public static final String POINT = ".";

    public static final String NOT_EXCEL_FILE = " : Not the Excel file!";

    public static final String PROCESSING = "Processing...";

	public static List<ArrayList<String>> readExcel(String path,int sheelNum,int startRow,int cols){

		 if (path == null || EMPTY.equals(path)) {

	            return null;

	        } else {

	            String postfix = getPostfix(path);

	            if (!EMPTY.equals(postfix)) {

	                if (OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {

	                    return readXls(path,sheelNum,startRow,cols);

	                } else if (OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {

	                    return readXlsx(path,sheelNum,startRow,cols);

	                }

	            } else {

	                System.out.println(path + NOT_EXCEL_FILE);

	            }

	        }

	        return null;

	}

	 /**

     * Read the Excel 2003-2007

     * @param path the path of the Excel

     * @return

     * @throws IOException

     */

    public static List<ArrayList<String>> readXls(String path,int sheelNum,int startRow,int cols) {

        System.out.println(PROCESSING + path);

        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();

		try {

			InputStream is = new FileInputStream(path);

			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

			    HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(sheelNum);

			    if (hssfSheet == null) {

			        return list;

			    }

			    // Read the Row

			    for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

			        HSSFRow hssfRow = hssfSheet.getRow(rowNum);

			        if (hssfRow != null) {

			        	ArrayList<String> rowList = new ArrayList<String>();

			        	for (int col = 0; col < cols; col++) {

							rowList.add(hssfRow.getCell(col).getStringCellValue());

						}

			            list.add(rowList);

			        }

			    }

		} catch (FileNotFoundException e) {

			e.printStackTrace();

		} catch (IOException e) {

			e.printStackTrace();

		}

        return list;

    }

	 /**

     * Read the Excel 2010

     * @param path the path of the excel file

     * @return

     * @throws IOException

     */

    public static List<ArrayList<String>> readXlsx(String path,int sheelNum,int startRow,int cols) {

        System.out.println(PROCESSING + path);

        List<ArrayList<String>> list = new ArrayList<ArrayList<String>>();

        InputStream is;

		try {

			is = new FileInputStream(path);

			XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

	        // Read the Sheet

            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(sheelNum);

            if (xssfSheet == null) {

                return list;

            }

            // Read the Row

            for (int rowNum = startRow; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {

                XSSFRow xssfRow = xssfSheet.getRow(rowNum);

                if (xssfRow != null) {

                	ArrayList<String> rowList = new ArrayList<String>();

                	for (int col = 0; col < cols; col++) {

						rowList.add(xssfRow.getCell(col).getStringCellValue());

					}

                    list.add(rowList);

                }

            }

		} catch (FileNotFoundException e) {

			e.printStackTrace();

		}catch (IOException e) {

			e.printStackTrace();

		}

        return list;

    }

    /**

     * get postfix of the path

     * @param path

     * @return

     */

    private static String getPostfix(String path) {

        if (path == null || EMPTY.equals(path.trim())) {

            return EMPTY;

        }

        if (path.contains(POINT)) {

            return path.substring(path.lastIndexOf(POINT) + 1, path.length());

        }

        return EMPTY;

    }

}