package com.sincere.student.utils;

import com.aliyun.oss.OSSClient;
import com.aliyun.oss.model.OSSObject;
import com.sincere.student.model.Point;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {

    public static OSSClient ossClient = null;

    private static final String key = "QiuM3PwHTnVotcGy";
    private static final String secret = "Yqs7RlaC1MioZu2YYJ6u0TdeO13VFC";
    private static final String endPoint = "http://oss-cn-hangzhou.aliyuncs.com";
    private static final String bucketName = "szyundisk";
    private static final String domain = "https://szyundisk.oss-cn-hangzhou.aliyuncs.com/";

    public static synchronized OSSClient getInstance() {
        if (ossClient == null) {
            synchronized (ExcelUtils.class) {
                if (ossClient == null) {
                    ossClient = new OSSClient(endPoint, key, secret);
                }
            }
        }
        return ossClient;
    }

    public static List<Point> analysisExcel(String url) throws ResultException {
        InputStream inputStream = null;
        OSSClient ossClient = getInstance();
        OSSObject object = ossClient.getObject(bucketName, url.replace(domain, ""));
        try {
            File excelFile = new File(url);
            // 获得工作簿
            String file = excelFile.getName();
            Workbook workbook = null;
            inputStream = object.getObjectContent();
            if (inputStream == null) {
                throw new ResultException(901, "路径错误");
            }
            if (file.endsWith("xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else {
                workbook = new XSSFWorkbook(inputStream);
            }
            // 获得工作表
            Sheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            List<Point> list = new ArrayList<>();
            for (int i = 1; i <= rows; i++) {
                // 获取第i行数据
                Row sheetRow = sheet.getRow(i);
                if (sheetRow != null) {
                    Point point = new Point();
                    Cell cell1 = sheetRow.getCell(0);
                    if (cell1 != null) {
                        cell1.setCellType(CellType.STRING);
                        point.setUniversityName(cell1.getStringCellValue().trim());
                    }
                    Cell cell2 = sheetRow.getCell(1);
                    if (cell2 != null) {
                        cell2.setCellType(CellType.STRING);
                        point.setMajor(cell2.getStringCellValue().trim());
                    }
                    Cell cell3 = sheetRow.getCell(2);
                    if (cell3 != null) {
                        cell3.setCellType(CellType.STRING);
                        if(StringUtils.isNotBlank(cell3.getStringCellValue())){
                            point.setEnrollNumber(Integer.valueOf(cell3.getStringCellValue().trim()));
                        }else{
                            point.setEnrollNumber(null);
                        }
                    }
                    Cell cell4 = sheetRow.getCell(3);
                    if (cell4 != null) {
                        cell4.setCellType(CellType.STRING);
                        if(StringUtils.isNotBlank(cell4.getStringCellValue())){
                            point.setGrade(Integer.valueOf(cell4.getStringCellValue().trim()));
                        }else{
                            point.setGrade(null);
                        }
                    }
                    Cell cell5 = sheetRow.getCell(4);
                    if (cell5 != null) {
                        cell5.setCellType(CellType.STRING);
                        if(StringUtils.isNotBlank(cell5.getStringCellValue())){
                            point.setRank(Integer.valueOf(cell5.getStringCellValue().trim()));
                        }else{
                            point.setRank(null);
                        }
                    }
                    Cell cell6 = sheetRow.getCell(5);
                    if (cell6 != null) {
                        cell6.setCellType(CellType.STRING);
                        point.setProvince(cell6.getStringCellValue().trim());
                    }
                    Cell cell7 = sheetRow.getCell(6);
                    if (cell7 != null) {
                        cell7.setCellType(CellType.STRING);
                        point.setCity(cell7.getStringCellValue());
                    }
                    list.add(point);
                }
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            throw new ResultException(999, "系统错误");
        } finally {
            try {
                inputStream.close();
                object.close();
            } catch (Exception e) {

            }

        }
    }
}