package com.fp.excel;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

/**
 *
 * @author malmeida
 */
public class ExcelToMap implements ExcelToMapMessages {

    private WorkbookFactory wf;
    private WorkbookFactoryStreamBytes wfsb;

    public ExcelToMap(String file_Path) {
        this.wf = new WorkbookFactory(file_Path);
    }

    /**
     *
     * @param arrayBytesExcel
     * @param extension
     */
    public ExcelToMap(byte[] arrayBytesExcel, String extension) {
        this.wfsb = new WorkbookFactoryStreamBytes(arrayBytesExcel, extension);
    }

    /**
     *
     * @return
     */
    public String get_Workbook_Status() {
        return this.wf.get_WorkbookFactory_Status();
    }

    public String getWorkbookStatusByte() {
        return this.wfsb.getWorkbookFactoryStatus();
    }

    /**
     *
     * @return
     */
    public Map<String, Object> getExcelMap() {
        String workbookStatus = this.wfsb.getWorkbookFactoryStatus();
        if (workbookStatus.equals(ExcelToMapMessages.WORKBOOK_CREATED)) {
            Workbook wb = wfsb.getExcelWorkBook();
            FormulaEvaluator fev = wb.getCreationHelper().createFormulaEvaluator();
            int activeSheetIndex = wb.getActiveSheetIndex();
            String activeSheetName = wb.getSheetName(activeSheetIndex);
            Sheet activeSheet = wb.getSheet(activeSheetName);
            Map<String, Object> cellList = new HashMap<String, Object>();
            for (Row row : activeSheet) {
                for (Cell cell : row) {
                    int cell_Row_Index = cell.getRowIndex();
                    int cell_Colum_Index = cell.getColumnIndex();
                    String MapKey = "" + cell_Row_Index + ":" + cell_Colum_Index;
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BOOLEAN:
//                 System.out.println(MapKey);
                            String cell_value_boolean_str = cell.getStringCellValue();
                            Object bool_obj = Boolean.parseBoolean(cell_value_boolean_str);
                            cellList.put(MapKey, bool_obj);
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
//                     System.out.println(MapKey);
                                Date cellValue_date = cell.getDateCellValue();
                                long cellValue_long = cellValue_date.getTime();
                                java.sql.Date sqlDate = new java.sql.Date(cellValue_long);
                                Object date_obj = sqlDate;
                                cellList.put(MapKey, date_obj);
                            } else {
//                     System.out.println(MapKey);
                                double cellValue_double = cell.getNumericCellValue();
                                Object cell_value_bd = new BigDecimal(cellValue_double);
                                cellList.put(MapKey, cell_value_bd);
                            }
                            break;
                        case Cell.CELL_TYPE_STRING:
//                 System.out.println(MapKey);
                            Object cell_value_str = cell.getStringCellValue();
                            cellList.put(MapKey, cell_value_str);
                            break;
                        case Cell.CELL_TYPE_BLANK:
//                 System.out.println(MapKey);
                            Object cell_value_blank_str = "";
                            cellList.put(MapKey, cell_value_blank_str);
                            break;
                        case Cell.CELL_TYPE_ERROR:
//                 System.out.println(MapKey);
                            Object cell_value_error_str = "";
                            cellList.put(MapKey, cell_value_error_str);
                            break;
                        case Cell.CELL_TYPE_FORMULA:
//                 System.out.println(MapKey);
                            CellValue cellValue = fev.evaluate(cell);

                            switch (cellValue.getCellType()) {
                                case Cell.CELL_TYPE_BOOLEAN:
//                                    System.out.println(MapKey);
                                    String cell_value_boolean_f_str = cell.getStringCellValue();
                                    Object cell_value_boolean_f = Boolean.parseBoolean(cell_value_boolean_f_str);
                                    cellList.put(MapKey, cell_value_boolean_f);
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    if (DateUtil.isCellDateFormatted(cell)) {
//                                        System.out.println(MapKey);
                                        Date cellValue_date = cell.getDateCellValue();
                                        long cellValue_long = cellValue_date.getTime();
                                        java.sql.Date sqlDate = new java.sql.Date(cellValue_long);
                                        Object date_obj = sqlDate;
                                        cellList.put(MapKey, date_obj);
                                    } else {
//                                        System.out.println(MapKey);
                                        double cellValue_double = cell.getNumericCellValue();
                                        Object cell_value_bd = new BigDecimal(cellValue_double);
                                        cellList.put(MapKey, cell_value_bd);
                                    }
                                    break;
                                case Cell.CELL_TYPE_STRING:
//                                    System.out.println(MapKey);    
                                    Object cell_value_str_f = cell.getStringCellValue();
                                    cellList.put(MapKey, cell_value_str_f);
                                    break;
                                case Cell.CELL_TYPE_BLANK:
//                                    System.out.println(MapKey);    
                                    Object cell_value_blank_str_f = "";
                                    cellList.put(MapKey, cell_value_blank_str_f);
                                    break;
                                case Cell.CELL_TYPE_ERROR:
//                                    System.out.println(MapKey);
                                    Object cell_value_error_str_f = "";
                                    cellList.put(MapKey, cell_value_error_str_f);
                                    break;
                                case Cell.CELL_TYPE_FORMULA:
//                                  NUNCA OCURRE
                                    break;
                                default:
//                                    System.out.println(MapKey);
                                    Object cell_value_indeterminado_str_f = "";
                                    cellList.put(MapKey, cell_value_indeterminado_str_f);
                                    break;
                            }
                            break;
                        default:
//                 System.out.println(MapKey);
                            Object cell_value_indeterminado_str = "";
                            cellList.put(MapKey, cell_value_indeterminado_str);
                            break;
                    }
                }
            }
            return cellList;
        } else {
            return null;
        }
    }

    /**
     *
     * @param fila_init
     * @param fila_final
     * @param colum_init
     * @param colum_final
     * @return
     */
    public List<Map<String, Object>> get_Especific_Data(int fila_init, int fila_final, int colum_init, int colum_final) {
        Map<String, Object> cell_list = this.getExcelMap();
        List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
        for (int i = fila_init; i <= fila_final; i++) {
            Map<String, Object> m = new HashMap<String, Object>();
            for (int j = colum_init; j <= colum_final; j++) {
                String Key = "" + i + ":" + j;
                if (cell_list.containsKey(Key)) {
                    Object obj = cell_list.get(Key);
                    m.put(Key, obj);
//                        System.out.println(obj.toString());            
                } else {
                    Object obj = "blank";
                    m.put(Key, obj);
//                        System.out.println(obj.toString());
                }
            }
            ldata.add(m);
        }

        return ldata;
    }
}