package incheon.ags.aip.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.zip.ZipInputStream;

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.xssf.usermodel.XSSFRichTextString;
import org.egovframe.rte.fdl.string.EgovDateUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtil {
    /**
     * 파일 확장자 체크
     * @param file
     * @return 결과 값
     */
    public static boolean isXlsxFile(MultipartFile file) throws IOException {
        if (file == null || file.isEmpty()) return false;

        String fileName = file.getOriginalFilename();
        if (fileName == null || !fileName.toLowerCase().endsWith(".xlsx")) return false;

        try (InputStream is = file.getInputStream();
             ZipInputStream zis = new ZipInputStream(is)) {
            return zis.getNextEntry() != null;
        } catch (IOException e) {
            return false;
        }
    }
    
    /**
     * 엑셀의 셀값을 String 타입으로 변환하여 리턴한다.
     * @param cell
     * @return 결과 값
     */
	public static String getValue(Cell cell) {
	    if (cell == null) return "";

	    String result = "";
	    switch (cell.getCellType()) {
	        case BOOLEAN:
	            result = String.valueOf(cell.getBooleanCellValue());
	            break;

	        case ERROR:
	            break;

	        case FORMULA:
	            CellType formulaResultType = cell.getCachedFormulaResultType();
	            if (formulaResultType == CellType.STRING) {
	                result = cell.getStringCellValue();
	            } else if (formulaResultType == CellType.NUMERIC) {
	                result = DateUtil.isCellDateFormatted(cell)
	                    ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
	                    : doubleToString(cell.getNumericCellValue());
	            } else if (formulaResultType == CellType.BOOLEAN) {
	                result = String.valueOf(cell.getBooleanCellValue());
	            }
	            break;

	        case NUMERIC:
	            result = DateUtil.isCellDateFormatted(cell)
	                ? EgovDateUtil.toString(cell.getDateCellValue(), "yyyy/MM/dd", null)
	                : doubleToString(cell.getNumericCellValue());
	            break;

	        case STRING:
	            result = cell.getStringCellValue();
	            break;

	        case BLANK:
	            break;

	        default:
	            break;
	    }

	    return result;
	}

    /**
     * double 형의 셀 데이터를 String 형으로 변환하여 리턴한다.
     * @param d
     * @return 결과 값
     */
    public static String doubleToString(double d) {
        long lValue = (long) d;
        return (lValue == d) ? Long.toString(lValue) : Double.toString(d);
    }
}