package com.mzl.flower.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.LocaleUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

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

/**
 * excel读写工具类 */
public class POIUtil {
    private final static String xls = "xls";
    private final static String xlsx = "xlsx";

    public static SheetContent readExcel(MultipartFile file, int sheetNum) throws IOException{
        //检查文件
        checkFile(file);
        //获得Workbook工作薄对象
        Workbook workbook = getWorkBook(file);

        return readExcel(workbook, sheetNum);
    }

    public static SheetContent readExcel(File file, int sheetNum) throws IOException{
        Workbook workbook = getWorkBook(file);

        return readExcel(workbook, sheetNum);
    }

    public static SheetContent readExcel(InputStream is, String fileName, int sheetNum) throws IOException{
        Workbook workbook = getWorkBook(is, fileName);

        return readExcel(workbook, sheetNum);
    }

    public static SheetContent readExcel(Workbook workbook, int sheetNum) throws IOException{
        SheetContent sheetContent = null;

        //获得当前sheet工作表
        Sheet sheet = workbook.getSheetAt(sheetNum);
        if(sheet == null){
            return sheetContent;
        }
        String sheetName = sheet.getSheetName();
        List<String[]> list = new ArrayList<>();
        //获得当前sheet的开始行
        int firstRowNum  = sheet.getFirstRowNum();
        //获得当前sheet的结束行
        int lastRowNum = sheet.getLastRowNum();
        //循环除了第一行的所有行
        for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++){
            //获得当前行
            Row row = sheet.getRow(rowNum);
            if(row == null){
                continue;
            }
            //获得当前行的开始列
            int firstCellNum = row.getFirstCellNum();
            if(firstCellNum == -1){
                list.add(new String[0]);
                continue;
            }
            //获得当前行的列数
            int lastCellNum = row.getPhysicalNumberOfCells();
            String[] cells = new String[row.getPhysicalNumberOfCells()];
            //循环当前行
            for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                Cell cell = row.getCell(cellNum);
                cells[cellNum] = getCellValue(cell);
            }
            list.add(cells);
        }

        sheetContent = new SheetContent(sheetName, list);

        return sheetContent;
    }

    public static List<SheetContent> readExcel(Workbook workbook) throws IOException{
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<SheetContent> list = new ArrayList<>();
        if(workbook != null){
            for(int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++){
                list.add(readExcel(workbook, sheetNum));
            }
        }
        return list;
    }

    public static void checkFile(MultipartFile file) throws IOException{
        //判断文件是否存在
        if(null == file){
            throw new FileNotFoundException("文件不存在！");
        }
        //获得文件名
        String fileName = file.getOriginalFilename();
        //判断文件是否是excel文件
        if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
            throw new IOException(fileName + "不是excel文件");
        }
    }

    public static Workbook getWorkBook(MultipartFile file) {
        //获得文件名
        String fileName = file.getOriginalFilename();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            workbook = getWorkBook(file.getInputStream(), fileName);
        } catch (IOException e) {
        }
        return workbook;
    }

    public static Workbook getXSSFWorkbook(InputStream is) throws IOException {
        return new XSSFWorkbook(is);
    }

    public static Workbook getHSSFWorkbook(InputStream is) throws IOException {
        return new HSSFWorkbook(is);
    }

    public static Workbook getWorkBook(File file) {
        String fileName = file.getName();
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        try {
            workbook = getWorkBook(new FileInputStream(file), fileName);
        } catch (IOException e) {
        }

        return workbook;
    }

    public static Workbook getWorkBook(InputStream is, String fileName) {
        Workbook workbook = null;
        try {
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if(fileName.endsWith(xls)){
                //2003
                workbook = getHSSFWorkbook(is);
            }else if(fileName.endsWith(xlsx)){
                //2007
                workbook = getXSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    public static String getCellValue(Cell cell){
        String cellValue = "";
        if(cell == null){
            return cellValue;
        }
        int tt = cell.getCellStyle().getDataFormat();
        //判断数据的类型
        switch (cell.getCellType()){
            case NUMERIC: //数字
                short format = cell.getCellStyle().getDataFormat();
                SimpleDateFormat sdf = null;
                if (format == 57) { // 日期
                    sdf = new SimpleDateFormat("yyyy年MM月");
                }
                if(sdf != null){
                    try {
                        double value = cell.getNumericCellValue();
                        Date date = DateUtil.getJavaDate(value);
                        cellValue = sdf.format(date);
                    } catch (Exception e) {
                    }
                }else{
                    cell.setCellType(CellType.STRING);
                    cellValue = String.valueOf(cell.getStringCellValue());
                }
                break;
            case STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case FORMULA: //公式
                //cellValue = String.valueOf(cell.getCellFormula());
                try {
                    cellValue = String.valueOf(cell.getNumericCellValue());
                } catch (IllegalStateException e) {
                }
                if(cellValue.length() == 0){
                    try {
                        cellValue = String.valueOf(cell.getStringCellValue());
                    } catch (IllegalStateException e) {
                    }
                }
                break;
            case BLANK: //空值
                cellValue = "";
                break;
            case ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static class SheetContent{
        private String sheetName;
        private List<String[]> data;
        public SheetContent(String sheetName, List<String[]> data){
            this.sheetName = sheetName;
            this.data = data;
        }

        public String getSheetName(){
            return sheetName;
        }

        public List<String[]> getData(){
            return  data;
        }
    }
}