package com.madeu.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.FormulaEvaluator;
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;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import com.madeu.init.ServiceInitializeServlet;
@Component
public class ExcelUtil {

    private static String defaultPath;
    @Value("${file.upload-path}")
    public void setPath(String url) {
        defaultPath = url;
    }

    private static List<String[]> read(Workbook workbook, int cells) throws IOException{
        List<String[]> list = new ArrayList<String[]>();
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        for(int r=1;r<rows;r++){ // 0은 제목
            Row row = sheet.getRow(r);
            String[] str = new String[cells];
            for(int c=0;c<cells;c++){
                Cell cell = row.getCell(c);
                if(cell!=null){
                    if(cell.getCellType()== CellType.NUMERIC){
                        if (DateUtil.isCellDateFormatted(cell)){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            str[c] = sdf.format(cell.getDateCellValue());
                        } else{
                            str[c] = String.valueOf((int)cell.getNumericCellValue());
                        }
                    }else if(cell.getCellType()==CellType.STRING){
                        str[c] = cell.getStringCellValue();
                    }else if(cell.getCellType()==CellType.BOOLEAN){
                        str[c] = String.valueOf(cell.getBooleanCellValue());
                    }else if(cell.getCellType()==CellType.FORMULA){

                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        if(evaluator.evaluateFormulaCell(cell)==CellType.NUMERIC){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            double val = cell.getNumericCellValue();
                            str[c] = sdf.format(val);
                        }else if(evaluator.evaluateFormulaCell(cell)==CellType.STRING){
                            str[c] = cell.getStringCellValue();
                        }else if(evaluator.evaluateFormulaCell(cell)==CellType.BOOLEAN){
                            str[c] = String.valueOf(cell.getBooleanCellValue());
                        }
                    }
                }
            }
            if(null==str[0]||("").equals(str[0])||("null").equals(str[0])){

            }else{
                list.add(str);
            }
        }
        return list;
    }

    private static List<String[]> read(Workbook workbook, int startRow, int cells) throws IOException{
        List<String[]> list = new ArrayList<String[]>();
        Sheet sheet = workbook.getSheetAt(0);
        int rows = sheet.getPhysicalNumberOfRows();
        for(int r=startRow;r<rows;r++){ // 0은 제목
            Row row = sheet.getRow(r);
            String[] str = new String[cells];
            for(int c=0;c<cells;c++){
                Cell cell = row.getCell(c);
                if(cell!=null){
                    if(cell.getCellType()==CellType.NUMERIC){
                        if (DateUtil.isCellDateFormatted(cell)){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            str[c] = sdf.format(cell.getDateCellValue());
                        } else{
                            str[c] = String.valueOf((int)cell.getNumericCellValue());
                        }
                    }else if(cell.getCellType()==CellType.STRING){
                        str[c] = cell.getStringCellValue();
                    }else if(cell.getCellType()==CellType.BOOLEAN){
                        str[c] = String.valueOf(cell.getBooleanCellValue());
                    }else if(cell.getCellType()==CellType.FORMULA){

                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        if(evaluator.evaluateFormulaCell(cell)==CellType.NUMERIC){
                            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                            double val = cell.getNumericCellValue();
                            str[c] = sdf.format(val);
                        }else if(evaluator.evaluateFormulaCell(cell)==CellType.STRING){
                            str[c] = cell.getStringCellValue();
                        }else if(evaluator.evaluateFormulaCell(cell)==CellType.BOOLEAN){
                            str[c] = String.valueOf(cell.getBooleanCellValue());
                        }
                    }
                }
            }
            if(null==str[0]||("").equals(str[0])||("null").equals(str[0])){

            }else{
                list.add(str);
            }
        }
        return list;
    }

    public static List<String[]> readXls(MultipartFile file, int cells) throws Exception {
//        String defaultPath   = ServiceInitializeServlet.getDefaultFileStorePath();
        String defaultFolder = ServiceInitializeServlet.getDefaultFileStoreFolder();

        String path = FileStoreUtil.saveFile("excel", file);
        String newPath = defaultPath.concat(defaultFolder).concat(path);
        File f = new File(newPath);

        Workbook workbook = new HSSFWorkbook(new FileInputStream(f));
        return read(workbook, cells);
    }

    public static List<String[]> readXls(MultipartFile file, int startRow, int cells) throws Exception {
//        String defaultPath   = ServiceInitializeServlet.getDefaultFileStorePath();
        String defaultFolder = ServiceInitializeServlet.getDefaultFileStoreFolder();

        String path = FileStoreUtil.saveFile("excel", file);
        String newPath = defaultPath.concat(defaultFolder).concat(path);
        File f = new File(newPath);

        Workbook workbook = new HSSFWorkbook(new FileInputStream(f));
        return read(workbook, startRow, cells);
    }

    public static List<String[]> readXlsx(MultipartFile file, int cells) throws Exception {
//        String defaultPath   = ServiceInitializeServlet.getDefaultFileStorePath();
        String defaultFolder = ServiceInitializeServlet.getDefaultFileStoreFolder();

        String path = FileStoreUtil.saveFile("excel", file);
        String newPath = defaultPath.concat(defaultFolder).concat(path);
        File f = new File(newPath);

        Workbook workbook = new XSSFWorkbook(new FileInputStream(f));
        return read(workbook, cells);
    }

    public static List<String[]> readXlsx(MultipartFile file, int startRow, int cells) throws Exception {
//        String defaultPath   = ServiceInitializeServlet.getDefaultFileStorePath();
        String defaultFolder = ServiceInitializeServlet.getDefaultFileStoreFolder();
        String path = FileStoreUtil.saveFile("excel", file);
        String newPath = defaultPath.concat(defaultFolder).concat(path);
        File f = new File(newPath);

        Workbook workbook = new XSSFWorkbook(new FileInputStream(f));
        return read(workbook, startRow, cells);
    }
}
