Java的XSSFWorkbook和XSSFSheet处理Excel(偷偷告诉同学们。。。)

XSSFWorkbook、XSSFSheet对xlsx和xls都支持

public class ExcelUtil {

private Map<String,Integer> map;

//工作簿
private XSSFWorkbook xssfWorkbook = null;

//工作表
private XSSFSheet xssfSheet = null;

public ExcelUtil(){

}

//获取工作簿和工作表
public ExcelUtil(InputStream inputStream, String sheetName){
    try {
        xssfWorkbook = new XSSFWorkbook(inputStream);
        xssfSheet = xssfWorkbook.getSheet(sheetName);
    } catch (IOException e) {
        e.printStackTrace();
    }

}

//test
public void readFirstRowFirstCell(){
    XSSFRow row = xssfSheet.getRow(0);
    String str = row.getCell(2).toString().substring(0,9);
    System.out.println(str);
}


//有不及格科目学生 返回不及格学生人数
public int findFailStudent(int majorIDColumn,int scoreColumn,int nameColumn,String majorID){
    //存储有不及格科目学生的名字和不及格门数
    map = new HashMap<>();
    float score;
    String rowMajorID;

    int rows = xssfSheet.getPhysicalNumberOfRows();
    for(int i=0;i<rows;i++){
        XSSFRow row = xssfSheet.getRow(i);

        //班级号
        String cell = row.getCell(majorIDColumn).toString();
        //获得专业号
        if(cell.length() == 10)
            rowMajorID = cell.substring(0,9);
        else continue;
        if(rowMajorID.equals(majorID)){
            //获得姓名
            String name = row.getCell(nameColumn).toString();
            //获得成绩
            String scoreStr = row.getCell(scoreColumn).toString() ;
            //匹配非负数
            if(scoreStr.matches("\\d+|(\\d+\\.\\d+)")) {
                score = Float.parseFloat(scoreStr);
                if(score < 60){
                    addFailStudent(map,name);
                }
            }
            else {
                if(scoreStr.equals("不及格"))
                    addFailStudent(map,name);
            }
        }else continue;
    }

    for(String key : map.keySet()){
        System.out.println("姓名："+key+" 不及格门数："+map.get(key));
    }
    System.out.println("两门及以上不及格人数："+moreThan2FailSubject(map));
    return map.size();
}

//添加不及格学生
public void addFailStudent(Map<String,Integer> map,String name){
    if(!map.containsKey(name)){
        map.put(name,1);
    }else {
        int subjects = map.get(name);
        map.put(name,subjects+1);
    }
}

//返回两门及以上不及格人数
public int moreThan2FailSubject(Map<String,Integer> map){
    int count = 0;
    for(String key : map.keySet()){
        if(map.get(key) >= 2){
            count++;
        }
    }
    return count;
}

//输出绩点满足但有不及格科目的同学名字(绩点满足的同学已经准备好)
public int pointButFailSubject(Set<String> set){
    System.out.println("======================\n绩点满足但有不及格科目的同学名字:");
    int count = 0;
    for(String name : map.keySet()){
        if(set.contains(name)){
            count++;
            System.out.println(name);
        }
    }
    return count;
}

public void sportScore(int course, int scoreColumn,int nameColumn, Set<String> names){
    System.out.println("====================");
    //存储同学们的体育成绩
    map = new HashMap<>();
    int score;
    int rows = xssfSheet.getPhysicalNumberOfRows();
    for(int i=0;i<rows;i++){
        XSSFRow row = xssfSheet.getRow(i);
        //体育成绩
        String sportSco = row.getCell(scoreColumn).toString();
        if(sportSco.matches("\\d+|(\\d+\\.\\d+)")){//85.0
            float tmp = Float.parseFloat(sportSco);
            score = (int) tmp;
        }
        else continue;
        //姓名
        String name = row.getCell(nameColumn).toString();
        //课程名
        String couName = row.getCell(course).toString();
        if(couName.length() > 2)
            couName = couName.substring(0,2);
        if(names.contains(name) && couName.equals("体育")){
            map.put(name,score);
        }
    }
    for(String key : map.keySet()){
        System.out.println("姓名："+key+" 体育成绩："+map.get(key));
    }
    names.removeAll(map.keySet());
    System.out.println("剩下的同学没有体育成绩：");
    for(String name : names){
        System.out.print(name+" ");
    }
}

}