package com.sincere.student.utils;

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.FileInputStream;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtils {


    public static InputStream GetFileInputStream(String fileUrl){
        try{
            URL url = new URL(fileUrl);
            HttpURLConnection conn = (HttpURLConnection)url.openConnection();
            //设置超时间为3秒
            conn.setConnectTimeout(8*1000);
            //得到输入流
            InputStream inputStream = conn.getInputStream();

            return inputStream ;
        }catch (Exception e){

        }
        return null ;
    }

    public static List<Point> analysisExcel(String url) throws ResultException{
        try{
            File excelFile = new File(url);
            // 获得工作簿
            String file = excelFile.getName();
            Workbook workbook = null;
            InputStream inputStream = GetFileInputStream(url);
            //InputStream inputStream = new FileInputStream(url);
            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()));
                    }
                    list.add(point);
                }
            }
            return list ;
        }catch (Exception e){
            e.printStackTrace();
            throw new ResultException(999,"系统错误");
        }
    }
}