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.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);
                        point.setEnrollNumber(Integer.valueOf(cell3.getStringCellValue().trim()));
                    }
                    Cell cell4 = sheetRow.getCell(3);
                    if(cell4 != null){
                        cell4.setCellType(CellType.STRING);
                        point.setGrade(Integer.valueOf(cell4.getStringCellValue().trim()));
                    }
                    Cell cell5 = sheetRow.getCell(4);
                    if(cell5 != null){
                        cell5.setCellType(CellType.STRING);
                        point.setRank(Integer.valueOf(cell5.getStringCellValue().trim()));
                    }
                    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){

            }

        }
    }
}