package com.changhong.epc.rely.api.tool;

import com.github.andyczy.java.excel.LocalExcelUtils;
import com.iemsoft.framework.cloud.core.tools.ObjectUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Consumer;

public class ExcelTool {

    public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

    public static int importExcel(InputStream inputStream, String fileName, Consumer<Map<String, String>> consumer) throws IOException {
        Workbook workbook = getWorkBook(inputStream, fileName);
        if (workbook == null) {
            return 0;
        }
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null) {
            return 0;
        }
        Iterator<Row> rowIterator = sheet.rowIterator();
        // 略过汉字标题
        if(rowIterator.hasNext()) rowIterator.next();
        // 记录code标题
        String[] columns, values;
        if(rowIterator.hasNext()) {
            columns = getCallVals(rowIterator.next());
        }else{
            return 0;
        }
        Map<String, String> res;
        int line = 0;
        while (rowIterator.hasNext()){
            values = getCallVals(rowIterator.next());
            res = new HashMap<>();
            for (int i = 0, j = Math.min(columns.length, values.length); i < j; i++) {
                res.put(columns[i], values[i]);
            }
            consumer.accept(res);
            line++;
        }
        return line;
    }

    public static String[] getCallVals(Row row){
        Iterator<Cell> cellIterator = row.cellIterator();
        List<String> res = new LinkedList<>();
        while (cellIterator.hasNext()){
            Cell cell = cellIterator.next();
            res.add(convertCellToString(cell));
        }
        return res.stream().toArray(String[]::new);
    }

    public static String convertCellToString(Cell cell){
        //如果为null会抛出异常，应当返回空字符串
        if (cell == null)
            return "";

        //POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型
        //解决日期2006/11/02格式读入后出错的问题，POI读取后变成“02-十一月-2006”格式
        if(cell.toString().contains("-") && checkDate(cell.toString())){
            String ans;
            try {
                ans = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
            } catch (Exception e) {
                ans = cell.toString();
            }
            return ans;
        }

        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    /**
     * 判断是否是“02-十一月-2006”格式的日期类型
     */
    private static boolean checkDate(String str){
        String[] dataArr =str.split("-");
        try {
            if(dataArr.length == 3){
                int x = Integer.parseInt(dataArr[0]);
                String y =  dataArr[1];
                int z = Integer.parseInt(dataArr[2]);
                if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
                    return true;
                }
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }

    private static Workbook getWorkBook(InputStream inputStream, String fileName) throws IOException {
        if(fileName.endsWith(OFFICE_EXCEL_2003_POSTFIX)){
            return new HSSFWorkbook(inputStream);
        }else if(fileName.endsWith(OFFICE_EXCEL_2010_POSTFIX)){
            return new XSSFWorkbook(inputStream);
        }else{
            return null;
        }
    }

    public static void outExcel(OutputStream os, Map<String, String> title, List<Map<String, String>> data) throws IOException {
        SXSSFWorkbook workbook = new SXSSFWorkbook(new XSSFWorkbook());
        Sheet sheet = workbook.createSheet();
        if(ObjectUtil.notEmpty(title)){
            int i = 0;
            Row titleName = sheet.createRow(0);
            Row titleCode = sheet.createRow(1);
            for(Map.Entry<String, String> entry : title.entrySet()){
                Cell cellName = titleName.createCell(i);
                Cell cellCode = titleCode.createCell(i);
                cellName.setCellValue(entry.getValue());
                cellCode.setCellValue(entry.getKey());
                i++;
            }
        }
        if(ObjectUtil.notEmpty(data)){

        }
        workbook.write(os);
    }


}